INF503 Week 3 (7.8 lab)

 

7. LAB

7.1.

SELECT DISTINCT Pnumber, Pname
FROM PROJECT
WHERE  Pnumber IN
(SELECT Pnumber
FROM PROJECT, EMPLOYEE_DEPARTMENT, EMPLOYEE_REC
WHERE Dnum = Dnumber AND
Mgr_ssn = Super_ssn AND Lname = 'Zelaya')
OR
Pnumber IN
(SELECT Pno
FROM WORKS_ON, EMPLOYEE_REC
WHERE Essn = Ssn AND Lname = 'Smith');

 

7.2

SELECT Lname, Fname

FROM EMPLOYEE_REC

WHERE Salary > (SELECT MAX (Salary)

FROM EMPLOYEE_REC

WHERE Dno = 5);

 

7.3

SELECT E.Lname AS Employee_name,

S.Lname AS Supervisor_name

FROM (EMPLOYEE_REC AS E LEFT OUTER JOIN EMPLOYEE_REC AS S

ON E.Super_ssn = S.Ssn);

 

7.4

SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)

FROM (EMPLOYEE_REC JOIN EMPLOYEE_DEPARTMENT ON Dno = Dnumber)

WHERE Dname = 'Research';

 

7.5

SELECT COUNT (*)

FROM EMPLOYEE_REC;

 

7.6

SELECT Pnumber, Pname, COUNT (*)

FROM PROJECT, WORKS_ON

WHERE Pnumber = Pno

GROUP BY Pnumber, Pname

HAVING COUNT (*) > 1;

 

7.7

UPDATE EMPLOYEE_REC

SET Salary =

CASE WHEN Dno = 5 THEN Salary + 2000

WHEN Dno = 4 THEN Salary + 1500

WHEN Dno = 1 THEN Salary + 3000

ELSE Salary + 0;

 

7.8

CREATE ASSERTION SALARY_CONSTRAINT

CHECK (EXISTS (SELECT *

FROM EMPLOYEE_REC E,

EMPLOYEE_DEPARTMENT D

WHERE E.Salary > 25000

AND E.Dno = D.Dnumber

AND D.Mgr_ssn = E.Ssn));

 

7.9

CREATE VIEW WORKS_ON1

AS SELECT Fname, Lname, Pname, Hours

FROM EMPLOYEE_REC, PROJECT, WORKS_ON

WHERE Ssn = Essn AND Pno = Pnumber;

 

7.10

ALTER TABLE COMPANY.EMPLOYEE_REC ADD COLUMN Job VARCHAR(12);

8.LAB

8.1

SELECT DISTINCT Sex, Salary
FROM EMPLOYEE_REC;

 

8.2

SELECT City FROM EMP_CUSTOMERS

UNION

SELECT City FROM EMP_SUPPLIERS

ORDER BY City;

 

8.3

SELECT *

FROM EMPLOYEE_REC

WHERE Dno = 4 AND Salary > 25000;

8.4