Pour pouvoir tester vos compétences en sql , et plus précisément , dans la partie de définition des données; voilà un TP ( tp sql ldd) qui présente un cas réel de définition des données.
Vous devez suivez les questions et proposez votre solution dans la partie des commentaires. Ainsi, vous serez amené à comprendre vos erreurs et donc ne les refaire jamais par la suite.
tp sql ldd : enoncé
Exercice I : Soit le MODELE LOGIQUE DE DONNEE suivant :
ARTICLE (NoArt, Libelle, Stock)
FOURNISSEUR (NoFour, NomF, Adresse, VilleFour)
FOURNIR (NoFour#, NoArt#, PrixArticle, Delai)
– Les attributs soulignés sont des clés primaires, Les attributs avec # sont des clés étrangères.
– Stock, PrixArticle et Delai ont une valeur strictement positive
-l’attribut nom du fournisseur doit être non null.
– délai a comme valeur par défaut 2.
1- créer la bd correspondante.
2- remplir les tables par des exemples.
3– donner les numéros et les libellés des articles de stock inférieur à 10.
4- afficher la liste des articles dont le prix est compris entre 100 et 300
5- lister les fournisseurs dont le nom commence par “Fo” .
6- donner les noms et les adresses des fournisseurs qui proposent des articles pour lesquels le délai d’approvisionnement est supérieur à 20 jours.
7- afficher la liste pour chaque article (numéro et libellé) qui a du prix d’achat maximum, minimum ou moyen.
Exercice 2 : Soit le modèle relationnel suivant :
Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d’une promotion d’étudiants :
ETUDIANT (NEtudiant, Nom, Prénom)
MATIERE (CodeMat, LibelléMat, CoeffMat)
EVALUER (NEtudiant, CodeMat, Date, Note)
Créer la base de données avec les règles de gestion suivant :
- Les attributs en gras clé primaire, Les attributs souligné clé étrangère
- Note a une valeur entre 0 et 20
1 – Calculer le nombre total d’étudiants .
2 – Quelles sont, parmi l’ensemble des notes, la note la plus haute et la note la plus basse ?
3 – Afficher les moyennes de chaque étudiant dans chacune des matières.
4 – Quelles sont les moyennes par matière ? On utilisera la requête de la question 3 comme table source
5 – Trouver la moyenne générale de chaque étudiant . On utilisera la requête de la question 3 comme table source
6 – Quelle est la moyenne générale de la promotion ? On utilisera la requête de la question 5 comme table source
7 – Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?
On utilisera la requête de la question 5 comme table source (vue)
Solution
Solution Exercice 1 :
Create DataBase S4Ex2
USE S4Ex2
Create Table ARTICLES (NoArt int primary key, Libelle varchar(50), Stock int);
Create Table FOURNISSEURS (NoFour int primary key, NomFour varchar(50), AdrFour varchar(100), VilleFour varchar(50));
Create Table FOURNIR (NoFour int foreign key references FOURNISSEURS (NoFour), NoArt int foreign key references ARTICLES(NoArt), PrixArticle money, Delai int);
insert into ARTICLES values (1,’PC Dell’,55),(2,’PC HP’,6),(3,’PC Acer’,100)
insert into FOURNISSEURS values (1,’Ali’,’rue 5, N 6′,’Casa’)
insert into FOURNIR values (1,1,100.5,15)
insert into FOURNIR values(1,2,500.5,5)
–Execution requêtte
–1) SELECT NoArt, Libelle FROM Articles Where Stock<10
–2) SELECT * FROM Articles WHERE PrixInvent between 100 AND 300
–3) SELECT * FROM Fournisseurs WHERE NomFour LIKE ‘STE%’
–4) SELECT Fournisseurs.NomFour, AdrFour
FROM Fournisseurs INNER JOIN Fournir ON Fournisseurs.NoFour=Fournir.NoFour
WHERE Delai>20
–5) SELECT NoArt, Libelle FROM Articles ORDER BY Stock DESC
–6) SELECT Articles.NoArt , Libelle , Max(PrixArticle) AS ‘Prix Max’, Min(PrixArticle) AS ‘Prix Min’, AVG(PrixArticle) AS ‘Prix Moyenne’
FROM Articles INNER JOIN Fournir ON Articles.NoArt=Fournir.NoArt
GROUP BY Articles.NoArt , Libelle
Exercice 2 – Solution :
Create DataBase S4Ex4
USE S4Ex4
Create table ETUDIANT (NEtudiant int identity primary key, Nom varchar(30), Prénom
varchar(30))
Create table MATIERE (CodeMat int identity primary key, LibelleMat varchar(30),
CoeffMat int)
Create table EVALUER (NEtudiant int foreign key references ETUDIANT (NEtudiant),
CodeMat int foreign key references MATIERE (CodeMat), Date smalldatetime, Note
decimal(4,2) check (Note between 0 and 20))
Go
insert into ETUDIANT values(‘Ali’,’P-Ali’)
insert into ETUDIANT values(‘Ahmed’,’P-Ahmed’)
Select * from ETUDIANT
Go
insert into MATIERE values (‘SGBD1’,2)
insert into MATIERE values (‘SGBD2’,2)
insert into MATIERE values (‘ASP.Net’,3)
Select * From MATIERE
truncate table EVALUER
insert into EVALUER values (1,1,’24/10/2010′,12)
insert into EVALUER values (1,3,’24/10/2010′,12)
–1) SELECT Count(NEtudiant) FROM Etudiant
–2) SELECT Max(Note) as ‘Note Max’, Min(Note) as ‘Note Min’ FROM Evaluer
–3) SELECT Nom, LibelleMat, AVG(Note) as ‘Moyenne’ FROM Etudiant, Evaluer, Matiere
WHERE Etudiant.NEtudiant=Evaluer.NEtudiant
AND Evaluer.CodeMat=Matiere.CodeMat
GROUP By Etudiant.NEtudiant,Nom, Matiere.CodeMat,LibelleMat
Order by nom
–4) SELECT LibelleMat, AVG(Note) as ‘Moyenne’ FROM Evaluer, Matiere
WHERE Evaluer.CodeMat=Matiere.CodeMat
GROUP By Matiere.CodeMat,LibelleMat
–5) SELECT Nom, SUM(Note* CoeffMat)/SUM(CoeffMat) as ‘MgEtud’
FROM Etudiant, Evaluer, Matiere
WHERE Etudiant.NEtudiant=Evaluer.NEtudiant
AND Evaluer.CodeMat=Matiere.CodeMat
GROUP By Etudiant.NEtudiant,Nom
–6) Create View MgEtud
as SELECT SUM(Note* CoeffMat)/SUM(CoeffMat) as ‘MgEtud’
FROM Etudiant, Evaluer, Matiere
WHERE Etudiant.NEtudiant=Evaluer.NEtudiant
AND Evaluer.CodeMat=Matiere.CodeMat
GROUP By Etudiant.NEtudiant
Go
SELECT AVG(MgEtud) as ‘MoyPromotion’
FROM MgEtud
–7)Create View MoyPromotion
as SELECT AVG(MgEtud) as ‘MoyPromotion’
FROM MgEtud
SELECT Nom, SUM(Note* CoeffMat)/SUM(CoeffMat) as ‘MgEtud’
FROM Etudiant, Evaluer, Matiere
WHERE Etudiant.NEtudiant=Evaluer.NEtudiant
AND Evaluer.CodeMat=Matiere.CodeMat
GROUP By Etudiant.NEtudiant,Nom
Having SUM(Note* CoeffMat)/SUM(CoeffMat)>(select MoyPromotion
from MoyPromotion)
Vous pouvez suivre notre cours complet d’SQL . Il vous aide à approfondir vos compétences sur la création d’une base de données relationnelle et la manipulation des données avec les ordres sql : select, update, delete, insert
Aussi, vous avez une playlist des vidéos (Explication en Darija arabe marocaine) bien organisée qui traite : Les bases de données (partie SQL)