# data[].attributes.attachment_link.description
Un cours sur le langage SQL n’est vraiment utile que si on essai de le mettre en pratique dans un contexte d’usage réel. Exercices 1 : villes de France Cette première série d’exercices possède un niveau de difficulté “facile” permettant …

# data[].attributes.attachment_link.title
SQL Online Compiler - Next gen SQL Editor

# data[].attributes.attachment_link.description
SQL OnLine - Next gen SQL Editor: SQLite, MariaDB / MySQL, PostgreSQL, MS SQL Server. User-friendly interface for data science. No registration for start, No DownLoad, No Install. Online test SQL script. Online Open/Save SQLite file. Online view all table DB. Fiddle link SQL text and DB file.

# data[].attributes.attachment_link.readable_text
-- je cr��e ma table de donn��es
create table client(
id int
, nom varchar(255)
, prenom varchar(255)
, dateN date
, primary key (id)
);

-- j'insert une ligne de donn��es
insert into client(id, nom, prenom, dateN)
values (1, "Smith", "john", "2019-11-23");
insert into client(id, nom, prenom, dateN)
values (2, "Sut", "john", "2018-11-23");
insert into client(id, nom, prenom, dateN)
values (3, "Macron", "manu", "2017-11-23");
insert into client(id, nom, prenom, dateN)
values (4, "Gates", "Bill", "2016-11-23");
insert into client(id, nom, prenom, dateN)
values (5, "Jobs", "Steve", "2015-11-23");
insert into client(id, nom, prenom, dateN)
values (6, "bbacron", "Steve", "2015-11-23");

-- j'affiche ma table de donn��es
select * -- affiche tous les attributs de la table
from client;

# data[].attributes.body
Q1  Donner les noms, marques et prix des produits

Q2  Donner les colonnes des clients

Q3  Donner les différentes marques de produit

Q4  Donner les références des produits et leurs prix majorés de 20%

Q5  Donner les noms des produits de marque IBM

Q6  Lister les clients dont le nom comporte la lettre A en 2ième position

Q7  Lister les produits dont le prix est compris entre 1200€ et 1400€

Q8  Lister les produits de marque IBM ou Apple

Q9  Lister les produits dont le prix est inconnu

Q10 Lister les produits de marque IBM dont le prix est inférieur à 12000€

Q11 Lister les produits en les triant par marques et à l'intérieur d'une marque par prix décroissants

# data[].attributes.attachment_link.readable_text
-- cr��ation d'un table
create TABLE client(
	IdCli 	INT 		NOT NULL AUTO_INCREMENT PRIMARY KEY,
  	Nom    	VARCHAR(50) NOT NULL,
    Ville 	VARCHAR(50) NOT NULL
);
-- insertion des donn��es
INSERT INTO client(Nom, Ville) 
VALUES 
('Jean', 'Paris'),
('Paul', 'PAris'),
('Vincent', 'Evry'),
('Pierre', 'Lyon');

-- suppresion des clients en trop
DELETE FROM client WHERE IdCli > 4
-- insertion des donn��es
INSERT INTO client (Nom, Ville) VALUES ('John', 'Lyon');

-- =======================
-- PARIE : etude des affichages
-- ======================
SELECT 2*5
SELECT * FROM client
SELECT * FROM client WHERE Ville = 'Paris'
-- convertis en majuscule les valeurs dans la colonne "ville"
SELECT * FROM client WHERE UPPER(Ville) = 'PARIS'
/* 
Fonction Scalaire
convertis :
	- en minuscule les valeurs dans la colonne "ville" dans le select
	- en majuscule les valeurs dans la colonne "ville" dans le where
*/
SELECT *, lower(Ville), lower(nom) 
FROM client 
WHERE UPPER(Ville) = 'PARIS'
-- decouverte du LIKE
-- '%' n'importe quel caractere illimit��, 
-- '-' n'importe quel caractere 1 fois
SELECT * FROM client WHERE nom LIKE 'P%'
-- fonction aggr��gation : qui retourne qu'une seule ligne
SELECT max(idcli), MIN(idcli), sum(idcli), COUNT(*) FROM client

-- group by pour regroupper et order by pour trier
-- desc : decroissant, asc (par defaut) : croissant
SELECT COUNT(*), ville 
FROM client 
GROUP BY ville
ORDER BY ville DESC

