Liste des tables :
SELECT * FROM tab;

(par curiosité, afficher le détail d'une table :
DESCRIBE [nom_table];)



Question 1 :

SELECT * FROM [NOM_TABLE];
ou NOM_TABLE est le nom de la table à afficher



Question 2 :

SELECT
	NomServeur, VilleServeur
FROM
	Serveur
ORDER BY
	NomServeur ASC;


Question 3 :

SELECT
	VilleServeur
FROM
	Serveur
ORDER BY
	Villeserveur ASC;


Question 4 :

SELECT
	NomServeur, VilleServeur
FROM
	Serveur
ORDER BY
	Villeserveur ASC;


Question 5 :

SELECT
	NbPlace
FROM
	Tables
WHERE
	NumTable = 4


Question 6 :
SELECT
	NumCons, LibCons, PrixCons
FROM
	Consommation
WHERE
	PrixCons > 1.5




Question 7 :

SELECT
	Numserveur, NomServeur, VilleServeur
FROM
	Serveur
WHERE
	VilleServeur IN('BELFORT', 'DELLE');


Question 8 :

SELECT 
	*
FROM
	Facture
WHERE
	DateFacture = '22/02/06';




Question 9 :


SELECT
	NomServeur
FROM 
	Serveur
WHERE
	NomServeur LIKE '_i%';




Question 10 :

SELECT
	NomServeur
FROM 
	Serveur
WHERE
	NomServeur LIKE 'P%';





Question  11 :

SELECT
	LibCons, NumCons, PrixCons
FROM 
	Consommation
ORDER BY
	LibCons ASC;



Question 12 :

SELECT
	NomServeur, DateNServeur
FROM
	Serveur
WHERE
	DateNServeur LIKE '__/__/76'
ORDER BY
	NomServeur 
ASC;


Question 13 :

SELECT
	LibCons, NumCons, PrixCons
FROM 
	Consommation
WHERE
	LibCons LIKE '%Bière%'
ORDER BY
	LibCons ASC;



Question 14 :
Liste des factures du 22/02/06 servies par Martin Cathy

14.1

SELECT
	Facture.NumFacture
	, Facture.NumTable
	, Tables.NomTable
FROM
	Facture
	, Serveur
	, Tables
WHERE
	Facture.NumServeur = Serveur.NumServeur
AND
	Facture.numTable = Tables.NumTable
AND
	Serveur.NomServeur = 'Martin Cathy'
AND
	Facture.DateFacture = '22/02/06';


14.2
(Sous requete)

SELECT
	Facture.NumTable
	, Tables.NomTable
FROM
	Facture
	, Serveur
	, Tables
WHERE
	Facture.NumServeur = Serveur.NumServeur
AND
	Facture.numTable = Tables.NumTable
AND
	Serveur.NomServeur = 'Martin Cathy'
AND
	Facture.DateFacture = '22/02/06';


14.3

SELECT
	Facture.NumFacture
	, Tables.NomTable
FROM
	Facture
NATURAL JOIN
	Serveur
NATURAL JOIN
	Tables
WHERE
	Serveur.NomServeur = 'Martin Cathy'
AND
	Facture.DateFacture = '22/02/06';


14.4 // ? Ne fonctionne pas

SELECT
	Facture.NumFacture
	, Tables.NomTable
FROM
	Facture
INNER JOIN
	Serveur
USING ("NumServeur")
INNER JOIN
	Tables
USING ("NumTable")
WHERE
	Serveur.NomServeur = 'Martin Cathy'
AND
	Facture.DateFacture = '22/02/06'
;



14.5 

SELECT
	Facture.NumFacture
	, Tables.NomTable
FROM
	Facture
JOIN
	Serveur
ON
	Facture.NumServeur = Serveur.Numserveur
JOIN
	Tables
ON
	FActure.NumTable = Tables.NumTable
WHERE
	Serveur.NomServeur = 'Martin Cathy'
AND
	Facture.DateFacture = '22/02/06';



15
Liste des consommations de la facture 1203
(Numcons, Libcons, Prixcons, Qte, Prixcons*Qte, Nomserveur)
-- Utiliser des alias de tables


