Correction TD n°6 : Langage SQL

• Les villes de Ali et Salem.

Select Ville from Personne where Nom= Ali and Nom=Salem;

• Les noms des personnes domicili s Sousse.

Select Nom from Personne where Ville=Sousse ;

Exercice n 3 :

Soit le sch ma de base de donn es relationnel suivant :

AGENCE (Num\_Agence, Nom, Ville)

CLIENT (Num\_Client, Nom, Ville)

COMPTE (Num\_Compte, #Num\_Agence, #Num\_Client, Solde)

EMPRUNT (Num\_Emprunt, #Num\_Agence, #Num\_Client, Montant)

1. Ecrire les requ tes suivantes en SQL:

1. Les noms et les villes des Agences.

select Nom, Ville from Agence ;

• 1. Les montants des emprunts.

select montant from Emprunt ;

1. Liste des clients ayant la ville = Sousse.

select \* from Client where Ville=Sousse ;

• 1. Les num ros des emprunts ayant un montant sup rieur 1200.

select num\_emprunt from Emprunt where montant>1200 ;

• 1. Les noms des clients qui habitant Sidi Bouzid.

select \* from Client where Ville=Sidi Bouzid ;

• 1. Les montants des emprunts des clients num ro 12 et 13.

select montant from Emprunt where num\_client =12 and num\_client=13;

1. Ecrire les requ tes suivantes en SQL :

1. Le nombre des clients.

Select count(\*) from Client ;

1. Le montant maximum des emprunts.

Select max(montant) from Emprunt ;

1. Le montant minimum des emprunts.

Select min(montant) from Emprunt ;

1. La moyenne des soldes des Comptes.

Select avg(solde) from Compte ;

1. La liste des agences ayant des comptes-clients.

Select A.\* from Agence A, Compte Cp where A.num\_client = Cp.num\_client;

1. Les Clients ayant un compte une agence paris.

Select C.\* from Client C, Agence A, Compte Cp where C.num\_client = Cp.num\_client and A.num\_agence = Cp.num\_agence and A.ville = Sidi Bouzid;

1. Nombre de clients habitant Sidi Bouzid.

Select count(\*) from Client where Ville =Sidi Bouzid;

Exercice n 4 :

Vous travaillez dans une agence immobili re qui a mis en place un mod le relationnel

afin de g rer son portefeuille client.

Le mod le relationnel est le suivant :

Client (codeclt, nomclt, prenomclt, villeclt)

Representant (coderep, nomrep, prenomrep)

Appartement (ref, superficie, prix, #coderep, #codeclt)

On consid re que les types des attributs sont :

• codeclt, nomclt, prenomclt, villeclt, coderep, nomrep, prenomrep, ref : chaines de 30 caract res.
• superficie, prix : entier de 15 chiffres.

1- Ecrire les requ tes SQL n cessaires la cr ation de la Base de Donn es d crites ci-dessus,tout en respectant le type et la longueur donn e ci-dessus pour les diff rents attributs, et en sp cifiant les contraintes cl s primaires et cl s trang res.

Table 1 : Client (codeclt, nomclt, prenomclt, villeclt)

Create table Client (

Codeclt varchar2(30),

Nomclt varchar2(30),

Prenomclt varchar2(30),

Villeclt varchar2(30),

Constraint pk\_client primary key(codeclt)

) ;

Table 2 : Representant (coderep, nomrep, prenomrep)

Create table Representant (

Coderep varchar2(30),

Nomrep varchar2(30),

Prenomrep varchar2(30),

Constraint pk\_rep primary key (coderep)

) ;

Table 3 : Appartement (ref, superficie, prix, #coderep, #codeclt)

Create table Appartement (

Ref varchar2(30),

Superficie number(15),

Prix number(15),

Coderep varchar2(30),

Codeclt varchar2(30),

Constraint pk\_app primary key (ref),

Constraint fk\_app\_rep foreign key (coderep) references Representant (coderep),

Constraint fk\_app\_clt foreign key (codeclt) references Client (codeclt)

) ;

2- Ecrire les commandes n cessaires linsertion des extensions suivantes pour chaque table

de la base de donn es :

| | | | |

| --- | --- | --- | --- |

| Client | | | |

| | | | |

| codeclt | nomcl | Prenomcl | Villecl |

| | | | |

| C1 | jerbi | Ali | Tunis |

| | | | |

| C2 | ayadi | Sami | Sfax |

| | | | |

| C3 | zaydi | Hela | Sousse |

| | | | |

Insert into Client Values(C1, jerbi, Ali, Tunis) ;

Insert into Client Values(C2, ayadi, Sami, Sfax) ;

Insert into Client Values(C3, zaydi, Hela, Sousse) ;

Representant

| | | | |

| --- | --- | --- | --- |

| | coderep | Nomrep | prenomrep |

| | | | |

| | R1 | Tounsi | Ala |

| | | | |

| | R2 | Sfaxi | hedi |

| | | | |

| | R3 | Gabsi | amine |

| | | | |

Insert into Representant values (R1, Tounsi, Ala) ;

Insert into Representant values (R2, Sfaxi, Hedi) ;

Insert into Representant values (R3, Gabsi, amine) ;

| | | | | |

| --- | --- | --- | --- | --- |

| Appartement | | | | |

| | | | | |

| ref | superficie | prix | Coderep | codecl |

| | | | | |

| A1 | 500 | 100 | R2 | C1 |

| | | | | |

| A2 | 700 | 50 | R1 | C1 |

| | | | | |

| A3 | 900 | 150 | R2 | C3 |

| | | | | |

Insert into Appartement values (A1, 500, 100, R2, C1)

Insert into Appartement values (A2, 700, 50, R1, C1)

Insert into Appartement values (A3, 900, 150, R2, C3)

3- Lagent immobilier souhaite avoir un certain nombre dinformations, effectuer lesrequ tes SQL n cessaires afin de satisfaire lagent immobilier.

1. La liste des repr sentants

Select \* from Representant ;

1. Les diff rentes villes des clients.

Select Distinct ville from Client ;

1. Le nombre de Client.

Select count(\*) from Client ;

1. Les informations du client de code C2.

Select \* from Client where codeclt =C2 ;

1. Le maximum des prix des appartements.

Select max(prix) from Appartement ;

1. Le minimum des prix des appartements.

Select min(prix) from Appartement ;

1. La liste des clients class s par ordre alphab tique de leurs pr noms.

Select \* from Client order by prenomcl ;

1. La liste des appartements g r s par Sfaxi hedi.

Select \* from Appartement A, Representant R where A.coderep = R.coderep and nomrep = Sfaxi and prenomrep = Hedi ;

1. La moyenne des prix des appartements.

Select avg(prix) from Appartement ;

1. Le nombre dappartements dont la superficie est sup rieur 700.

Select coun(\*) from Appartement where superficie > 700 ;

Exercice n 5 :

Soit les relations suivantes de la soci t Gavasoft

Emp(NumE, NomE, Fonction, Embauche, Salaire, Comm,#NumD)

Dept(NumD, NomD, Lieu)

Exemple :Soit les extensions suivantes pour chaque table :

| | | | | | | | |

| --- | --- | --- | --- | --- | --- | --- | --- |

| Dept | NumD | NomD | | Lieu | | | |

| | | | | | | | |

| | 1 | Droit | | Sfax | | | |

| | | | | | | | |

| | 2 | Commerce | | Sousse | | | |

| | | | | | | | |

| | | | | | | | |

| Emp | NomE | Fonction | | Embauche | Salaire | Comm | NumD |

| | | | | | | | |

| | Amin | Pr sident | | 10/10/1979 | 10000 | NULL | NULL |

| | | | | | | | |

| | Anas | Doyen | | 01/10/2006 | 5000 | NULL | 1 |

| | | | | | | | |

| | Toto | Stagiaire | | 01/10/2006 | 0 | NULL | 1 |

| | | | | | | | |

| | Al-Capone | Commercial | | 01/10/2006 | 5000 | 100 | 2 |

| | | | | | | | |

Avec :

1. NumD, Salaire, Comm : entier de 20 chiffres

2. NomD, Lieu, NomE, Fonction : chaine de 30 caract res (au maximum).

3. Embauche : date

Travail demand :

1- Ecrire les requ tes SQL n cessaires la cr ation de la Base de Donn es d crites ci-dessus, tout en respectant le type et la longueur donn e ci-dessus pour les diff rents attributs, et en sp cifiant les contraintes cl s primaires et cl s trang res.

M me principe que lexercice 1

2- Ecrire les commandes n cessaires linsertion des extensions suivantes pour chaquetable de la base de donn es.

M me principe que lexercice 1

3- Ecrire les requ tes suivantes en langage SQL:

1. Donnez la liste des employ s ayant une commission (Comm) (non NULL) class par commission d croissante.

SELECT \* FROM Emp WHERE Comm IS NOT NULL AND Comm!=0 ORDER BY Comm DESC ;

1. Donnez les noms des personnes embauch es depuis le 01-09-2006

SELECT NomE FROM Emp WHERE Embauche > 01/10/2006 ;

1. Donnez la liste des employ s travaillant Cr teil

SELECT \* FROM Emp E, Dept D WHERE E.NumD=D.NumD AND Lieu="Cr teil";

1. Donnez la liste des subordonn s de "Guimezanes"

SELECT \*FROM Emp WHERE NomE="Guimezanes";

1. Donnez la moyenne des salaires.

SELECT AVG(Salaire) FROM Emp;

1. Donnez le nombre de commissions non NULL.

SELECT COUNT(Comm) FROM Emp WHERE Comm IS NOT NULL;

1. Donnez la liste des employ s gagnant plus que la moyenne des salaires de lentreprise

SELECT \* FROM Emp WHERESalaire>(SELECT AVG(Salaire) FROM Emp);

Exercice n 6 :

Soit le mod le relationnel suivant relatif une base de donn es sur des repr sentations musicales :

REPRESENTATION (num\_repr sentation, titre\_repr sentation, lieu)

MUSICIEN (nom, #num\_repr sentation)

PROGRAMMER (#nom, #num\_repr sentation, tarif, date)

Ecrire les requ tes suivantes en Langage SQL :

1. Donner la liste des titres des repr sentations.

SELECT titre\_repr sentation FROM REPRESENTATION ;

1. Donner la liste des titres des repr sentations ayant lieu l'op ra Bastille.

SELECT titre\_repr sentation FROM REPRESENTATION WHERE lieu="Op ra Bastille" ;

1. Donner la liste des noms des musiciens et des titres des repr sentationsauxquelles ils participent.

SELECT nom, titre\_repr sentation FROM MUSICIEN M, REPRESENTATION R WHERE M.num\_repr sentation = R.num\_repr sentation ;

1. Donner la liste des titres des repr sentations, les lieux et les tarifs pour lajourn e du 14/09/96.

SELECT titre\_repr sentation, lieu, tarifFROM REPRESENTATION R, PROGRAMMER P WHERE P.num\_repr sentation = R.num\_repr sentationAND date='14/06/96' ;