Practical Session 3 Answers

Exercise 1

--a.
SELECT fname,lname
FROM employee AS e1
WHERE NOT EXISTS (SELECT *
                   FROM employee AS e2, works_on, project
                 WHERE e2.ssn = e1.ssn AND
                     works_on.essn = e2.ssn AND
                      works_on.pno = project.pnumber AND
                     pname = 'ProductZ');

--b.
  SELECT DISTINCT fname,lname
  FROM employee AS e1, works_on AS wo1
  WHERE e1.ssn = wo1.essn AND
      wo1.pno IN (SELECT pno
                     FROM works_on AS wo2, employee AS e2
                   WHERE e2.ssn = wo2.essn AND
                       e2.fname = 'John' AND
                       e2.lname = 'Smith'
                        );
--c.
SELECT DISTINCT pname
FROM employee AS e1, employee AS sup, works_on, project
WHERE sup.ssn = e1.superssn AND
    sup.fname = 'John' AND
    sup.lname = 'James' AND
    works_on.essn = e1.ssn AND
    project.pnumber = works_on.pno;

--d.
SELECT fname, lname
FROM employee AS e1, works_on AS wo1, project AS p1
WHERE wo1.essn = e1.ssn AND
    wo1.pno = p1.pnumber AND
    p1.pname = 'ProductX' AND
    NOT EXISTS(SELECT *
               FROM employee as e2, works_on AS wo2, project AS p2
               WHERE e1.ssn = e2.ssn AND
                   e2.ssn = wo2.essn AND
                   wo2.pno = p2.pnumber AND
                   NOT pname = 'ProductX');

-- For a Positive example, test this one on 'ProductZ'

Exercise 2

--a.
SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))

--b.
SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )

Exercise 3

--a.

--The query may still be specified in SQL by using a nested query as follows (not all
--implementations may support this type of query):
SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNO
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG (SALARY) > 30000 )
GROUP BY DNAME

--Result:
--DNAME DNUMBER COUNT(*)
--Research 5 3
--Administration 4 1
--Headquarters 1 1

--b.

CREATE VIEW ex_2d AS
SELECT COUNT(employee.ssn), sum(works_on.hours), department.dname
FROM project AS p1, department, employee, works_on
WHERE works_on.essn = employee.ssn AND
    works_on.pno = p1.pnumber AND
    p1.dnum = department.dnumber AND
     (SELECT count(wo2.pno) 
      FROM works_on as wo2 
      WHERE wo2.pno = p1.pnumber) > 1
GROUP BY department.dname;