TP_SQL_et_Requetage/tp04.sql at main · Vityy/TP_SQL_et_Requetage · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
-- Exo a --

SELECT *
FROM article
ORDER BY designation;

-- Exo b --

SELECT *
FROM article
ORDER BY prix DESC;

-- Exo c --

SELECT *
FROM article
WHERE designation LIKE '%boulon%'
ORDER BY prix;

-- Exo d --

SELECT *
FROM article
WHERE designation LIKE '%sachet%';

-- Exo e --

SELECT *
FROM article
WHERE LOWER(designation) LIKE '%sachet%';

-- Exo f --

SELECT *
FROM article, fournisseur
WHERE article.id_fou = fournisseur.id
ORDER BY fournisseur.nom ASC, article.prix DESC;

-- Exo g --

SELECT *
FROM article, fournisseur
WHERE article.id_fou = fournisseur.id && fournisseur.nom = 'Dubois & Fils';

-- Exo h --

SELECT AVG(prix)
FROM article, fournisseur
WHERE article.id_fou = fournisseur.id && fournisseur.nom = 'Dubois & Fils';

-- Exo i --

SELECT fournisseur.nom, AVG(prix)
FROM article, fournisseur
WHERE article.id_fou = fournisseur.id
GROUP BY fournisseur.nom;

-- Exo j --

SELECT *
FROM bon
WHERE date_cmde BETWEEN '2019-03-01' AND '2019-04-05 12:00:00';

-- Exo k --

SELECT DISTINCT bon.numero
FROM bon, compo, article
WHERE bon.id = compo.id_bon && compo.id_art = article.id && LOWER(designation) like '%boulon%';

-- Exo l --

SELECT DISTINCT bon.numero, fournisseur.nom
FROM bon, compo, article, fournisseur
WHERE bon.id = compo.id_bon && bon.id_fou = fournisseur.id && compo.id_art = article.id && LOWER(designation) like '%boulon%';

-- Exo m --

SELECT id_bon, SUM(qte * prix)
FROM bon, compo, article
WHERE bon.id = compo.id_bon && compo.id_art = article.id
GROUP BY id_bon;

-- Exo n --

SELECT id_bon, SUM(qte)
FROM bon, compo, article
WHERE bon.id = compo.id_bon && compo.id_art = article.id
GROUP BY id_bon;

-- Exo o --

SELECT id_bon, SUM(qte)
FROM bon, compo, article
WHERE bon.id = compo.id_bon && compo.id_art = article.id
GROUP BY id_bon
HAVING SUM(qte)>25;

-- Exo p --

SELECT SUM(qte * prix)
FROM bon, compo, article
WHERE bon.id = compo.id_bon && compo.id_art = article.id
AND MONTH(date_cmde) = 4;


-- Exo a difficile --

SELECT *
FROM article a, article b
WHERE a.designation = b.designation && a.id_fou != b.id_fou;

-- Exo b difficile --

SELECT YEAR(date_cmde), MONTH(date_cmde), SUM(qte*prix)
FROM bon, compo, article
WHERE bon.id = compo.id_bon && compo.id_art = article.id
GROUP BY YEAR(date_cmde), MONTH(date_cmde);

-- Exo c difficile --

SELECT * FROM bon b
WHERE NOT EXISTS (
    SELECT id_bon FROM compo WHERE id_bon = b.id
);

-- Exo d difficile --

SELECT bon.id_fou, AVG(virtual_table.cout)
FROM bon, (
    SELECT bon.id, SUM(qte * prix) AS cout
    FROM bon, compo, article
    WHERE bon.id = compo.id_bon and compo.id_art = article.id
    GROUP BY bon.id) AS virtual_table
WHERE BON.id= virtual_table.id
GROUP BY BON.id_fou;