Scribd
Upload
33 views15 pages

SQL Queries for Database Management

The document contains examples of SQL queries on tables in a database including: 1) Select queries to retrieve data from single or multiple tables joined together with filters on columns. 2) Aggregate functions like count, avg, max, min used with group by to calculate values across groups. 3) Date calculations and comparisons using functions like date(), dateDiff(). 4) Parameterized queries with placeholders for user input. 5) Cross-tabulation and analysis across table columns. 6) Update queries to modify data in tables. The document appears to provide examples of SQL queries for students learning to work with relational databases.

Uploaded by

jabrane halima
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
33 views15 pages

SQL Queries for Database Management

The document contains examples of SQL queries on tables in a database including: 1) Select queries to retrieve data from single or multiple tables joined together with filters on columns. 2) Aggregate functions like count, avg, max, min used with group by to calculate values across groups. 3) Date calculations and comparisons using functions like date(), dateDiff(). 4) Parameterized queries with placeholders for user input. 5) Cross-tabulation and analysis across table columns. 6) Update queries to modify data in tables. The document appears to provide examples of SQL queries for students learning to work with relational databases.

Uploaded by

jabrane halima
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CONCEPTION ET MODELISATION D’UN SI

TP Access base de donnée

Année universitaire 2019 – 2020


SELECT Nom,Prenom FROM clients

Select Code_client,Nom&' '&Prenom , Ville from clients Order by Nom&' '&Prenom DESC

Select Nom&' '&Prenom from clients where Ville ='Mohammedia'

Select * from details_commandes where Ref_pdt ='Pdt01' AND Quantité > 10

Année universitaire 2019 – 2020


Select * from details_commandes where Ref_pdt ='Pdt02' AND Quantité <= 10

Select * from details_commandes where Ref_pdt ='Pdt01' AND ( Quantité > 20 OR Num_cmd > 10 )

Select * from commandes where Num_cmd >= 3 AND Num_cmd <= 10

Année universitaire 2019 – 2020


Select * from clients where Ville ='Mohammedia' OR Ville ='Rabat'

Select * from clients where Nom LIKE 'A*'

Select * from clients where Nom LIKE '[!A]*'

Select * from clients where Ville='Casablanca' Order by Nom DESC

Année universitaire 2019 – 2020


Select Code_client,Nom,Ville from clients where Nom LIKE 'S*' AND Prenom LIKE '*m*' AND (
Ville='Casablanca' OR Ville='Rabat')

Select Code_client,Nom,Ville from clients where Nom LIKE '[!S]*' AND ( Ville='Casablanca' OR
Ville='Mohammedia' OR Ville='Fes')

Select Num_cmd,Date_cmd FROM commandes where Cmd_reglee.Value = 'OUI' AND Date_cmd >
format('15/01/2009' ,'dd/mm/yyyy')

Select Num_cmd,Date_cmd FROM commandes where Date_cmd Between format('10/01/2009'


,'dd/mm/yyyy') AND format('15/06/2009','dd/mm/yyyy')

Année universitaire 2019 – 2020


Select Num_cmd,Date_cmd FROM commandes where Year ([Date_cmd]) = 2009

Select Num_cmd,Date_cmd,Cmd_reglee FROM commandes where Cmd_reglee.Value = 'NON' AND


Date_cmd < format('15/05/2009' ,'dd/mm/yyyy') AND Year ([Date_cmd]) = 2009

Select Num_cmd,Date_cmd FROM commandes where Month([Date_cmd]) IN (3,5,6) AND Year


([Date_cmd]) = 2009

Select Num_cmd,Date_cmd FROM commandes where Day([Date_cmd]) = 01 AND Year ([Date_cmd])


= 2009

Année universitaire 2019 – 2020


Select Num_cmd,Date_cmd FROM commandes where Month([Date_cmd]) = Month(Date ())

Select Num_cmd,Date_cmd,DateDiff('yyyy',[Date_cmd],Date()) as 'duree


