LAB-8
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’);
2.
SELECT Lname, Fname
FROM EMPLOYEE_REC
WHERE Salary > (SELECT MAX (Salary)
FROM EMPLOYEE_REC
WHERE Dno = 5);
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);
4.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM (EMPLOYEE_REC JOIN EMPLOYEE_DEPARTMENT ON Dno = Dnumber)
WHERE Dname = 'Research';
5.
SELECT COUNT (*)
FROM EMPLOYEE_REC;
6.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 1;
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;
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));
9.
CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE_REC, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = Pnumber;
10.
ALTER TABLE COMPANY.EMPLOYEE_REC ADD COLUMN Job VARCHAR(12);
LAB-9
1.
SELECT DISTINCT Sex, Salary
FROM EMPLOYEE_REC;
2.
SELECT City FROM EMP_CUSTOMERS
UNION
SELECT City FROM EMP_SUPPLIERS
ORDER BY City;
3.
SELECT *
FROM EMPLOYEE_REC
WHERE Dno = 4 AND Salary > 25000;
4.
Other Links:
See other websites for quiz:
Check on QUIZLET