Consider the following database for the company.
EMPLOYEE ( Ssn : Int, Fname : String, Lname : String, Address : String, Gender : Char, Salary : Int, Super_SSN: Int, Dno: Int)
DEPARTMENT (Dnumber: Int, Dname: String, Mgr_SSN: Int, Mgr_start_date: Date)
PROJECT (Pnumber: Int, Pname: String, Plocation: String, Dno: Int)
DEPENDENT (Essn: Int, Depedent_name: String, Gender: Char, BDate: Date, Relationship: String)
WORKS_ON (Essn: Int, Pnumber: Int, Hours: Real)
1. Create above tables with properly specifying primary keys and foreign keys.
SQL> CREATE TABLE EMPLOYEE(
2 SSN INT,
3 FNAME VARCHAR(10),
4 LNAME VARCHAR(10),
5 ADDRESS VARCHAR(20),
6 GENDER CHAR,
7 SALARY FLOAT,
8 PRIMARY KEY(SSN));
Table created.
SQL> CREATE TABLE DEPARTMENT(
2 DNUMBER INT,
3 DNAME VARCHAR(20),
4 MGR_SSN INT,
5 MGR_START_DATE DATE,
6 PRIMARY KEY(DNUMBER),
7 FOREIGN KEY(MGR_SSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL);
Table created.
SQL> ALTER TABLE EMPLOYEE
2 ADD SUPER_SSN INT;
Table altered.
SQL> ALTER TABLE EMPLOYEE
2 ADD CONSTRAINT EFK1
3 FOREIGN KEY(SUPER_SSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL;
Table altered.
SQL> ALTER TABLE EMPLOYEE
2 ADD DNO INT;
Table altered.
SQL> ALTER TABLE EMPLOYEE
2 ADD CONSTRAINT EFK2
3 FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET NULL;
Table altered.
SQL> CREATE TABLE PROJECT(
2 PNUMBER INT,
3 PNAME VARCHAR(20),
4 PLOCATION VARCHAR(10),
5 DNO INT,
6 PRIMARY KEY(PNUMBER),
7 FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET NULL);
Table created.
SQL> CREATE TABLE DEPENDENT(
2 ESSN INT,
3 DEPENDENT_NAME VARCHAR(10),
4 GENDER CHAR,
5 BDATE DATE,
6 RELATIONSHIP VARCHAR(10),
7 PRIMARY KEY(ESSN,DEPENDENT_NAME),
8 FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL);
Table created.
SQL> CREATE TABLE WORKS_ON(
2 ESSN INT,
3 PNUMBER INT,
4 HOUR REAL,
5 PRIMARY KEY(ESSN,PNUMBER),
6 FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL,
7 FOREIGN KEY(PNUMBER) REFERENCES PROJECT(PNUMBER) ON DELETE SET NULL);
Table created.
2. Enter at least 7-8 tuples for each relation.
SQL> INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,NULL,NULL);
Enter value for ssn: 333445555
Enter value for fname: FRANKLIN
Enter value for lname: WONG
Enter value for address: HOUSTON
Enter value for gender: M
Enter value for salary: 40000
old 1: INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,NULL,NULL)
new 1: INSERT INTO EMPLOYEE VALUES(333445555,'FRANKLIN ','WONG','HOUSTON
1 row created.
SQL> INSERT INTO DEPARTMENT VALUES(&DNUMBER,'&DNAME',&MGR_SSN,'&MGR_START_DATE');
Enter value for dnumber: 5
Enter value for dname: RESEARCH
Enter value for mgr_ssn: 333445555
Enter value for mgr_start_date: 22-AUG-1988
old 1: INSERT INTO DEPARTMENT VALUES(&DNUMBER,'&DNAME',&MGR_SSN,'&MGR_START_DATE')
new 1: INSERT INTO DEPARTMENT VALUES(5,'RESEARCH',333445555,'22-AUG-1988')
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,&SUPER_SSN,&DNO);
Enter value for ssn: 123456789
Enter value for fname: JOHN
Enter value for lname: SMITH
Enter value for address: HOUSTON
Enter value for gender: M
Enter value for salary: 30000
Enter value for super_ssn: 333445555
Enter value for dno: 5
old 1: INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,&SUPER_SSN,&DNO)
new 1: INSERT INTO EMPLOYEE VALUES(123456789,'JOHN','SMITH','HOUSTON
1 row created.
SQL> UPDATE EMPLOYEE
2 SET SUPER_SSN=888665555, DNO=5
3 WHERE SSN=333445555;
1 row updated.
SQL> INSERT INTO PROJECT VALUES(&PNUMBER,'&PNAME','&PLOCATION',&DNO);
Enter value for pnumber: 1
Enter value for pname: PRODUCTX
Enter value for plocation: BELLAIRE
Enter value for dno: 5
old 1: INSERT INTO PROJECT VALUES(&PNUMBER,'&PNAME','&PLOCATION',&DNO)
new 1: INSERT INTO PROJECT VALUES(1,'PRODUCTX','BELLAIRE',5)
1 row created.
SQL> INSERT INTO DEPENDENT VALUES(&ESSN,'&DEPENDENT_NAME','&GENDER','&BDATE','&RELATIONSHIP');
Enter value for essn: 333445555
Enter value for dependent_name: ALICE
Enter value for gender: F
Enter value for bdate: 04-MAY-1986
Enter value for relationship: DAUGHTER
old 1: INSERT INTO DEPENDENT VALUES(&ESSN,'&DEPENDENT_NAME','&GENDER','&BDATE','&RELATIONSHIP')
new 1: INSERT INTO DEPENDENT VALUES(333445555,'ALICE
1 row created.
SQL> INSERT INTO WORKS_ON VALUES(&ESSN,&PNUMBER,&HOUR);
Enter value for essn: 123456789
Enter value for pnumber: 1
Enter value for hour: 32.5
old 1: INSERT INTO WORKS_ON VALUES(&ESSN,&PNUMBER,&HOUR)
new 1: INSERT INTO WORKS_ON VALUES(123456789,1,32.5)
1 row created.
3. Displaying the table contents.
SQL> SELECT * FROM EMPLOYEE;
SSN FNAME LNAME ADDRESS GENDER SALARY SUPER_SSN DNO
333445555 FRANKLIN WONG HOUSTON
987654321 JENNIFER WALLACE BELLAIRE F 43000
888665555 JAMES BORG HOUSTON
3 rows selected.
SQL> SELECT * FROM DEPARTMENT;
DNUMBER DNAME MGR_SSN MGR_START
5 RESEARCH 333445555 22-MAY-88
4 ADMINISTRATION 987654321 01-JAN-95
1 HEADQUARTERS 888665555 19-JUN-81
3 rows selected.
SQL> SELECT * FROM EMPLOYEE;
SSN FNAME LNAME ADDRESS GENDER SALARY SUPER_SSN DNO
--------- ---------- ---------- ---------- ------ --------- --------- ---------
123456789 John Smith Houston
333445555 Franklin Wong Houston
999887777 Alicia Zelaya Spring Female 25000 987654321 4
987654321 Jennifer Wallacia Bellaire Female 43000 888665555 4
666884444 Ramesh Narayan Humble Male 38000 333445555 5
453453453 Joyce English Houston
987987987 Ahmad Jabbar Houston
888665555 James Borg Houston male 55000 1
8 rows selected.
SQL> SELECT * FROM PROJECT;
PNUMBER PNAME PLOCATION DNO
1 PRODUCTX BELLAIRE 5
2 PRODUCTY SUGARLAND 5
3 PRODUCTZ HOUSTON
10 COMPUTERIZATION STAFFORD 4
20 REORGANIZATION HOUSTON
30 NEWBENEFITS STAFFORD 4
6 rows selected.
SQL> SELECT * FROM DEPENDENT;
ESSN DEPENDENT_ NAME GENDER BDATE RELATIONS
333445555 ALICE
333445555 THEODORE M 25-OCT-83 SON
333445555 JOY F 03-MAY-58 SPOUSE
987654321 ABNER M 28-FEB-42 SPOUSE
123456789 MICHEAL M 04-JAN-88 SON
123456789 ALICE
123456789 ELIZABETH
7 rows selected.
SQL> SELECT * FROM WORKS_ON;
ESSN PNUMBER HOUR
123456789 1 32.5
123456789 2 7.5
666884444 3 40
453453453 1 20
453453453 2 20
333445555 2 10
333445555 3 10
333445555 10 10
333445555 20 10
999887777 30 30
999887777 10 10
987987987 10 35
987987987 30 5
987654321 30 20
987654321 20 15
888665555 20
16 rows selected.
4. Update salaries of all employees who work for Research department by 10%.
SQL> UPDATE EMPLOYEE
2 SET SALARY=SALARY+(SALARY*0.1)
3 WHERE DNO
4 IN (SELECT DNUMBER
5 FROM DEPARTMENT
6 WHERE DNAME='RESEARCH');
4 rows updated.
SQL> SELECT * FROM EMPLOYEE;
SSN FNAME LNAME ADDRESS GENDER SALARY SUPER_SSN DNO
--------- ---------- ---------- ---------- ------ --------- --------- ---------
123456789 John Smith Houston
333445555 Franklin Wong Houston
999887777 Alicia laya Spring Female 250000 987654321 4
987654321 Jennifer Wallacia Bellaire Female 43000 888665555 4
666884444 Ramesh Narayan Humble Male 41800 333445555 5
453453453 Joyce English Houston
987987987 Ahmad Jabbar Houston
888665555 James Borg Houston male 55000 1
8 rows selected.
5. Retrieve the names of each employee who work on all the projects controlled by department number 5.
SQL> SELECT FNAME,LNAME
2 FROM EMPLOYEE
3 WHERE SSN
4 IN (SELECT W.ESSN
5 FROM WORKS_ON W
6 WHERE W.PNUMBER
7 IN (SELECT P1.PNUMBER
8 FROM PROJECT P1
9 WHERE P1.DNO=5)
10 GROUP BY W.ESSN
11 HAVING COUNT(W.PNUMBER)=(SELECT COUNT(P2.PNUMBER)
12 FROM PROJECT P2
13 WHERE P2.DNO=5
14 GROUP BY P2.DNO)
15 );
no rows selected.
6. For each department that has three employees retrieve the department number and the number of employees making salary more than or equals to 30000.
SQL> SELECT COUNT(*) AS NO_OF_EMPLOYEES,DNO AS DEPT_NUMBER
2 FROM EMPLOYEE
3 WHERE SALARY>=30000 AND DNO
4 IN (SELECT DNO
5 FROM EMPLOYEE
6 GROUP BY DNO
7 HAVING COUNT(SSN)=3)
8 GROUP BY DNO;
NO_OF_EMPLOYEES DEPT_NUMBER
1 4
7. Retrieve the employees that do not have dependents
SQL> SELECT SSN,FNAME,LNAME
2 FROM EMPLOYEE
3 WHERE SSN
4 IN((SELECT SSN
5 FROM EMPLOYEE)
6 MINUS
7 (SELECT ESSN
8 FROM DEPENDENT));
SSN FNAME LNAME
453453453 JOYCE ENGLISH
666884444 RAMESH NARAYAN
888665555 JAMES BORG
987987987 AHMAD JABBAR
999887777 ALICIA ZELAYA
8. For each department retrieve the department number, minimum salary and the details of the employees who earn minimum salary in the department.
SQL> SELECT SSN,FNAME,SALARY,DNO
2 FROM EMPLOYEE
3 WHERE SALARY
4 IN (SELECT MIN(SALARY)
5 FROM EMPLOYEE
6 GROUP BY DNO);
SSN FNAME SALARY DNO
999887777 ALICIA 25000 4
987987987 AHMAD 25000 4
453453453 JOYCE 27500 5
888665555 JAMES 55000 1
Thanks for taking the initative to make me learn. Thank u very much.
ReplyDelete