SELECT 
	Consommation.NumCons
	, Consommation.LibCons
	, Consommation.PrixCons
	, Comprend.Qte
	, Consommation.PRixCons * Comprend.Qte AS Total
	, Serveur.NomServeur
FROM
	Consommation
INNER JOIN
	Comprend
ON Comprend.NumCons = Consommation.NumCons
INNER JOIN
	Facture
ON Facture.NumFacture = Comprend.NumFacture
INNER JOIN
	Serveur
On Serveur.NumServeur = Facture.NumServeur
WHERE
	Facture.NumFacture = 1203
;



16 Liste des consommations plus chères que l’Orangina
(Numcons, Libcons)
-- Utiliser une sous-requête puis une inéquijointure et aujointure ANSI


SELECT
	NumCons
	, LibCons
FROM
	Consommation
WHERE
	PrixCons > (SELECT PRixCons FROM Consommation WHERE LibCons LIKE '%Orangina%');



17 Noms des serveurs qui n’ont pas encore servi (Numserveur, Nomserveur)
-- 3 solutions : Not Exits, Not In et Minus

SELECT
	NumServeur
	, NomServeur
FROM 
	Serveur
WHERE
	NumServeur NOT IN (SELECT DISTINCT NUmServeur FROM Facture)
;

18 Liste des serveurs qui n’ont pas servi la table n° 1 (Numserveur, Nomserveur)
-- 3 solutions : Not Exits, Not In et Minus

SELECT
	NumServeur
	, NomServeur
FROM 
	Serveur
WHERE
	NumServeur NOT IN (SELECT DISTINCT NUmServeur FROM Facture WHERE NUMTABLE = 1)
;

19 Liste des consommations des factures 1200 et 1201 ( sans lignes en double)
(Numcons, Libcons)
-- jointure prédicative et Jointure Naturelle ANSI

SELECT 	DISTINCT Consommation.NumCons
		, Consommation.LibCons
FROM
	Consommation
INNER JOIN
	Comprend
ON
	Comprend.NumCons = Consommation.NumCons
INNER JOIN
	Facture
ON
	Facture.Numfacture = Comprend.NumFacture
WHERE
	Facture.NumFacture IN (1200, 1201)
;


=====
Pourquoi ne peut-on pas utiliser dans la clause WHERE une colonne utilisée pour une jointure en cas de NATURAL JOIN ?
=====

20 Liste des serveurs qui ont servi la table fenetre3 le 21/02/06
(Numserveur, Nomserveur, Numfacture)

SELECT 
	Serveur.Numserveur
	, Serveur.Nomserveur
	, Facture.Numfacture
FROM
	Serveur
INNER JOIN
	Facture
ON
	Facture.NumServeur = Serveur.NumServeur
INNER JOIN
	Tables
ON
	Tables.NumTable = Facture.NumTable
WHERE
	Tables.NomTable = 'fenetre3'
AND
	Facture.DateFacture = '21/02/06'
;
	


21 Liste des serveurs qui habitent dans la même ville que Durant Pierre
(Numserveur, Nomserveur, Villeserveur)



SELECT
	Numserveur
	, NomServeur
	, VilleServeur
FROM
	Serveur
WHERE
	VilleServeur = (SELECT VilleServeur FROM Serveur WHERE NomServeur = 'Durant Pierre')
;




22 Liste des tables servies après le 21/02/06
(Nomtable, Numserveur, Nomserveur, Numfacture)


SELECT DISTINCT
	NomTable
	, NumServeur
	, NomServeur
	, NumFacture
FROM
	Tables
INNER JOIN
	Facture
ON	
	Facture.NumTable = Tables.NumTable
INNER JOIN
	Serveur
ON
	Serveur.NumServeur = Facture.NumServeur
WHERE
	DateFacture > '21/02/06'
ORDER BY
	NomTable;



23 Liste des consommations qui n’ont encore jamais été commandées
(Numcons, Libcons)
-- Not Exists

SELECT
	NumCons
	, LibCons
FROM
	Consommation
WHERE
	NumCons NOT IN (SELECT DISTINCT NumCons FROM  Comprend)
;


24 Liste des consommations et pour celles qui ont été consommées, liste des
factures dans lesquelles elles sont intervenues.
(Numcons, Libcons, Numfacture)
-- jointure externe 8i et ANSI

