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;
Other Links:
Statistics Quiz
Networking Quiz
See other websites for quiz:
Check on QUIZLET
Check on CHEGG