-- affiche le nombre de client par ville, si elle contienne un 'y'
SELECT COUNT(*), ville 
FROM client 
GROUP BY ville
HAVING ville LIKE '%y%'
ORDER BY ville DESC


INSERT INTO client (Nom, Ville) 
SELECT Nom, Ville
FROM client 

-- AFFICHER LE CLIENT (nom, ville) dont le idcli est le max
SELECT * FROM client ORDER by idcli desc LIMIT 1


-- Q1  Donner les noms, marques et prix des produits
SELECT nom, marque, prix from produit
-- Q2  Donner les colonnes des clients
SELECT * FROM client
-- Q3  Donner les diff��rentes marques de produit
SELECT DISTINCT marque from produit
-- Q4  Donner les r��f��rences des produits et leurs prix major��s de 20%
SELECT idPro,(prix*.2+ prix) from produit
SELECT idPro, prix*1.2 from produit

# data[].attributes.attachment_link.readable_text
-- une instruction manque, laquelle ?


--
-- Structure de la table `post`
--

CREATE TABLE IF NOT EXISTS `post` (
  `post_id` int(11) NOT NULL auto_increment,
  `post_title` varchar(50) NOT NULL,
  `category_id` int(11) default NULL,
  PRIMARY KEY  (`post_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Contenu de la table `post`
--

INSERT INTO `post` (`post_id`, `post_title`, `category_id`) VALUES
(1, 'Bienvenue', 1),
(2, 'Settimeout Javascript', 2),
(3, 'Sprites CSS', 3),
(4, 'Firebug', 2),
(5, 'Google Apps', NULL),
(6, 'Colorisation Photoshop', 3),
(7, 'Base photographie', 5),
(8, 'Jointure SQL', 4),
(9, 'Article top secret', NULL);

--
-- Structure de la table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `category_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Contenu de la table `category`
--

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'News'),
(2, 'Javascript'),
(3, 'Graphisme'),
(4, 'SQL'),
(5, 'Photographie'),
(6, 'PHP');




-- JOIN


-- INNER JOIN


-- LEFT (OUTER) JOIN


-- RIGHT (OUTER) JOIN


-- FULL (OUTER) JOIN

# data[].attributes.attachment_link.readable_text
-- cr��ation d'un table
create TABLE client(
	IdCli 	INT 		NOT NULL AUTO_INCREMENT PRIMARY KEY,
  	Nom    	VARCHAR(50) NOT NULL,
    Ville 	VARCHAR(50) NOT NULL
);
-- insertion des donn��es
INSERT INTO client(Nom, Ville) 
VALUES 
('Jean', 'Paris'),
('Paul', 'PAris'),
('Vincent', 'Evry'),
('Pierre', 'Lyon');

-- suppresion des clients en trop
DELETE FROM client WHERE IdCli > 4
-- insertion des donn��es
INSERT INTO client (Nom, Ville) VALUES ('John', 'Lyon');

-- =======================
-- PARIE : etude des affichages
-- ======================
SELECT 2*5
SELECT * FROM client
SELECT * FROM client WHERE Ville = 'Paris'
-- convertis en majuscule les valeurs dans la colonne "ville"
SELECT * FROM client WHERE UPPER(Ville) = 'PARIS'
/* 
Fonction Scalaire
convertis :
	- en minuscule les valeurs dans la colonne "ville" dans le select
	- en majuscule les valeurs dans la colonne "ville" dans le where
*/
SELECT *, lower(Ville), lower(nom) 
FROM client 
WHERE UPPER(Ville) = 'PARIS'
-- decouverte du LIKE
-- '%' n'importe quel caractere illimit��, 
-- '-' n'importe quel caractere 1 fois
SELECT * FROM client WHERE nom LIKE 'P%'
-- fonction aggr��gation : qui retourne qu'une seule ligne
SELECT max(idcli), MIN(idcli), sum(idcli), COUNT(*) FROM client

-- group by pour regroupper et order by pour trier
-- desc : decroissant, asc (par defaut) : croissant
SELECT COUNT(*), ville 
FROM client 
GROUP BY ville
ORDER BY ville DESC

-- affiche le nombre de client par ville, si elle contienne un 'y'
SELECT COUNT(*), ville 
FROM client 
GROUP BY ville
HAVING ville LIKE '%y%'
ORDER BY ville DESC


INSERT INTO client (Nom, Ville) 
SELECT Nom, Ville
FROM client 

-- AFFICHER LE CLIENT (nom, ville) dont le idcli est le max
SELECT * FROM client ORDER by idcli desc LIMIT 1


-- Q1  Donner les noms, marques et prix des produits
SELECT nom, marque, prix from produit
-- Q2  Donner les colonnes des clients
SELECT * FROM client
-- Q3  Donner les diff��rentes marques de produit
SELECT DISTINCT marque from produit
-- Q4  Donner les r��f��rences des produits et leurs prix major��s de 20%
SELECT idPro,(prix*.2+ prix) from produit
SELECT idPro, prix*1.2 from produit

-- Q5  Donner les noms des produits de marque IBM
SELECT nom from produit where marque = 'IBM'

-- Q6  Lister les clients dont le nom comporte la lettre A en 2i��me position
SELECT * from client where nom LIKE '_A%' -- mysql n'est pas sensible �� la case

-- Q7  Lister les produits dont le prix est compris entre 1200��� et 1400���
SELECT * from produit where prix BETWEEN 1200 AND 1400 -- avec egalit��
SELECT * from produit WHERE prix > 1200 AND prix <= 1400 -- �� pr��ciser les ��galit��s

-- Q8  Lister les produits de marque IBM ou Apple
SELECT * from produit where marque = 'IBM' or marque = 'Apple'
SELECT * from produit where marque IN ('IBM', 'Apple')

-- Q9  Lister les produits dont le prix est inconnu
SELECT * from produit where prix IS NULL
insert into produit (nom, marque) VALUES ('S50', 'samsung')

-- Q10 Lister les produits de marque IBM dont le prix est inf��rieur �� 12000���
select * from produit where marque = 'IBM' AND prix < 12000

-- Q10 bonus
select * from produit where marque = 'IBM' AND (prix < 1200 or marque = 'Apple')

-- Q11 Lister les produits en les triant par marques 
-- et �� l'int��rieur d'une marque par prix d��croissants
SELECT * from produit Order by marque asc, prix desc

-- les clients ayant pass��s une vente : jointure
SELECT * FROM client INNER JOIN vente on client.IdCli = vente.IdCli

-- Q12 donner les noms des produits ayant ��t�� vendu
SELECT DISTINCT nom from produit INNER JOIN vente ON produit.idPro = vente.idPro
SELECT DISTINCT p.nom from produit p INNER JOIN vente v ON p.idPro = v.idPro
SELECT DISTINCT p.nom from produit p JOIN vente v ON p.idPro = v.idPro
SELECT DISTINCT p.nom from produit p, vente v WHERE p.idPro = v.idPro

-- Q13 tous les clients et leur vente meme ceux avec 0 vente
SELECT * FROM client LEFT JOIN vente on client.IdCli = vente.IdCli
SELECT * FROM vente RIGHT JOIN client on client.IdCli = vente.IdCli

-- Q14 les clients n'ayant pas fait de vente
SELECT c.nom FROM client c 
LEFT JOIN vente v on c.IdCli = v.IdCli
WHERE v.idVt IS NULL

-- les clients ayant pass��s une vente : sous requete
SELECT * from client c where c.IdCli IN (SELECT v.IdCli from vente v)

-- les clients qui ont achet�� le 2001-03-16 
-- ==> 2Versions 1) sous rqt 2) jointure
-- 2)
SELECT * from client c join vente v on c.IdCli = v.IdCli
Where v.dateV = '2001-03-16'
-- 1)
SELECT * from client c 
where c.IdCli IN (SELECT v.IdCli from vente v
				  Where v.dateV = '2001-03-16')
				  
				  
