· 8 years ago · Mar 07, 2018, 01:18 AM
1--1.
2
3--practical 5 Exercise 1
4
5CREATE TABLE newstaff
6 (
7 snum NUMBER(4) NOT NULL,
8 fname VARCHAR2(20) NOT NULL,
9 surname VARCHAR2(20) NOT NULL ,
10 jobtitle VARCHAR2(20),
11 manager_id NUMBER(4),
12 sex VARCHAR2(1),
13 joindate DATE,
14 div_id NUMBER(3) NOT NULL,
15 salary NUMBER(8,2),
16 bonus NUMBER(8,2),
17 CONSTRAINT snum1_pk PRIMARY KEY (snum),
18 CONSTRAINT salary_min1 CHECK (salary > 0)
19 );
20COMMIT;
21
22INSERT
23INTO newstaff VALUES
24 (
25 3200,
26 'PAUL',
27 'QUINN',
28 'MANAGER',
29 3812,
30 'F',
31 to_date('01-Jul-2010','dd-mon-yyyy'),
32 20,35872,500
33 );
34INSERT
35INTO newstaff VALUES
36 (
37 3365,
38 'MARIA',
39 'HUGHES',
40 'ACCOUNTANT',
41 3414,
42 'M',
43 to_date('17-Nov-2013','dd-mon-yyyy'),
44 10,33760,
45 NULL
46 );
47INSERT
48INTO newstaff VALUES
49 (
50 3414,
51 'AOIFE',
52 'MCDERMOT',
53 'MANAGER',
54 3813,
55 'M',
56 to_date('22-Jun-2017','dd-mon-yyyy'),
57 10,45872,
58 NULL
59 );
60INSERT
61INTO newstaff VALUES
62 (
63 3698,
64 'MARY',
65 'FRENCH',
66 'CONSULTANT',
67 3788,
68 'M',
69 to_date('01-Apr-2000','dd-mon-yyyy'),
70 30,50184,0
71 );
72INSERT
73INTO newstaff VALUES
74 (
75 3724,
76 'OWEN',
77 'MARKEY',
78 'CONSULTANT',
79 3788,
80 'M',
81 to_date('28-Oct-2009','dd-mon-yyyy'),
82 30,59400,825
83 );
84INSERT
85INTO newstaff VALUES
86 (
87 3788,
88 'RONAN',
89 'BOYLE',
90 'MANAGER',
91 3813,
92 'M',
93 to_date('10-Feb-2005','dd-mon-yyyy'),
94 30,30816,1586
95 );
96INSERT
97INTO newstaff VALUES
98 (
99 3812,
100 'CIAN',
101 'FLEMING',
102 'DIRECTOR',
103 3926,
104 'M',
105 to_date('14-Dec-2002','dd-mon-yyyy'),
106 20,62000,950
107 );
108INSERT
109INTO newstaff VALUES
110 (
111 3813,
112 'JULIE',
113 'HOWLEY',
114 'DIRECTOR',
115 3926,
116 'F',
117 to_date('20-Apr-2000','dd-mon-yyyy'),
118 10,66752,
119 NULL
120 );
121INSERT
122INTO newstaff VALUES
123 (
124 3926,
125 'ELAINE',
126 'BURKE',
127 'CHAIRMAN',
128 NULL,
129 'F',
130 to_date('03-Sep-2004','dd-mon-yyyy'),
131 10,65816,
132 NULL
133 );
134INSERT
135INTO newstaff VALUES
136 (
137 3944,
138 'JAMIE',
139 'OLIVER',
140 'CONSULTANT',
141 3788,
142 'M',
143 to_date('24-Aug-2017','dd-mon-yyyy'),
144 30,38000,825
145 );
146ALTER TABLE newstaff ADD (CONSTRAINT Nper_branch_fk FOREIGN KEY (div_id) REFERENCES Branch(div_id) ,
147 CONSTRAINT Nper_manager_fk FOREIGN KEY (manager_id) REFERENCES newstaff(snum) );
148
149
150
151
152--2. Obtain script on BB to create newstaff
153
154commit;
155SELECT * FROM newstaff;
156
157a) Create the newstaff table using the script provided.
158
159
160SELECT * FROM PERSONNEL
161INTERSECT
162SELECT * FROM NEWSTAFF;
163
164b) Find the staff who are in both tables.
165
166
167SELECT *
168FROM PERSONNEL
169MINUS
170SELECT * FROM NEWSTAFF;
171
172
173
174c) Find the staff who have left – i.e. who are in the personnel table but not the newstaff table.
175
176
177SELECT *
178FROM NEWSTAFF
179MINUS
180SELECT * FROM PERSONNEL;
181
182
183d) Find the new staff - i.e. who are in the newstaff table but not the personnel table.
184SELECT *
185FROM PERSONNEL
186UNION
187SELECT * FROM NEWSTAFF;
188
189
190e) Create a query that will produce a list of all the staff – everyone.
191
192
193SELECT *
194FROM PERSONNEL
195UNION ALL
196SELECT * FROM NEWSTAFF;
197
198
199f) Drop table newstaff before continuing.
200
201
202DROP TABLE NEWSTAFF;
203
204-- Inserting Stuff into a table
205
206
207-- 4. Show the full details of all staff who are not managers. i.e. they do not manage other members of staff(their snum does not appear in the manager_id column) (Here as a subquery, find out who are not managers. Hint: use a recursive join, and then output those that are not managers (NOT IN))
208
209SELECT distinct m.snum, m.surname
210FROM PERSONNEL S,
211 PERSONNEL M
212WHERE S.manager_id = M.snum;
213
214--and I don't want my output to list managers
215SELECT SNUM, SURNAME
216FROM PERSONNEL
217WHERE SNUM <> ALL(SELECT m.snum
218FROM PERSONNEL S,
219 PERSONNEL M
220WHERE S.manager_id = M.snum);
221
222SELECT SNUM, SURNAME
223FROM PERSONNEL
224WHERE SNUM NOT IN (SELECT m.snum
225FROM PERSONNEL S,
226 PERSONNEL M
227WHERE S.manager_id = M.snum);
228-- this is checking that the snums listed are not any of these numbers
229
230SELECT * FROM PERSONNEL
231WHERE SNUM <> ALL (SELECT DISTINCT MANAGER_ID FROM PERSONNEL WHERE MANAGER_ID IS NOT NULL);
232
233SELECT * FROM PERSONNEL
234WHERE SNUM <> all (3788,3414,3812,3813,3926,3365,3200);
235
236--could use NOT IN
237SELECT * FROM PERSONNEL
238WHERE SNUM NOT IN
239 (SELECT DISTINCT MANAGER_ID
240 FROM PERSONNEL WHERE
241 MANAGER_ID IS NOT NULL);
242
243-- CAN'T COMPARE ANYTHING TO NULL, so good practice here
244
245-- could use exists too!
246SELECT * FROM PERSONNEL P1
247WHERE NOT EXISTS (SELECT * FROM PERSONNEL P2 WHERE P1.SNUM = P2.MANAGER_ID );
248
249
250--5) List the name, job title and salary of employees who have the same job title as Thomas Sweeney. Ensure that you
251do not list Thomas Sweeney’s name in your output. You can use a subquery in the WHERE or a subquery in the FROM to answer
252this query.(Find out Thomas Sweeney’s jobtitle first)
253
254select p1.surname, p1.jobtitle
255from personnel p1, (select jobtitle, snum from personnel p2 where lower(surname) = 'sweeney') p2
256where p1.jobtitle = p2.jobtitle
257and p1.snum <> p2.snum;
258--using a subquery in the where
259
260SELECT fname, surname,
261 jobtitle,
262 salary
263FROM personnel p1
264WHERE jobtitle =
265 (SELECT jobtitle
266 FROM personnel p2
267 WHERE p2.surname = 'SWEENEY'
268 AND P1.SNUM <> P2.SNUM
269 );
270
271 --same job title as thomas sweeneym but dont include him in your output
272select p1.surname, p1.jobtitle
273from personnel p1, (select jobtitle, snum from personnel p2 where lower(surname) = 'sweeney') p2
274where p1.jobtitle = p2.jobtitle
275and p1.snum <> p2.snum;
276
277
278
279-- 6) Find the employees located in DUBLIN who have the same job title as HOWLEY.
280
281SELECT *
282FROM PERSONNEL P,
283 BRANCH B
284WHERE P.DIV_ID = B.DIV_ID
285AND B.CITY = 'DUBLIN'
286AND P.jobtitle =
287 (SELECT jobtitle FROM personnel WHERE surname = 'HOWLEY'
288 ) ;
289
290
291-- 7) Does anyone earn more than their manager? If so, who?
292-- take 2 copies the the same table or use a subquery
293
294SELECT m.snum AS Mgr#,
295 m.surname AS Manager,
296 m.salary AS mgrsalary,
297 s.snum AS staff#,
298 S.surname AS Employee,
299 s.salary AS staffsalary
300FROM PERSONNEL S,
301 PERSONNEL M
302WHERE S.manager_id = M.snum
303AND s.salary > m.salary
304ORDER BY m.surname;
305
306
307SELECT S.snum AS staff#, S.surname AS Employee, s.salary AS "staff salary", MSURNAME "Manager", MSALARY "Manager Salary"
308FROM PERSONNEL S,
309 (SELECT SNUM MANAGERID, SURNAME MSURNAME, SALARY MSALARY FROM PERSONNEL) M
310WHERE S.manager_id = MANAGERID
311AND s.salary > Msalary;
312
313--8) List the job title groups that have an average salary greater than the average salary of managers.
314
315SELECT *
316FROM
317 (
318 SELECT JOBTITLE,
319 ROUND(AVG(salary),0) Avgsalary
320 FROM personnel
321 GROUP BY JOBTITLE
322 )
323WHERE Avgsalary > (SELECT AVG(salary) as FROM personnel WHERE JOBTITLE='MANAGER' );
324
325
326-- 9) Find the division with the highest average salary. List the divisionÂ’s name, number and average salary.
327-- use rownum, used it last semester in mysql called limit
328
329
330SELECT DIVNAME Division, DIV_ID Division#, TO_CHAR(avgsalary, 'L999,999') AvgSalary
331FROM (SELECT DIVNAME, B.DIV_ID, avg(SALARY) avgsalary
332 FROM PERSONNEL P
333 JOIN BRANCH B
334 ON P.DIV_ID = B.DIV_ID
335GROUP BY B.DIV_ID, DIVNAME
336ORDER BY AVGSALARY DESC)
337WHERE ROWNUM =1;
338
339
340-- 10. List staff details with the same job title but different divisions.
341-- similar to Thomas Sweeney question form earlier
342SELECT
343 jobtitle, surname,
344 div_id
345FROM personnel P1
346WHERE jobtitle IN
347 (SELECT jobtitle
348 FROM personnel P2
349 WHERE P2.jobtitle =P1.jobtitle
350 AND P2.div_id <> P1.DIV_ID
351 )
352 ORDER BY jobtitle;
353
354 --many ways to acheive same result.....
355
356 SELECT surname,
357 jobtitle,
358 div_id
359FROM personnel P1
360WHERE EXISTS
361 (SELECT 1
362 FROM personnel P2
363 WHERE P2.jobtitle =P1.jobtitle
364 AND P2.div_id <> P1.DIV_ID
365 ) ;