1.
CREATE TABLE EMPLOYEE_UC
(Ssn VARCHAR(5) NOT NULL PRIMARY KEY,
Fname VARCHAR(15) NOT NULL,
Minit VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Gender CHAR,
Salary Decimal(10,2),
Age INT
);
2.
CREATE TABLE DEPARTMENT_UC
(Dptname VARCHAR(15),
Dptnmbr INT NOT NULL PRIMARY KEY,
Mgr_ssn CHAR(9),
Mgr_start_date DATE,
Mgr_last_date DATE,
Ssn VARCHAR(5) FOREIGN KEY REFERENCES EMPLOYEE_UC(Ssn) ON UPDATE CASCADE
);
3.
ALTER TABLE EMPLOYEE_UC
ADD CHECK (Age>=18);
4.
SELECT Bdate, Address
FROM EMPLOYEE_REC
WHERE Fname = 'John' AND Lname = 'Smith';
5.
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, EMPLOYEE_DEPARTMENT, EMPLOYEE_REC
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Plocation = 'Stafford';
6.
SELECT E.Fname, E.LName, E.Address
FROM EMPLOYEE_REC AS E, EMPLOYEE_DEPARTMENT AS D
WHERE D.DName = 'Research' AND D.Dnumber = E.Dno;
7.
SELECT *
FROM EMPLOYEE_REC
WHERE Dno = 5;
8.
SELECT *
FROM EMPLOYEE_REC, EMPLOYEE_DEPARTMENT
WHERE Dname = 'Research' AND Dno = Dnumber;
9.
SELECT *
FROM EMPLOYEE_REC, EMPLOYEE_DEPARTMENT;
10.
SELECT DISTINCT Pnumber, Pname
FROM PROJECT, EMPLOYEE_DEPARTMENT, EMPLOYEE_REC WHERE Dnum = Dnumber AND Mgr_ssn = Ssn
AND Lname = 'Wong'
UNION
SELECT DISTINCT Pnumber, Pname
FROM PROJECT, WORKS_ON, EMPLOYEE_REC
WHERE Pnumber = Pno AND Essn = Ssn
AND Lname = 'Zelaya';
11.
SELECT Fname, Lname
FROM EMPLOYEE_REC
WHERE Address LIKE '%Houston%';
12.
SELECT *
FROM EMPLOYEE_REC
WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;
13.
SELECT D.Dname, E.Lname, E.Fname, P.Pname
FROM EMPLOYEE_DEPARTMENT AS D, EMPLOYEE_REC AS E, WORKS_ON AS W, PROJECT AS P
WHERE D.Dnumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P.Pnumber
ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC;
14.
INSERT INTO EMPLOYEE_REC (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', '98 Oak Forest, Katy, TX', 'M', 37000, '653298653', 4);
15.
INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE_REC E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;
16.
DELETE * FROM EMPLOYEE_REC
WHERE Lname = 'Wallace';
17.
UPDATE PROJECT
SET Plocation = 'Bellaire', Dnum = 5
WHERE Pnumber = 10;
Other Links:
Statistics Quiz
Networking Quiz
See other websites for quiz:
Check on QUIZLET
Check on CHEGG