Location Employee Empld Fname Lname Gender Mgrld Locld EmailAddress Locld LocNam

Question

Location Employee Empld Fname Lname Gender Mgrld Locld EmailAddress Locld LocName Cit State 1CoffeeTreeSeattleWA EvergreenTacoma WA Angelview Los AngelesCA 4PleasentvilleSan DiegoCA DenverCO 106 PetrinaTillmanF NULL 2 106@company.com 112Alec WilhoitM 1061 112@company.com 117LourieJohns 119.Florencio . Chaves M | 112. 5·119@company.com 122 , Tanya Vanasse. F | 112 5·122@company.com 127 | Chana ·Thorman | F | 112. 5·127@company.com 139 Geoffrey Sjorgren M 1173 139@company.com 140 Gerardo BorregoM 1173 140@company.com 142 | Blain ·Wishon. M | 117. 2·142@company.com 150Danita LansfordF 1222 150@company.com 2 F 1063 117@company.com Mile High , , TrainingStatus Classld Status 106 106 106 112 112 1005 COMPLETED 1002 REGISTERED 1003 COMPLETED 1003 REGISTERED 1001 COMPLETED 1001 COMPLETED 1002 COMPLETED 1001 REGISTERED 1002 REGISTERED 1003 COMPLETED 1004 REGISTERED 1006 REGISTERED 1003 COMPLETED 1005 REGISTERED 1007 COMPLETED Class Classld Cname Instructorld Locld TotHours 119 119 112 5 1001 Customer Service 101 1002 1003Conflict Management 1004Building Relationships 1005Management Essentials112 1006 1007 Finance Systems 20 10 25 40 127 127 127 139 139 142 142 150 3 Office Relaxation How to Succeed 3 3 112 10

Explanation / Answer

QES 1 SOL:

SELECT CONCAT(Fname,' ',Lname) AS Name FROM Employee WHERE Mgrid IS NULL;

QES 2 SQL:

SELECT CONCAT(e.Fname,' ',e.Lname) AS Name FROM Employee e INNER JOIN TrainingStatus t on e.Empid=t.Empid WHERE t.Status='Completed';

QES 3 SQL:

execte below 3 queries all at once

SELECT e.empid, e.Fname As FirstName,e.Lname AS LastName,l.State As EmployeeState into #Temp1
FROM Employee e INNER JOIN TrainingStatus t
on e.Empid=t.Empid
INNER JOIN Location l
ON e.LocId=l.LocId
WHERE t.Status='REGISTERED';

SELECT e.empid,c.Cname AS CourseName,l.State as CourseState into #Temp2
FROM Employee e INNER JOIN Location l on e.locID=l.locId
Inner join Class c
on l.locId=c.LocId
where e.LocId<>c.LocId;

SELECT T1.FirstName,T1.LastName,T1.EmployeeState,T2.CourseName,T2.CourseState FROM #Temp1 T1 INNER JOIN #Temp2 ON T.empid=T2.empid;

QES 4 SQL:

SELECT e.Fname ,e.Lname,e.EmailAddress FROM Employee e INNER JOIN TrainingStatus t
on e.Empid=t.Empid
inner join
where t.status not in ('COMLETED','RESGISTERED')
OR t.ClassID='1005'

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.