tp3-bdd.md

TP3 : Requêtes SQL intermédiaire

Question 1

SELECT anom as ARTICLE, count(acoul) as NB_COUL
FROM Articles
GROUP BY anom;

Question 2

SELECT anom, MAX(prix), MIN(prix), COUNT(fid) as nb_fournisseurs
FROM Articles JOIN Catalogue USING (aid)
GROUP BY anom
HAVING COUNT(anom) > 1
ORDER BY AVG(prix) DESC;

Question 3

SELECT acoul
FROM Articles
GROUP BY acoul
HAVING COUNT(aid) = 1;

Question 4

SELECT acoul as COULEUR, ROUND(AVG(prix), 2) as PRIX_MOYEN
FROM Articles JOIN Catalogue USING (aid)
WHERE aid NOT IN (SELECT aid FROM Articles WHERE anom LIKE '%Ferrari%')
GROUP BY acoul
HAVING COUNT(fid) > 1
ORDER BY AVG(prix) DESC;

Question 5

SELECT anom, acoul, COUNT(fid)
FROM Articles JOIN Catalogue USING (aid)
GROUP BY anom, acoul;

Question 6

SELECT anom, COUNT(fid)
FROM Articles FULL JOIN Catalogue USING (aid)
GROUP BY anom;

Question 7

C'est faux, mais la question est impossible sinon !

SELECT fnom AS FOURNISSEUR, COUNT(aid) AS NB_A
FROM Catalogue JOIN Fournisseurs USING (fid)
GROUP BY fnom
HAVING COUNT(aid) > 1;

Question 8

SELECT fnom, anom
FROM Fournisseurs NATURAL JOIN Catalogue NATURAL JOIN Articles
GROUP BY fnom, anom
HAVING COUNT(*) > 1;

question 9

SELECT anom
FROM Articles JOIN Catalogue USING (aid)
GROUP BY anom
HAVING COUNT(fid) = 1;

Question 10

SELECT DISTINCT UPPER(LEFT(anom, 1)), COUNT(*)
FROM Articles
GROUP BY anom
ORDER BY UPPER(LEFT(anom, 1));

Question 11

SELECT DISTINCT UPPER(LEFT(acoul, 1)), COUNT(*)
FROM Articles
GROUP BY UPPER(LEFT(acoul, 1))
ORDER BY UPPER(LEFT(acoul, 1));

Question 12

There is no Internet connection

Try: Checking the network cables, modem, and router Reconnecting to Wi-Fi ERR_INTERNET_DISCONNECTED