Annee',DateDiff('q',[Date_cmd],Date()) as Trimistre,DateDiff('m',[Date_cmd],Date()) as 'duree
Mois',DateDiff('ww',[Date_cmd],Date()) as 'duree Semaine',DateDiff('y',[Date_cmd],Date()) as
Jours,DateDiff('h',[Date_cmd],Date()) as hours,DateDiff('n',[Date_cmd],Date()) as 'duree
minutes',DateDiff('s',[Date_cmd],Date()) as 'duree Secondes' FROM commandes

requete paramétrée

Select * FROM clients where Ville = [Entrez la ville]

Année universitaire 2019 – 2020


Select * from details_commandes where Quantité <= [Entrez la valeur]

Select * FROM commandes where Date_cmd = [Entrez La date sous forme mm/jj/aaaa]

Année universitaire 2019 – 2020


Select * FROM commandes as c,clients as cl,details_commandes as dt,produits as p where
cl.Code_client =c.Code_client AND c.Num_cmd = dt.Num_cmd AND p.Ref_pdt = dt.Ref_pdt

Select cl.Code_client,cl.Nom

FROM commandes as c,clients as cl

where cl.Code_client =c.Code_client

AND Ville='Casablanca'

Année universitaire 2019 – 2020


AND Year ([c.Date_cmd]) < 2010

Select cl.Code_client,cl.Nom,cl.Ville

FROM commandes as c,clients as cl,details_commandes as dt,produits as p

where cl.Code_client =c.Code_client

AND c.Num_cmd = dt.Num_cmd

AND p.Ref_pdt = dt.Ref_pdt

AND dt.Quantité > 20

Select DISTINCT cl.Code_client,cl.Nom

FROM commandes as c,clients as cl,details_commandes as dt,produits as p

where cl.Code_client =c.Code_client

AND c.Num_cmd = dt.Num_cmd

AND p.Ref_pdt = dt.Ref_pdt

AND p.PU > 700

AND cl.Ville ='Casablanca'

Année universitaire 2019 – 2020


Requete avec operations

Select Count(Code_client) as NombreTotalClient

FROM clients

Select Ville,Count(Code_client) as NombreParVille

FROM clients

GROUP BY Ville

Select c.Num_cmd,Avg(dt.Quantité) as 'la quantité moyenne',Max(dt.Quantité) as 'la quantité


Maximale',Min(dt.Quantité) as 'la quantité minimale'

FROM commandes as c,details_commandes as dt

where c.Num_cmd = dt.Num_cmd

GROUP BY c.Num_cmd

Année universitaire 2019 – 2020


Select dt.Ref_pdt,Avg(dt.Quantité) as 'la quantité moyenne',Max(dt.Quantité) as 'la quantité
Maximale',Min(dt.Quantité) as 'la quantité minimale'

FROM commandes as c,details_commandes as dt

where c.Num_cmd = dt.Num_cmd

AND (c.Date_cmd < (Date() - 180))

GROUP BY dt.Ref_pdt

Select c.Num_cmd,c.Date_cmd,(dt.Quantité*p.PU) as montant_HT,p.TVA,montant_HT*1.2 as montant_TTC

FROM commandes as c,details_commandes as dt,produits as p

where c.Num_cmd = dt.Num_cmd

AND p.Ref_pdt = dt.Ref_pdt

Année universitaire 2019 – 2020


Select c.Num_cmd,c.Date_cmd,DateDiff('y',[c.Date_cmd],Date()) as Nbr_Jours,(dt.Quantité*p.PU) as
montant_HT,montant_HT*1.2 as montant_TTC

FROM commandes as c,details_commandes as dt,produits as p

where c.Num_cmd = dt.Num_cmd

AND p.Ref_pdt = dt.Ref_pdt

AND DateDiff('y',[c.Date_cmd],Date()) >= 365

AND (dt.Quantité*p.PU*1.2 >= 8000) and (dt.Quantité*p.PU*1.2 <= 15000)

Requête analyse croisée

Année universitaire 2019 – 2020


Année universitaire 2019 – 2020
requete de mise à jour

Année universitaire 2019 – 2020

You might also like