· 6 years ago · Nov 13, 2019, 09:24 AM
1*******************************************************************
2Modified By SilentKiller
3********************************************************************
4
5CREATE TABLE DEPARTMENT
6(
7 DNo VARCHAR(20) PRIMARY KEY,
8 DName VARCHAR(20),
9 MgrStartDate DATE
10);
11
12CREATE TABLE EMPLOYEE
13(
14 SSN VARCHAR (20) PRIMARY KEY,
15 FName VARCHAR(20),
16 LName VARCHAR(20),
17 Address VARCHAR (40),
18 Sex VARCHAR (10),
19 Salary INTEGER,
20 SuperSSN REFERENCES EMPLOYEE (SSN),
21 Dno REFERENCES DEPARTMENT (DNo)
22);
23
24ALTER TABLE DEPARTMENT
25ADD MgrSSN REFERENCES EMPLOYEE (SSN);
26
27CREATE TABLE DLOCATION
28(
29 DNo REFERENCES DEPARTMENT (DNo),
30 DLoc VARCHAR(20),
31 PRIMARY KEY (DNo, DLoc)
32);
33
34CREATE TABLE PROJECT
35(
36 PNo INTEGER PRIMARY KEY,
37 PName VARCHAR(20),
38 PLocation VARCHAR (20),
39 DNo REFERENCES DEPARTMENT (DNo)
40);
41
42CREATE TABLE WORKS_ON
43(
44 SSN REFERENCES EMPLOYEE (SSN),
45 PNo REFERENCES PROJECT(PNo),
46 Hours NUMBER (2),
47 PRIMARY KEY (SSN, PNo)
48);
49
50
51
52INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSCSE01','John','Scott','Bangalore','Male', 500000);
53INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSCSE02','Hearn','Baker','Bangalore','Male', 700000);
54INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSCSE03','Edward','Scott','Mysore','Male', 500000);
55INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSCSE04','Pavan','Hegde','Mangalore','Male', 650000);
56INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSCSE05','Girish','Malya','Mysore','Male', 450000);
57INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSCSE06','Neha','SN','Bangalore','Female', 800000);
58INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSACC01','Ahana','Kishore','Mysore','Female', 350000);
59INSERT INTO EMPLOYEE (SSN, FName, LName, Address, Sex, Salary) VALUES ('RNSACC02','Santhosh','Kumar','Mangalore','Male', 300000);
60
61
62INSERT INTO DEPARTMENT VALUES ('1','Accounts','01-JAN-01','RNSACC01');
63INSERT INTO DEPARTMENT VALUES ('5','CSE','01-JUN-02','RNSCSE04');
64
65
66UPDATE EMPLOYEE SET
67SUPERSSN='RNSCSE02', DNO='5'
68WHERE SSN='RNSCSE01';
69UPDATE EMPLOYEE SET
70SUPERSSN='RNSCSE03', DNO='5'
71WHERE SSN='RNSCSE02';
72UPDATE EMPLOYEE SET
73SUPERSSN='RNSCSE04', DNO='5'
74WHERE SSN='RNSCSE03';
75UPDATE EMPLOYEE SET
76DNO='5', SUPERSSN='RNSCSE05'
77WHERE SSN='RNSCSE04';
78UPDATE EMPLOYEE SET
79DNO='5', SUPERSSN='RNSCSE06'
80WHERE SSN='RNSCSE05';
81UPDATE EMPLOYEE SET
82DNO='5', SUPERSSN=NULL
83WHERE SSN='RNSCSE06';
84UPDATE EMPLOYEE SET
85DNO='1', SUPERSSN='RNSACC02'
86WHERE SSN='RNSACC01';
87UPDATE EMPLOYEE SET
88DNO='1', SUPERSSN=NULL
89WHERE SSN='RNSACC02';
90
91
92INSERT INTO DLOCATION VALUES ('1','Bangalore');
93INSERT INTO DLOCATION VALUES ('5','Mangalore');
94
95INSERT INTO PROJECT VALUES (100,'IoT','Bangalore','5');
96INSERT INTO PROJECT VALUES (101,'Cloud','Bangalore','5');
97INSERT INTO PROJECT VALUES (102,'Big Data','Bangalore','5');
98INSERT INTO PROJECT VALUES (104,'Bank Management','Bangalore','1');
99INSERT INTO PROJECT VALUES (105,'Salary Management','Bangalore','1');
100
101
102INSERT INTO WORKS_ON VALUES ('RNSCSE01',100, 4);
103INSERT INTO WORKS_ON VALUES ('RNSCSE01',101, 6);
104INSERT INTO WORKS_ON VALUES ('RNSCSE01',102, 8);
105INSERT INTO WORKS_ON VALUES ('RNSCSE02',100, 10);
106INSERT INTO WORKS_ON VALUES ('RNSCSE04',100, 3);
107INSERT INTO WORKS_ON VALUES ('RNSCSE05',101, 4);
108INSERT INTO WORKS_ON VALUES ('RNSCSE06',102, 5);
109INSERT INTO WORKS_ON VALUES ('RNSCSE03',102, 6);
110INSERT INTO WORKS_ON VALUES ('RNSACC01',104, 5);
111INSERT INTO WORKS_ON VALUES ('RNSACC02',105, 6);
112
113
114-- 1. Make a list of all project numbers for projects that involve
115-- an employee whose last name is ‘Scott’,
116-- either as a worker or as a manager of the department that controls the project.
117
118SELECT P.PNo
119 FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
120 WHERE E.SSN = D.MgrSSN
121 AND P.DNo = D.DNo
122 AND E.LName='Scott'
123 UNION
124 SELECT WO.PNo
125 FROM WORKS_ON WO, EMPLOYEE E
126 WHERE WO.SSN = E.SSN
127 AND E.LName = 'Scott';
128
129
130-- 2. Show the resulting salaries if every employee working
131-- on the ‘IoT’ project is given a 10 percent raise.
132
133SELECT E.FName, E.LName, 1.1*E.Salary AS INC_SAL
134 FROM EMPLOYEE E, WORKS_ON WO, PROJECT P
135 WHERE E.SSN = WO.SSN
136 AND WO.PNO = P.PNO
137 AND P.PNAME='IoT';
138
139-- 3. Find the sum of the salaries of all employees of the ‘Accounts’ department,
140-- as well as the maximum Salary, the minimum Salary,
141-- and the average Salary in this department
142
143SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
144 FROM EMPLOYEE E, DEPARTMENT D
145 WHERE E.DNo = D.DNo
146 AND D.DName = 'Accounts';
147
148
149-- 4. Retrieve the name of each employee who works on all the projects
150-- Controlled by department number 5 (use NOT EXISTS operator).
151
152SELECT E.FName, E.LName FROM EMPLOYEE E
153 WHERE NOT EXISTS (
154 SELECT PNo FROM PROJECT P
155 WHERE DNo = 5
156 MINUS
157 SELECT PNo FROM WORKS_ON WO
158 WHERE WO.SSN = E.SSN);
159
160
161-- 5. For each department that has more than five employees,
162-- retrieve the department number and the number of its employees
163-- who are making more than Rs. 6, 00,000.
164
165SELECT E.DNo, COUNT(E.DNo)
166 FROM EMPLOYEE E
167 WHERE E.Salary > 600000
168 GROUP BY E.DNo
169 HAVING E.DNo IN (SELECT E1.DNo FROM EMPLOYEE E1
170 GROUP BY E1.DNo HAVING COUNT(*) > 5);