SQL Server exercise

I want to provide this series of exercises:
1
Exercice n°1
Soit le modèle relationnel suivant relatif à une base de données sur des
représentations musicales :
REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°représentation*, tarif)
Remarque : les clés primaires sont soulignées et les clés étrangères sont
marquées par *
Questions :
1 - Donner la liste des titres des représentations.
2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.
3 - Donner la liste des noms des musiciens et des titres des représentations
auxquelles ils participent.
4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la

2
Correction de l'exercice n°1
1 - Donner la liste des titres des représentations.
SELECT titre_représentation FROM REPRESENTATION ;
2 - 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" ;
3 - Donner la liste des noms des musiciens et des titres des représentations
auxquelles ils participent.
SELECT nom, titre_représentation
FROM MUSICIEN, REPRESENTATION
WHERE MUSICIEN.n°représentation = REPRESENTATION.n°représentation ;
4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la
journée du 14/09/96.
SELECT titre_représentation, lieu, tarif
FROM REPRESENTATION, PROGRAMMER
WHERE PROGRAMMER.n°représentation =
REPRESENTATION.n°représentation
AND date='14/06/96' ;

Exercice n°2
Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d'une
promotion d'étudiants :
ETUDIANT(N°Etudiant, Nom, Prénom)
MATIERE(CodeMat, LibelléMat, CoeffMat)
EVALUER(N°Etudiant*, CodeMat*, Date, Note)
Remarque : les clés primaires sont soulignées et les clés étrangères sont
marquées par *
Questions :
1 - Quel est 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 - Quelles sont 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 - Quelle est 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

4
Correction de l'exercice n°2
1 - Quel est le nombre total d'étudiants ?
SELECT COUNT(*) FROM ETUDIANT ;
2 - Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la
plus basse ?
SELECT MIN(Note), MAX(Note) FROM EVALUER ;
3 - Quelles sont les moyennes de chaque étudiant dans chacune des matières?
SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat,
AVG(Note) AS MoyEtuMat
FROM EVALUER, MATIERE, ETUDIANT
WHERE EVALUER.CodeMat = MATIERE.CodeMat
AND EVALUER.N°Etudiant = ETUDIANT.N°Etudiant
GROUP BY ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat;
4 - Quelles sont les moyennes par matière ?
SELECT LibelléMat, AVG(MoyEtuMat)
FROM MOYETUMAT
GROUP BY LibelléMat ;
5 - Quelle est la moyenne générale de chaque étudiant ?
SELECT N°Etudiant, Nom, Prénom,
SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT
GROUP BY N°Etudiant, Nom, Prénom ;
6 - Quelle est la moyenne générale de la promotion ?
Avec la vue MGETU de la question 5 :
SELECT AVG(MgEtu)
FROM MGETU ;
7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou
égale à la moyenne générale de la promotion ?
Avec la vue MGETU de la question 5 :
SELECT N°Etudiant, Nom, Prénom, MgEtu
FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;

Exercice n°3
Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du
Tour de France 97, dont une des étapes de type "contre la montre individuel" se
déroula à Saint-Etienne :
EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)
COUREUR(NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)
PAYS(CodePays, NomPays)
TYPE_ETAPE(CodeType, LibelléType)
ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType*)
PARTICIPER(NuméroCoureur*, NuméroEtape*, TempsRéalisé)
ATTRIBUER_BONIFICATION(NuméroEtape*, km, Rang, NbSecondes,
NuméroCoureur*)
Remarque : les clés primaires sont soulignées et les clés étrangères sont
marquées par *
Questions :
1 - Quelle est la composition de l'équipe Festina (Numéro, nom et pays des
coureurs) ?
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
3 - Quel est le nombre de kilomètres total des étapes de type "Haute
Montagne"?
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
6 - Quel est le classement général des coureurs (nom, code équipe, code pays et
temps des coureurs) à l'issue des 13 premières étapes sachant que les
bonifications ont été intégrées dans les temps réalisés à chaque étape ?
7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et
temps des équipes) ?
COPEMED - Formation SGBD - ACCESS Tunis, 25 septembre – 2 octobre 2001
6
Correction de l'exercice n°3
1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des
coureurs) ?
SELECT NuméroCoureur, NomCoureur, NomPays
FROM EQUIPE A, COUREUR B, PAYS C
WHERE A.CodeEquipe=B.CodeEquipe And B.CodePays=C.CodePays
And NomEquipe="FESTINA" ;
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
SELECT SUM(Nbkm) FROM ETAPE ;
3 - Quel est le nombre de kilomètres total des étapes de type HAUTE
MONTAGNE ?
SELECT SUM(Nbkm) FROM ETAPE A, TYPE_ETAPE B
WHERE A.CodeType=B.CodeType And LibelléType="HAUTE MONTAGNE" ;
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
SELECT NomCoureur FROM COUREUR
WHERE NuméroCoureur NOT IN (SELECT NuméroCoureur FROM
ATTRIBUER_BONIFICATION) ;
5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
SELECT NomCoureur FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur
GROUP BY NuméroCoureur, NomCoureur
HAVING COUNT(*)=(SELECT COUNT(*) FROM ETAPE) ;
6 - Quel est le classement général des coureurs (nom, code équipe, code pays
et temps des coureurs) à l'issue des 13 premières étapes sachant que les
bonifications ont été intégrées dans les temps réalisés à chaque étape ?
SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur and NuméroEtape<=13
GROUP BY A.NuméroCoureur, NomCoureur, CodeEquipe, CodePays
ORDER BY Total;
7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et
temps des équipes) ?
SELECT NomEquipe, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B, EQUIPE C
WHERE A.NuméroCoureur=B.NuméroCoureur And
B.CodeEquipe=C.CodeEquipe
And NuméroEtape<=13
GROUP BY B.CodeEquipe, NomEquipe
ORDER BY Total;

