--1
CREATE TABLE DEPT( DEPTNO NUMBER(2),
DNAME CHAR(20),
LOC CHAR(20),
CONSTRAINT DEPT_CLEP PRIMARY KEY (DEPTNO),
CONSTRAINT DEPT_DOM1 CHECK (DNAME IN
('ACCOUNTING','RESEARCH','SALES','OPERATIONS')));
CREATE TABLE EMP ( EMPNO NUMBER(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT EMP_PK PRIMARY KEY(EMPNO),
CONSTRAINT EMP_HIERARCHY FOREIGN KEY(MGR) REFERENCES
EMP(EMPNO),
CONSTRAINT DEPT_FK FOREIGN KEY(DEPTNO) REFERENCES
DEPT(DEPTNO)
);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, '17-nov-1981', 5000,
NULL, 10);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839, '02-apr-1981', 2975,
NULL, 20);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566, '03-dec-1981', 3000,
NULL, 20);
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902, '17-dec-1980', 800,
NULL, 20);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, '01-may-1981', 2850,
NULL, 30);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '20-feb-1981', 1600,
300, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698, '22-feb-1981', 1250,
500, 30);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '28-sep-1981', 1250,
1400, 30);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698, '08-sep-1981', 1500,
0, 30);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698, '03-dec-1981', 950,
NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, '09-jun-1981', 2450,
NULL, 10);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782, '23-jan-1982', 1300,
NULL, 10);
--2
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW-YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
--4
INSERT INTO EMP VALUES
(7657, 'WILSON', 'MANAGER', 7839, '17-NOV-91', 3500.00, 600.00, 10);
--1
UPDATE DEPT SET LOC = 'PITTSBURGH' WHERE DNAME = 'SALES';
--2
UPDATE EMP SET SAL = SAL * 1.1 WHERE COMM > 0.5 * SAL;
--3
UPDATE EMP
SET COMM = (SELECT AVG(COMM) FROM EMP)
WHERE HIREDATE < '01/01/82'
AND COMM IS NULL;
--4
DELETE FROM DEPT WHERE DEPTNO=20;
--1
SELECT ENAME, SAL, COMM, SAL + COMM FROM EMP WHERE JOB = 'SALESMAN';
--2
SELECT ENAME FROM EMP ORDER BY COMM / SAL DESC;
--3
SELECT ENAME FROM EMP WHERE COMM < .25 * SAL;
--4
SELECT COUNT(EMPNO) FROM EMP WHERE DEPTNO = 10;
--5
SELECT COUNT(EMPNO) FROM EMP WHERE COMM IS NOT NULL;
--6
SELECT COUNT(DISTINCT JOB) FROM EMP;
--7
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;
--8
SELECT SUM(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
AND DNAME = 'SALES';
--9
SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
--10
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);
--11
SELECT ENAME FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='JONES');
--12
SELECT ENAME FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME='JONES')
AND ENAME <> ’JONES’;
--13
SELECT ENAME FROM EMP WHERE MGR = (SELECT MGR FROM EMP WHERE ENAME='CLARK')
AND ENAME <> 'CLARK';
--14
SELECT ENAME FROM EMP WHERE (JOB, MGR) IN
(SELECT JOB, MGR FROM EMP WHERE ENAME = 'TURNER')
AND ENAME <> 'TURNER';
--15
SELECT ENAME FROM EMP WHERE HIREDATE < ALL
(SELECT HIREDATE FROM EMP WHERE DEPTNO = 10);
--16
SELECT SUBALTERNE.ENAME, SUPERIEUR.ENAME FROM EMP SUBALTERNE, EMP SUPERIEUR
WHERE SUBALTERNE.MGR = SUPERIEUR.EMPNO;
--17
SELECT SUB.ENAME FROM EMP SUB, EMP SUP WHERE SUB.MGR = SUP.EMPNO
AND SUB.DEPTNO <> SUP.DEPTNO;
--18
SELECT LEVEL, EMPNO, ENAME, JOB, MGR FROM EMP
CONNECT BY MGR = PRIOR EMPNO
START WITH MGR IS NULL
ORDER BY LEVEL;
--19
SELECT LEVEL, ENAME FROM EMP
WHERE ENAME <> 'JONES'
CONNECT BY MGR = PRIOR EMPNO
START WITH ENAME = 'JONES'
ORDER BY LEVEL;
--20
SELECT LEVEL, AVG(SAL) FROM EMP
CONNECT BY MGR = PRIOR EMPNO
START WITH MGR IS NULL
GROUP BY LEVEL;
--21
SELECT ENAME FROM EMP
WHERE ENAME <> 'JONES' AND ENAME <> 'SCOTT'
CONNECT BY MGR = PRIOR EMPNO
START WITH ENAME = 'JONES';
--22
SELECT ENAME FROM EMP
WHERE ENAME <> 'JONES'
CONNECT BY MGR = PRIOR EMPNO AND ENAME <> 'SCOTT'
START WITH ENAME = 'JONES';
--1
CREATE VIEW EMPDIR AS (SELECT EMPNO, ENAME FROM EMP);
SELECT * FROM EMPDIR;
--2
UPDATE EMPDIR SET ENAME = 'DARMONT' WHERE EMPNO = 7839;
SELECT * FROM EMPDIR;
SELECT * FROM EMP;
--3
CREATE VIEW EMPDEPT AS ( SELECT EMPNO, ENAME, E.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO );
SELECT * FROM EMPDEPT;
-- Interroger la vue EMPDEPT permet à un utilisateur de ne pas exprimer la
-- jointure entre EMP et DEPT.
--4
UPDATE EMPDEPT SET ENAME = 'SINBAD' WHERE EMPNO = 7698;
SELECT * FROM EMPDEPT;
SELECT * FROM EMP;
--5
INSERT INTO EMPDEPT VALUES(9999, 'NEWEMP', 99, 'NEWDEPT');
-- Cela ne fonctionne pas car la requête concerne les deux tables jointes
-- EMP et DEPT.
--6
SELECT TABLE_NAME, OWNER FROM ALL_TABLES;
--7
SELECT TABLE_NAME, TABLE_TYPE FROM USER_CATALOG;
--8
SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS;
--9
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMP';
--10
SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'DEPTNO';
-- Les vues sont également présentes dans USER_TAB_COLUMNS.