SELECT
	Consommation.NumCons
	, Consommation.LibCons
	, Facture.NumFacture
FROM
	Consommation
FULL OUTER JOIN
	Comprend
ON
	Comprend.NumCons = Consommation.NumCons
FULL OUTER JOIN
	Facture
ON
	Facture.NumFacture = Comprend.NumFacture
ORDER BY
	Consommation.NumCons
;

25 Nombre de tables dans le café.
(nb tables)

SELECT
	COUNT(NumTable) AS NB_TABLES
FROM
	TABLES
;
	
26 Nombre de serveurs habitant Belfort. (Nbserveur)

SELECT
	COUNT(NUmServeur) AS NbServeur
FROM
	Serveur
WHERE
	VilleServeur = 'BELFORT'
;



27 Nombre de factures effectués par le serveur n° 53 depuis le début de l'année
2006. (Nombrefacture)


SELECT
	COUNT(NumFacture) As NombreFacture
FROM
	Facture
WHERE
	NumServeur = 53
AND
	DateFacture >= '01/01/06'
;



28 Nombre de clients servi par Martin depuis son embauche. (Nombrefacture)


SELECT
	COUNT(NumFacture) AS NombreFacture
FROM
	Facture
INNER JOIN
	Serveur
ON
	Serveur.NumServeur = Facture.NumServeur
WHERE
	Serveur.NomServeur = 'Martin Cathy'
;



29 Nombre de factures établies dans les journées du 21/02/06 et 22/02/06.
(Nbfactures)


SELECT
	COUNT(NumFacture) AS NombreFacture
FROM
	Facture
WHERE
	DateFacture BETWEEN '21/02/06' AND '22/02/06'
;



30 Montant de la facture N° 1204.
(Numfacture, Montant)
-- Afficher seulement le montant, puis le numéro de facture et le montant

SELECT
	SUM(Consommation.PrixCons * Comprend.QTE)  AS Montant
FROM
	Comprend
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
WHERE
	Comprend.NumFacture = 1204
;

SELECT
	Comprend.NumFacture
	, SUM(Consommation.PrixCons * Comprend.QTE)  AS Montant
FROM
	Comprend
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
WHERE
	Comprend.NumFacture = 1204
GROUP BY
	Comprend.NumFacture
;


31 Chiffre d’affaires du mois de février 2006
(Montanttotalfacture)


SELECT
	SUM(Comprend.Qte * Consommation.PrixCons) AS MontantTotalFacture
FROM
	Comprend
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
INNER JOIN
	Facture
ON
	Facture.NumFacture = Comprend.NumFacture
WHERE
	Facture.DateFacture LIKE '__/02/06'
;


32 Nombre de consommations par facture.
(Numfacture, nbconso)
-- nbconso = somme des quantités

SELECT
	Facture.NumFacture
	, COUNT(Comprend.NumCons) AS NbConso
FROM
	Facture
INNER JOIN
	Comprend
ON
	Comprend.NumFacture = Facture.NumFacture
GROUP BY
	Facture.NumFacture
;

33 Consommation la moins chère.
(Numcons, Libcons, Prixcons)


SELECT
	NumCons
	, LibCons
	, PrixCons
FROM
	Consommation
WHERE
	ROWNUM <= 1
ORDER BY
	PRixCons 
ASC
;


34 Consommation la plus chère servie par Pillot.
(Numcons, Libcons, Prixcons)

/!\ NE FONCTIONNE PAS /!\

SELECT 
	Consommation.NumCons
	, Consommation.LibCons
	, Consommation.PrixCons
	, ROWNUM AS numLigne
FROM
	Consommation
INNER JOIN
	Comprend
ON
	Comprend.NumCons = Consommation.NumCons
INNER JOIN
	Facture
ON
	Facture.NumFacture = Comprend.NumFacture
INNER JOIN
	Serveur
ON
	Serveur.NumServeur = Facture.NumServeur
WHERE 
	Consommation.PrixCons = MAX