Hello everyone
here are some exercises +fix in SQL Server and I’m also training to start the SQL course I haven’t mastered well but with exercises, God willing, we’ll get there :

*Display the list of employees (last name and first name) hired between 1993 and 1995
select nom, prenom
from employes
where year(date_embauche) between 1993 and 1995
order by nom
*Display the total number of different products that belong to category 2, 3, 4, 5 or 8. (the category_code is in text format).
select count(*) as "total catégories 2,3,4,5,8"
from produits
where code_categorie in ('2','3','4','5','8')
Display the list of employees (last name, first name, position) whose position is “responsible” for something (e.g., “sales manager”, “personnel manager”, …)
select nom, prenom, fonction
from employes
where fonction like 'responsable%'
order by fonction
Display the total amount ordered (excluding discounts) for all orders whose number is greater than 150 (the order number will also be displayed)
select n_commande, sum(prix_unitaire*quantite) as total
from details_commandes
where n_commande > 11050
group by n_commande
order by n_commande
Display for each client (code_client) the largest delay observed between placing the order and sending the products, for clients delivered in France. Only show clients with a delay > 20 days..
select code_client, max(date_envoi-date_commande) as delai
from commandes
where pays_livraison = 'france'
group by code_client
having delai > 20
order by delai, code_client
Display the list of orders (order number, order date) placed between 1992 and 1994.
select n_commande, date_commande
from commandes
where year(date_commande) between 1992 and 1994
order by date_commande
Display the list of products (reference, name, unit price) whose name starts with “C”
select ref_produit, nom_du_produit, prix_unitaire
from produits
where nom_du_produit like 'C%'
order by ref_produit
Display the total number of suppliers who live in either France, the USA, or Germany.
select count(*)
from fournisseurs
where pays in ('france', 'usa', 'allemagne')
Display the total amount ordered for all orders whose number is less than or equal to 10270 (NB: the order number will also be displayed).
select n_commande, sum(prix_unitaire * quantite) as total
from details_commandes
where n_commande <= 10270
group by n_commande
Display for each product category (code_categorie) the total number of units ordered (NB: use the units_ordered field from the products table).
We will limit to category codes smaller than 5 and total units ordered greater than 100
select code_categorie, sum(unites_commandees) as total
from produits
where code_categorie < 5
group by code_categorie
having total >100
List of orders (order number, order date, delivery date) for which delivery must occur at most 2 weeks after the order and whose delivery department is 75.
select n_commande, date_commande, date_envoi
from commandes
where left(code_postal_livraison,2) = '75'
and date_envoi - date_commande < 14
Display the price of the cheapest product for all categories 2, 4 and 5.
select min(prix_unitaire)
from produits
where code_categorie in (2,4,5)
List of products (reference, name and unit price) whose price is between 50 and 80, ordered by price and by product names.
select ref_produit, nom_du_produit, prix_unitaire
from produits
where prix_unitaire between 50 and 80
order by prix_unitaire, nom_du_produit
Average age of employees by their function.
select fonction, avg(year(current date) - year(date_naissance))
from employes
group by fonction
Find the countries for which we have 3 or more clients who are “owner”, “buyer” or “sales chief”
select count(*) as "nbre clients", pays
from clients
where fonction in ('proprietaire', 'acheteur', 'chef des ventes')
group by pays
having "nbre clients" >=3
Select all orders (order number, date of order, client name) placed by the client whose code is “ALFKI”.
SELECT n_commande, date_commande, societe FROM commandes cd,
clients c WHERE c.code_client = cd.code_client AND c.code_client = 'ALFKI'
Display for each client (code_client) the number of orders placed with a French employee.
SELECT code_client , count(*) FROM commandes cd
JOIN employes e on cd.n_employe = cd. n_employe
WHERE e.pays = ‘France’
GROUP BY code_client
Display the number of items per order (with the order number), for orders that have more than 5 items.
SELECT n_commande, count(*) as nb
FROM details_commandes
GROUP BY n_commande
HAVING nb > 5
Total amount ordered (excluding discount) for each product whose PU is equal to 90. Show the product reference, its name and the total amount ordered.
SELECT p.ref_produit, p.nom_du_produit, sum(quantité * p.prix_unitaire) FROM details_commandes d, produits p WHERE d.ref_produit = p. ref_produit
AND p.prix_unitaire = 90
GROUP BY ref_produit, p.nom_du_produit
For each product (product reference, product name) of category “2” (use the category code stored as text) show the total number of products ordered (note: use the quantity from the details_commandes table).
NB: do not show products that were not ordered more than 100 units.
SELECT nom_du_produit, sum(quantite) FROM produits p, details_commandes d
WHERE p.ref_produit = d.ref_produit
AND p.code_categorie = ‘2’
GROUP BY p.ref_produit, p.nom_du_produit
HAVING sum(quantite) > 100
Select all products (product number, product name, category name) whose category code is 3 (NB: category code is stored in text format).
SELECT ref_produit,nom_du_produit, nom_de_categorie
FROM produits, categorie
WHERE produits.ref_produit=categorie.code_categorie
AND categorie.code_categorie='3'
Display for each employee (employee number) the number of orders placed.
SELECT n_employe, count(*)
FROM commandes
GROUP BY n_employe
Display the number of products per category (with the category code and category name), for categories that have more than 5 products.
SELECT c.code_categorie, c.nom_de_categorie, count(p.ref_produit) as tot
FROM Categories c, produits p
WHERE p.code_categorie = c.code_categorie
GROUP BY c.code_categorie, c.nom_de_categorie
HAVING tot > 5
Stock valuation (in francs) for each product whose supplier lives in the United States. Show the product reference, its name and the valuation amount.
SELECT p.ref_produit, p.nom_du_produit, p.prix_unitaire*unites_en_stock
FROM produits p, fournisseurs f
WHERE f.n_fournisseur = p.n_fournisseur
AND f.pays = 'Etats-unis'
Display for all orders (order number, date of order) delivered in the city of Aarhus the total billed amount (excluding discount). NB: do not show orders where the total billed amount is less than 5000.
SELECT c.n_commande, c.date_commande, sum(prix_unitaire * quantite) as tot
FROM commandes c, details_commandes d
WHERE d.n_commande = c.n_commande
AND ville_livraison = 'arhus'
GROUP BY c.n_commande, c.date_commande
HAVING tot >5000
Display the list of suppliers (company name) who offer at least one product priced at 25 or less. NB: Do not display the number of products offered by each supplier.
SELECT societe FROM Fournisseurs f, produits p
f.n_fournisseur = p.n_fournisseur
AND p.prix_unitaire <=25
GROUP BY societe
Display for each client (company name) the total number of units ordered for the product with reference 1 (ref_produit is in text format). Results should be presented in alphabetical order of company names.
SELECT societe, sum(quantite) FROM clients c, commandes cd, details_commandes d
WHERE cd.code_client = c.code_client AND d.n_commande = cd.n_commande
AND ref_produit = 1
GROUP BY societe
ORDER BY societe
Display for each client (company name) the number of orders to be delivered after May 31, 1994 (use the a_livrer_avant field). If this number is less than 10, the client will not be displayed.
SELECT societe, count(n_commande) as nb FROM clients c, commandes cd
WHERE cd.code_client = c.code_client
AND a_livrer_avant >= '1994-05-31'
GROUP BY societe
HAVING nb >=10
Display the total number of units ordered (use the field unites_commandees) for each supplier (also show supplier number and company name).
NB: do not display suppliers who have not ordered anything.
SELECT p.n_fournisseur, societe, sum(unites_commandees) as total FROM Fournisseurs f, produits p
WHERE f.n_fournisseur = p.n_fournisseur
GROUP BY p.n_fournisseur, societe
HAVING total <>0
Display the number of employees hired between 1993 and 1999 and who do not live in Brazil, Argentina, Paraguay, Chile or Cuba. NB: only a single number should be returned.
SELECT count(*) from employes
WHERE year(date_embauche) between 1993 and 1999 and pays NOT IN ('bresil', 'argentine', 'paraguay', 'chili', 'cuba')
Display for each order (order number, order date) the number of different products ordered with unit price >= 150
SELECT c.n_commande, date_commande, count(ref_produit) FROM commandes c, details_commandes d WHERE c.n_commande = d.n_commande
AND d.prix_unitaire>=150
GROUP BY c.n_commande, date_commande
Display for each messenger (messenger number, messenger name) the number of orders placed.
SELECT m.n_messager, nom_du_messager, count(n_commande)
FROM messagers m, commandes c
WHERE c.n_messager = m.n_messager
GROUP BY m.n_messager, nom_du_messager
Display the list of categories (category name, category code, number of units in stock) for which the total number of units in stock is less than 200
SELECT c.code_categorie, nom_de_categorie, sum(unites_en_stock) as stock FROM categories c , produits p WHERE p.code_categorie = c.code_categorie
GROUP BY c.code_categorie, nom_de_categorie
HAVING stock <=200
Display for each recipient (recipient name) the total number of products ordered that have reference 2. Results should be sorted alphabetically by recipient name.
SELECT destinataire, sum(quantite) FROM commandes cd, details_commandes d
WHERE d.n_commande = cd.n_commande
AND ref_produit = 2
GROUP BY destinataire
ORDER BY destinataire
Display for categories 1, 3, 5, 7 and 8 the total number of units ordered (use the field « unites_commandees » from the products table)
On affichera le code de la catégorie et le nom de la catégorie si le nombre total d’unités commandées est différent de 0.
SELECT c.code_categorie, nom_de_categorie, sum (unites_commandees) as tot
FROM produits p, categories c
WHERE c.code_categorie=p.code_categorie
AND c.code_categorie IN (1,3,5,7,8)
GROUP BY c.code_categorie, nom_de_categorie
HAVING tot <> 0
List of employees who live in a city where at least 1 supplier is located
SELECT DISTINCT f.Ville, e.Nom FROM Fournisseurs f, Employes e
WHERE f.Ville = e.Ville
Display for each city of my suppliers the number of employees working in that city
SELECT f.Ville, count(N_employe) from Fournisseurs f, Employes

