Nom et prénom : ELATTAOUI SALMA

• Création des tables :

DROP TABLE ligne_commande;

DROP TABLE commande;

DROP TABLE client;

DROP TABLE produit;

• Table client

CREATE TABLE client(

id NUMBER CONSTRAINT pk_client PRIMARY KEY,

nom VARCHAR2(30),

prenom VARCHAR2(30),

adresse VARCHAR2(60),

code_postal NUMBER(5) CHECK(code_postal BETWEEN 00001

AND 99999)

);

• Table commande

CREATE TABLE commande(

no_commande NUMBER CONSTRAINT pk_commande PRIMARY

KEY,

cdate DATE DEFAULT SYSDATE,

 id_client CONSTRAINT fk_commande REFERENCES client(id)

);

• Table produit

CREATE TABLE produit(

id NUMBER CONSTRAINT pk_produit PRIMARY KEY,

libelle VARCHAR2(30),

stock NUMBER(10),

prix NUMBER(6,2) CHECK (prix > 0)

);

• Table ligne commande

CREATE TABLE ligne_commande(

id NUMBER CONSTRAINT pk_ligne PRIMARY KEY,

no_commande CONSTRAINT fk1_ligne REFERENCES

commande(no_commande),

no_ligne_commande NUMBER CONSTRAINT no_ligne NOT NULL,

id_produit CONSTRAINT fk2_ligne REFERENCES produit(id),

quantite NUMBER,

CONSTRAINT unique_ligne UNIQUE(no_commande,

no_ligne_commande)

);

• 2-Insertion :

