SQL 1) For each employee having a dependent, retrieve the employee’s name along
ID: 3817179 • Letter: S
Question
SQL
1) For each employee having a dependent, retrieve the employee’s name along with his/her dependent name(s).
2) For the department that controls more projects than any other department, retrieve its department number.
3) For the department with more locations than any other department, retrieve its name and department number.
4) Find the name of employee who supervises more employees than any other supervisor.
5) Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee. Use join operations instead of a nested query.
EMPLOYEE Fname it Lname ssn Bdate Sex Salary Super ssn Dno Address B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 John Franklin T LWong 333445555 1955-12-08 638 Vass, Houston, TX LM 40000 888665555 5 Alicia J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak. Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972 07.31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 98765432 1 4 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT LOCATIONS Dnumber Mgr ssn Mgr. start date Dnumber Dlocation Dname 5 1 Houston Research 333445555 1988-05-22 Administration 4 4 Stafford 987654321 1995-01-01 Headquarters 888665555 1981-06-19 5 Sugarland 5 HoustonExplanation / Answer
1) For each employee having a dependent, retrieve the employee’s name along with his/her dependent name(s).
Select Emp.FName AS EmpFirstName, Depd.Dependent_name AS EmpDependentName
FROM Employee Emp INNER JOIN Dependent Depd On Emp.Ssn = Depd.Essn
Group By Emp.FName
Order By Emp.FName
2) For the department that controls more projects than any other department, retrieve its department number.
Select top 1 Dept.Dname AS DeptName, Count(Proj.Pnumber) As ProjectCount
From DepartMent Dept INNER JOIN Project Proj ON Dept.Dnumber = Proj.Dnum
group By Dept.Dname
Order By Count(Proj.Pnumber) DESC
3) For the department with more locations than any other department, retrieve its name and department number.
Select top 1 Dept.Dname AS DeptName, Count(Loc.Dlocation) As DeptLocation
From DepartMent Dept INNER JOIN Dept_Location loc ON Dept.Dnumber = loc.Dnumber
group By Dept.Dname
Order By Count(Loc.Dlocation) DESC
4) Find the name of employee who supervises more employees than any other supervisor.
Select E2.Fname As EmpName
From Employee E2
Where E2.Ssn = E1.Ssn AND EXIST(Select top 1 E1.Super_ssn As SuperierIds, COUNT(E1.Super_ssn) As NumEmployeeSupervise
From Employee E1
group by E1.Super_ssn)
5) Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee. Use join operations
instead of a nested query.
Select Emp.Fname as EmployeeName
From Employee Emp INNER JOIN Dependent depd on Emp.Ssn = depd.Essn
Where Emp.Fname = depd.Dependent_name AND Emp.Sex = depd.Sex
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.