Cours 2 : 10 septembre (révisions MySQL)

/* cours 2 : 10 septembre 2014*/

mysql -u root

CREATE DATABASE Sales;

USE Sales;

CREATE TABLE IF NOT EXISTS Salespeople(
Snum INT (4) NOT NULL AUTO_INCREMENT,
Sname VARCHAR (30) NOT NULL,
City VARCHAR (30) NOT NULL,
Comm INT (2) NOT NULL,
PRIMARY KEY (Snum)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Customers(
Cnum INT (4) NOT NULL AUTO_INCREMENT,
Cname VARCHAR (30) NOT NULL,
City VARCHAR (30) NOT NULL,
Rating INT(3),
Snum INT (4) NOT NULL,
PRIMARY KEY (Cnum),
FOREIGN KEY (Snum) REFERENCES Salespeople(Snum)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Orders(
Onum INT (4) NOT NULL AUTO_INCREMENT,
Amt DOUBLE NOT NULL,
Odate DATE,
Cnum INT (4),
Snum INT (4),
PRIMARY KEY (Onum),
FOREIGN KEY (Cnum) REFERENCES Customers(Cnum),
FOREIGN KEY (Snum) REFERENCES Salespeople(Snum)
)ENGINE=INNODB;

/*Questions :

1 : Liste de des vendeurs qui ont déjà vendu à des clients qui ne lui sont pas attitrés?*/
SELECT DISTINCT Sname FROM Salespeople WHERE Snum IN (
SELECT Orders.Snum FROM Orders INNER JOIN Customers ON Customers.Cnum = Orders.Cnum WHERE Customers.Snum != Orders.Snum)
ORDER BY Sname DESC;

/*2: Liste des clients qui n'ont pas de rating?*/
SELECT DISTINCT Cname FROM Customers WHERE Rating IS NULL;
SELECT * FROM Customers WHERE Rating IS NULL;

/*3: Liste des vendeurs dont le nom va de C à F compris.*/
SELECT DISTINCT Sname FROM Salespeople WHERE Sname LIKE 'C%' OR Sname LIKE 'D%' OR Sname LIKE 'E%' OR Sname LIKE 'F%' ORDER BY Sname ASC;
SELECT DISTINCT Sname FROM Salespeople WHERE Sname REGEXP '^C' OR Sname REGEXP '^D' OR Sname REGEXP '^E' OR Sname REGEXP '^F' ORDER BY Sname ASC;
SELECT DISTINCT Sname FROM Salespeople WHERE Sname REGEXP '^[c-fC-F]';
SELECT sname FROM Salespeople WHERE Sname BETWEEN 'C' AND 'G';
SELECT Sname FROM Salespeople WHERE Sname > 'C' AND  Sname < 'G';

/*4: Liste des ventes entre le 1er et le 30 janvier.*/
SELECT DISTINCT Onum FROM Orders WHERE Odate BETWEEN '2014-01-01' AND '2014-01-30';
SELECT * FROM orders WHERE Odate BETWEEN '2014-01-01' AND '2014-01-30';

/*5: La somme des ventes par jour et par vendeur pour autant que cette somme soit supérieure à 3000€*/

SELECT Odate, Snum, SUM(Amt) FROM Orders HAVING SUM(Amt) > 3000 GROUP BY Odate, Snum;
SELECT Odate, Snum, SUM(Amt) AS Somme FROM Orders GROUP BY Odate, Snum HAVING Somme > 3000;

/*6: Nom du vendeur, nom du client, montant de la vente et la date de la vente*/
SELECT Sname AS Vendeur, Cname AS Client, Amt AS Montant, Odate AS DateVente FROM Orders
LEFT JOIN Salespeople ON Salespeople.Snum = Orders.Snum
LEFT JOIN Customers ON Customers.Cnum=Orders.Cnum;

SELECT Sname, Cname, Amt, Odate FROM Customers, Salespeople, Orders
WHERE  Customers.Cnum=Orders.Cnum AND Salespeople.Snum=Orders.Snum;

/* Table EMP

ID Name Mail IDResponsable
1 Tintin NULL
2 Hadock 1
3 Milou 1
4 Castafiore 2

Je veux le nom de chaque employé et de son chef*/

SELECT TChef.name, TEmpl.name FROM EMP AS TChef, EMP AS TEmpl
WHERE TEmpl.IDResponsable=TChef.ID;

SELECT e1.name AS Employe, e2.Name AS Chef FROM EMP AS E1
INNER JOIN EMP AS e2 ON e1.IDResponsable=e2.ID;

/*7: Dans la table client, je veux les noms de ceux qui habitent dans la même ville*/

SELECT TVille.City, TClient.Cname FROM Customers AS TVille, Customers AS TClient
Where TVille.City = TClient.City AND TVille.Cnum < TClient.Cnum;

SELECT TVille.City, TClient.Cname FROM Customers AS TVille, Customers AS TClient
Where TVille.City = TClient.City AND TVille.Cnum > TClient.Cnum;

/*8: Le noms des clients avec leur ville suivi des noms des vendeurs avec leur ville*/
SELECT Cname AS Nom, City FROM Customers
UNION SELECT  Sname AS Nom, City FROM Salespeople;

/* UNION supprime automatiquement les doublons
Pour ne pas supprimer : UNION ALL*/

Merci beaucoup patron!

C'est surtout pour ceux qui ne viennent (déjà) plus aux cours.

Note, vu le prix du minerval chez nous comparé à celui d'autres écoles, je peux comprendre la priorité...

Tu es fort médisant Fab

Je ne médis pas, j'envisage une hypothèse afin d'expliquer l'apparente nonchalance voire le manque d’intérêt des absentéistes pour le cours du mercredi.

Oh que c'est joliment dit!

J'ai failli me tordre le cou au premier mise à part sa ... RAS

merci fab, je confirme que Nico est nonchalant

Monsieur Philippe L.,

Pourrais-je vous signaler, en tout bien tout honneur, que c'est votre propre personne qui était absente lors de ce dernier cours!

Cette nonchalance ne m'implique dès lors pas le moins du monde, tout du moins dans l'hypothèse de ma présence continue lors des prochains cours.

Je vous prie donc de cesser toute affabulation à mon égard.

Bien à vous,

Nicolas D.

Je viens de trouver la preuve sur internet que Phil est nonchalant !!!



Même coiffure, toussa toussa ;-)

hooo mais une présence physique ne signifie pas une présence active au cours mon cher

Monsieur Philippe L.,

Sachez que ma participation aux cours est actuellement très bonne. Pourront en témoigner les quelques témoins qui ont bien voulu aller aux cours cette semaine.

Bien à vous,

Nicolas D.