SELECT *
	, (select GROUP_CONCAT(dateV SEPARATOR ' - ') from vente v2 WHERE v2.IdCli = c.IdCli) as liste_date
	, (select count(*) from vente v2 WHERE v2.IdCli = c.IdCli) as nb_vente
from client c 
where c.IdCli IN (SELECT v.IdCli from vente v
				  Where v.dateV = '2001-03-16')
				  
-- GROUP_CONCAT : regroupe tout les cellules en une seule cellule	  
SELECT GROUP_CONCAT(concat('voici le client ', upper(nom), ' qui habite �� ', ville)) as message from client

-- Q17) == Q14 donn��e les noms des produits n'ont ��t�� achet��
SELECT * from produit P
WHERE NOT EXISTS (SELECT * FROM vente v WHERE v.idPro = P.idPro)

-- Q18) donner les nom des produits achet�� par tous les clients de Nice (sous rqt)
SELECT p.nom from produit p 
WHERE EXISTS (
              SELECT * FROM vente v 
			  WHERE v.idPro = p.idPro 
			  AND EXISTS (
							SELECT * FROM client c
							WHERE c.IdCli = v.IdCli 
							AND c.ville = 'Nice'
					     )
			  )

-- 
-- select qte from vente where idcli = 1
-- 5 10 15 8