Thank you essadouq I passed my SQL exam and I succeeded :) but I will work on this series.
tell me if you will be able to help me if I don’t know how to answer :d:d:d

I want SQL Server exercises pleaseeeeeeeeeeeeeeee

Hello there.
I need it too.
Thank you.

Two things are infinite: the universe and human stupidity; and I'm not sure about the universe.

take this site: http://allforup.blogspot.com/ it has magnificent exercises. good luck

Hello
if you happen to have found some interesting addresses, send them to me!!!
thanks in advance

Hi Ana, I’m studying in the information development field and I have a problem with SQL. Please help me with the exercises, and if there was a glimpse of that exam last year, it would really help me.

take this site the http://allforup.blogspot.com/ it has magnificent exercises. good luck

thank you very much my friends...

translate to English: given the following relational model:
DEPT</gras>(DNO, DNOM,DIR,VILLE)
EMP(ENO,ENOM,PROF,DATEEMB,SAL,COMM,DNO)

1° display the department name of each employee
2° give the jobs with the lowest average salary; also provide their average salary
3° give the list of employees who have a commission
4° give the number of employees in the PRODUCTION department
5° the department numbers and their maximum salary
6° the professions and their average salaries
7° give the departments that have no employees

please I have a question:
is it mandatory to use every time:

