SQL Server exercise

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

Hello there.
I need it too.
Thank you.

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

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

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, I’m looking for exercises on stored procedures, functions, cursors and triggers plus their solutions.

www. ista4all.com

Thank you, Mr. Boko. If you have other exercises, send them to me at
talal1029@hotmail.com

I want SQL Server exercises pleaseeeeeeeeeeeeeeee

thank you very much my friends...

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

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

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.

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

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

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.

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