IST 331 SQL Practice Questions & Answers

V. Matos.

Consider the relation schema of the COMPANY database given below

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno) KEY: ssn

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) KEY: dnumber.

PROJECT (pname, pnumber, plocation, dnum) KEY: pnumber.

WORKS_ON (essn, pno, hours) KEY: (essn, pno)

DEPENDENT (essn, dependent-name, sex, bdate, relationship) KEY: (essn, dependent-name)

1. Formulate the following question in SQL:

  • Give the last name and SSN of those managers who work on two or more projects located in Cleveland.

select Lname
from Employee e, Department d
where (e.ssn = d.mgrssn)
and ssn in (
select w.essn
from works_on w, Project p
where w.pno = p.pnumber
and p.plocation = 'Cleveland'
group by w.essn
having count(*) >= 2
)
  • Give the last name and SSN of those managers who DO NOT work on any one of the projects located in Cleveland.

select Lname
from Employee e, Department d
where (e.ssn = d.mgrssn)
and ssn NOT in (
select w.essn
from works_on w, Project p
where w.pno = p.pnumber
and p.plocation = 'Cleveland'
)
  • Give the last name and SSN of the unmarried employees who work on two or more projects.

SELECT e.Lname, e.ssn
FROM Employee AS e
WHERE e.ssn in (
select w.essn
from works_on w
group by w.essn
having count(*) >= 2
)
AND e.ssn NOT in (
select essn
from dependent
where relationship = 'Spouse'
);
  • Give the last name of those employees who work in any project(s) where there are more female than male employees.

SELECT w.pno, count(*) as TotalGent
INTO Guys
FROM Employee e, Works_On w
WHERE e.ssn = w.essn and e.sex = 'M'
GROUP BY w.pno
SELECT w.pno, count(*) as TotalGals
INTO Ladies
FROM Employee e, Works_On w
WHERE e.ssn = w.essn and e.sex = 'F'
GROUP BY w.pno
SELECT g.pno
INTO LadiesProject
FROM Guys g, Ladies L
WHERE g.Pno = L.Pno
AND g.TotalGent < L.TotalGals
SELECT e.Lname
FROM Employee e, Works_On w, LadiesProject L
WHERE e.ssn = w.essn
AND w.pno = L.pno
  • List all the pairs of employees who (a) are managers, (b) have the same age, and (c) have the same number of children. For instance, employees 123 and 456are department managers, both are 29 years old, and each is raising three children. Therefore the tuple <123,456> (but not the <456,123>) must be included in the output.

SELECT mgrssn
INTO Manager
FROM Department
SELECT d.mgrssn , (format(now, 'YYYY') - format (e.Bdate, 'YYYY') ) as Age
INTO BossAge
FROM Department d, Employee e
WHERE d.mgrssn = e.ssn
SELECT d.mgrssn, count(*) as Kids
INTO BossKids
FROM Department d, Employee e, Dependent f
WHERE d.mgrssn = e.ssn
AND e.ssn = f.essn
AND f.relationship in ('Son', 'Daughter')
GROUP BY d.mgrssn
SELECT e1.ssn, e2.ssn
INTO Answer
FROM Employee e1, Employee e2
WHERE e1.ssn > e2.ssn
AND e1.ssn in (select * from Manager)
AND e2.ssn in (select * from Manager)
AND (select ba.Age from BossAge ba where ba.ssn = e1.ssn) = (select ba.Age from BossAge ba where ba.ssn = e2.ssn)
AND (select bk.Kids from BossKids bk where bk.ssn = e1.ssn) = (select bk.Kids from BossKids bk where bk.ssn = e1.ssn)
  • Give the SSN of employees who have at least one son and work on four (or more projects). Pick from those employees the ones whose salary is less than the company’s average salary.

2. Formulate the following maintenance operation in SQL:

  • Make employee 111222333 be the new supervisor of each of the managers who DO NOT work on any one of the projects located in Cleveland.

UPDATE Employee
SET SuperSSN = 111222333
WHERE ssn in (select mgrssn from department)
AND ssn NOT in (select w.essn from Works_On w, Project p where w.pno = p.pnumber and p.plocation = 'Cleveland')
  • Assign to department 7 each of the employees who currently satisfy either one of the following conditions
  • their salary is less than the average salary of all the female employees
  • they do not work for any project controlled by department 5

1