(SELECT
	Consommation.PrixCons
FROM
	Consommation
INNER JOIN
	Comprend
ON
	Comprend.NumCons = Consommation.NumCons
INNER JOIN
	Facture
ON
	Facture.NumFacture = Comprend.NumFacture
INNER JOIN
	Serveur
ON
	Serveur.NumServeur = Facture.NumServeur
WHERE
	Serveur.NomServeur LIKE 'Pillot %')
;
Solution : Récupérer la consommation la plus chere servie par le serveur, et "égaliser" avec une 2eme requete



35 Nombre de factures établies chaque jour.
(Datefacture, Nbfactures)

SELECT
	DateFacture
	, COUNT(NumFacture) AS NbFacture
FROM
	Facture
GROUP BY
	DateFacture
ORDER BY
	DateFacture
;



36 Nombre de factures établies par chaque serveur.
(Numserveur, Nbfacture)
-- Tous les serveurs doivent être dans le résultat

SELECT
	Serveur.NumServeur
	, COUNT(Facture.NumFacture) As Nbfacture
FROM
	Serveur
LEFT JOIN
	Facture
ON
	Facture.NumServeur = Serveur.NumServeur
GROUP BY
	Serveur.NumServeur
;




37 Nombre de factures établies par chaque serveur.
(Numserveur, Nomserveur, Nbfacture)


SELECT
	Serveur.NumServeur
	, Serveur.Nomserveur
	, COUNT(Facture.NumFacture) AS Nbfacture
FROM
	Serveur
LEFT JOIN
	Facture
ON
	Facture.NumServeur = Serveur.NumServeur
GROUP BY
	Serveur.NumServeur
	, Serveur.NomServeur
;



38 Liste des serveurs qui ont établi plus de 3 factures.
(Numserveur, Nomserveur, Nbfacture)


SELECT
	Serveur.NumServeur
	, Serveur.Nomserveur
	, COUNT(Facture.NumFacture) AS Nbfacture
FROM
	Serveur
LEFT JOIN
	Facture
ON
	Facture.NumServeur = Serveur.NumServeur
GROUP BY
	Serveur.NumServeur
	, Serveur.NomServeur
HAVING COUNT(Facture.NumFacture) >= 3
;



39 Chiffre d’affaire de chaque serveur
(Numserveur, montanttotalfacture)

SELECT
	Serveur.NumServeur
	,SUM(Comprend.Qte * Consommation.PrixCons) AS montanttotalfacture
FROM
	Serveur
INNER JOIN
	Facture
ON
	Facture.NumServeur = Serveur.NumServeur
INNER JOIN
	Comprend
ON
	Comprend.NumFacture = Facture.NumFacture
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
GROUP BY
	Serveur.NumServeur
;


40 Prix moyen des consommations.
(prixmoyen)

SELECT AVG(PRixCons)
FROM
	Consommation
;


41 Prix moyen du café.
(prixmoyen)

SELECT 
	AVG(PRixCons) AS PrixMoyen
FROM
	Consommation
WHERE
	LibCONs LIKE '%Café%'
;




42 Quantité moyenne consommée pour chaque consommation.
(Numcons, Qtémoyenne)


SELECT
	NumCons
	, AVG(Qte) As QteMoyenne
FROM
	Comprend
GROUP BY
	NumCons
;



43 Quantité moyenne consommée pour chaque consommation.
(Numcons, Libcons, Qtémoyenne)

SELECT
	Consommation.NumCons
	, AVG(Comprend.Qte) As QteMoyenne
	, Consommation.LibCons
FROM
	Comprend
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
GROUP BY
	Consommation.NumCons
	, Consommation.LibCons
;


44 Nombre de serveurs par ville
(Villeserveur, nbserveur)

SELECT
	COUNT(NumServeur) AS Nbserveur
	, VilleServeur
FROM
	Serveur
GROUP BY
	VilleServeur
;



45 Factures établies avant le 22/02/06 et qui comportent plus de 3 consommations
différentes.
(Numfacture, DateFacture, Nbconso)

SELECT
	Facture.Numfacture
	, Facture.Datefacture
	, COUNT(Comprend.NumCons) AS NbConso
FROM
	Facture
INNER JOIN
	Comprend
ON
	Comprend.NumFacture = Facture.Numfacture
GROUP BY
	Facture.NumFacture
	, Facture.DateFacture
HAVING COUNT(Comprend.NumCons) > 3
;



