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
Comments
Post a Comment