Employee EmployeeldFirstName LastName GenderManagerId EmailAddress NULLPetrina.T
ID: 3882082 • Letter: E
Question
Employee EmployeeldFirstName LastName GenderManagerId EmailAddress NULLPetrina.TillmanQcompany.com Tillman Wilhoit Petrina 106 112 Alec.WilhoitQcompany.com Lourie.JohnsQcompany.com Florencio.ChavesQcompany.com Tanya.VanasseQcompany.com Chana.ThormanQcompany.com Geoffrey.SjorgrenQcompany.com Gorardo.Borrego@company.com Blain.WishonQcompany.com Danita.Lansford@companv.comm ec Lourie FlorencioChaves Johns 119 122 112 112 Vanasse Chana Geoffre Gorardo Borrego Thorman 139 140 Wishon 150 Danita ansfon 122 Class ClassId Name InstructorId TotalHours 1001 Customer Service 101 1002Synergy For Dummies 1003 Conflict Management 1004 Building Relationships 1005Management Essentials 1006 Office Synergy 1007 How to Succeed 119 112 112 Transcript TranscriptId EmployeeldClassId Status 106 106 106 112 112 127 127 127 139 139 142 142 150 1005COMPLETED 1002 REGISTERED 1003 COMPLETED 1003 REGISTERED 1001 COMPLETED 1001 COMPLETED 1002COMPLETED 1001 REGISTERED 1002 REGISTEREI 1003 COMPLETED 1004 REGISTERED 1006 REGISTERED 1003 COMPLETED 1005 REGISTERED 1005 COMPLETEDExplanation / Answer
Query 1:
Select c.name, COUNT(c.ClassId) as ccount from
class c left join Transcript t on c.ClassId=t.ClassId group by c.ClassId,c.name order by ccount desc
Query 2:
SELECT FirstName, SUM(SUBQUERY.completed) as completedhours,SUM(SUBQUERY.registered) as registeredhours from
(select Distinct e.EmployeeID, e.FirstName,
case when t.[status]='c' then c.TotalHour else null end as completed,
case when t.[status]='r' then c.TotalHour else null end as registered
from employee e left join Transcript t on e.EmployeeID=t.EmployeeId
left join class c on t.ClassId=c.ClassId group by e.EmployeeID,c.TotalHour ,e.FirstName,t.[Status]) as SUBQUERY group by Employeeid,FirstName
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.