Y3E7KkHB

1DROP TABLE EMP;
2DROP TABLE DEPT;
3DROP TABLE SALGRADE;
4CREATE TABLE EMP
5(EMPNO NUMBER(4) NOT NULL,
6ENAME VARCHAR2(10),
7JOB VARCHAR2(9),
8MGR NUMBER(4),
9HIREDATE DATE,
10SAL NUMBER(7, 2),
11COMM NUMBER(7, 2),
12DEPTNO NUMBER(2));
13INSERT INTO EMP VALUES
14(7369, 'SMITH', 'CLERK', 7902,
15TO_DATE('17-MAR-1980', 'DD-MON-YYYY'), 800, NULL, 20);
16INSERT INTO EMP VALUES
17(7499, 'ALLEN', 'SALESMAN', 7698,
18TO_DATE('20-MAR-1981', 'DD-MON-YYYY'), 1600, 300, 30);
19INSERT INTO EMP VALUES
20(7521, 'WARD', 'SALESMAN', 7698,
21TO_DATE('22-MAR-1981', 'DD-MON-YYYY'), 1250, 500, 30);
22INSERT INTO EMP VALUES
23(7566, 'JONES', 'MANAGER', 7839,
24TO_DATE('2-MAR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
25INSERT INTO EMP VALUES
26(7654, 'MARTIN', 'SALESMAN', 7698,
27TO_DATE('28-MAR-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
28INSERT INTO EMP VALUES
29(7698, 'BLAKE', 'MANAGER', 7839,
30TO_DATE('1-MAR-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
31INSERT INTO EMP VALUES
32(7782, 'CLARK', 'MANAGER', 7839,
33TO_DATE('9-MAR-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
34INSERT INTO EMP VALUES
35(7788, 'SCOTT', 'ANALYST', 7566,
36TO_DATE('09-MAR-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
37INSERT INTO EMP VALUES
38(7839, 'KING', 'PRESIDENT', NULL,
39TO_DATE('17-MAR-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
40INSERT INTO EMP VALUES
41(7844, 'TURNER', 'SALESMAN', 7698,
42TO_DATE('8-MAR-1981', 'DD-MON-YYYY'), 1500, 0, 30);
43INSERT INTO EMP VALUES
44(7876, 'ADAMS', 'CLERK', 7788,
45TO_DATE('12-MAR-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
46INSERT INTO EMP VALUES
47(7900, 'JAMES', 'CLERK', 7698,
48TO_DATE('3-MAR-1981', 'DD-MON-YYYY'), 950, NULL, 30);
49INSERT INTO EMP VALUES
50(7902, 'FORD', 'ANALYST', 7566,
51TO_DATE('3-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
52INSERT INTO EMP VALUES
53(7934, 'MILLER', 'CLERK', 7782,
54TO_DATE('23-MAR-1982', 'DD-MON-YYYY'), 1300, NULL, 10); 
55DROP TABLE DEPT;
56CREATE TABLE DEPT
57(DEPTO NUMBER (2),
58DNAME VARCHAR2(14),
59LOC VARCHAR2(13) );
60INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
61INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
62INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
63INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
64 
65CREATE TABLE SALGRADE
66(GRADE NUMBER,
67LOSAL NUMBER,
68HISAL NUMBER);
69INSERT INTO SALGRADE VALUES (1, 700, 1200);
70INSERT INTO SALGRADE VALUES (2, 1201, 1400);
71INSERT INTO SALGRADE VALUES (3, 1401, 2000);
72INSERT INTO SALGRADE VALUES (4, 2001, 3000);
73INSERT INTO SALGRADE VALUES (5, 3001, 9999); 
74COMMIT;
75SELECT ENAME, DEPTNO, SAL 
76FROM EMP
77WHERE SAL >1000  AND SAL < 2000;
78DESC DEPT;
79SELECT DEPTO, DNAME
80FROM DEPT
81ORDER BY DEPTO;
82SELECT JOB
83FROM EMP;
84SELECT * FROM EMP
85WHERE DEPTNO IN ( 10,20)
86ORDER BY JOB;
87SELECT ENAME, DEPTNO, JOB
88FROM EMP
89WHERE DEPTNO = 20 AND JOB ='CLERK';
90SELECT ENAME
91FROM EMP
92WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';
93SELECT ENAME, SAL
94FROM EMP
95WHERE (SAL NOT BETWEEN 1000 AND 2000) AND ENAME LIKE '_____';
96SELECT ENAME, JOB,SAL
97FROM EMP
98WHERE ENAME NOT LIKE '%L%' AND SAL NOT IN(800,1600,3000);
99SELECT *
100FROM EMP
101WHERE 
102
103
104SELECT ENAME, DNAME
105FROM EMP, DEPT;
106DESC EMP;
107SELECT ENAME, DNAME
108FROM EMP, DEPT
109ORDER BY ENAME;
110DESC DEPT;
111SELECT ENAME, DEPTO, DNAME
112FROM EMP, DEPT;
113SELECT ENAME, DEPTNO, DNAME
114FROM EMP, DEPT
115WHERE EMP.DEPTNO = DEPT.DEPTO
116ORDER BY ENAME;
117DESC DEPT;
118SELECT ENAME, LOC, DNAME
119FROM EMP, DEPT
120WHERE EMP.DEPTNO = DEPT.DEPTO AND SAL > 1500;
121DESC EMP;
122DESC SALGRADE;
123SELECT ENAME, JOB, SAL, GRADE
124FROM EMP, SALGRADE
125WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL;
126SELECT ENAME
127FROM EMP, SALGRADE
128WHERE (EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL) AND GRADE =3;
129SELECT ENAME, LOC
130FROM EMP, DEPT
131WHERE EMP.DEPTNO = DEPT.DEPTO AND LOC = 'DALLAS';
132SELECT ENAME, DEPTO
133FROM EMP, DEPT
134WHERE EMP.DEPTNO (+)= DEPT.DEPTO AND DEPT.DEPTO IN (30,40);
135SELECT E.ENAME,E.SAL, B.ENAME, B.SAL
136FROM EMP E, EMP B
137WHERE E.MGR = B.EMPNO AND E.SAL > B.SAL;
138SELECT JOB
139FROM EMP
140WHERE DEPTNO= 10
141UNION
142SELECT JOB
143FROM EMP
144WHERE DEPTNO= 30;
145
146SELECT JOB
147FROM EMP
148WHERE DEPTNO= 10
149INTERSECT
150SELECT JOB
151FROM EMP
152WHERE DEPTNO= 30;
153
154SELECT JOB
155FROM EMP
156WHERE DEPTNO= 10
157MINUS
158SELECT JOB
159FROM EMP
160WHERE DEPTNO= 30;
161
162SELECT AVG(SAL)
163FROM EMP;
164
165DESC EMP;
166
167SELECT MIN(SAL)  
168FROM EMP
169WHERE JOB = 'CLERK';
170
171SELECT *
172FROM EMP;
173
174SELECT COUNT(EMPNO)
175FROM EMP
176WHERE DEPTNO = 20;
177
178
179SELECT AVG(SAL), JOB
180FROM EMP
181GROUP BY JOB;
182
183SELECT AVG(SAL), JOB
184FROM EMP
185WHERE NOT JOB= 'MANAGER'
186GROUP BY JOB;
187
188SELECT MAX(SAL), JOB
189FROM EMP
190GROUP BY JOB;
191
192SELECT *
193FROM EMP;
194
195SELECT AVG(SAL), JOB
196FROM EMP
197GROUP BY JOB
198HAVING COUNT(EMPNO)>3;
199
200SELECT JOB, AVG(SAL)
201FROM EMP
202GROUP BY JOB
203HAVING AVG(SAL)>=3000;
204
205SELECT JOB, AVG(SAL), AVG((SAL*12)+NVL(COMM,0))
206FROM EMP
207GROUP BY JOB;
208
209SELECT MAX(SAL)-MIN(SAL)
210FROM EMP;
211
212DESC EMP;
213
214SELECT DNAME
215FROM EMP, DEPT
216WHERE EMP.DEPTNO = DEPT.DEPTO
217GROUP BY DNAME
218HAVING COUNT(EMPNO)>3;
219
220SELECT COUNT(EMPNO)-COUNT(DISTINCT( EMPNO))
221FROM EMP;
222
223SELECT MIN(SAL),MGR
224FROM EMP
225GROUP BY MGR
226HAVING MIN(SAL)>1000
227ORDER BY MIN(SAL);
228
229SELECT COUNT(EMPNO)
230FROM EMP, DEPT
231WHERE EMP.DEPTNO = DEPT.DEPTO AND LOC='DALLAS';
232
233
234SELECT MAX(SAL), GRADE
235FROM EMP, SALGRADE
236WHERE EMP.SAL BETWEEN LOSAL AND HISAL
237GROUP BY GRADE;
238
239SELECT *
240FROM EMP;
241
242SELECT SAL, COUNT (SAL)
243FROM EMP
244GROUP BY SAL
245;
246DESC EMP;
247DESC DEPT;
248SELECT MIN(SAL)
249FROM EMP;
250
251SELECT ENAME
252FROM EMP
253WHERE JOB = (
254SELECT JOB
255FROM EMP
256WHERE ENAME = 'BLAKE');
257
258SELECT ENAME, DEPTNO, SAL
259FROM EMP
260WHERE SAL IN (
261SELECT MIN(SAL)
262FROM EMP
263GROUP BY DEPTNO);
264
265SELECT E.ENAME, E.DEPTNO, E.SAL
266FROM EMP E
267WHERE E.SAL = (
268SELECT MIN(SAL)
269FROM EMP
270WHERE DEPTNO=E.DEPTNO);
271
272SELECT ENAME, SAL
273FROM EMP
274WHERE SAL > ANY (
275SELECT SAL
276FROM EMP
277WHERE DEPTNO=30);
278
279SELECT MAX(SAL)
280FROM EMP
281WHERE DEPTNO=30;
282
283SELECT ENAME, SAL
284FROM EMP
285WHERE SAL > ALL (
286SELECT SAL
287FROM EMP
288WHERE DEPTNO=30);
289
290SELECT DEPTNO
291FROM EMP
292WHERE AVG(SAL) > ANY(
293SELECT AVG(SAL)
294FROM EMP
295WHERE DEPTNO=30);
296
297SELECT DEPTNO
298FROM EMP
299HAVING AVG(SAL) > (
300SELECT AVG(SAL)
301FROM EMP
302WHERE DEPTNO=30)
303GROUP BY DEPTNO;
304
305SELECT JOB
306FROM EMP
307HAVING AVG(SAL)=
308(SELECT MAX(AVG(SAL))
309FROM EMP
310GROUP BY JOB)
311GROUP BY JOB;
312
313SELECT ENAME, SAL
314FROM EMP
315WHERE SAL >= 
316(SELECT MAX(SAL) 
317FROM EMP
318WHERE DEPTNO=
319(SELECT DEPTNO
320FROM DEPT
321WHERE DNAME='SALES'));
322
323SELECT * 
324FROM DEPT;
325
326SELECT ENAME
327FROM EMP
328WHERE SAL >
329(SELECT AVG(SAL)
330FROM EMP
331GROUP BY DEPTNO);
332
333SELECT E.ENAME, E.DEPTNO, E.SAL
334FROM EMP E
335WHERE E.SAL > (
336SELECT AVG(SAL)
337FROM EMP
338WHERE DEPTNO=E.DEPTNO);
339
340SELECT *
341FROM DEPT;
342
343SELECT M.ENAME
344FROM EMP M
345WHERE EXISTS
346(SELECT E.ENAME
347FROM EMP E
348WHERE E.MGR=M.EMPNO);
349
350SELECT ENAME
351FROM EMP
352WHERE DEPTNO NOT IN 
353(SELECT DEPTNO
354 FROM DEPT );
355
356SELECT E.ENAME, E.SAL, E.JOB
357FROM EMP E
358WHERE E.SAL = 
359(SELECT MAX(SAL)
360FROM EMP
361WHERE JOB=E.JOB)
362ORDER BY E.SAL DESC;
363
364SELECT ENAME
365FROM EMP
366WHERE (JOB,SAL) IN 
367(SELECT JOB, MAX(SAL) 
368FROM EMP
369GROUP BY JOB);
370
371SELECT *
372FROM EMP;
373
374
375SELECT E.ENAME, E.SAL,E.DEPTNO
376FROM EMP E
377WHERE SAL=
378(SELECT MIN(SAL)
379FROM EMP
380WHERE DEPTNO=E.DEPTNO)
381ORDER BY ENAME;
382
383SELECT E.ENAME, E.HIREDATE
384FROM EMP E
385WHERE (HIREDATE,DEPTNO)  IN
386(SELECT MAX(HIREDATE),DEPTNO 
387FROM EMP
388GROUP BY DEPTNO;)
389ORDER BY E. HIREDATE;
390
391SELECT E.ENAME, E.HIREDATE
392FROM EMP E
393WHERE HIREDATE=
394(SELECT MAX(HIREDATE)
395FROM EMP
396WHERE  DEPTNO=E.DEPTNO)
397ORDER BY E. HIREDATE;
398
399SELECT E.ENAME, E.DEPTNO, E.SAL
400FROM EMP E
401WHERE E.SAL > (
402SELECT AVG(SAL)
403FROM EMP
404WHERE DEPTNO=E.DEPTNO);
405
406SELECT D.DEPTO
407FROM DEPT D
408WHERE NOT EXISTS (
409SELECT EMPNO
410FROM EMP
411WHERE DEPTNO=D.DEPTO);
412
413DESC EMP;
414
415SELECT  E.ENAME, E.SAL
416FROM EMP E
417WHERE 3>(
418SELECT COUNT(EMPNO)
419FROM EMP
420WHERE SAL > E.SAL);
421
422SELECT E.DEPTNO
423FROM  EMP E
424GROUP BY  E.DEPTNO 
425HAVING COUNT(E.EMPNO)= (
426SELECT MAX(COUNT(EMPNO))
427FROM EMP
428GROUP BY DEPTNO);
429
430SELECT ENAME
431FROM EMP
432WHERE HIREDATE=(
433SELECT MAX(HIREDATE)
434FROM EMP);
435
436DESC EMP;
437
438CREATE TABLE PEOPLE(
439IDP NUMBER(4) PRIMARY KEY,
440SURNNAME VARCHAR2(10),
441POSITION VARCHAR2 (9),
442INCOME NUMBER(7,2));
443
444INSERT INTO PEOPLE VALUES
445(0001, 'KOWALSKI', 'EXPERT', 3800);
446 
447INSERT INTO PEOPLE SELECT EMPNO, ENAME, JOB, SAL
448FROM EMP
449WHERE JOB = 'ANALYST';
450
451SELECT * 
452FROM PEOPLE;
453
454DESC DEPT;
455
456CREATE TABLE DIVISION(
457DIVNO,
458DIVNAME,
459DIVLOC)
460AS SELECT *
461FROM DEPT;
462
463ALTER TABLE DIVISION
464ADD HEAD VARCHAR2(20);
465
466RENAME DIVISION TO BRANCH;
467
468DROP TABLE DIVISION;
469
470DESC DEPT;
471
472UPDATE BRANCH
473SET DIVLOC='WARSAW';
474SELECT *
475FROM BRANCH;
476
477CREATE TABLE GROUPS(
478IDG NUMBER(4) PRIMARY KEY,
479GROUPNAME VARCHAR2(14)
480);
481
482CREATE TABLE STUDENT(
483IDS NUMBER(4) PRIMARY KEY,
484SURNAME VARCHAR2(15),
485HISG NUMBER(4),
486FOREIGN KEY(HISG) REFERENCES GROUPS(IDG)
487);
488
489CREATE TABLE AEMP AS 
490SELECT *
491FROM EMP;
492
493CREATE TABLE ASALGRADE AS 
494SELECT *
495FROM SALGRADE;
496
497CREATE TABLE ADEPT AS 
498SELECT *
499FROM DEPT;
500
501SET SERVEROUTPUT ON
502CREATE OR REPLACE TRIGGER R
503AFTER INSERT OR UPDATE
504ON AEMP
505FOR EACH ROW
506BEGIN 
507DBMS_OUTPUT.PUT_LINE('MY TRIGGER WORKS');
508END;
509
510SELECT *
511FROM AEMP;
512INSERT INTO AEMP VALUES
513(7483, 'KOWALSKI','CLERK', 7902, TO_DATE('8-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
514
515SET SERVEROUTPUT ON
516CREATE OR REPLACE TRIGGER T
517AFTER INSERT
518ON AEMP
519FOR EACH ROW
520BEGIN 
521DBMS_OUTPUT.PUT_LINE('MORE COLLEGUES');
522END;
523
524INSERT INTO AEMP VALUES
525(7485, 'LAZY','CLERK', 7902, TO_DATE('9-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
526
527DESC DEPT;
528SET SERVEROUTPUT ON
529CREATE OR REPLACE TRIGGER D
530AFTER INSERT
531ON ADEPT
532DECLARE X INTEGER;
533BEGIN 
534SELECT COUNT(DEPTO)
535INTO X
536FROM ADEPT;
537DBMS_OUTPUT.PUT_LINE('NOW WE HAVE '|| X || 'DEPARTMENTS');
538END;
539
540INSERT INTO ADEPT VALUES (50, 'MANAGERING', 'NEW YORK');
541
542CREATE OR REPLACE TRIGGER E
543BEFORE INSERT
544ON ADEPT
545FOR EACH ROW
546BEGIN
547SELECT NVL(MAX(DEPTO)+10,10)
548INTO :NEW.DEPTO
549FROM ADEPT;
550END;
551
552INSERT INTO ADEPT( DNAME, LOC) VALUES ( 'SECURITY', 'WARSAW');
553INSERT INTO ADEPT VALUES (71, 'MARKETING', 'JAKARTA');
554SELECT *
555FROM AEMP;
556
557CREATE OR REPLACE TRIGGER L
558BEFORE INSERT
559ON AEMP
560FOR EACH ROW
561BEGIN
562
563IF :NEW.ENAME='LAZY'
564THEN
565--DBMS_OUTPUT.PUT_LINE('WE DONT WANT LAZY WORKER');
566RAISE_APPLICATION_ERROR(-20003, 'WE DONT WANT LAZY WORKER');
567END IF;
568END;
569
570INSERT INTO AEMP VALUES
571(7777, 'LAZY','CLERK', 7902, TO_DATE('9-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
572
573CREATE OR REPLACE TRIGGER S
574BEFORE UPDATE
575ON AEMP
576FOR EACH ROW
577BEGIN
578IF :NEW.SAL>7000
579THEN
580:NEW.SAL:=:OLD.SAL;
581--RAISE_APPLICATION_ERROR(-20003, 'WE DONT WANT LAZY WORKER');
582END IF;
583END;
584
585INSERT INTO AEMP VALUES
586(7777, 'ANA','CLERK', 7902, TO_DATE('9-MAR-1981', 'DD-MON-YYYY'), 7800, NULL, 20);
587UPDATE AEMP SET SAL = 8000 WHERE EMPNO = 7485;
588SELECT *
589FROM ASALGRADE;
590
591CREATE OR REPLACE TRIGGER Z
592BEFORE INSERT
593ON AEMP
594FOR EACH ROW
595DECLARE X INTEGER;
596BEGIN
597SELECT MAX(HISAL) 
598INTO X
599FROM ASALGRADE;
600IF :NEW.SAL > X
601THEN 
602
603
604END;