select c.nom, v.qte
from client c 
join vente v on c.idCli = v.IdCli
WHERE v.qte >= ALL (select v2.qte from vente v2 where v2.idcli = 1)


-- Q19) donner le nombre total de 'PS1' vendu avec le nombre de commande
-- exemple 100 PS1 sur 3 commandes
SELECT count(*) "Nb commande", sum(v.qte) "Total vendu"
FROM produit p INNER JOIN vente v ON p.idPro = v.idPro
WHERE p.nom = 'PS1'

-- Q20)Donner les noms des produits moins chers 
-- que la moyenne des prix de tous les produits (fonction :AVG)
SELECT p.nom, p.prix
FROM produit p
WHERE p.prix < (SELECT avg(p1.prix) from produit p1)

-- Q21) Donner les noms des clients habitant la meme ville que john
SELECT c1.nom
FROM client c1, client c2
WHERE c1.ville = c2.ville
AND c1.nom <> 'John'
AND c2.nom = 'John'

-- Q22) Donner les quantit��s totals vendu par marque
SELECT p.marque,  sum(v.qte) "quantit��", ROUND(AVG(v.qte), 2) "Moy qte"
FROM vente v
JOIN produit p on p.idPro = v.idPro
GROUP BY p.marque

-- Mise �� jour : mettre les noms des villes en majuscule
UPDATE client set ville = upper(ville)

-- Mettre �� jours les nom des clients en majuscule unique ceux de PARIS
UPDATE client set nom  = upper(nom) WHERE ville = 'PARIS'

-- delete : supprimer
-- supprimer les clients qui n'ont jamais rien achet��
-- pas compatible MySQL & MariaDB
DELETE FROM client c 
LEFT JOIN vente v on c.IdCli = v.IdCli
WHERE v.idVt IS NULL

--  compatible
DELETE FROM client c WHERE IdCli NOT IN (SELECT DISTINCT IdCli from vente)


-- exemple:
DELETE 
FROM WorkRecord2 
INNER JOIN Employee 
        ON EmployeeRun=EmployeeNo
WHERE Company = '1' 
    AND Date = '2013-05-06'
	
	
-- Q23) donner les quantit��s total acheter par tous les clients
-- et pour ceux qui n'ont rien acheter (null) mettre 0
select c.nom, ifnull(SUM(v.qte), 0) -- ifnull retourn 0 si la valeur est null
FROM client c 
LEFT JOIN vente v on c.IdCli = v.IdCli
GROUP BY c.nom

-- Q24) donner les noms des marque dont les prix moyen par marque est < 1000���
SELECT p.marque
FROM produit p
GROUP BY p.marque
HAVING AVG(p.prix) <1000

# data[].attributes.attachment_link.description
SQL, pour Structured Query Language, est un langage qui permet d’interroger une base de données relationnelle afin de pouvoir modifier ou récupérer des informations. Les bases de données relationnelles permettent de sauvegarder les informations sous forme de tableau à 2 dimensions.

# data[].attributes.attachment_link.readable_text
-- cr��ation d'un table
create TABLE client(
	IdCli 	INT 		NOT NULL AUTO_INCREMENT PRIMARY KEY,
  	Nom    	VARCHAR(50) NOT NULL,
    Ville 	VARCHAR(50) NOT NULL
);
-- insertion des donn��es
INSERT INTO client(Nom, Ville) 
VALUES 
('Jean', 'Paris'),
('Paul', 'PAris'),
('Vincent', 'Evry'),
('Pierre', 'Lyon');

