Ingénierie Informatique et Réseaux
3ème Année
Année universitaire 2023-2024
TP 2: Plus de LMD sur SQL Server
correction
--1 Tous les clients.
select * from CLIENT;
select * from COMMANDE;
select * from PRODUIT;
--2 Les noms et les numéros de téléphone des clients de Rabat.
select Nom, Tel from CLIENT where Ville= 'Rabat';
--3 Le nom, prénom et ville des clients de Casablanca dont le prénom est ‘Ahmed’.
select Nom from CLIENT where Ville= 'Casablanca' And Prénom ='Ahmed';
--4 Le nom, prénom des clients dont le prénom est soit ‘Sanaa’ soit ‘Hajar’.
select Nom from CLIENT where Prénom in ('Sanaa', 'Hajar');
--5 La désignation et le prix unitaire de tous les produits dont la quantité stockée
est supérieure à 200.
select Designation, Prix from PRODUIT where QteStock >= 200;
--6 Toutes les commandes dont la date de commande est entre ‘2023-01-05’ et ‘2023-03-
15’ classées dans l’ordre croissant des numéros de commandes.
select * from COMMANDE where Datecom BETWEEN '2023-01-05' and '2023-03-15';
--7 Les clients dont le nom commence par ’E'.
select * from CLIENT where Nom LIKE 'E%';
--8 Annulez le numéro de téléphone des clientes ‘Fatima’ et ‘Sanaa’.
Update CLIENT set Tel= null where prénom in ('Sanaa', 'Fatima');
--9 Les clients qui n’ont pas de numéro de téléphone.
select * from CLIENT where Tel is null;
--10 Les noms et les prénoms des clients triés par nom et pour chaque nom triés
parprénom.
select nom, prénom from client
order by nom, prénom;
--11 Les noms, les prénoms et les pays des clients triés par nom
select nom, prénom, pays from client order by nom;
--12 Le numéro du produit le plus commandé.
select top 1 Numprod, count(Numcom) as nbrcmd from LIGNE_COMMANDE
group by Numprod order by nbrcmd desc;
--13 Les numéros des commandes avec le nombre de produits commandés dans chaque
commande.
select Numcom , count(Numprod) as total from LIGNE_COMMANDE
group by Numcom;
--14 Les numéros des commandes avec le total (prix*qtecom) de chaque commande.
select Numcom, sum(Qtecom*Prix) as Prix_total from LIGNE_COMMANDE
group by Numcom;
-- 15 Les numéros des commandes dont la date de livraison est ‘10/12/00’ avec le total
de chaque commande.
select [Link], ([Link] * [Link]) as Total_Commande from Ligne_commande LC,
COMMANDE CM
where [Link] = [Link] and [Link] ='2023-05-25';
--16 Les produits dont le prix est supérieur à la moyenne des prix de tous les
produits.
select designation from PRODUIT where prix > (select avg(prix) from produit);
--17 Les noms, les prénoms et les pays des clients triés par nom (le pays de ‘Naciri’
doit être caché par des ‘*’).
select Nom, Prénom, REPLACE(Pays, 'Naciri', '***') as Pays from CLIENT order by Nom;
--18 Les noms des clients qui ont commandé au moins un produit de prix supérieur à
3000DH.
select distinct [Link], [Link] from CLIENT cl
join COMMANDE cm on [Link] = [Link]
Khaoula AJBAL | EMSI TANGER
Ingénierie Informatique et Réseaux
3ème Année
Année universitaire 2023-2024
join LIGNE_COMMANDE lc on [Link] = [Link]
join Produit p on [Link] = [Link] where [Link] >=3000;
-- ou bien
select distinct [Link], [Link] from CLIENT cl , PRODUIT p , COMMANDE cm,
LIGNE_COMMANDE lc
where [Link] = [Link] and [Link] = [Link] and [Link] = [Link] and
[Link] >=3000;
--19 Les numéros, noms et prénoms des clients qui n’ont pas commandé le produit N°1.
select [Link], [Link], [Link]énom from CLIENT cl
where [Link] Not in (select [Link] from CLIENT cl
join COMMANDE cm on [Link] = [Link]
join LIGNE_COMMANDE lc on [Link] = [Link]
where [Link] = 1)
order by Numcli asc;
--ou bien
select [Link] from Client cl where Numcli
not in (select [Link] from Client C, COMMANDE CM, LIGNE_COMMANDE LC
where [Link] = [Link] and [Link] = [Link] and [Link] = 1);
-- ou bien
select Numcli from CLIENT where Numcli NOT IN (
select distinct Numcli from COMMANDE where Numcom IN (
select Numcom from LIGNE_COMMANDE where Numprod = 1));
--20 Les désignations, les prix, les numéros des produits qui ont été commandés par le
client N°1, ainsi que le numéro de ce dernier.
Select [Link], [Link], [Link], [Link] from PRODUIT p
join LIGNE_COMMANDE lc on [Link] = [Link]
join COMMANDE cm on [Link] = [Link]
join CLIENT cl on [Link] = [Link]
where [Link] = 1;
--21 Les numéros, noms, prénoms des clients ayant commandé plus d’un produit. Avec Le
total des produit commandés.
select [Link], [Link], [Link]énom, COUNT([Link]) as Totalproduit from COMMANDE
cm
JOIN CLIENT CL on [Link] = [Link]
JOIN LIGNE_COMMANDE lc ON [Link] = [Link]
group by [Link], [Link], [Link]énom
having count([Link]) >1;
--22Les produits commandés par tous les clients.
--méthode 1 : vérifier tous les produits, pour vérifier si ce produit a été acheté
par tout le monde il faut que
--la requête qui retourne la liste des clients qui ne l'ont pas commandé soit vide
--cette requete est créée en vérifiant que le client n'existe pas dans la liste des
client qui on acheté ce produit spéficique qu'on est entrain de vérifier ( le lien se
fait avec P de produit)
select * from PRODUIT P
where not exists (select * from CLIENT
where Numcli not in (select Numcli from
COMMANDE M, LIGNE_COMMANDE LC
where [Link] = [Link]
and [Link] = [Link]));
-- Méthode 2 : dans la table ligne de commande on va vérifier le nombre total des
commandes passées pour un produit
-- et le comparer au nombre total des client qu'on a . s'il y'a égalité ça veut dire
que le produit concerné a été acheté par tout le monde.
select *
from PRODUIT
where Numprod IN (
select Numprod
from LIGNE_COMMANDE
group by Numprod
having COUNT(distinct Numcom) = (select COUNT(*) from CLIENT)
);
Khaoula AJBAL | EMSI TANGER
Ingénierie Informatique et Réseaux
3ème Année
Année universitaire 2023-2024
--23 les clients ayant commandés des produits commandés par cl3
select DISTINCT [Link], [Link] from CLIENT cl
join COMMANDE cm on [Link] = [Link]
join LIGNE_COMMANDE lc on [Link] = [Link]
where [Link] in (select Numprod from LIGNE_COMMANDE lc
join COMMANDE cm on [Link] = [Link]
where [Link] = 3) and [Link]<>3;
--24 le principe est de comparer l'ensemble de proruits commandés par chaque client
autre que cl3, avec les produits commandé par cl3
select [Link], [Link] -- chaque client
from CLIENT cl
where [Link] <> 3 -- autre que client 3
and NOT EXISTS (
--avec les produits commandé par cl3
SELECT [Link]
FROM COMMANDE cm3
JOIN LIGNE_COMMANDE lc3 ON [Link] = [Link]
WHERE [Link] = 3
EXCEPT -- il faut soustraire les produits hors [Link]
--l'ensemble de proruits commandés par chaque client
SELECT [Link]
FROM COMMANDE cm
JOIN LIGNE_COMMANDE lc ON [Link] = [Link]
WHERE [Link] = [Link] -- chaque client
);
-- donc si on soustrait de l'ensemble de produits de cl3 A les produits différents B
d'un autre client et qu'on a un résultat donc on skip le client
-- on n'affiche que les clients dont le résultat est vide c'est à dire A-B=0 et donc 1
A et B sont identiques
-- on obtiendrait le même résultat pour la question 15 en utilisant exists avec
intersect
--25 Les numéros et les noms des clients qui ont commandé tous les produits.
select [Link], [Link] from CLIENT cl, COMMANDE cm, LIGNE_COMMANDE lc
where [Link]=[Link] and [Link]=[Link] and
Numprod in (select Numprod from PRODUIT)
Khaoula AJBAL | EMSI TANGER