This problem is based on Elmasri\'s company database. For each of the following,

Question

This problem is based on Elmasri's company database. For each of the following, specify the query using SQL this is a paper and pencil problem, you do not need to implement these queries, but if you want to implement them, you are free to do so Feel free to create views or to save queries (to help answer a more complex query) or to create other tables (eg: using the INTO command SQL to create the views and subqueries. ) if that helps you. If you are using views or subqueries, show the (a) What are the names of the projects on which the combined hours (i.e. the total of all the hours of all the employees who work on that project) are more than 50. (b) Specify the following view in SQL: A view that has the department name and the number of locations for that department for each department. Please note that here you have to use the "CREATE VIEW" statement (c) List the names of the projects such that every employee works on that project. (d) How many employees from the Administration department have at least three dependents. (e) What is the name of the department whose employees have the lowest average salary.

Explanation / Answer

Answer a:

select pr.Pname as Project_Name from PROJECT pr

inner join (select Pno from WORKS_ON group by Pno having sum(Hours)>50) wrk on pr.Pnumber = wrk.Pno;

-------------------------------------------------------------------------------------------------------------------------------

Answer b:

create view Department_Location_Count as

select dprt.Dname as Department_Name, dtLoc.Locations as All_Locations from DEPARTMENT dprt

inner join (select Dnumber,count(*) as Locations from DEPT_LOCATIONS group by Dnumber) dtLoc

on dprt.Dnumber = dtLoc.Dnumber;

---------------------------------------------------------------------------------------------------------------------

Answer c:

select pr.Pname as Project_Name from PROJECT pr

inner join (select Pno from WORKS_ON group by Pno having count(*) in (select count(*) from EMPLOYEE)) wrk

on pr.Pnumber = wrk.Pno;

---------------------------------------------------------------------------------------------

Answer d:

select count(*) as Employee_Count from (select Essn from DEPENDENT group by Essn having count(*)>=3) depd

inner join ( select Ssn from EMPLOYEE where Dno in (select Dnumber from DEPARTMENT where Dname = 'Administration' ) ) emp

on depd.Essn = emp.Ssn;

-------------------------------------------------------------------------------------------------------

Answer e:

create view Dept_Avg_Salary as
select Dno,avg(Salary) as avgSalary from EMPLOYEE group by Dno;

select dprt.Dname as Department_Name from DEPARTMENT dprt
inner join Dept_Avg_Salary das on dprt.Dnumber = das.Dno
where das.avgSalary in (select min(avgSalary) as Min_Avg_Salary from Dept_Avg_Salary);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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