INSERT INTO client VALUES (5, 'Dupond','Martin','1 avenue du parc

Cergy', 95011);

INSERT INTO client VALUES (2, 'Martin', 'Philippe', '35 Champs

Elysées Paris', 75008);
 INSERT INTO client VALUES (4, 'Martin', 'Philippe', '35 Champs Elysées
Paris', 75008);

INSERT INTO produit VALUES (1,'Evian 2lx6', 4000, 2.99);

INSERT INTO produit VALUES (2,'Lu Cracotte Framboise',800, 3.99);

INSERT INTO produit VALUES (3, 'Nectar de Mangue', 4000,2);

INSERT INTO produit VALUES (4, 'Contrex', 0, 2);

SELECT*FROM client;

SELECT*FROM produit;

1-Ajouter dans la table Client les données :

INSERT INTO client VALUES ( 5, ‘Dupont’, ‘Jean’, ‘1 avenue du parc

Cergy’, 95011)

INSERT INTO client VALUES (2, ‘Martin’,’ Philippe’,’ 35Champs Elysées

Paris’, 75008)

INSERT INTO client VALUES (4, ‘Martin’, ‘Philippe’, ‘35Champs Elysées

Paris’, 75008)

2- Ajouter la commande numéro 1 du client numéro 2 dont la date est le

30 août 2008.
• INSERT INTO commande VALUES (1,’30/august/2008’,2);

3. Ajouter la commande numéro 2 du client numéro 5.

INSERT INTO commande(no_commande,id_cmient) VALUES(2,5)

4. Ajouter dans la table Ligne_commande les lignes suivantes: 1, 1, 1, 1, 5

2, 1, 1, 2, 53

3, 5, 2, 1, 7
INSERT INTO ligne_commande VALUES(1, 1, 1, 1, 5)

INSERT INTO ligne_commande VALUES (2, 1, 1, 2, 53)

INSERT INTO ligne_commande VALUES (3, 5, 2, 1, 7)

5-Modifier le code postal de 35 rue St Honoré Fontainebleau par77305.

UPDATE client

SET code_postal=77305

WHERE adresse=’35 rue ST Honoré Fontainebleau’;

6. Supprimer le produit dont le libellé contient le mot « Cracotte »

DELETE FROM produit

WHERE libelle= ‘cracotte’ ;

7. Supprimer le client Dupond François

DELETE FROM client

WHERE nom= ‘Dupond’ and prenom=’françois’;

 L'objectif de ce travail dirigé est de vous familiariser avec : - les requêtes
SELECT simples du langage SQL : avec une seule table, avec ou sans la clause
WHERE, avec le tri ORDER BY et avec l'opérateur LIKE, etc - les fonctions de
calcul sur un n-uplet (+, -, *, /, …) - les fonctions d’agrégation sur une colonne
(MIN, MAX, AVG, COUNT, …) - les sous-requêtes indépendantes avec différents
opérateurs (ANY, ALL, IN …)

Exercice 1

Soit le schéma relationnel suivant

- Individu (num_ind, nom, prenom)

– Jouer (num_ind, num_film, role)

– Film (num_film, num_ind, titre, genre, annee)

– Projection (num_cine, num_film, pdate)

– Cinéma (num_cine, nom, adresse)

1. Quel est le contenu de la table individu ?

• 1. select * from individu;

2. Quels sont les prénoms des individus en conservant les doublons ?

• select prenom from individu;

3. Quels sont les prénoms des individus en conservant les doublons, mais en les
classant par ordre alphabétique ?
 • select prenom from individu order by prenom;

4. Quels sont les prénoms des individus sans doublons ? Observez le résultat
en effectuant un classement alphabétique et sans effectuer de classement.

• select distinct prenom from individu; select distinct prenom from

individu order by prenom;

5. Quels sont les individus dont le prénom est John ?

• select * from individu where prenom = 'John';

6. Quel est le nom des individus dont le prénom est John ?

• select nom from individu where prenom = 'John';

7. Quels sont les prénoms des individus qui contiennent la lettre s ?

• select prenom from individu where prenom like '%s%';

8. Même question que la précédente mais sans distinguer les lettres en

fonction de la casse.

• select prenom from individu where LOWER(prenom) like '%s%';

9. Quels sont les prénoms des individus dont le prénom commence par la lettre
s sans tenir compte de la casse ?

• select prenom from individu where LOWER(prenom) like 's%';

10. Quels sont les prénoms des individus dont le prénom se termine par la
lettre s sans tenir compte de la casse ?

• select prenom from individu where LOWER(prenom) like '%s';

11. Quels sont les prénoms des individus qui ne contiennent pas la lettre e?

• select prenom from individu where LOWER(prenom) not like '%e%';

12. Quels sont les prénoms des individus qui contiennent les lettres a et l dans
un ordre quelconque et sans tenir compte de la casse ?

• select prenom from individu where LOWER(prenom) like '%l%' AND

LOWER(prenom) like '%a%';

13. Quels sont les noms des individus qui contiennent la chaîne an ou la chaîne
on ?

• select nom from individu where nom like '%an%' or nom like '%on%'

14. Quels sont les titres des films qui contiennent au moins trois e ?

• select titre from film where titre like '%e%e%e%';

15. Afficher les projections de l'année 1996.

• select * from projection where to_char(pdate,'YYYY') = '1996';

16. Quelle est le nombre d’acteurs par film ?

 • Select num_film,count(*) from jouer group by num_film;

17. Quel est le nombre de projections par cinéma ?

• Select num_cine,count(*) from projection group by num_cine;

18. Calculer pour chaque réalisateur le nombre de films qu’il a réalisé depuis
1/1/2000.

• Select num_ind,count(*) from film where annee >= 2000 group by

num_ind;

19. Quels sont les réalisateurs dont le nombre de films réalisés est supérieur
ou égal à 2 ?

• selectnum_ind,count(*) from film group by num_ind having count(*) >

= 2;

Exercice 2

On réutilise le schéma relationnel du dernier TP sur la gestion de

commande : Client(id, nom, prenom, adresse, code_postal)

Commande(no_commande, date, id_client)

Ligne_commande(id, no_commande, no_ligne_commande, id_produit,

quantite) Produit(id, libelle, stock, prix)

1. Calculer le prix total de chaque produit en stock.

• select libelle, stock*prix as total from produit;

2. Calculer la somme des prix de tous les produits en stock.

• select sum(stock*prix) from produit;

3. Calculer le prix moyen des produits.

• select avg(prix) from produit;

4. Quel est le produit le plus cher ?

• select libelle from produit where prix = (select max(prix) from

produit);

5. Compter le nombre de commandes de l’année 2021.

• select count(*) from commande where to_char(cdate,’YYYY') =

'2021' ;

6. Donner les libellés des produits qui ont le même prix qu’une
bouteille de Nectar de mangue.

• select libelle from produit where prix = (select prix from produit
where libelle like '%Nectar de mangue%');

7. Quels sont les produits qui coutent plus cher que la moyenne des
prix de tous les produits ?
• select libelle from produit where prix > (select avg(prix) from
produit);

8. Donner le nom et l’adresse des clients qui ont commandé après le

01-01-2008.

• select nom, adresse from client where id in (select id_client from

commande where cdate > date '2008-01-01');

9. Editer toutes les commandes dans lesquelles il existe au moins une

ligne de commande avec une quantité entre 50 et 200 (utiliser
BETWEEN).

• select no_commande, cdate, id_client from commande where

no_commande in (select no_commande from ligne_commande where
quantite between 50 and 200);

10. Editer toutes les commandes dans lesquelles le produit Evian a été
commandé.

• Selectno_commande,cdate,id_clientfromcommandewhereno_comman
dein(select no_commande from ligne_commande where id_produit in
(select id from produit where libelle = 'Evian'));
 Rapport du TP 3

Exercice 1 : opérations ensemblistes

Nous réutilisons le schéma relationnel du dernier TP sur les films,

cinéma et projection :

– Individu(num_ind, nom, prenom)

– Jouer(num_ind, num_film, role)

– Film(num_film, num_ind, titre, genre, annee)

– Projection(num_cine, num_film, pdate)

– Cinéma(num_cine, nom, adresse)

Écrire en SQL les requêtes suivantes :

1. Intersection : Quels sont les prénoms des individus qui sont

également les rôles dans un film ?

• select prenom from individu

intersect

select role from jouer;

2. Union : Donner les prénoms des individus et les rôles des

acteurs dans les différents films.

select prenom from individu

union

select role from jouer;

 3. Différence : Quels sont les noms des individus qui commencent
par la lettre T ou W et qui ne s’appelle pas John ?

• select nom from individu where nom like 'T%' or nom like 'W%'

minus

select nom from individu where prenom = 'John';

4. Produit cartésien : Donnez la liste de toutes les associations

possibles entre un individu et un film (il n’y a pas nécessairement de
lien entre l’individu et le film qu’on lui associe).

Écrire en SQL les requêtes suivantes (en utilisant des sous-requêtes

mais pas des jointures !) :

• select * from individu, film;

5.Donner le nom et le prénom de tous les acteurs et les réalisateurs.

• select nom, prenom from individu where num_ind in (select num_ind

from jouer)

union

select nom, prenom from individu where num_ind in (select num_ind

from film);

6. Quels sont les noms et prénoms des acteurs qui sont également
réalisateurs ?

• select nom, prenom from individu where num_ind in (select num_ind

from jouer)
 intersect

select nom, prenom from individu where num_ind in (select num_ind

from film);

7. Quels sont les acteurs qui n’ont pas joué dans des films
dramatiques ?

• select nom, prenom from individu where num_ind in (select num_ind

from jouer)

minus

select nom, prenom from individu where num_ind in (select num_ind

from jouer where num_film in (select num_film from film where
genre = 'Drame'))

Exercice 2 :

Jointure Écrire en SQL les requêtes suivantes en utilisant 2 versions

différentes : jointure avec pivot et jointure avec la commande JOIN (naturelle
ou interne):

1. Quel est le nom et le prénom du réalisateur du film « Pulp Fiction » ?

select nom, prenom from individu i, film f where i.num_ind = f.num_ind and
titre = 'Pulp Fiction';

2. Quel est le titre des films réalisés par Lars von Trier ?

• select titre from film f, individu i where f.num_ind = i.num_ind and

nom = 'von Trier' and prenom = 'Lars';

•
 select titre from film natural join individu where nom = 'von Trier'
and prenom = 'Lars';

• select titre from film f join individu i on f.num_ind = i.num_ind where

nom = 'von Trier' and prenom = 'Lars';

3. Quel est le titre des films qui ont été projetés en 2002 ?

select distinct titre from film f, projection p where f.num_film = p.num_film and
to_char(pdate,'YYYY') = '2002';

4. Quels sont les noms et prénoms des acteurs qui sont également
réalisateurs ?

select nom, prenom from individu i, jouer j where i.num_ind = j.num_ind

intersect select nom, prenom from individu i, film f where i.num_ind =
f.num_ind;

5. Quels sont les acteurs qui n’ont pas joué dans des films dramatiques ?

• select distinct nom from individu i, jouer j where i.num_ind =

j.num_ind

minus

select distinct nom from individu i, jouer j, film f where i.num_ind =

j.num_ind and j.num_film = f.num_film and genre = 'Drame';

6. Quels sont les titres des films où Nicole Kidman a joué un rôle et qui ont
été projetés au cinéma Le Fontenelle

select distinct f.titre

from cinema c, projection p, film f, jouer j, individu i

where c.num_cine = p.num_cine

and p.num_film = f.num_film

and f.num_film = j.num_film

and j.num_ind = i.num_ind

and i.nom = 'Kidman'

and i.prenom = 'Nicole'

and c.nom = 'Le Fontenelle';

 En utilisant des fonctions d’agrégat et des jointures, exprimez les
requêtes suivantes sous la forme d’une commande SQL que vous testerez
sur votre base de données.

1. Quel est le nom et le prénom des acteurs et le nombre de films dans

lesquels ils ont joué ?

select nom, prenom, count(num_film)

from individu i, jouer j

where i.num_ind = j.num_ind

group by nom,prenom;

2. Quel est le nom et le prénom des acteurs et le nombre de films dans

lesquels ils ont joué pour les acteurs ayant joué dans strictement plus
d’un film?

select nom, prenom, count(num_film) from individu i, jouer j

where i.num_ind = j.num_ind

group by nom,prenom

having count(num_film) > 1;

3. Quel est le nom et le prénom des acteurs et le nombre de drames dans

lesquels ils ont joué ?
 select nom, prenom, count(j.num_film)

from individu i, jouer j, film f

where i.num_ind = j.num_ind

and j.num_film = f.num_film

and genre = 'Drame' group by nom,prenom;

4. Quel est le nombre moyen de projections de l’ensemble des cinémas ?

select n1/n2 from (select count (*) as n1 from projection), (select

count(*) as n2 from cinema) ;

5. Quels sont les cinémas qui ont projeté tous les films ?

select c.nom
from cinema c, projection p
where c.num_cine = p.num_cine
group by c.nom
having count(distinct p.num_film) = (select count(*) from film);

6. Quels sont les acteurs qui ont joué dans un nombre de films supérieur à
la moyenne des acteurs ?

select a.nom, a.prenom, COUNT(distinct j.num_film)

from individu a, jouer j

where a.num_ind = j.num_ind

group by a.nom, a.prenom

having COUNT(distinct j.num_film) >

(select AVG(COUNT(distinct j.num_film))

from individu a, jouer j

 where a.num_ind = j.num_ind group by a.num_ind );

7. On veut obtenir le nom et le prénom de tous les individus classés du

meilleur acteur au plus mauvais en nombre de films.

select i.nom, i.prenom, COUNT(j.num_film)

from individu i left join jouer j on i.num_ind = j.num_ind

group by i.nom, i.prenom

order by COUNT(distinct j.num_film) DESC;

8. Quel est le nombre de films réalisés par les réalisateurs, dont on

désire connaître le nom et le prénom, ayant réalisé au moins un film du
même genre que l'un des films réalisés par David Cronenberg ?

select nom, prenom, count(num_film)

from film f, individu i

where f.num_ind = i.num_ind

and genre IN (select genre

from film f, individu i

where f.num_ind = i.num_ind

and nom = 'Cronenberg'

and prenom = 'David')

group by i.num_ind, nom, prenom;