Nicolas HUBERT
Antonin LAPICHE



II - Interrogation

1)

SELECT TITRE, PRIX, TYPE_J, PERIODE FROM JOURNAL;

2)

SELECT * FROM JOURNAL ORDER BY TYPE_J, PRIX;

3)

SELECT DISTINCT CODE_J, N_DEP FROM LIVRAISON ORDER BY CODE_J DESC, N_DEP ASC;

4)

SELECT MAX(PRIX), SUM(PRIX)/COUNT(*), MIN(PRIX) FROM JOURNAL;

5)

SELECT COUNT(CODE_J), COUNT(DISTINCT TYPE_J) FROM JOURNAL;

6)

SELECT CODE_J FROM JOURNAL WHERE TYPE_J IS NULL;

7)

SELECT TYPE_J, MAX(PRIX), SUM(PRIX)/COUNT(*), MIN(PRIX) FROM JOURNAL GROUP BY TYPE_J;

8)

SELECT N_DEP, SUM(QTE_L)/COUNT(N_DEP), COUNT(N_DEP) FROM LIVRAISON GROUP BY N_DEP HAVING SUM(QTE_L)/COUNT(N_DEP) > 15;

9)

SELECT ADR FROM DEPOT GROUP BY ADR HAVING COUNT(*) >=5;

10)

SELECT COUNT(DISTINCT ADR) FROM DEPOT GROUP BY ADR HAVING COUNT(*)>4; 

11)

SELECT CODE_J, TITRE FROM JOURNAL WHERE PRIX > 2*(SELECT MIN(PRIX) FROM JOURNAL);

12)

SELECT N_DEP FROM LIVRAISON WHERE QTE_L>=50 GROUP BY  N_DEP HAVING COUNT(N_DEP) >= 3;

13)

SELECT TITRE FROM JOURNAL WHERE CODE_J IN (SELECT CODE_J FROM LIVRAISON GROUP BY CODE_J HAVING COUNT(N_DEP) >= 5);

14)

SELECT DISTINCT LIVRAISON.N_DEP, DEPOT.N_DEP, DEPOT.NOM_DEP FROM LIVRAISON INNER JOIN DEPOT ON LIVRAISON.N_DEP = DEPOT.N_DEP WHERE CODE_J NOT IN (SELECT CODE_J FROM JOURNAL WHERE TITRE='LIBERATION');

15)

SELECT DISTINCT N_DEP FROM LIVRAISON  WHERE CODE_J IN (SELECT CODE_J FROM JOURNAL WHERE PERIODE = 'HEBDO');

16)
a)

SELECT * FROM JOURNAL;

b)

SELECT CODE_J FROM JOURNAL WHERE CODE_J NOT IN (SELECT CODE_J FROM LIVRAISON WHERE N_DEP = 379);

c)

SELECT CODE_J, N_DEP FROM LIVRAISON WHERE N_DEP = 379;

d)

SELECT NOM_DEP FROM DEPOT WHERE N_DEP IN ( SELECT N_DEP FROM LIVRAISON GROUP BY N_DEP HAVING COUNT(*) = (SELECT COUNT(*) FROM JOURNAL);						)

e)

SELECT * FROM JOURNAL WHERE TYPE_J = 'FEMININ' AND PERIODE = 'MENSUEL';

f)

SELECT * FROM JOURNAL WHERE TYPE_J = 'FEMININ' AND PERIODE = 'MENSUEL' and CODE_J NOT IN (SELECT CODE_J FROM LIVRAISON WHERE N_DEP = 379);

g)

SELECT * FROM DEPOT WHERE ;

h)

SELECT * FROM DEPOT WHERE ADR = 'PARIS';

i)

SELECT NOM_DEP FROM DEPOT WHERE ADR = 'PARIS' AND N_DEP IN ( SELECT N_DEP FROM LIVRAISON GROUP BY N_DEP HAVING COUNT(*) = ( SELECT COUNT(*) FROM JOURNAL));

j)

SELECT NOM_DEP FROM DEPOT WHERE ADR = 'PARIS' AND N_DEP IN (SELECT N_DEP FROM LIVRAISON INNER JOIN JOURNAL ON LIVRAISON.CODE_J = JOURNAL.CODE_J WHERE TYPE_J = 'FEMININ' GROUP BY N_DEP HAVING COUNT(*) = ( SELECT COUNT(*) FROM JOURNAL WHERE TYPE_J = 'FEMININ'));

17)

SELECT TITRE FROM JOURNAL WHERE TYPE_J = 'FEMININ' AND PERIODE = 'MENSUEL' AND CODE_J IN (SELECT CODE_J FROM LIVRAISON INNER JOIN DEPOT ON LIVRAISON.N_DEP = DEPOT.N_DEP WHERE DEPOT.ADR = 'PARIS' GROUP BY CODE_J HAVING COUNT(*) = ( SELECT COUNT(*) FROM DEPOT WHERE ADR = 'PARIS'));

18)

SELECT NOM_DEP FROM DEPOT WHERE N_DEP IN ( SELECT N_DEP FROM LIVRAISON GROUP BY N_DEP HAVING COUNT(*) = ( SELECT COUNT(*)FROM JOURNAL));


VI - INDEX

CREATE INDEX NDXLIVRAISON ON LIVRAISON (CODE_J asc);

III - Gestion de la confidentialité

1)

GRANT ALL ON DEPOT TO USER2

3)

INSERT INTO USER1.DEPOT (N_DEP, NOM_DEP, ADR) VALUES (400, 'CAFE DES AMIS','VILLEJUIF');
UPDATE USER1.DEPOT SET NOM_DEP = 'CARREFOUR' WHERE N_DEP = 400 ;
COMMIT;

4)

REVOKE ALL ON DEPOT FROM USER2 ;
GRANT SELECT, INSERT ON DEPOT TO USER2;

5)

On a pas les droits

6)

GRANT ALL ON LIVRAISON TO USER2;
INSERT INTO LIVRAISON (N_DEP, CODE_J, DATE_L, QTE_L, QTE_R, NOM_LIVREUR) VALUES (379, 115, sysdate, 50, 0, USER);
INSERT INTO USER1.LIVRAISON (N_DEP, CODE_J, DATE_L, QTE_L, QTE_R, NOM_LIVREUR) VALUES (379, 120, sysdate, 50, 0, USER);

7)

CREATE VIEW LIVRAISON379 AS ( SELECT * FROM LIVRAISON WHERE N_DEP=379);
UPDATE USER1.LIVRAISON379 SET QTE_L = QTE_L + 100 WHERE NOM_LIVREUR = 'USER2';

8)

CREATE VIEW VLIVRAISON AS (	SELECT N_DEP, CODE_J, DATE_L, QTE_L, NOM_LIVRAISON);

9)

GRANT SELECT ON CUR_LIVRAISON TO USER2;

10)

SELECT * FROM CUR_LIVRAISON;
SELECT * FROM USER1.CUR_LIVRAISON;

11)

CREATE VIEW LIVRAISON_d AS ( SELECT CODE_J, N_DEP FROM LIVRAISON);
GRANT SELECT ON LIVRAISON_d TO USER2;