Corrigé Exercice SQLPLUS Oracle TD Oracle SQL PLUS

- Consulter la page de l'exercice.
- Solution de l'exercice.


CORRECTION


Création de la base de données, contraintes d’intégrité



1)    CREATE TABLE DEPT( DEPTNO NUMBER(2),
                                                                DNAME CHAR(20),
                                                                LOC CHAR(20),
      CONSTRAINT D_CLEP PRIMARY KEY (DEPTNO),
      CONSTRAINT D_DOM CHECK (DNAME IN
      ('ACCOUNTING','RESEARCH','SALES','OPERATIONS')));

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’);

3)    CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

4)    INSERT INTO EMP VALUES (7369, 'Bidon', NULL, NULL, NULL, NULL, NULL, NULL);

5)    ROLLBACK;

6)    ALTER TABLE EMP ADD CONSTRAINT E_CLEP PRIMARY KEY(EMPNO);
       ALTER TABLE EMP ADD CONSTRAINT E_CLET1 FOREIGN KEY(DEPTNO) REFERENCES
       DEPT(DEPTNO);
       ALTER TABLE EMP ADD CONSTRAINT E_CLET2 FOREIGN KEY(MGR) REFERENCES   
       EMP(EMPNO);

7)    INSERT INTO EMP VALUES
(7657, 'WILSON', 'MANAGER', 7839, '17/11/91', 3500.00, 600.00, 10);

8)    COMMIT;



Mise à jour de la base de données


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)    ROLLBACK;

5)    DELETE FROM DEPT WHERE DEPTNO=20;


Interrogation de la base de données


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>=ALL(SELECT 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;