-- suppresion des clients en trop
DELETE FROM client WHERE IdCli > 4
-- insertion des donn��es
INSERT INTO client (Nom, Ville) VALUES ('John', 'Lyon');

-- =======================
-- PARIE : etude des affichages
-- ======================
SELECT 2*5
SELECT * FROM client
SELECT * FROM client WHERE Ville = 'Paris'
-- convertis en majuscule les valeurs dans la colonne "ville"
SELECT * FROM client WHERE UPPER(Ville) = 'PARIS'
/* 
Fonction Scalaire
convertis :
	- en minuscule les valeurs dans la colonne "ville" dans le select
	- en majuscule les valeurs dans la colonne "ville" dans le where
*/
SELECT *, lower(Ville), lower(nom) 
FROM client 
WHERE UPPER(Ville) = 'PARIS'
-- decouverte du LIKE
-- '%' n'importe quel caractere illimit��, 
-- '-' n'importe quel caractere 1 fois
SELECT * FROM client WHERE nom LIKE 'P%'
-- fonction aggr��gation : qui retourne qu'une seule ligne
SELECT max(idcli), MIN(idcli), sum(idcli), COUNT(*) FROM client

-- group by pour regroupper et order by pour trier
-- desc : decroissant, asc (par defaut) : croissant
SELECT COUNT(*), ville 
FROM client 
GROUP BY ville
ORDER BY ville DESC

-- affiche le nombre de client par ville, si elle contienne un 'y'
SELECT COUNT(*), ville 
FROM client 
GROUP BY ville
HAVING ville LIKE '%y%'
ORDER BY ville DESC


INSERT INTO client (Nom, Ville) 
SELECT Nom, Ville
FROM client 

-- AFFICHER LE CLIENT (nom, ville) dont le idcli est le max
SELECT * FROM client ORDER by idcli desc LIMIT 1


-- Q1  Donner les noms, marques et prix des produits
SELECT nom, marque, prix from produit
-- Q2  Donner les colonnes des clients
SELECT * FROM client
-- Q3  Donner les diff��rentes marques de produit
SELECT DISTINCT marque from produit
-- Q4  Donner les r��f��rences des produits et leurs prix major��s de 20%
SELECT idPro,(prix*.2+ prix) from produit
SELECT idPro, prix*1.2 from produit

-- Q5  Donner les noms des produits de marque IBM
SELECT nom from produit where marque = 'IBM'

-- Q6  Lister les clients dont le nom comporte la lettre A en 2i��me position
SELECT * from client where nom LIKE '_A%' -- mysql n'est pas sensible �� la case

-- Q7  Lister les produits dont le prix est compris entre 1200��� et 1400���
SELECT * from produit where prix BETWEEN 1200 AND 1400 -- avec egalit��
SELECT * from produit WHERE prix > 1200 AND prix <= 1400 -- �� pr��ciser les ��galit��s

-- Q8  Lister les produits de marque IBM ou Apple
SELECT * from produit where marque = 'IBM' or marque = 'Apple'
SELECT * from produit where marque IN ('IBM', 'Apple')

-- Q9  Lister les produits dont le prix est inconnu
SELECT * from produit where prix IS NULL
insert into produit (nom, marque) VALUES ('S50', 'samsung')

-- Q10 Lister les produits de marque IBM dont le prix est inf��rieur �� 12000���
select * from produit where marque = 'IBM' AND prix < 12000

-- Q10 bonus
select * from produit where marque = 'IBM' AND (prix < 1200 or marque = 'Apple')

-- Q11 Lister les produits en les triant par marques 
-- et �� l'int��rieur d'une marque par prix d��croissants
SELECT * from produit Order by marque asc, prix desc

-- les clients ayant pass��s une vente : jointure
SELECT * FROM client INNER JOIN vente on client.IdCli = vente.IdCli

-- Q12 donner les noms des produits ayant ��t�� vendu
SELECT DISTINCT nom from produit INNER JOIN vente ON produit.idPro = vente.idPro
SELECT DISTINCT p.nom from produit p INNER JOIN vente v ON p.idPro = v.idPro
SELECT DISTINCT p.nom from produit p JOIN vente v ON p.idPro = v.idPro
SELECT DISTINCT p.nom from produit p, vente v WHERE p.idPro = v.idPro

