Please answer all ten questions to recieve full credit. Thank you! (The descript
ID: 3554240 • Letter: P
Question
Please answer all ten questions to recieve full credit. Thank you!
(The descriptions of the schema is from the textbook ) The following relations form part of a database held in a relational DBMS: Where Employee contains employee details and empNo is the primary key; Department contains department details and deptNo is the primary key; mgrEmpNo identifies the employee who is the manager of the department. There is only one manager for each department. Project contains details of the projects in each department and the primary key is projNo (no two departments can run the same project); WorksOn contains details of the hours worked by employees on each project, and (empNo, projNo, dateWorked) form the primary key. Write SQL statements for the following queries: (for each)Explanation / Answer
-- 1
SELECT *
FROM employee
ORDER BY lName, fName;
-- 2
SELECT *
FROM employee
WHERe sex = 'F';
-- 3
SELECT e.lName, e.fName, e.address
FROM employee e, department d
WHERE e.deptNo = d.deptNo
AND e.empNo = d.mgrEmpNo;
-- 4
SELECT e.lName, e.fName, e.address
FROM employee e, department d
WHERE e.deptNo = d.deptNo
AND d.deptName = 'IT';
-- 5
SELECT e.lName, e.fName
FROM employee e, project p, WorksOn w
WHERE e.empNo = w.empNo
AND p.projNo = w.projNo
AND p.projName = 'SCCS';
-- 6
SELECT count(*) num_of_employee
FROM employee mgr, department d, employee emp
WHERE mgr.lName = 'Adams'
AND mgr.fName = 'James'
AND mgr.empNo = d.mgrEmpNo
AND emp.deptNo = emp.deptNo;
-- 7
SELECT e.lName, e.fName, e.deptNo, SUM(w.hoursWorked) hoursWorked
FROM employee e, project p, WorksOn w
WHERE e.empNo = w.empNo
AND p.projNo = w.projNo
GROUP BY e.lName, e.fName
ORDER BY e.deptNo, e.lName
-- 8
SELECT p.projNo, p.projName, count(e.empno) num_of_employee
FROM employee e, project p, WorksOn w
WHERE e.empNo = w.empNo
AND p.projNo = w.projNo
GROUP BY p.projNo, p.projName
HAVING count(e.empno) > 2
;
-- 9
SELECT d.deptName, count(e.empNo) num_of_employee
FROM employee e, department d
WHERE e.deptNo = d.deptNo
GROUP BY d.deptName
HAVING count(e.empNo) > 10;
-- 10
create or replace view workHours
(empno, lname, fname, hoursworked)
as
SELECT e.empno, e.lName, e.fName, w.hoursWorked
FROM employee e, project p, WorksOn w
WHERE e.empNo = w.empNo
AND p.projNo = w.projNo;