IST331
Lab Assignement 3
Due: March 23, 2015
SS Chung Spring 2015
Querying a Relational Database COMPANY database
For Lab3, you use the Company database you created from Lab2
1. Add yourself into the database: Add yourself and the related info into Employee,
Dependent, and Works_On tables. Then Retrieve all the entries you just added with
only 1 Select statement.
Adding myself to the database:
INSERT INTO EMPLOYEE VALUES(
'Daniel',
'J',
'Miller',
'111223333',
'15-Nov-1990',
'8615 Hendricks, Mentor, OH',
'M',
35000,
'987654321',
5);
INSERT INTO WORKS_ON VALUES(
'111223333',
20,
15);
INSERT INTO WORKS_ON VALUES(
'111223333',
2,
10);
INSERT INTO DEPENDENT VALUES (
'111223333',
'Kayla',
'F',
'19-Jan-1991',
'Spouse');
Retrieve the entries with only 1 select statement:
SELECT *
FROM EMPLOYEE E, WORKS_ON W, DEPENDENT D
WHERE E.SSN = '111223333' AND D.Essn = E.SSN AND W.Essn = E.SSN;
2. Write SQL Select statements to retrieve data in the followings:
Q1: Retrieve the name and address of all the female managers
Q2: Retrieve the name and address of all employees who work in the 'Research'
department.
Q3: Retrieve the name and address and his/her department name of the highest ranked
employee who does not report to anybody in the company.
SELECT Fname, Minit, Lname, Address, Dname
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.Super_SSN IS NULL
AND E.Dno = D.Dnumber;
Q4: For each Employee, retrieve the employee’s first and last name and first and last
name of his or her immediate supervisor
SELECT E1.Fname, E1.Lname, E2.Fname, E2.Lname
FROM EMPLOYEE E1 LEFT OUTER JOIN EMPLOYEE E2
ON E1.Super_SSN = E2.SSN;
Q5: Make a list of all project numbers for projects that involve an employee whose last
name is ‘Smith’, either as a worker or as a manager of the department that controls the
project.
SELECT P.Pnumber
FROM PROJECT P
WHERE P.Pnumber IN (
SELECT P.Pnumber
FROM PROJECT P,
DEPARTMENT D, EMPLOYEE E
WHERE D.Mgr_SSN = E.SSN AND
P.Dnum = D.Dnumber AND
E.Lname = 'Smith')
OR
P.Pnumber IN (
SELECT W.Pno
FROM WORKS_ON W,
EMPLOYEE E
WHERE W.ESSN = E.SSN AND
E.Lname = 'Smith');
Or Same Query with UNION:
(SELECT Distinct Pnumber
FROM PROJECT P,
DEPARTMENT D, EMPLOYEE E
WHERE P.Dnum = D.Dnumber AND
D.Mgr_SSN = E.SSN AND
E.Lname = 'Smith')
UNION
(SELECT Distinct Pnumber
FROM WORKS_ON W,
PROJECT P, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND
W.Essn = E.Ssn AND
E.Lname = 'Smith');
Q6: For every project located in 'Stafford', list the project number, the controlling
department number, and the department manager's last name, address, and birthdate.
OR
SELECT P.Pnumber, P.Dnum, E1.Lname, E1.Address, E1.Bdate
FROM PROJECT P, EMPLOYEE E
WHERE P.Plocation = ‘stafford”
AND E1.SSN IN (SELECT E2.SSN
FROM DEPARTMENT D, EMPLOYEE E2
WHERE P.Dnum = D.Dnumber
AND E2.SSN = D. Mgr_SSN);