-- Q13 tous les clients et leur vente meme ceux avec 0 vente
SELECT * FROM client LEFT JOIN vente on client.IdCli = vente.IdCli
SELECT * FROM vente RIGHT JOIN client on client.IdCli = vente.IdCli

-- Q14 les clients n'ayant pas fait de vente
SELECT c.nom FROM client c 
LEFT JOIN vente v on c.IdCli = v.IdCli
WHERE v.idVt IS NULL

-- les clients ayant pass��s une vente : sous requete
SELECT * from client c where c.IdCli IN (SELECT v.IdCli from vente v)

-- les clients qui ont achet�� le 2001-03-16 
-- ==> 2Versions 1) sous rqt 2) jointure
-- 2)
SELECT * from client c join vente v on c.IdCli = v.IdCli
Where v.dateV = '2001-03-16'
-- 1)
SELECT * from client c 
where c.IdCli IN (SELECT v.IdCli from vente v
				  Where v.dateV = '2001-03-16')
				  
				  
SELECT *
	, (select GROUP_CONCAT(dateV SEPARATOR ' - ') from vente v2 WHERE v2.IdCli = c.IdCli) as liste_date
	, (select count(*) from vente v2 WHERE v2.IdCli = c.IdCli) as nb_vente
from client c 
where c.IdCli IN (SELECT v.IdCli from vente v
				  Where v.dateV = '2001-03-16')
				  
-- GROUP_CONCAT : regroupe tout les cellules en une seule cellule	  
SELECT GROUP_CONCAT(concat('voici le client ', upper(nom), ' qui habite �� ', ville)) as message from client

-- Q17) == Q14 donn��e les noms des produits n'ont ��t�� achet��
SELECT * from produit P
WHERE NOT EXISTS (SELECT * FROM vente v WHERE v.idPro = P.idPro)

-- Q18) donner les nom des produits achet�� par tous les clients de Nice (sous rqt)
SELECT p.nom from produit p 
WHERE EXISTS (
              SELECT * FROM vente v 
			  WHERE v.idPro = p.idPro 
			  AND EXISTS (
							SELECT * FROM client c
							WHERE c.IdCli = v.IdCli 
							AND c.ville = 'Nice'
					     )
			  )

-- 
-- select qte from vente where idcli = 1
-- 5 10 15 8

select c.nom, v.qte
from client c 
join vente v on c.idCli = v.IdCli
WHERE v.qte >= ALL (select v2.qte from vente v2 where v2.idcli = 1)


-- Q19) donner le nombre total de 'PS1' vendu avec le nombre de commande
-- exemple 100 PS1 sur 3 commandes
SELECT count(*) "Nb commande", sum(v.qte) "Total vendu"
FROM produit p INNER JOIN vente v ON p.idPro = v.idPro
WHERE p.nom = 'PS1'

-- Q20)Donner les noms des produits moins chers 
-- que la moyenne des prix de tous les produits (fonction :AVG)
SELECT p.nom, p.prix
FROM produit p
WHERE p.prix < (SELECT avg(p1.prix) from produit p1)

-- Q21) Donner les noms des clients habitant la meme ville que john
SELECT c1.nom
FROM client c1, client c2
WHERE c1.ville = c2.ville
AND c1.nom <> 'John'
AND c2.nom = 'John'

-- Q22) Donner les quantit��s totals vendu par marque
SELECT p.marque,  sum(v.qte) "quantit��", ROUND(AVG(v.qte), 2) "Moy qte"
FROM vente v
JOIN produit p on p.idPro = v.idPro
GROUP BY p.marque

-- Mise �� jour : mettre les noms des villes en majuscule
UPDATE client set ville = upper(ville)

-- Mettre �� jours les nom des clients en majuscule unique ceux de PARIS
UPDATE client set nom  = upper(nom) WHERE ville = 'PARIS'

-- delete : supprimer
-- supprimer les clients qui n'ont jamais rien achet��
-- pas compatible MySQL & MariaDB
DELETE FROM client c 
LEFT JOIN vente v on c.IdCli = v.IdCli
WHERE v.idVt IS NULL

--  compatible
DELETE FROM client c WHERE IdCli NOT IN (SELECT DISTINCT IdCli from vente)


-- exemple:
DELETE 
FROM WorkRecord2 
INNER JOIN Employee 
        ON EmployeeRun=EmployeeNo