select A.num1 , B.num2
from table1 A , table2 B
where A.num1 = B.num2

even if the field names are different

or can we write like this :

select num1 , num2
from table1 , table2
where num1 = num2

--
===========
Two things are infinite: the universe and human stupidity; and I'm not sure which is greater.

Thank you essadouq for your exercises, it’s perfect timing because I’m studying SQL as I have an exam soon, I’m not very good at SQL... ^^

I have a question about correction 4

4- What are the averages by subject?
SELECT LibelléMat, AVG(MoyEtuMat)
FROM MOYETUMAT
GROUP BY LibelléMat;

Why after FROM, essadouq put MOYETUMAT even though this table doesn’t exist?!

Thank you, that would be very kind. :D

Hello, I’m looking for trigger exercises

Hello,
I need your help I need to download SQL Server and I don’t know how to do it

Hi
thanks essadouq

Hello everyone,
I just saw the exercises published by essadouq, on Saturday May 12, 2007 (there's a bit of time :-) ). For the 3rd exercise (the Tour de France one), I think the sixth query does not answer the question asked, because we are looking for the general classification of the riders after the first 13 stages, so the riders must have completed the first 13 stages to be able to compute their classification, whereas the query did not satisfy this requirement with the condition 'NuméroEtape <= 13'.
I think the correct answer is the following:
SELECT NomCoureur, codeEquipe, CodePays, Sum(TempRéalisé) as TempCoureur FROM Coureur C, Participer P
WHERE C.NuméroCoureur = P.NuméroCoureur AND NuméroEtape between (1,13) AND count(distinct NuméroEtape) = 13
GROUP BY NomCoureur
We require filtering on NuméroEtape: only those between 1 and 13

g

I wanted to know how to display the average scores by exam paper, by establishment, of an examination in SQL