46 Liste des villes dans lesquelles habitent plus d’un serveur.
(villeserveur, nbserveur)


SELECT 
	Villeserveur
	, COUNT(NumServeur) AS Nbserveur
FROM
	Serveur
GROUP BY
	VilleServeur
HAVING
	Count(NumServeur) > 1
;




47 Consommations qui interviennent dans plus de deux factures.
(Numcons, Libcons, Nbfactures)



SELECT
	Consommation.NumCons
	, Consommation.LibCons
	, COUNT(Comprend.NumFacture) AS Nbfactures
FROM
	Consommation
INNER JOIN
	Comprend
ON
	Comprend.NumCons = Consommation.NumCons
GROUP BY
	Consommation.NumCons
	, Consommation.LibCons
HAVING
	COUNT(Comprend.NumFacture) > 2
;








*****************************

TP Séance 3 

*****************************




48 Tables pour lesquelles le chiffre d’affaires réalisé est le plus élevé.
(Numtable, montanttotalfacture)


SELECT
	Tables.NumTable
	, SUM(Comprend.Qte * Consommation.PrixCons) AS montanttotalfacture
FROM
	Tables
INNER JOIN
	Facture
ON
	Facture.NumTable = Tables.NumTable
INNER JOIN
	Comprend
ON
	Comprend.NumFacture = Facture.NumFacture
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
GROUP BY
	Tables.NumTable
ORDER BY
	SUM(Comprend.Qte * Consommation.PrixCons) DESC


Le résultat attendu semble etre le bon (La "meilleure" table est la 5), mais je n'ai pas réussi à faire les "sous" requetes (R1 et R2) comme demandé dans le poly.


49 Calculer le chiffre d'affaires par Année et Mois
(Année, Mois, MontantTotalFactures)


50 Calculer le chiffre d'affaires par Année et Mois (inclure le total par année et


général) (Année, Mois, MontantTotalFactures)

51 Calculer le chiffre d'affaires par Ville et Année (inclure le total par ville, année
et général) (Villeserveur, Année, MontantTotalFactures)

SELECT
	EXTRACT(YEAR FROM Facture.DateFacture)          AS Annee
	, EXTRACT(MONTH FROM Facture.DateFacture)       AS Mois
	, SUM(Comprend.NumCons * Consommation.PrixCons) AS montanttotalfacture
FROM
	Facture
INNER JOIN
	Comprend
ON
	comprend.NumFacture = Facture.NumFacture
INNER JOIN
	Consommation
ON
	Consommation.NumCons = Comprend.NumCons
GROUP BY
	ROLLUP(EXTRACT(YEAR FROM Facture.DateFacture)	
	, EXTRACT(MONTH FROM Facture.DateFacture))
;

Note : le EXTRACT n'est pas censé servir à ca, mais plus pour bidouiller du XML.


52 Liste des consommations qui apparaissent dans toutes les factures du mois de
février 2006.
(Numcons, Libcons)


SELECT NUMCONS,LIBCONS
FROM CONSOMMATION C1
WHERE NOT EXISTS
(
  SELECT NUMFACTURE
  FROM FACTURE
  WHERE NOT EXISTS
  (
    SELECT NUMFACTURE
    FROM COMPREND
    WHERE COMPREND.NUMFACTURE=FACTURE.NUMFACTURE
    AND COMPREND.NUMCONS=C1.NUMCONS
  )
  AND DATEFACTURE LIKE '%/02/06'
);


53 A l'aide de l'opérarteur REGEXP_LIKE afficher tous les serveurs sont l'adresse
EMAIL est conforme au modèle p.nom @ .fr

SELECT
	Serveur.Email
FROM
	Serveur
WHERE
	REGEXP_LIKE (Serveur.Email, 'p+.[\w]@[\w].fr');

??!?

54 Affichez les adresses contenant un L ou R en lettre majuscule

SELECT
	Serveur.Email
FROM
	Serveur
WHERE
	REGEXP_LIKE (Serveur.Email, '*[L,R]*');




55 Extraire le prénom du serveur.

SELECT 
	REGEXP_REPLACE(SERVEUR.NomServeur, '[A-Za-z]+[ ]', '') AS PrenomServeur
FROM 
	Serveur;