Nouveau : Partenariat Judo Club Solesmes / InterSport Aulnoy (cliquez sur le logo)
1. Schéma relationnel
2. Réalisation du script (script 1)
DROP DATABASE if exists DCL2 ;
CREATE DATABASE DCL2 ;
USE DCL2 ;
CREATE TABLE PAYS (
pays_id INT NOT NULL AUTO_INCREMENT,
pays_libelle VARCHAR(50),
CONSTRAINT PK_PAYS PRIMARY KEY (pays_id)
) ENGINE=INNODB ;
INSERT INTO PAYS VALUES
(1,'FRANCE'),
(2,'USA'),
(3,'ALGERIE') ;
CREATE TABLE REALISATEURS (
real_id INT NOT NULL AUTO_INCREMENT,
real_nom VARCHAR(50),
real_prenom VARCHAR(50),
real_date_nais DATE,
real_nb_films_ecrits INT,
real_nb_films_real INT,
real_pays INT NOT NULL,
CONSTRAINT PK_REALISATEURS PRIMARY KEY (real_id),
FOREIGN KEY (real_pays) REFERENCES PAYS (pays_id)
) ENGINE=INNODB ;
INSERT INTO REALISATEURS VALUES
(1,'BESSON','Luc','1959-03-18',40,99,1),
(2,'LUCAS','Georges','1944-05-14',79,64,2),
(3,'CAMERON','James','1954-08-16',22,23,2),
(4,'BOON','Dany','1966-06-26',5,1,1) ;
CREATE TABLE FILMS (
film_id INT NOT NULL AUTO_INCREMENT,
film_titre VARCHAR(50),
film_genre1 VARCHAR(50),
film_genre2 VARCHAR(50),
film_date_sortie DATE,
film_pays_id INT NOT NULL,
film_real_id INT NOT NULL,
film_distributeur VARCHAR(50),
film_resume VARCHAR(150),
film_duree INT,
film_nb_ent_fr INT,
film_recette_usa FLOAT,
film_recette_monde FLOAT,
film_budget FLOAT ,
CONSTRAINT PK_FILMS PRIMARY KEY (film_id),
FOREIGN KEY (film_pays_id) REFERENCES PAYS (pays_id),
FOREIGN KEY (film_real_id) REFERENCES REALISATEURS (real_id)
) ENGINE=INNODB ;
INSERT INTO FILMS VALUES
(1,'SUBWAY','Policier','Drame','1985-04-10',1,1,'GAUMONT','description Subway',104,2917562,390659,1272637,2.6),
(2,'NIKITA','Drame','Romantique','1990-02-21',1,1,'GAUMONT','description Nikita',118,3787845,5017971,0,7.6),
(3,'STAR WARS - LE RETOUR DU JEDI','Action','SF','1983-10-19',2,2,'20th Century Fox','description Jedi',133,4263000,191648000,472000000,32),
(4,'AVATAR','Action','SF','2009-10-16',2,3,'20th Century Fox','description Avatar',170,12018251,760505847,2946271769,237),
(5,'BIENVENUE CHEZ LES CHTI','Comédie',NULL,'2008-02-27',1,4,'PATHE','description Les Chti',100,21000000,0,245000000,11) ;
CREATE VIEW STATISTIQUES AS SELECT film_id, film_duree, film_nb_ent_fr, film_recette_usa, film_recette_monde, film_budget
FROM FILMS ;
CREATE TABLE ACTEURS (
act_id INT NOT NULL AUTO_INCREMENT,
act_nom VARCHAR(50),
act_prenom VARCHAR(50),
act_date_nais DATE,
act_nb_films INT,
act_pays INT NOT NULL,
CONSTRAINT PK_ACTEURS PRIMARY KEY (act_id),
FOREIGN KEY (act_pays) REFERENCES PAYS (pays_id)
) ENGINE=INNODB ;
INSERT INTO ACTEURS VALUES
(1,'ADJANI','Isabelle','1955-06-27',42,1),
(2,'LAMBERT','Christophe','1957-03-29',64,1),
(3,'BOHRINGER','Richard','1942-06-16',132,1),
(4,'GALABRU','Michel','1922-10-27',277,1),
(5,'PARILLAUD','Anne','1960-05-06',35,1),
(6,'FORD','Harrison','1942-06-13',64,2),
(7,'FISHER','Carrie','1956-10-21',74,2),
(8,'SALDANA','Zoe','1978-06-19',31,2),
(9,'WEAVER','Sigourney','1949-10-08',66,2),
(10,'RENO','Jean','1948-06-30',75,1),
(11,'BOON','Dany','1966-06-28',23,1),
(12,'MERAD','Kad','1964-03-27',55,3) ;
CREATE TABLE CASTING (
cast_film_id INT NOT NULL,
cast_act_id INT NOT NULL,
cast_role VARCHAR(50),
cast_nb_jrs_tour INT,
CONSTRAINT PK_ACTEURS PRIMARY KEY (cast_film_id,cast_act_id),
FOREIGN KEY (cast_film_id) REFERENCES FILMS (film_id),
FOREIGN KEY (cast_act_id) REFERENCES ACTEURS (act_id)
) ENGINE=INNODB ;
INSERT INTO CASTING VALUES
(1,1,'HELENA',100),
(1,2,'FRED',100),
(1,3,'INSPECTEUR GESBERG',NULL),
(1,4,'LE FLEURISTE',35),
(1,10,'LE BATTEUR',20),
(2,5,'NIKITA',68),
(2,10,'VICTOR LE NETTOYEUR',9),
(3,6,'HAN SOLO',201),
(3,7,'PRINCESSE LEIA',203),
(4,8,'NEYTIRI',50),
(4,9,'Dr. Grace Augustine ',45),
(5,11,'ANTOINE BAILLEUL',125),
(5,12,'PHILIPPE ABRAMS',126);3. Visualisation et contrôle des tables
SHOW TABLES ;+----------------+ | Tables_in_DCL2 | +----------------+ | ACTEURS | | CASTING | | FILMS | | PAYS | | REALISATEURS | | STATISTIQUES | +----------------+
3.1. Table PAYS
DESC PAYS ;+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | pays_id | int(11) | NO | PRI | NULL | auto_increment | | pays_libelle | varchar(50) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+
3.2. Table ACTEURS
DESC ACTEURS ;+---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | act_id | int(11) | NO | PRI | NULL | auto_increment | | act_nom | varchar(50) | YES | | NULL | | | act_prenom | varchar(50) | YES | | NULL | | | act_date_nais | date | YES | | NULL | | | act_nb_films | int(11) | YES | | NULL | | | act_pays | int(11) | NO | MUL | NULL | | +---------------+-------------+------+-----+---------+----------------+
3.3. Table REALISATEURS
DESC REALISATEURS ;+----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+----------------+ | real_id | int(11) | NO | PRI | NULL | auto_increment | | real_nom | varchar(50) | YES | | NULL | | | real_prenom | varchar(50) | YES | | NULL | | | real_date_nais | date | YES | | NULL | | | real_nb_films_ecrits | int(11) | YES | | NULL | | | real_nb_films_real | int(11) | YES | | NULL | | | real_pays | int(11) | NO | MUL | NULL | | +----------------------+-------------+------+-----+---------+----------------+
3.4. Table CASTING
DESC CASTING ;+------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | cast_film_id | int(11) | NO | PRI | NULL | | | cast_act_id | int(11) | NO | PRI | NULL | | | cast_role | varchar(50) | YES | | NULL | | | cast_nb_jrs_tour | int(11) | YES | | NULL | | +------------------+-------------+------+-----+---------+-------+
3.5. Table FILMS
DESC FILMS ;+--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | film_id | int(11) | NO | PRI | NULL | auto_increment | | film_titre | varchar(50) | YES | | NULL | | | film_genre1 | varchar(50) | YES | | NULL | | | film_genre2 | varchar(50) | YES | | NULL | | | film_date_sortie | date | YES | | NULL | | | film_pays_id | int(11) | NO | MUL | NULL | | | film_real_id | int(11) | NO | MUL | NULL | | | film_distributeur | varchar(50) | YES | | NULL | | | film_resume | varchar(150) | YES | | NULL | | | film_duree | int(11) | YES | | NULL | | | film_nb_ent_fr | int(11) | YES | | NULL | | | film_recette_usa | float | YES | | NULL | | | film_recette_monde | float | YES | | NULL | | | film_budget | float | YES | | NULL | | +--------------------+--------------+------+-----+---------+----------------+
3.6. Table STATISTIQUES
| La table STATISTIQUES est une vue de la table FILM. J’aurais aussi pu aborder ce cas comme un héritage / spécialisation ce qui aurait allégé la table FILMS ; toutefois comme la relation est de type (1,1 - 1,1) j’ai préféré aborder le problème avec une vue. |
DESC STATISTIQUES ;+--------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------+------+-----+---------+-------+ | film_id | int(11) | NO | | 0 | | | film_duree | int(11) | YES | | NULL | | | film_nb_ent_fr | int(11) | YES | | NULL | | | film_recette_usa | float | YES | | NULL | | | film_recette_monde | float | YES | | NULL | | | film_budget | float | YES | | NULL | | +--------------------+---------+------+-----+---------+-------+
4. Contrôle de la bonne insertion des données
4.1. Table PAYS
SELECT * FROM PAYS ;+---------+--------------+ | pays_id | pays_libelle | +---------+--------------+ | 1 | FRANCE | | 2 | USA | | 3 | ALGERIE | +---------+--------------+
4.2. Table ACTEURS
SELECT * FROM ACTEURS ;+--------+-----------+------------+---------------+--------------+----------+ | act_id | act_nom | act_prenom | act_date_nais | act_nb_films | act_pays | +--------+-----------+------------+---------------+--------------+----------+ | 1 | ADJANI | Isabelle | 1955-06-27 | 42 | 1 | | 2 | LAMBERT | Christophe | 1957-03-29 | 64 | 1 | | 3 | BOHRINGER | Richard | 1942-06-16 | 132 | 1 | | 4 | GALABRU | Michel | 1922-10-27 | 277 | 1 | | 5 | PARILLAUD | Anne | 1960-05-06 | 35 | 1 | | 6 | FORD | Harrison | 1942-06-13 | 64 | 2 | | 7 | FISHER | Carrie | 1956-10-21 | 74 | 2 | | 8 | SALDANA | Zoe | 1978-06-19 | 31 | 2 | | 9 | WEAVER | Sigourney | 1949-10-08 | 66 | 2 | | 10 | RENO | Jean | 1948-06-30 | 75 | 1 | | 11 | BOON | Dany | 1966-06-28 | 23 | 1 | | 12 | MERAD | Kad | 1964-03-27 | 55 | 3 | +--------+-----------+------------+---------------+--------------+----------+
4.3. Table REALISATEURS
SELECT * FROM REALISATEURS ;+---------+----------+-------------+----------------+----------------------+--------------------+-----------+ | real_id | real_nom | real_prenom | real_date_nais | real_nb_films_ecrits | real_nb_films_real | real_pays | +---------+----------+-------------+----------------+----------------------+--------------------+-----------+ | 1 | BESSON | Luc | 1959-03-18 | 40 | 99 | 1 | | 2 | LUCAS | Georges | 1944-05-14 | 79 | 64 | 2 | | 3 | CAMERON | James | 1954-08-16 | 22 | 23 | 2 | | 4 | BOON | Dany | 1966-06-26 | 5 | 1 | 1 | +---------+----------+-------------+----------------+----------------------+--------------------+-----------+
4.4. Table CASTING
SELECT * FROM CASTING ;+--------------+-------------+----------------------+------------------+ | cast_film_id | cast_act_id | cast_role | cast_nb_jrs_tour | +--------------+-------------+----------------------+------------------+ | 1 | 1 | HELENA | 100 | | 1 | 2 | FRED | 100 | | 1 | 3 | INSPECTEUR GESBERG | NULL | | 1 | 4 | LE FLEURISTE | 35 | | 1 | 10 | LE BATTEUR | 20 | | 2 | 5 | NIKITA | 68 | | 2 | 10 | VICTOR LE NETTOYEUR | 9 | | 3 | 6 | HAN SOLO | 201 | | 3 | 7 | PRINCESSE LEIA | 203 | | 4 | 8 | NEYTIRI | 50 | | 4 | 9 | Dr. Grace Augustine | 45 | | 5 | 11 | ANTOINE BAILLEUL | 125 | | 5 | 12 | PHILIPPE ABRAMS | 126 | +--------------+-------------+----------------------+------------------+
4.5. Table FILMS
SELECT * FROM FILMS ;+---------+-------------------------------+-------------+-------------+------------------+-----------+--------------+-------------------+----------------------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_titre | film_genre1 | film_genre2 | film_date_sortie | film_pays | film_real_id | film_distributeur | film_resume | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+-------------------------------+-------------+-------------+------------------+-----------+--------------+-------------------+----------------------+------------+----------------+------------------+--------------------+-------------+ | 1 | SUBWAY | Policier | Drame | 1985-04-10 | 1 | 1 | GAUMONT | description Subway | 104 | 2917562 | 390659 | 1272640 | 2.6 | | 2 | NIKITA | Drame | Romantique | 1990-02-21 | 1 | 1 | GAUMONT | description Nikita | 118 | 3787845 | 5017970 | 0 | 7.6 | | 3 | STAR WARS - LE RETOUR DU JEDI | Action | SF | 1983-10-19 | 2 | 2 | 20th Century Fox | description Jedi | 133 | 4263000 | 191648000 | 472000000 | 32 | | 4 | AVATAR | Action | SF | 2009-10-16 | 2 | 3 | 20th Century Fox | description Avatar | 170 | 12018251 | 760506000 | 2946270000 | 237 | | 5 | BIENVENUE CHEZ LES CHTI | Comédie | NULL | 2008-02-27 | 1 | 4 | PATHE | description Les Chti | 100 | 21000000 | 0 | 245000000 | 11 | +---------+-------------------------------+-------------+-------------+------------------+-----------+--------------+-------------------+----------------------+------------+----------------+------------------+--------------------+-------------+
4.6. Table STATISTIQUES
| La table STATISTIQUES est une vue de la table FILM. |
SELECT * FROM STATISTIQUES ;+---------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+------------+----------------+------------------+--------------------+-------------+ | 1 | 104 | 2917562 | 390659 | 1272640 | 2.6 | | 2 | 118 | 3787845 | 5017970 | 0 | 7.6 | | 3 | 133 | 4263000 | 191648000 | 472000000 | 32 | | 4 | 170 | 12018251 | 760506000 | 2946270000 | 237 | | 5 | 100 | 21000000 | 0 | 245000000 | 11 | +---------+------------+----------------+------------------+--------------------+-------------+
5. Questions
5.1. Sélection de données
5.1.1. Sélectionner toutes les informations sur les films réalisés par un réalisateur français triés par le nom du film.
SELECT * FROM FILMS WHERE film_pays_id = 1 ORDER BY 2;SELECT * FROM FILMS, PAYS WHERE film_pays_id = pays_id AND pays_libelle = 'FRANCE' ORDER BY 2;SELECT * FROM FILMS INNER JOIN PAYS ON (film_pays_id = pays_id AND pays_libelle = 'FRANCE') ORDER BY 2 ;+---------+-------------------------+-------------+-------------+------------------+--------------+--------------+-------------------+----------------------+------------+----------------+------------------+--------------------+-------------+---------+--------------+ | film_id | film_titre | film_genre1 | film_genre2 | film_date_sortie | film_pays_id | film_real_id | film_distributeur | film_resume | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | pays_id | pays_libelle | +---------+-------------------------+-------------+-------------+------------------+--------------+--------------+-------------------+----------------------+------------+----------------+------------------+--------------------+-------------+---------+--------------+ | 5 | BIENVENUE CHEZ LES CHTI | Comédie | NULL | 2008-02-27 | 1 | 4 | PATHE | description Les Chti | 100 | 21000000 | 0 | 245000000 | 11 | 1 | FRANCE | | 2 | NIKITA | Drame | Romantique | 1990-02-21 | 1 | 1 | GAUMONT | description Nikita | 118 | 3787845 | 5017970 | 0 | 7.6 | 1 | FRANCE | | 1 | SUBWAY | Policier | Drame | 1985-04-10 | 1 | 1 | GAUMONT | description Subway | 104 | 2917562 | 390659 | 1272640 | 2.6 | 1 | FRANCE | +---------+-------------------------+-------------+-------------+------------------+--------------+--------------+-------------------+----------------------+------------+----------------+------------------+--------------------+-------------+---------+--------------+
5.1.2. Sélectionner le nom du film, la date de sortie, le nom du réalisateur, le nom des acteurs, leur date de naissance, ainsi que le budget du film. Le tout trié en ordre descendant par titre du film et nom des acteurs.
SELECT DISTINCT film_titre AS 'Titre du Film', film_date_sortie AS 'Date de sortie', real_nom AS "Réalisateur", real_date_nais AS "Date de naissance du Réalisateur", act_nom AS "Acteur(s)", act_date_nais AS "Date de naissance des acteurs", film_budget FROM FILMS JOIN REALISATEURS ON (film_real_id = real_id) JOIN CASTING ON (film_id = cast_film_id) JOIN ACTEURS ON (act_id = cast_act_id) ORDER BY 1 DESC, 4 ;+-------------------------------+----------------+--------------+-----------------------------------+-----------+-------------------------------+-------------+ | Titre du Film | Date de sortie | Réalisateur | Date de naissance du Réalisateur | Acteur(s) | Date de naissance des acteurs | film_budget | +-------------------------------+----------------+--------------+-----------------------------------+-----------+-------------------------------+-------------+ | SUBWAY | 1985-04-10 | BESSON | 1959-03-18 | ADJANI | 1955-06-27 | 2.6 | | SUBWAY | 1985-04-10 | BESSON | 1959-03-18 | LAMBERT | 1957-03-29 | 2.6 | | SUBWAY | 1985-04-10 | BESSON | 1959-03-18 | BOHRINGER | 1942-06-16 | 2.6 | | SUBWAY | 1985-04-10 | BESSON | 1959-03-18 | GALABRU | 1922-10-27 | 2.6 | | SUBWAY | 1985-04-10 | BESSON | 1959-03-18 | RENO | 1948-06-30 | 2.6 | | NIKITA | 1990-02-21 | BESSON | 1959-03-18 | PARILLAUD | 1960-05-06 | 7.6 | | NIKITA | 1990-02-21 | BESSON | 1959-03-18 | RENO | 1948-06-30 | 7.6 | | STAR WARS - LE RETOUR DU JEDI | 1983-10-19 | LUCAS | 1944-05-14 | FORD | 1942-06-13 | 32 | | STAR WARS - LE RETOUR DU JEDI | 1983-10-19 | LUCAS | 1944-05-14 | FISHER | 1956-10-21 | 32 | | AVATAR | 2009-10-16 | CAMERON | 1954-08-16 | SALDANA | 1978-06-19 | 237 | | AVATAR | 2009-10-16 | CAMERON | 1954-08-16 | WEAVER | 1949-10-08 | 237 | | BIENVENUE CHEZ LES CHTI | 2008-02-27 | BOON | 1966-06-26 | BOON | 1966-06-28 | 11 | | BIENVENUE CHEZ LES CHTI | 2008-02-27 | BOON | 1966-06-26 | MERAD | 1964-03-27 | 11 | +-------------------------------+----------------+--------------+-----------------------------------+-----------+-------------------------------+-------------+
5.1.3. Trouver le nombre d’acteurs par film dans la base de données. Afficher le titre, la date de sortie, le nom du réalisateur et le distributeur.
SELECT film_titre AS 'TITRE DU FILM', count(film_titre) 'NB ACTEURS / FIM' , film_date_sortie AS 'DATE DE SORTIE', real_nom AS 'NOM DU REALISATEUR', film_distributeur AS 'NOM DU DISTRIBUTEUR' FROM FILMS JOIN CASTING ON (film_id = cast_film_id) JOIN
ACTEURS ON (cast_act_id = act_id) JOIN REALISATEURS ON (film_real_id = real_id) GROUP BY (film_titre) ORDER BY film_titre ;+-------------------------------+------------------+----------------+--------------------+---------------------+ | TITRE DU FILM | NB ACTEURS / FIM | DATE DE SORTIE | NOM DU REALISATEUR | NOM DU DISTRIBUTEUR | +-------------------------------+------------------+----------------+--------------------+---------------------+ | AVATAR | 2 | 2009-10-16 | CAMERON | 20th Century Fox | | BIENVENUE CHEZ LES CHTI | 2 | 2008-02-27 | BOON | PATHE | | NIKITA | 2 | 1990-02-21 | BESSON | GAUMONT | | STAR WARS - LE RETOUR DU JEDI | 2 | 1983-10-19 | LUCAS | 20th Century Fox | | SUBWAY | 5 | 1985-04-10 | BESSON | GAUMONT | +-------------------------------+------------------+----------------+--------------------+---------------------+
5.1.4. Sélectionner le titre du film, la date de sortie, le nom et prénom du réalisateur, le nom et prénom de l’acteur, sa date de naissance, le budget du film et le nombre d’entrées en France des films qui ont un acteur algérien.
SELECT film_titre AS 'TITRE DU FILM', film_date_sortie AS 'DATE DE SORTIE', real_prenom AS 'PRENOM REALISATEUR', real_nom AS 'NOM REALISATEUR', act_prenom AS 'PRENOM ACTEUR', act_nom AS 'NOM ACTEUR', act_date_nais AS 'DATE NAISSANCE ACTEUR', film_budget AS 'BUDGET', film_nb_ent_fr AS 'NB entrées FRANCE' FROM FILMS JOIN CASTING ON (film_id = cast_film_id) JOIN ACTEURS ON (cast_act_id = act_id) JOIN REALISATEURS ON (film_real_id = real_id) WHERE act_pays = 3 ORDER BY film_titre ;SELECT film_titre AS 'TITRE DU FILM', film_date_sortie AS 'DATE DE SORTIE', real_prenom AS 'PRENOM REALISATEUR', real_nom AS 'NOM REALISATEUR', act_prenom AS 'PRENOM ACTEUR', act_nom AS 'NOM ACTEUR', act_date_nais AS 'DATE NAISSANCE ACTEUR', film_budget AS 'BUDGET', film_nb_ent_fr AS 'NB entrées FRANCE' FROM FILMS JOIN CASTING ON (film_id = cast_film_id) JOIN ACTEURS ON (cast_act_id = act_id) JOIN REALISATEURS ON (film_real_id = real_id) JOIN PAYS ON (pays_id = act_pays) WHERE pays_libelle = 'ALGERIE' ;+-------------------------+----------------+--------------------+-----------------+---------------+------------+-----------------------+--------+--------------------+ | TITRE DU FILM | DATE DE SORTIE | PRENOM REALISATEUR | NOM REALISATEUR | PRENOM ACTEUR | NOM ACTEUR | DATE NAISSANCE ACTEUR | BUDGET | NB entrées FRANCE | +-------------------------+----------------+--------------------+-----------------+---------------+------------+-----------------------+--------+--------------------+ | BIENVENUE CHEZ LES CHTI | 2008-02-27 | Dany | BOON | Kad | MERAD | 1964-03-27 | 11 | 21000000 | +-------------------------+----------------+--------------------+-----------------+---------------+------------+-----------------------+--------+--------------------+
5.1.5. Sélectionner le film qui a réalisé la recette la plus élevée dans le monde.
SELECT MAX(film_recette_monde) AS 'FILM / PLUS GROSSE RECETTE' FROM STATISTIQUES ;+----------------------------+ | FILM / PLUS GROSSE RECETTE | +----------------------------+ | 2946270000 | +----------------------------+
SELECT film_titre, MAX(film_recette_monde) AS 'FILM / PLUS GROSSE RECETTE' FROM FILMS ;+------------+----------------------------+ | film_titre | FILM / PLUS GROSSE RECETTE | +------------+----------------------------+ | SUBWAY | 2946270000 | +------------+----------------------------+
SELECT F.film_titre, MAX(S.film_recette_monde) AS 'FILM / PLUS GROSSE RECETTE' FROM FILMS AS F, STATISTIQUES AS S
WHERE F.film_id = S.film_id ;+------------+----------------------------+ | film_titre | FILM / PLUS GROSSE RECETTE | +------------+----------------------------+ | SUBWAY | 2946270000 | +------------+----------------------------+
5.1.6. Sélectionner l’acteur qui a joué dans deux films différents.
SELECT COUNT(act_nom) AS 'NOMBRE DE FILMS', act_nom AS 'NOM ACTEUR', film_titre FROM FILMS JOIN CASTING ON (film_id = cast_film_id) JOIN ACTEURS ON (act_id = cast_act_id) GROUP BY act_nom HAVING COUNT(act_nom) <> 1 ;+-----------------+------------+------------+ | NOMBRE DE FILMS | NOM ACTEUR | film_titre | +-----------------+------------+------------+ | 2 | RENO | SUBWAY | +-----------------+------------+------------+
5.1.7. Sélectionner la personne qui est à la fois réalisateur et acteur.
SELECT act_prenom AS 'PRENOM ACTEUR / REALISATEUR', act_nom AS 'NOM ACTEUR / REALISATEUR' FROM ACTEURS JOIN CASTING ON (act_id = cast_act_id) JOIN FILMS ON (cast_film_id = film_id) JOIN REALISATEURS ON (real_id = film_real_id) WHERE act_nom = real_nom ;+-----------------------------+--------------------------+ | PRENOM ACTEUR / REALISATEUR | NOM ACTEUR / REALISATEUR | +-----------------------------+--------------------------+ | Dany | BOON | +-----------------------------+--------------------------+
5.1.8. Sélectionner les acteurs qui ont joué dans des films dont le nom du film commence par la lettre S. Indiquer leur rôle et leur nationalité.
SELECT act_prenom AS 'PRENOM ACTEUR', act_nom AS 'NOM ACTEUR', pays_libelle AS 'NATIONALITE' FROM ACTEURS JOIN CASTING ON (act_id = cast_act_id) JOIN FILMS ON (cast_film_id = film_id) JOIN PAYS ON (act_pays = pays_id) WHERE film_titre LIKE 'S%' ;+---------------+------------+-------------+ | PRENOM ACTEUR | NOM ACTEUR | NATIONALITE | +---------------+------------+-------------+ | Isabelle | ADJANI | FRANCE | | Christophe | LAMBERT | FRANCE | | Richard | BOHRINGER | FRANCE | | Michel | GALABRU | FRANCE | | Jean | RENO | FRANCE | | Harrison | FORD | USA | | Carrie | FISHER | USA | +---------------+------------+-------------+
5.1.9. Sélectionner les acteurs qui sont nés entre 1948 et mai 1978 ainsi que le nombre de jours total de tournage qu’ils ont réalisés.
SELECT act_prenom AS 'PRENOM ACTEUR', act_nom AS 'NOM ACTEUR', SUM(cast_nb_jrs_tour) FROM ACTEURS LEFT JOIN CASTING ON (act_id = cast_act_id) JOIN FILMS ON (cast_film_id = film_id) WHERE act_date_nais BETWEEN '1948-01-01' AND '1978-05-31' GROUP BY act_id ;+---------------+------------+-----------------------+ | PRENOM ACTEUR | NOM ACTEUR | SUM(cast_nb_jrs_tour) | +---------------+------------+-----------------------+ | Isabelle | ADJANI | 100 | | Christophe | LAMBERT | 100 | | Anne | PARILLAUD | 68 | | Carrie | FISHER | 203 | | Sigourney | WEAVER | 45 | | Jean | RENO | 29 | | Dany | BOON | 125 | | Kad | MERAD | 126 | +---------------+------------+-----------------------+
5.2. Insertion de données
5.2.1. Ajouter un nouveau film dans la table FILM :
Numéro 6, Die Hard 4, film d’action sorti le 4 juillet 2007. Le réalisateur est Len Wiseman et distribué par la Twentieth Century Fox Film Corporation. Résumé : « Pour sa quatrième aventure, l’inspecteur John McClane se trouve confronté à un nouveau genre de terrorisme. Le réseau informatique national qui contrôle absolument toutes les communications, les transports et l’énergie des États-Unis est détruit de façon systématique, plongeant le pays dans le chaos. »
INSERT INTO REALISATEURS VALUES
(5,'WISEMAN','Len',NULL,NULL,NULL,2) ;
INSERT INTO FILMS VALUES
(6,'DIE HARD 4','ACTION',NULL,'2007-07-04',2,5,'20th Century Fox','description DIE HARD 4',NULL,NULL,NULL,NULL,NULL) ;+---------+----------+-------------+----------------+----------------------+--------------------+-----------+ | real_id | real_nom | real_prenom | real_date_nais | real_nb_films_ecrits | real_nb_films_real | real_pays | +---------+----------+-------------+----------------+----------------------+--------------------+-----------+ | 1 | BESSON | Luc | 1959-03-18 | 40 | 99 | 1 | | 2 | LUCAS | Georges | 1944-05-14 | 79 | 64 | 2 | | 3 | CAMERON | James | 1954-08-16 | 22 | 23 | 2 | | 4 | BOON | Dany | 1966-06-26 | 5 | 1 | 1 | | 5 | WISEMAN | Len | NULL | NULL | NULL | 2 | +---------+----------+-------------+----------------+----------------------+--------------------+-----------+
+---------+-------------------------------+-------------+-------------+------------------+--------------+--------------+-------------------+------------------------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_titre | film_genre1 | film_genre2 | film_date_sortie | film_pays_id | film_real_id | film_distributeur | film_resume | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+-------------------------------+-------------+-------------+------------------+--------------+--------------+-------------------+------------------------+------------+----------------+------------------+--------------------+-------------+ | 1 | SUBWAY | Policier | Drame | 1985-04-10 | 1 | 1 | GAUMONT | description Subway | 104 | 2917562 | 390659 | 1272640 | 2.6 | | 2 | NIKITA | Drame | Romantique | 1990-02-21 | 1 | 1 | GAUMONT | description Nikita | 118 | 3787845 | 5017970 | 0 | 7.6 | | 3 | STAR WARS - LE RETOUR DU JEDI | Action | SF | 1983-10-19 | 2 | 2 | 20th Century Fox | description Jedi | 133 | 4263000 | 191648000 | 472000000 | 32 | | 4 | AVATAR | Action | SF | 2009-10-16 | 2 | 3 | 20th Century Fox | description Avatar | 170 | 12018251 | 760506000 | 2946270000 | 237 | | 5 | BIENVENUE CHEZ LES CHTI | Comédie | NULL | 2008-02-27 | 1 | 4 | PATHE | description Les Chti | 100 | 21000000 | 0 | 245000000 | 11 | | 6 | DIE HARD 4 | ACTION | NULL | 2007-07-04 | 2 | 5 | 20th Century Fox | description DIE HARD 4 | NULL | NULL | NULL | NULL | NULL | +---------+-------------------------------+-------------+-------------+------------------+--------------+--------------+-------------------+------------------------+------------+----------------+------------------+--------------------+-------------+
5.2.2. Créer une nouvelle table ACTEUR_FRANCE à partir de la table ACTEUR en ne sélectionnant que les acteurs français.
CREATE VIEW ACTEURS_FRANCE AS SELECT * FROM ACTEURS JOIN PAYS ON (pays_id = act_pays) WHERE pays_libelle = 'FRANCE' ;+----------------+ | Tables_in_DCL2 | +----------------+ | ACTEURS | | ACTEURS_FRANCE | | CASTING | | FILMS | | PAYS | | REALISATEURS | | STATISTIQUES | +----------------+
+--------+-----------+------------+---------------+--------------+----------+---------+--------------+ | act_id | act_nom | act_prenom | act_date_nais | act_nb_films | act_pays | pays_id | pays_libelle | +--------+-----------+------------+---------------+--------------+----------+---------+--------------+ | 1 | ADJANI | Isabelle | 1955-06-27 | 42 | 1 | 1 | FRANCE | | 2 | LAMBERT | Christophe | 1957-03-29 | 64 | 1 | 1 | FRANCE | | 3 | BOHRINGER | Richard | 1942-06-16 | 132 | 1 | 1 | FRANCE | | 4 | GALABRU | Michel | 1922-10-27 | 277 | 1 | 1 | FRANCE | | 5 | PARILLAUD | Anne | 1960-05-06 | 35 | 1 | 1 | FRANCE | | 10 | RENO | Jean | 1948-06-30 | 75 | 1 | 1 | FRANCE | | 11 | BOON | Dany | 1966-06-28 | 23 | 1 | 1 | FRANCE | +--------+-----------+------------+---------------+--------------+----------+---------+--------------+
5.2.3. Insérer « Bruce Willis » né le 19 mars 1955 avec 89 films à son actif et 152 jours de tournage dans la table des acteurs et insérer un enregistrement dans la table CASTING le reliant au film numéro 6.
| pb nb jours tournage / c’est dans la table casting |
INSERT INTO ACTEURS VALUES
(13,'WILLIS','Bruce','1955-03-19',89,2) ;
INSERT INTO CASTING VALUES
(6,13,NULL,152) ;+--------+-----------+------------+---------------+--------------+----------+ | act_id | act_nom | act_prenom | act_date_nais | act_nb_films | act_pays | +--------+-----------+------------+---------------+--------------+----------+ | 1 | ADJANI | Isabelle | 1955-06-27 | 42 | 1 | | 2 | LAMBERT | Christophe | 1957-03-29 | 64 | 1 | | 3 | BOHRINGER | Richard | 1942-06-16 | 132 | 1 | | 4 | GALABRU | Michel | 1922-10-27 | 277 | 1 | | 5 | PARILLAUD | Anne | 1960-05-06 | 35 | 1 | | 6 | FORD | Harrison | 1942-06-13 | 64 | 2 | | 7 | FISHER | Carrie | 1956-10-21 | 74 | 2 | | 8 | SALDANA | Zoe | 1978-06-19 | 31 | 2 | | 9 | WEAVER | Sigourney | 1949-10-08 | 66 | 2 | | 10 | RENO | Jean | 1948-06-30 | 75 | 1 | | 11 | BOON | Dany | 1966-06-28 | 23 | 1 | | 12 | MERAD | Kad | 1964-03-27 | 55 | 3 | | 13 | WILLIS | Bruce | 1955-03-19 | 89 | 2 | +--------+-----------+------------+---------------+--------------+----------+
+--------------+-------------+----------------------+------------------+ | cast_film_id | cast_act_id | cast_role | cast_nb_jrs_tour | +--------------+-------------+----------------------+------------------+ | 1 | 1 | HELENA | 100 | | 1 | 2 | FRED | 100 | | 1 | 3 | INSPECTEUR GESBERG | NULL | | 1 | 4 | LE FLEURISTE | 35 | | 1 | 10 | LE BATTEUR | 20 | | 2 | 5 | NIKITA | 68 | | 2 | 10 | VICTOR LE NETTOYEUR | 9 | | 3 | 6 | HAN SOLO | 201 | | 3 | 7 | PRINCESSE LEIA | 203 | | 4 | 8 | NEYTIRI | 50 | | 4 | 9 | Dr. Grace Augustine | 45 | | 5 | 11 | ANTOINE BAILLEUL | 125 | | 5 | 12 | PHILIPPE ABRAMS | 126 | | 6 | 13 | NULL | 152 | +--------------+-------------+----------------------+------------------+
5.3. Suppression de données
5.3.1. Supprimer « CARRIE FISHER » de la table ACTEUR.
| Relance du script avec les ordres ON DELETE CASCADE et ON UPDATE CASCADE. cela permettra d’éviter l’erreur dûe aux contraintes d’intégrité référentielles entre tables parentes et tables -enfants_ lors de la suppression ou mise à jour d’enregistrements. (avec injection des données des questions précédentes.) |
DROP DATABASE if exists DCL2 ;
CREATE DATABASE DCL2 ;
USE DCL2 ;
CREATE TABLE PAYS (
pays_id INT NOT NULL AUTO_INCREMENT,
pays_libelle VARCHAR(50),
CONSTRAINT PK_PAYS PRIMARY KEY (pays_id)
) ENGINE=INNODB ;
INSERT INTO PAYS VALUES
(1,'FRANCE'),
(2,'USA'),
(3,'ALGERIE') ;
CREATE TABLE REALISATEURS (
real_id INT NOT NULL AUTO_INCREMENT,
real_nom VARCHAR(50),
real_prenom VARCHAR(50),
real_date_nais DATE,
real_nb_films_ecrits INT,
real_nb_films_real INT,
real_pays INT NOT NULL,
CONSTRAINT PK_REALISATEURS PRIMARY KEY (real_id),
FOREIGN KEY (real_pays) REFERENCES PAYS (pays_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB ;
INSERT INTO REALISATEURS VALUES
(1,'BESSON','Luc','1959-03-18',40,99,1),
(2,'LUCAS','Georges','1944-05-14',79,64,2),
(3,'CAMERON','James','1954-08-16',22,23,2),
(4,'BOON','Dany','1966-06-26',5,1,1) ;
CREATE TABLE FILMS (
film_id INT NOT NULL AUTO_INCREMENT,
film_titre VARCHAR(50),
film_genre1 VARCHAR(50),
film_genre2 VARCHAR(50),
film_date_sortie DATE,
film_pays_id INT NOT NULL,
film_real_id INT NOT NULL,
film_distributeur VARCHAR(50),
film_resume VARCHAR(150),
film_duree INT,
film_nb_ent_fr INT,
film_recette_usa FLOAT,
film_recette_monde FLOAT,
film_budget FLOAT ,
CONSTRAINT PK_FILMS PRIMARY KEY (film_id),
FOREIGN KEY (film_pays_id) REFERENCES PAYS (pays_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (film_real_id) REFERENCES REALISATEURS (real_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB ;
INSERT INTO FILMS VALUES
(1,'SUBWAY','Policier','Drame','1985-04-10',1,1,'GAUMONT','description Subway',104,2917562,390659,1272637,2.6),
(2,'NIKITA','Drame','Romantique','1990-02-21',1,1,'GAUMONT','description Nikita',118,3787845,5017971,0,7.6),
(3,'STAR WARS - LE RETOUR DU JEDI','Action','SF','1983-10-19',2,2,'20th Century Fox','description Jedi',133,4263000,191648000,472000000,32),
(4,'AVATAR','Action','SF','2009-10-16',2,3,'20th Century Fox','description Avatar',170,12018251,760505847,2946271769,237),
(5,'BIENVENUE CHEZ LES CHTI','Comédie',NULL,'2008-02-27',1,4,'PATHE','description Les Chti',100,21000000,0,245000000,11) ;
CREATE VIEW STATISTIQUES AS SELECT film_id, film_duree, film_nb_ent_fr, film_recette_usa, film_recette_monde, film_budget
FROM FILMS ;
CREATE TABLE ACTEURS (
act_id INT NOT NULL AUTO_INCREMENT,
act_nom VARCHAR(50),
act_prenom VARCHAR(50),
act_date_nais DATE,
act_nb_films INT,
act_pays INT NOT NULL,
CONSTRAINT PK_ACTEURS PRIMARY KEY (act_id),
FOREIGN KEY (act_pays) REFERENCES PAYS (pays_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB ;
INSERT INTO ACTEURS VALUES
(1,'ADJANI','Isabelle','1955-06-27',42,1),
(2,'LAMBERT','Christophe','1957-03-29',64,1),
(3,'BOHRINGER','Richard','1942-06-16',132,1),
(4,'GALABRU','Michel','1922-10-27',277,1),
(5,'PARILLAUD','Anne','1960-05-06',35,1),
(6,'FORD','Harrison','1942-06-13',64,2),
(7,'FISHER','Carrie','1956-10-21',74,2),
(8,'SALDANA','Zoe','1978-06-19',31,2),
(9,'WEAVER','Sigourney','1949-10-08',66,2),
(10,'RENO','Jean','1948-06-30',75,1),
(11,'BOON','Dany','1966-06-28',23,1),
(12,'MERAD','Kad','1964-03-27',55,3) ;
CREATE TABLE CASTING (
cast_film_id INT NOT NULL,
cast_act_id INT NOT NULL,
cast_role VARCHAR(50),
cast_nb_jrs_tour INT,
CONSTRAINT PK_ACTEURS PRIMARY KEY (cast_film_id,cast_act_id),
FOREIGN KEY (cast_film_id) REFERENCES FILMS (film_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (cast_act_id) REFERENCES ACTEURS (act_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB ;
INSERT INTO CASTING VALUES
(1,1,'HELENA',100),
(1,2,'FRED',100),
(1,3,'INSPECTEUR GESBERG',NULL),
(1,4,'LE FLEURISTE',35),
(1,10,'LE BATTEUR',20),
(2,5,'NIKITA',68),
(2,10,'VICTOR LE NETTOYEUR',9),
(3,6,'HAN SOLO',201),
(3,7,'PRINCESSE LEIA',203),
(4,8,'NEYTIRI',50),
(4,9,'Dr. Grace Augustine ',45),
(5,11,'ANTOINE BAILLEUL',125),
(5,12,'PHILIPPE ABRAMS',126);Avant
SELECT * FROM ACTEURS ;+--------+-----------+------------+---------------+--------------+----------+ | act_id | act_nom | act_prenom | act_date_nais | act_nb_films | act_pays | +--------+-----------+------------+---------------+--------------+----------+ | 1 | ADJANI | Isabelle | 1955-06-27 | 42 | 1 | | 2 | LAMBERT | Christophe | 1957-03-29 | 64 | 1 | | 3 | BOHRINGER | Richard | 1942-06-16 | 132 | 1 | | 4 | GALABRU | Michel | 1922-10-27 | 277 | 1 | | 5 | PARILLAUD | Anne | 1960-05-06 | 35 | 1 | | 6 | FORD | Harrison | 1942-06-13 | 64 | 2 | | 7 | FISHER | Carrie | 1956-10-21 | 74 | 2 | | 8 | SALDANA | Zoe | 1978-06-19 | 31 | 2 | | 9 | WEAVER | Sigourney | 1949-10-08 | 66 | 2 | | 10 | RENO | Jean | 1948-06-30 | 75 | 1 | | 11 | BOON | Dany | 1966-06-28 | 23 | 1 | | 12 | MERAD | Kad | 1964-03-27 | 55 | 3 | | 13 | WILLIS | Bruce | 1955-03-19 | 89 | 2 | +--------+-----------+------------+---------------+--------------+----------+
DELETE FROM ACTEURS WHERE act_id = 7 ;Après
+--------+-----------+------------+---------------+--------------+----------+ | act_id | act_nom | act_prenom | act_date_nais | act_nb_films | act_pays | +--------+-----------+------------+---------------+--------------+----------+ | 1 | ADJANI | Isabelle | 1955-06-27 | 42 | 1 | | 2 | LAMBERT | Christophe | 1957-03-29 | 64 | 1 | | 3 | BOHRINGER | Richard | 1942-06-16 | 132 | 1 | | 4 | GALABRU | Michel | 1922-10-27 | 277 | 1 | | 5 | PARILLAUD | Anne | 1960-05-06 | 35 | 1 | | 6 | FORD | Harrison | 1942-06-13 | 64 | 2 | | 8 | SALDANA | Zoe | 1978-06-19 | 31 | 2 | | 9 | WEAVER | Sigourney | 1949-10-08 | 66 | 2 | | 10 | RENO | Jean | 1948-06-30 | 75 | 1 | | 11 | BOON | Dany | 1966-06-28 | 23 | 1 | | 12 | MERAD | Kad | 1964-03-27 | 55 | 3 | | 13 | WILLIS | Bruce | 1955-03-19 | 89 | 2 | +--------+-----------+------------+---------------+--------------+----------+
5.3.2. Supprimer la table STATISTIQUES dont la recette mondiale est égale à 0.
| Problème dans la formulation de la question, je suppose qu’il faut supprimer de la table (vue) STATISTIQUES le(s) enregistrement(s) dont la valeur correspondant au champs film_recette_monde = 0 |
Etat initial
SELECT * from STATISTIQUES ;+---------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+------------+----------------+------------------+--------------------+-------------+ | 1 | 104 | 2917562 | 390659 | 1272640 | 2.6 | | 2 | 118 | 3787845 | 5017970 | 0 | 7.6 | | 3 | 133 | 4263000 | 191648000 | 472000000 | 32 | | 4 | 170 | 12018251 | 760506000 | 2946270000 | 237 | | 5 | 100 | 21000000 | 0 | 245000000 | 11 | | 6 | NULL | NULL | NULL | NULL | NULL | +---------+------------+----------------+------------------+--------------------+-------------+
Suppression de l’enregistrement
DELETE FROM STATISTIQUES WHERE film_recette_monde = 0 ;Résultat
+---------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+------------+----------------+------------------+--------------------+-------------+ | 1 | 104 | 2917562 | 390659 | 1272640 | 2.6 | | 3 | 133 | 4263000 | 191648000 | 472000000 | 32 | | 4 | 170 | 12018251 | 760506000 | 2946270000 | 237 | | 5 | 100 | 21000000 | 0 | 245000000 | 11 | | 6 | NULL | NULL | NULL | NULL | NULL | +---------+------------+----------------+------------------+--------------------+-------------+
5.4. Modification de données
5.4.1. Modifier le GENRE2 de la table FILM afin de compléter la zone lorsque celle-ci n’est pas renseignée, puis remplacer « SF » par « SCIENCE FICTION ».
| Question mal formulée.. Compléter par quoi ? obliger à renseigner la cellule par contrainte NOT NULL ou appliquer une valeur par défaut ??? |
Avant
SELECT film_titre, film_genre1, film_genre2 from FILMS ;+-------------------------------+-------------+-------------+ | film_titre | film_genre1 | film_genre2 | +-------------------------------+-------------+-------------+ | SUBWAY | Policier | Drame | | STAR WARS - LE RETOUR DU JEDI | Action | SF | | AVATAR | Action | SF | | BIENVENUE CHEZ LES CHTI | Comédie | NULL | | DIE HARD 4 | ACTION | NULL | +-------------------------------+-------------+-------------+
DESC FILMS ;+--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | film_id | int(11) | NO | PRI | NULL | auto_increment | | film_titre | varchar(50) | YES | | NULL | | | film_genre1 | varchar(50) | YES | | NULL | | | film_genre2 | varchar(50) | YES | | NULL | | | film_date_sortie | date | YES | | NULL | | | film_pays_id | int(11) | NO | MUL | NULL | | | film_real_id | int(11) | NO | MUL | NULL | | | film_distributeur | varchar(50) | YES | | NULL | | | film_resume | varchar(150) | YES | | NULL | | | film_duree | int(11) | YES | | NULL | | | film_nb_ent_fr | int(11) | YES | | NULL | | | film_recette_usa | float | YES | | NULL | | | film_recette_monde | float | YES | | NULL | | | film_budget | float | YES | | NULL | | +--------------------+--------------+------+-----+---------+----------------+
Après
UPDATE FILMS SET film_genre2 = REPLACE(film_genre2, 'SF', 'SCIENCE FICTION');
ALTER TABLE FILMS MODIFY COLUMN film_genre2 VARCHAR(50) NOT NULL;+-------------+-----------------+ | film_genre1 | film_genre2 | +-------------+-----------------+ | Policier | Drame | | Action | SCIENCE FICTION | | Action | SCIENCE FICTION | | Comédie | NULL | | ACTION | NULL | +-------------+-----------------+
5.4.2. Diviser la RECETTE_USA et RECETTE_MONDE par 1 000 000 afin d’exprimer le chiffre en millions dans la table STATISTIQUES.
Visualisation de la table
SELECT * from STATISTIQUES ;+---------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+------------+----------------+------------------+--------------------+-------------+ | 1 | 104 | 2917562 | 390659 | 1272640 | 2.6 | | 3 | 133 | 4263000 | 191648000 | 472000000 | 32 | | 4 | 170 | 12018251 | 760506000 | 2946270000 | 237 | | 5 | 100 | 21000000 | 0 | 245000000 | 11 | | 6 | NULL | NULL | NULL | NULL | NULL | +---------+------------+----------------+------------------+--------------------+-------------+
UPDATE FILMS SET film_recette_usa = REPLACE(film_recette_usa, film_recette_usa, film_recette_usa / 1000000 );
UPDATE FILMS SET film_recette_monde = REPLACE(film_recette_monde, film_recette_monde, film_recette_monde / 1000000 );MariaDB [DCL2]> SELECT * from STATISTIQUES ; +---------+------------+----------------+------------------+--------------------+-------------+ | film_id | film_duree | film_nb_ent_fr | film_recette_usa | film_recette_monde | film_budget | +---------+------------+----------------+------------------+--------------------+-------------+ | 1 | 104 | 2917562 | 0.390659 | 1.27264 | 2.6 | | 3 | 133 | 4263000 | 191.648 | 472 | 32 | | 4 | 170 | 12018251 | 760.506 | 2946.27 | 237 | | 5 | 100 | 21000000 | 0 | 245 | 11 | | 6 | NULL | NULL | NULL | NULL | NULL | +---------+------------+----------------+------------------+--------------------+-------------+
5.5. Sécurité des données
5.5.1. Créer un utilisateur ALFRED et lui attribuer les droits de créer une session et de sélectionner des données dans la table CASTING.
| En mysql inutile de créer une session de façon spécifique comme dans d’autres langages,elle est prévue lors de la création du compte. Le droit USAGE est appliqué par défaut lors de la création du compte. |
CREATE USER 'alfred'@'%' IDENTIFIED BY 'alfred' ;SELECT user FROM mysql.user ;MariaDB [DCL2]> SELECT user FROM mysql.user ; +------------------+ | User | +------------------+ | CONTROLE_GESTION | | ASMITH | | BSMITH | | alfred | | root | | ASMITH | | BSMITH | | mariadb.sys | | mysql | | root | +------------------+
MariaDB [DCL2]> SHOW GRANTS FOR alfred ; +-------------------------------------------------------------------------------------------------------+ | Grants for alfred@% | +-------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `alfred`@`%` IDENTIFIED BY PASSWORD '*0A3FC730C575323703277D26307342D6BB257DDC' | +-------------------------------------------------------------------------------------------------------+
GRANT SELECT ON DCL2.CASTING TO 'alfred'@'%';
FLUSH PRIVILEGES;ubuntu@mariadb-client:~$ mysql -h mariadb-srv -ualfred -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 100 Server version: 10.6.11-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04 .. Copyright (c) 2000, 2018, Oracle, MariaDB Corpora .. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. .. MariaDB [(none)]> use DCL2 ; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A .. Database changed MariaDB [DCL2]> SELECT * FROM CASTING ; +--------------+-------------+----------------------+------------------+ | cast_film_id | cast_act_id | cast_role | cast_nb_jrs_tour | +--------------+-------------+----------------------+------------------+ | 1 | 1 | HELENA | 100 | | 1 | 2 | FRED | 100 | | 1 | 3 | INSPECTEUR GESBERG | NULL | | 1 | 4 | LE FLEURISTE | 35 | | 1 | 10 | LE BATTEUR | 20 | | 3 | 6 | HAN SOLO | 201 | | 4 | 8 | NEYTIRI | 50 | | 4 | 9 | Dr. Grace Augustine | 45 | | 5 | 11 | ANTOINE BAILLEUL | 125 | | 5 | 12 | PHILIPPE ABRAMS | 126 | | 6 | 13 | NULL | 152 | +--------------+-------------+----------------------+------------------+ 11 rows in set (0.001 sec) .. MariaDB [DCL2]> SELECT * FROM FILMS ; ERROR 1142 (42000): SELECT command denied to user 'alfred'@'mariadb-client.mshome.net' for table `DCL2`.`FILMS`
5.5.2. Attribuer à tous les utilisateurs le droit de sélectionner des données dans la table FILM.
GRANT SELECT ON DCL2.FILMS TO PUBLIC ;
FLUSH PRIVILEGES;| cette commande est spécifique à ORACLE et SQL serveur. je n’ai pas trouvé d’équivalent sur la version de mariadb que j’utilise (10.6). A priori cette fonctionnalité arrive avec la 10.7. (https://mariadb.com/kb/en/grant/#column-privileges). J’aimerais avoir la solution… j’ai passé beaucoup de temps à chercher… A chaque fois on trouve le moyen de donner les privilère à un seul utilisateur mais pas à TOUT LE MONDE. |
5.5.3. Attribuer des droits à l’utilisateur ALFRED afin qu’il puisse modifier dans la table FILM uniquement les colonnes TITRE et RESUME.
GRANT SELECT, UPDATE (film_titre, film_resume) ON DCL2.FILMS TO 'alfred'@'%' ;
FLUSH PRIVILEGES;MariaDB [(none)]> show grants ; +-------------------------------------------------------------------------------------------------------+ | Grants for alfred@% | +-------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `alfred`@`%` IDENTIFIED BY PASSWORD '*0A3FC730C575323703277D26307342D6BB257DDC' | | GRANT SELECT ON `DCL2`.`CASTING` TO `alfred`@`%` | | GRANT SELECT, UPDATE (film_resume, film_titre) ON `DCL2`.`FILMS` TO `alfred`@`%` | +-------------------------------------------------------------------------------------------------------+
5.5.4. Supprimer, pour l’utilisateur ALFRED, les droits sur la modification de la colonne RESUME
REVOKE UPDATE (film_resume) ON DCL2.FILMS FROM 'alfred'@'%' ;MariaDB [DCL2]> show grants ;
+-------------------------------------------------------------------------------------------------------+
| Grants for alfred@%
|
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `alfred`@`%` IDENTIFIED BY PASSWORD '*0A3FC730C575323703277D26307342D6BB257DDC' |
| GRANT SELECT ON `DCL2`.`CASTING` TO `alfred`@`%`
|
| GRANT SELECT, UPDATE (film_titre) ON `DCL2`.`FILMS` TO `alfred`@`%` |
+-------------------------------------------------------------------------------------------------------+