WHERE Company = '1' 
    AND Date = '2013-05-06'
	
	
-- Q23) donner les quantit��s total acheter par tous les clients
-- et pour ceux qui n'ont rien acheter (null) mettre 0
select c.nom, ifnull(SUM(v.qte), 0) -- ifnull retourn 0 si la valeur est null
FROM client c 
LEFT JOIN vente v on c.IdCli = v.IdCli
GROUP BY c.nom

-- Q24) donner les noms des marque dont les prix moyen par marque est < 1000���
SELECT p.marque
FROM produit p
GROUP BY p.marque
HAVING AVG(p.prix) <1000






SELECT TIMESTAMPDIFF(YEAR, datev, CURRENT_DATE), datev
FROM vente 



SELECT datev, ADDDATE(datev, INTERVAL 3 YEAR)
FROM vente 



SELECT nom, CASE 
			WHEN ville LIKE 'P%' THEN upper(ville)
			WHEN ville LIKE 'L%' THEN upper(ville)
            WHEN nom LIKE 'J%' THEN LOWER(ville)
            Else ville
        END as ville
FROM client 

-- avec CASE WHEN 
-- afficher "onereux" en lieux est place de la colonne marque pour les marques Apple et IBM
SELECT marque, 
		case marque
		WHEN 'IBM' THEN 'onereux'
        WHEn 'APPLE' THEN 'onereux'
        ELSE marque
        END
FROM produit


-- avec CASE WHEN 
-- afficher "onereux" en lieux est place de la colonne marque pour les marques Apple et IBM
SELECT marque, 
		(case 
		WHEN marque IN ('IBM', 'APPLE') THEN 'onereux'
        ELSE marque
        END) as marque2 -- penser �� l'alias
FROM produit


CREATE OR REPLACE VIEw v_client_details
as SELECT c.*, v.IdPro, v.qte, v.dateV
	FROM client c LEFT JOIN vente v ON c.IdCli = v.IdCli;
    
 SELECT * from v_client_details WHERE ville = 'Lyon';
 -- cr��er une vue pour savoir combien de client on a paris => son nom v_nb_parisien
 CREATE OR REPLACE VIEw v_nb_parisien
 as SELECT Count(*) as nb_parisien FROM client WHERE ville = 'Paris';
 
 -- cr��er une vue v_produit_dollars qui reprend toutes les colonnes sauf le prix qui le met en dollars
 -- tx de change $1 = 1,17���
 CREATE OR REPLACE VIEw v_produit_dollars
 as SELECT p.IdPro, p.nom, p.marque, p.prix*1.17 prix_dollars FROM produit p;


 
-- changer le nom des produits de nom IBM 5170 en lenovo45 avec v_produit_dollars
UPDATE v_produit_dollars set nom = 'lenovo45' WHERE nom = 'IBM 5170';
-- SELECT * FROM v_produit_dollars ;
-- mettre �� jour la ville �� Nantes pour le client num 1 via la vue v_client_details
UPDATE v_client_details SET ville = 'Nantes' WHERE idcli = 1;
SELECT * FROM v_client_details ;

CREATE  OR REPLACE VIEW v_client_light as SELECT nom FROM client;


DELIMITER //
CREATE OR REPLACE PROCEDURE afficher_cmd_client(IN p_id_clt INT)
BEGIN

SELECT * FROM vente WHERE idCli = p_id_clt;

END //
DELIMITER ;


CALL afficher_cmd_client(5);





DELIMITER //
CREATE OR REPLACE PROCEDURE afficher_cmd_client(inout p_id_clt INT)
BEGIN

SELECT * FROM vente WHERE idCli = p_id_clt;
SELECT p_id_clt + 1 INTO p_id_clt;
SELECT p_id_clt;

END //
DELIMITER ;

SET @var_id_clt := 4;
CALL afficher_cmd_client(@var_id_clt);
SELECT @var_id_clt;


-- cr��er un proc��dure stocker qui afficher le nom du client
-- l'id du client est donn�� parametre
DELIMITER //
CREATE OR REPLACE PROCEDURE getNomClient(in p_id_clt INT)
BEGIN
	SELECT nom FROM client WHERE idCli = p_id_clt;
END //
DELIMITER ;
CALL getNomClient(2);
