66 lines
1.8 KiB
MySQL
66 lines
1.8 KiB
MySQL
|
//2
|
||
|
|
//A
|
||
|
|
CREATE TABLE Client_C (
|
||
|
|
login VARCHAR2(20) PRIMARY KEY,
|
||
|
|
nomClient VARCHAR2(30),
|
||
|
|
prenomClient VARCHAR2(30)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE Film (
|
||
|
|
numFilm number PRIMARY KEY,
|
||
|
|
nomFilm VARCHAR2(30)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE Location_L (
|
||
|
|
login VARCHAR2(20) REFERENCES Client_C,
|
||
|
|
numFilm number REFERENCES Film,
|
||
|
|
dateLocation DATE,
|
||
|
|
PRIMARY KEY(login, numFilm)
|
||
|
|
);
|
||
|
|
|
||
|
|
//B
|
||
|
|
INSERT INTO Client_C VALUES('RABAN_PROP1', 'AURIEL', 'Samuel');
|
||
|
|
INSERT INTO Client_C VALUES('RABAN_PROP2', 'BEL', 'Commode');
|
||
|
|
INSERT INTO Client_C VALUES('BANANA', 'O', 'Beng');
|
||
|
|
|
||
|
|
INSERT INTO Film VALUES(01, 'La petite sirene d alarme');
|
||
|
|
INSERT INTO Film VALUES(02, 'Retour vers le présent');
|
||
|
|
INSERT INTO Film VALUES(03, 'starsheep trooper');
|
||
|
|
INSERT INTO Film VALUES(04, 'Splatoon');
|
||
|
|
INSERT INTO Film VALUES(05, 'BESOIN DE VIOLENCE 4');
|
||
|
|
INSERT INTO Film VALUES(06, 'Il étais une fois chez moi');
|
||
|
|
|
||
|
|
INSERT INTO Location_L VALUES('RABAN_PROP1', 01, '02-JAN-2002');
|
||
|
|
INSERT INTO Location_L VALUES('RABAN_PROP2', 02, '05-MAR-2004');
|
||
|
|
INSERT INTO Location_L VALUES('BANANA', 03, '07-DEC-2012');
|
||
|
|
INSERT INTO Location_L VALUES('RABAN_PROP1', 06, '02-JAN-2002');
|
||
|
|
INSERT INTO Location_L VALUES('RABAN_PROP2', 04, '05-MAR-2004');
|
||
|
|
INSERT INTO Location_L VALUES('BANANA', 05, '07-DEC-2012');
|
||
|
|
|
||
|
|
//C
|
||
|
|
CREATE VIEW MES_LOCATIONS (nomClient, numFilm, dateLocation)
|
||
|
|
AS SELECT nomClient, numFilm, dateLocation FROM Location_L NATURAL JOIN Client_C WHERE login = USER;
|
||
|
|
|
||
|
|
commit;
|
||
|
|
|
||
|
|
//3
|
||
|
|
|
||
|
|
//B
|
||
|
|
GRANT SELECT ON Client_C TO RABAN_PROP2;
|
||
|
|
|
||
|
|
//4
|
||
|
|
|
||
|
|
//A
|
||
|
|
|
||
|
|
GRANT SELECT ON MES_LOCATIONS TO RABAN_PROP2;
|
||
|
|
GRANT SELECT,INSERT,DELETE ON Location_L TO RABAN_PROP2;
|
||
|
|
GRANT INSERT,UPDATE(nomClient, prenomClient) ON Client_C TO RABAN_PROP2;
|
||
|
|
GRANT INSERT ON Film TO RABAN_PROP2;
|
||
|
|
|
||
|
|
//D
|
||
|
|
|
||
|
|
SELECT * FROM Client_C;
|
||
|
|
SELECT * FROM Film;
|
||
|
|
SELECT * FROM location_L;
|
||
|
|
|
||
|
|
COMMIT;
|