SQL

À partir de maintenant, vous allez untiliser une base de données qui gére le contenu d'une discothèque. Vous manipulerez des informations comme le nom des disques, leurs années de parution, les artistes, les textes des chansons, etc.

L'analyse ne sera pas présentée ici. Nous nous contenterons de donner la structure de la base.

MLD

CREATE TABLE artiste (
                    art_num INTEGER NOT NULL PRIMARY KEY,
                    art_prenom CHARACTER VARYING,
                    art_nom CHARACTER VARYING,
                    art_groupe CHARACTER VARYING,
                    art_modif TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE membre (
                    mem_membre INTEGER NOT NULL REFERENCES artiste(art_num),
                    mem_groupe INTEGER NOT NULL REFERENCES artiste(art_num),
                    mem_debut CHARACTER VARYING NOT NULL,
                    mem_fin CHARACTER VARYING NOT NULL,
                    PRIMARY KEY (mem_membre, mem_groupe, mem_debut, mem_fin));

CREATE TABLE disque (
                    dis_num INTEGER NOT NULL PRIMARY KEY,
                    dis_titre CHARACTER VARYING,
                    dis_artiste INTEGER REFERENCES artiste(art_num),
                    dis_annee CHARACTER VARYING,
                    dis_cddb CHARACTER VARYING,
                    dis_anneeachat CHARACTER VARYING,
                    dis_prixachat NUMERIC(8,2),
                    dis_etat INTEGER REFERENCES etat(eta_num),
                    dis_perdu BOOLEAN DEFAULT false,
                    dis_modif TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE genre (
                    gen_num INTEGER NOT NULL PRIMARY KEY,
                    gen_nom CHARACTER VARYING,
                    gen_modif TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE etat (
                    eta_num INTEGER NOT NULL PRIMARY KEY,
                    eta_nom CHARACTER VARYING);

CREATE TABLE chanson (
                    cha_num INTEGER NOT NULL PRIMARY KEY,
                    cha_titre CHARACTER VARYING,
                    cha_genre INTEGER REFERENCES genre(gen_num),
                    cha_texte CHARACTER VARYING,
                    cha_libre BOOLEAN,
                    cha_modif TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE langue (
                    lan_num CHARACTER VARYING NOT NULL PRIMARY KEY,
                    lan_nom CHARACTER VARYING);

CREATE TABLE langueartiste (
                    laa_langue CHARACTER VARYING NOT NULL REFERENCES langue(lan_num),
                    laa_artiste INTEGER NOT NULL REFERENCES artiste(art_num),
                    laa_maternelle BOOLEAN DEFAULT true,
                    PRIMARY KEY (laa_langue, laa_artiste));

CREATE TABLE languechanson (
                    lac_langue CHARACTER VARYING NOT NULL REFERENCES langue(lan_num),
                    lac_chanson INTEGER NOT NULL REFERENCES chanson(cha_num),
                    lac_principale BOOLEAN DEFAULT true NOT NULL,
                    PRIMARY KEY (lac_langue, lac_chanson, lac_principale));

CREATE TABLE interprete (
                    int_artiste INTEGER NOT NULL (int_artiste) REFERENCES artiste(art_num),
                    int_chanson INTEGER NOT NULL REFERENCES chanson(cha_num),
                    int_disque INTEGER NOT NULL REFERENCES disque(dis_num),
                    int_numero INTEGER NOT NULL,
                    PRIMARY KEY (int_artiste, int_chanson, int_disque, int_numero));

CREATE TABLE ecrit (
                    ecr_artiste INTEGER NOT NULL REFERENCES artiste(art_num),
                    ecr_chanson INTEGER NOT NULL REFERENCES chanson(cha_num),
                    PRIMARY KEY (ecr_artiste, ecr_chanson)
                    );

CREATE TABLE droits (
                    dro_num INTEGER NOT NULL PRIMARY KEY,
                    dro_nom CHARACTER VARYING,
                    dro_niveau INTEGER);

CREATE TABLE autorisations (
                    aut_login CHARACTER VARYING NOT NULL PRIMARY KEY,
                    aut_motdepasse CHARACTER VARYING,
                    aut_nom CHARACTER VARYING,
                    aut_prenom CHARACTER VARYING,
                    aut_droits INTEGER REFERENCES droits(dro_num));

Solution de l'exercice 1. 

Solution de l'exercice 2. 

Solution de l'exercice 3. 

Solution de l'exercice 4. 

Solution de l'exercice 5. 

Solution de l'exercice 6. 

Solution de l'exercice 7. 

On remarquera que cela donne 12 lignes c'est-à-dire 4×3, soit le produit du nombre de lignes de UN et du nombre de lignes de DEUX.

Solution de l'exercice 8. 

Solution de l'exercice 1. 

SELECT * FROM genre ;

Solution de l'exercice 2. 

SELECT * FROM langue ;

Solution de l'exercice 3. 

SELECT aut_nom, aut_prenom, aut_login
  FROM autorisations ;

Solution de l'exercice 4. 

SELECT aut_prenom
  FROM autoristations ;

Solution de l'exercice 5. 

SELECT DISTINCT aut_prenom
  FROM autoristations ;

Solution de l'exercice 6. 

SELECT dis_titre
  FROM disque
 WHERE dis_annee='1969'
   AND dis_etat=2 ;

La valeur 1969 est entre deux « ' » car la colonne DIS_ANNEE est alpha-numérique, ce qui n'est pas le cas pour DIS_ETAT qui est numérique.

Solution de l'exercice 7. 

SELECT gen_num, gen_nom
  FROM genre
 WHERE gen_num>5 ; 

Solution de l'exercice 8. 

SELECT dis_titre, dis_prixachat, dis_cddb
  FROM disque
 WHERE dis_annee = '1981'
   AND dis_etat = 2
   AND dis_prixachat > 15 ;

Solution de l'exercice 9. 

SELECT dis_titre,dis_cddb,dis_annee
  FROM disque
 WHERE dis_titre='IV'
    OR dis_titre='Killer'
    OR dis_titre='No Control' ;

Solution de l'exercice 10. 

SELECT dis_titre,dis_cddb,dis_annee
  FROM disque
 WHERE dis_annee='1971'
   AND (dis_titre='IV' OR dis_titre='Killer') ;

Si les clauses de restriction sont reliées par « AND » ou « OR », ATTENTION aux parenthèses.

Solution de l'exercice 11. 

SELECT dis_titre,dis_cddb,dis_annee
  FROM disque
 WHERE dis_titre='IV'
    OR (dis_annee='1971' AND dis_titre='Killer') ;

Solution de l'exercice 12. 

SELECT dis_titre,dis_cddb,dis_prixachat
  FROM disque
 WHERE dis_prixachat >= 13.5
   AND dis_prix <= 14 ;

La clause « WHERE dis_prixachat >= 13.5 AND dis_prix <= 14 » peut s'écrire avec l'opérateur « BETWEEN » :

SELECT dis_titre,dis_cddb,dis_prixachat
  FROM disque
 WHERE dis_prixachat BETWEEN 13.5 AND 14 ;

Solution de l'exercice 13. 

SELECT art_nom, art_prenom
  FROM artiste
 WHERE art_prenom='Louis' OR art_prenom='Jean' OR art_prenom='Alain' ;

La clause « WHERE colonne IN ('val1', 'val2') » permet d'éviter « WHERE colonne = 'val1' OR colonne = 'val2' » :

SELECT art_nom, art_prenom
  FROM artiste
 WHERE art_prenom IN ('Louis' , 'Jean' , 'Alain') ;

Solution de l'exercice 14. 

SELECT art_nom, art_prenom
  FROM artiste
 WHERE art_nom < art_prenom ;

Solution de l'exercice 15. 

SELECT art_nom
  FROM artiste
 WHERE art_nom LIKE 'H%' ;

Solution de l'exercice 16. 

SELECT art_nom
  FROM artiste
 WHERE art_nom LIKE '%n' ;

Solution de l'exercice 17. 

SELECT art_nom
  FROM artiste
 WHERE art_nom LIKE '__u%' ;

Solution de l'exercice 18. 

  SELECT dis_titre,dis_prixachat
    FROM disque
   WHERE dis_annee='1985'
ORDER BY dis_prixachat ;

Solution de l'exercice 19. 

  SELECT dis_titre,dis_prixachat
    FROM disque
   WHERE dis_annee='1985'
ORDER BY dis_prixachat DESC ;

Solution de l'exercice 20. 

  SELECT dis_titre,dis_annee
    FROM disque
ORDER BY dis_annee DESC, dis_titre ;

Solution de l'exercice 21. 

  SELECT *
    FROM artiste
   WHERE art_nom LIKE 'A%'
ORDER BY art_modif ;

Solution de l'exercice 22. 

SELECT *
  FROM artiste
 WHERE art_nom LIKE 'A%'
   AND art_modif IS NULL ;

Solution de l'exercice 23. 

SELECT *
  FROM artiste
 WHERE art_nom LIKE 'A%'
   AND art_modif IS NOT NULL ;

Solution de l'exercice 24. 

SELECT *
  FROM artiste
 WHERE art_nom LIKE 'A%'
   AND art_modif > '2009-01-01' ;

Par défaut, les colonnes alphanumériques non renseignées ne sont pas considérées comme une suite d'espaces, les colonnes numériques ne sont pas considérées comme égale à 0.

Solution de l'exercice 25. 

SELECT *
  FROM artiste
 WHERE art_nom LIKE 'A%'
   AND art_modif < '2009-01-01' ;

Une fois de plus, remarquez le comportement des lignes non renseignées.

Solution de l'exercice 26. 

SELECT dis_titre,dis_prixachat AS "Prix en euros",
       dis_prixachat/6.5 AS "Prix en francs"
  FROM disque
 WHERE dis_anneeachat < '2002' ;

Solution de l'exercice 27. 

SELECT int_artiste,int_chanson,int_disque,int_numero,
       COALESCE(int_remarque,'Aucune remarque disponible') AS "Remarque?"
  FROM interprete
 WHERE int_artiste = 170 ;

Solution de l'exercice 28. 

SELECT dis_titre,dis_prixachat AS "Prix en euros",
       ROUND(dis_prixachat/6.5,2) AS "Prix en francs"
  FROM disque
 WHERE dis_anneeachat < '2002' ;

Solution de l'exercice 29. 

SELECT art_prenom ||' '|| art_nom ||' '|| art_groupe AS "Artiste"
  FROM artiste ;

Solution de l'exercice 30. 

  SELECT cha_titre AS "Titre",
         CASE WHEN cha_texte IS NULL
              THEN 'paroles inconnues'
              ELSE 'paroles disponibles'
              END AS "Paroles ?"
    FROM chanson
   WHERE cha_titre LIKE 'A%'
ORDER BY 1 ;

Solution de l'exercice 31. 

SELECT SUBSTR (art_nom, 1,5)
  FROM artiste ;

Solution de l'exercice 32.  Pour la position à partir du début :

SELECT art_nom, STRPOS (art_nom,'r')
  FROM artiste ;

Pour avoir le résultat à partir de la troisième lettre, il suffit de « supprimer » les deux premières...

SELECT art_nom, STRPOS (SUBSTR(art_nom,3),'r')
  FROM artiste ;

Solution de l'exercice 33. 

SELECT art_nom, art_prenom, UPPER(art_prenom) , LOWER(art_prenom)
  FROM artiste
 WHERE UPPER(art_nom) = UPPER('Russell') ;

Solution de l'exercice 34. 

SELECT nom, LENGTH(nom)
  FROM emp ;

Solution de l'exercice 35. 

SELECT TRIM(art_prenom ||' '|| UPPER(art_nom) ||' '|| art_groupe) AS "Artiste"
  FROM artiste ;

Solution de l'exercice 36. 

  SELECT aut_nom
    FROM autorisations
UNION
  SELECT art_nom
    FROM artiste ;

Solution de l'exercice 37. 

   SELECT ecr_artiste  AS "Artiste"
     FROM ecrit
INTERSECT
   SELECT int_artiste AS "Artiste"
     FROM interprete ;

Solution de l'exercice 38. 

      SELECT lan_num
        FROM langue
EXCEPT
      SELECT lac_langue
        FROM languechanson ;

Solution de l'exercice 1. 

SELECT aut_nom, aut_prenom, aut_login, dro_nom
  FROM autorisations, droits
 WHERE aut_droits = dro_num ;

Solution de l'exercice 2. 

  SELECT dis_titre, art_prenom, art_nom, art_groupe
    FROM artiste, disque
   WHERE dis_artiste = art_num
ORDER BY art_nom, art_prenom, art_groupe ;

Solution de l'exercice 3. 

SELECT dis_titre, eta_nom
  FROM disque, etat
 WHERE dis_annee='1965' ;

Solution de l'exercice 4. 

SELECT dis_titre, eta_nom
  FROM disque, etat
 WHERE dis_etat=eta_num
   AND dis_annee='1965' ;

Solution de l'exercice 5. 

  SELECT TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe) AS artiste,
         TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe) AS groupe,
         mem_debut AS depuis,
         mem_fin AS "jusqu'à"
    FROM artiste a, artiste g, membre
   WHERE a.art_num = mem_membre
     AND g.art_num = mem_groupe
ORDER BY g.art_nom,g.art_prenom,a.art_nom,a.art_prenom,a.art_groupe,g.art_groupe,mem_debut ;

Solution de l'exercice 6. 

SELECT a.dis_titre,b.dis_titre
  FROM disque a,disque b
 WHERE a.dis_artiste = b.dis_artiste
   AND a.dis_num <> b.dis_num ;

Solution de l'exercice 7. 

SELECT a.dis_titre,b.dis_titre
  FROM disque a,disque b
 WHERE a.dis_artiste = b.dis_artiste
   AND a.dis_num <> b.dis_num
   AND  a.dis_num < b.dis_num ;

Solution de l'exercice 8. 

SELECT TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe) AS artiste,
       LENGTH(TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe)) AS "longueur artiste",
       TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe) AS groupe,
       LENGTH(TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe)) AS "longueur groupe"
  FROM artiste a, artiste g, membre
 WHERE a.art_num = mem_membre
   AND g.art_num = mem_groupe
   AND LENGTH(TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe))>LENGTH(TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe)) ;

Solution de l'exercice 9. 

SELECT d.dis_titre,d.dis_prixachat
  FROM disque d,disque m
 WHERE d.dis_prixachat > m.dis_prixachat
   AND LOWER(m.dis_titre) = 'morrison hotel' ;

Solution de l'exercice 10. 

SELECT d.dis_titre,d.dis_annee
  FROM disque d, disque f
 WHERE d.dis_artiste=f.dis_artiste
   AND LOWER(f.dis_titre) = 'flowers' ;

Solution de l'exercice 11. 

SELECT da.dis_titre,
       TRIM(aa.art_prenom||' '||aa.art_nom||' '||aa.art_groupe),da.dis_annee,
       TRIM(ab.art_prenom||' '||ab.art_nom||' '||ab.art_groupe),db.dis_annee
  FROM disque da,disque db,artiste aa,artiste ab
 WHERE da.dis_titre = db.dis_titre
   AND da.dis_artiste = aa.art_num
   AND db.dis_artiste = ab.art_num
   AND aa.art_num < ab.art_num ;

Solution de l'exercice 12. 

SELECT d.dis_titre,d.dis_annee
  FROM disque d, disque f
 WHERE d.dis_annee=f.dis_annee
   AND LOWER(f.dis_titre) = 'flowers' ;

Mais aussi... (et c'est mieux !) :

SELECT dis_titre,dis_annee
  FROM disque
 WHERE dis_annee = (SELECT dis_annee
                      FROM disque
                     WHERE LOWER(dis_titre) = 'flowers') ;

Solution de l'exercice 13. 

SELECT DISTINCT TRIM(art_prenom||' '||art_nom||' '||art_groupe)
  FROM disque,artiste
 WHERE dis_artiste = art_num
   AND dis_prixachat < ANY (SELECT dis_prixachat
                              FROM disque,artiste
                             WHERE dis_artiste = art_num
                               AND LOWER(art_prenom)='jimi'
                               AND LOWER(art_nom)='hendrix'
                               AND LOWER(art_groupe)='experience') ;

Solution de l'exercice 14. 

SELECT dis_titre
  FROM disque
 WHERE dis_prixachat > ALL (SELECT dis_prixachat
                              FROM disque
                             WHERE dis_annee = '1981') ;

Solution de l'exercice 15. 

SELECT aut_prenom,aut_nom
  FROM autorisations,droits
 WHERE aut_droits = dro_num
   AND LOWER(dro_nom) = 'membre'
   AND LOWER(aut_prenom) IN (SELECT LOWER(aut_prenom)
                               FROM autorisations,droits
                              WHERE aut_droits = dro_num
                                AND LOWER(dro_nom) = 'anonyme') ;

Solution de l'exercice 16. 

SELECT aut_prenom,aut_nom
  FROM autorisations,droits
 WHERE aut_droits = dro_num
   AND LOWER(dro_nom) = 'membre'
   AND LOWER(aut_prenom) NOT IN (SELECT LOWER(aut_prenom)
                                   FROM autorisations,droits
                                  WHERE aut_droits = dro_num
                                    AND LOWER(dro_nom) = 'anonyme') ;

Solution de l'exercice 17. 

SELECT aut_prenom, aut_nom
  FROM autorisations
 WHERE aut_droits = (SELECT aut_droits
                       FROM autorisations
                      WHERE LOWER(aut_nom) = 'fonfec'
                        AND LOWER(aut_prenom) = 'sophie') ;

Solution de l'exercice 18. 

  SELECT dis_titre,dis_prixachat,dis_artiste
    FROM disque d
   WHERE dis_prixachat > (SELECT AVG(dis_prixachat)
                            FROM disque
                           WHERE dis_artiste = d.dis_artiste)
ORDER BY dis_artiste ;

Solution de l'exercice 19. 

SELECT TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe)
  FROM artiste a
 WHERE EXISTS (SELECT NULL
                 FROM disque
                WHERE dis_perdu
                  AND dis_artiste = a.art_num) ;

Solution de l'exercice 20. 

SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe)
  FROM artiste
 WHERE art_num NOT IN (SELECT dis_artiste
                         FROM disque) ;

Solution de l'exercice 21. 

   SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe),dis_titre
     FROM artiste
LEFT JOIN disque ON dis_artiste = art_num
    WHERE art_nom LIKE 'A%' ;

Solution de l'exercice 22.  Avec LEFT JOIN cela donne :

   SELECT TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe) AS artiste,
          TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe) AS artiste
     FROM artiste a
LEFT JOIN membre ON a.art_num = mem_membre
LEFT JOIN artiste g ON g.art_num = mem_groupe
    WHERE a.art_nom LIKE 'A%'
 ORDER BY a.art_nom,a.art_prenom,a.art_groupe ;

On peut aussi se passer de LEFT JOIN (certains systèmes ne l'autorisent pas), mais c'est moins simple. Avec une union cela donne :

(
   SELECT TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe) AS artiste,
          TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe) AS artiste
     FROM artiste a,membre,artiste g
    WHERE a.art_num = mem_membre
      AND g.art_num = mem_groupe
      AND a.art_nom LIKE 'A%'
 UNION
   SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,
          NULL AS artiste
     FROM artiste
    WHERE art_nom LIKE 'A%'
)
ORDER BY 1 ;

Mais on a une ligne vide pour chaque artiste, même quand ce n'est pas nécessaire. On peut améliorer ainsi :

(
   SELECT TRIM(a.art_prenom||' '||a.art_nom||' '||a.art_groupe) AS artiste,
          TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe) AS artiste
     FROM artiste a,membre,artiste g
    WHERE a.art_num = mem_membre
      AND g.art_num = mem_groupe
      AND a.art_nom LIKE 'A%'
 UNION
   SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,
          NULL AS artiste
     FROM artiste
    WHERE art_nom LIKE 'A%'
      AND art_num NOT IN (SELECT mem_membre
                            FROM membre)
)
ORDER BY 1 ;

Solution de l'exercice 1. 

SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO 'European' ;
SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO 'ISO' ;
SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO 'Postgres' ;
SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO DEFAULT ;
SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO 'German' ;
SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO 'NonEuropean' ;
SELECT dis_titre,dis_modif FROM disque ;

SET DATESTYLE TO 'SQL' ;
SELECT dis_titre,dis_modif FROM disque ;

Solution de l'exercice 2. 

SELECT dis_titre,TO_CHAR(dis_modif,'dd/mm/yy')
  FROM disque
 WHERE dis_annee = '1981' ;

Solution de l'exercice 3. 

SELECT dis_titre,TO_CHAR(dis_modif,'Day DD Month YYYY')
  FROM disque
 WHERE dis_annee = '1981' ;

Solution de l'exercice 4. 

SELECT dis_titre,TO_CHAR(dis_modif,'DD MON yyyy HH24:MI:SS')
  FROM disque
 WHERE dis_annee = '1981' ;

Solution de l'exercice 5. 

SELECT TO_CHAR(NOW(),'DD/MM/YYYY') AS "Aujourd'hui",
       TO_CHAR(CURRENT_DATE,'DD/MM/YYYY') AS "Aujourd'hui" ;

Solution de l'exercice 6. 

SELECT TO_CHAR(NOW()::DATE - 1,'DD/MM/YYYY') AS "Hier",
       TO_CHAR(CURRENT_DATE - 1,'DD/MM/YYYY') AS "Hier" ;

Solution de l'exercice 7. 

SELECT CURRENT_DATE - TO_CHAR(CURRENT_DATE,'D')::INTEGER + 1 AS "Dimanche dernier" ;

Solution de l'exercice 8. 

SELECT cha_titre AS titre,TO_CHAR(cha_modif,'DD/MM/YYYY') AS date,
       (CURRENT_DATE-cha_modif::date)||' jours' AS Delai
  FROM chanson
 WHERE cha_modif=(SELECT MAX(cha_modif) FROM chanson) ;

Solution de l'exercice 1. 

  SELECT dis_annee,COUNT(*)
    FROM disque
GROUP BY dis_annee ;

Solution de l'exercice 2. 

  SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,
         AVG(dis_prixachat) as moyenne, SUM(dis_prixachat) as somme
    FROM disque,artiste
   WHERE dis_artiste = art_num
GROUP BY art_num,art_prenom,art_nom,art_groupe
ORDER BY art_nom,art_prenom,art_groupe ;

Solution de l'exercice 3. 

  SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,COUNT(*) AS nbre
    FROM artiste,disque
   WHERE art_num = dis_artiste
GROUP BY art_num,art_prenom,art_nom,art_groupe
  HAVING COUNT(*)>=10
ORDER BY COUNT(*) DESC ;

Solution de l'exercice 4. 

  SELECT SUBSTR(art_nom,1,1), COUNT(*)
    FROM artiste
GROUP BY SUBSTR(art_nom,1,1)
  HAVING COUNT(*) >= 20
ORDER BY 1  ;

Solution de l'exercice 5. 

SELECT MAX(dis_prixachat) ,
       MIN(dis_prixachat) ,
       MAX(dis_prixachat) - MIN(dis_prixachat) AS "Différence"
  FROM disque ;

Solution de l'exercice 6. 

SELECT COUNT(DISTINCT dis_annee)
  FROM disque ;

Essayez aussi, pour voir la différence 

SELECT COUNT(dis_annee)
  FROM disque ;

Solution de l'exercice 7. 

  SELECT gen_nom,lan_nom,COUNT(*)
    FROM chanson,genre,langue,languechanson
   WHERE gen_num=cha_genre
     AND cha_num=lac_chanson
     AND lac_langue=lan_num
GROUP BY lan_nom,gen_nom,lan_num,gen_num
ORDER BY gen_nom,lan_nom ;

Solution de l'exercice 8. 

  SELECT dis_annee, AVG(dis_prixachat)
    FROM disque
GROUP BY dis_annee
  HAVING AVG(dis_prixachat) >= (SELECT AVG(dis_prixachat)
                                  FROM disque
                                 WHERE dis_annee = '1981')
ORDER BY 2 DESC ;

Solution de l'exercice 9. 

SELECT COUNT(cha_texte) AS texte,
       COUNT(cha_libre) AS libre,
       COUNT(cha_modif) AS modif,
       COUNT(*) AS total
  FROM chanson ;

Solution de l'exercice 1. 

  SELECT COUNT(DISTINCT laa_langue) AS "nbre de langues",
         TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste
    FROM artiste,langueartiste
   WHERE art_num=laa_artiste
GROUP BY art_nom,art_prenom,art_groupe,art_num
  HAVING COUNT(DISTINCT laa_langue) > 1 ;

Solution de l'exercice 2. 

    (
        SELECT dis_titre,dis_prixachat,'Minimum' AS "min ou max?"
          FROM disque
         WHERE dis_prixachat=(
                              SELECT MIN(dis_prixachat)
                                FROM disque
                             )
    )
UNION
    (
        SELECT dis_titre,dis_prixachat,'Maximum' AS "min ou max?"
          FROM disque
         WHERE dis_prixachat=(
                              SELECT MAX(dis_prixachat)
                                FROM disque
                             )
    )
ORDER BY 2 ;

Solution de l'exercice 3. 

  SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,COUNT(*) AS nombre
    FROM artiste,disque
   WHERE art_num = dis_artiste
GROUP BY art_prenom,art_nom,art_groupe,art_num
  HAVING COUNT(*) = (
                      SELECT MAX(total)
                        FROM (
                                 SELECT dis_artiste,COUNT(*) AS total
                                   FROM disque
                               GROUP BY dis_artiste
                             ) nbreParArtiste
                    ) ;

Solution de l'exercice 4. 

SELECT dis_titre,dis_prixachat
  FROM disque
 WHERE (
        SELECT ABS(dis_prixachat-(SELECT AVG(dis_prixachat)
          FROM disque))
       )=(
        SELECT MIN(diff)
          FROM (
                 SELECT ABS(dis_prixachat-(
                                           SELECT AVG(dis_prixachat)
                                             FROM disque
                                          )
                            ) AS diff
                   FROM disque
               )  a
       ) ;

ou

SELECT dis_titre,dis_prixachat
  FROM disque,(
               SELECT AVG(dis_prixachat) AS moy
                 FROM disque
              ) r1,
              (
               SELECT MIN(diff) AS mini
                 FROM (
                       SELECT ABS(dis_prixachat-moy) AS diff
                         FROM disque,(
                                      SELECT AVG(dis_prixachat) AS moy
                                        FROM disque
                                     ) r2
                      ) r3
              ) r4
 WHERE mini = ABS(dis_prixachat-moy) ;

Solution de l'exercice 5. 

  SELECT eta_nom,COUNT(*)
    FROM disque,etat
   WHERE dis_etat=eta_num
GROUP BY eta_nom ;

Solution de l'exercice 6. 

  SELECT gen_nom,COUNT(*)
    FROM chanson,genre
   WHERE gen_num=cha_genre
GROUP BY gen_nom,gen_num ;

Solution de l'exercice 7. 

  SELECT lan_nom,COUNT(*)
    FROM chanson,languechanson,langue
   WHERE cha_num=lac_chanson
     AND lan_num=lac_langue
GROUP BY lan_nom ;

Solution de l'exercice 8. 

SELECT COUNT(*)
  FROM chanson
 WHERE cha_libre ;

Solution de l'exercice 9. 

  SELECT TRIM(m.art_prenom||' '||m.art_nom||' '||m.art_groupe) AS artiste,
         TRIM(g.art_prenom||' '||g.art_nom||' '||g.art_groupe) AS groupe,
         CASE WHEN mem_debut<>'' THEN mem_debut ELSE '' END AS depuis,
         CASE WHEN mem_fin<>'' THEN mem_fin ELSE '' END AS jusque
    FROM artiste m,membre,artiste g
   WHERE m.art_num=mem_membre
     AND mem_groupe=g.art_num
ORDER BY g.art_nom,g.art_prenom,g.art_groupe,
         m.art_nom,m.art_prenom,m.art_groupe,
         mem_debut,mem_fin ;

Solution de l'exercice 10. 

  SELECT aut_nom, aut_prenom, aut_login, dro_nom
    FROM autorisations, droits
   WHERE aut_droits = dro_num
ORDER BY 1,2 ;

Solution de l'exercice 11. 

SELECT CASE
            WHEN TO_CHAR(CURRENT_DATE,'D')='1' THEN 'dimanche'
            WHEN TO_CHAR(CURRENT_DATE,'D')='2' THEN 'lundi'
            WHEN TO_CHAR(CURRENT_DATE,'D')='3' THEN 'mardi'
            WHEN TO_CHAR(CURRENT_DATE,'D')='4' THEN 'mercredi'
            WHEN TO_CHAR(CURRENT_DATE,'D')='5' THEN 'jeudi'
            WHEN TO_CHAR(CURRENT_DATE,'D')='6' THEN 'vendredi'
            ELSE 'samedi'
        END AS "jour d'aujourd'hui" ;

Solution de l'exercice 12. 

SELECT TO_CHAR(NOW(),'HH24:MI:SS') AS "Au troisième top..." ;

Solution de l'exercice 13. 

SELECT  COUNT(*) AS nbre
  FROM  artiste,interprete
 WHERE  art_num=int_artiste
   AND  art_prenom='Robert'
   AND  art_nom='Cray'
   AND  art_groupe='' ;

Solution de l'exercice 14. 

  SELECT dro_nom AS Nom,dro_niveau AS niveau,COUNT(*) AS nbre
    FROM autorisations,droits
   WHERE aut_droits=dro_num
GROUP BY dro_nom,dro_niveau ;

Solution de l'exercice 15. 

  SELECT dis_titre,dis_annee
    FROM disque,artiste
   WHERE dis_artiste=art_num
     AND art_nom='Pink Floyd'
ORDER BY 2 DESC,1 ;

Solution de l'exercice 16. 

  SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,COUNT(*)
    FROM disque,artiste
   WHERE dis_artiste=art_num
GROUP BY art_num,art_prenom,art_nom,art_groupe
ORDER BY COUNT(*) DESC
   LIMIT 10 ;

Solution de l'exercice 17. 

  SELECT cha_titre
    FROM chanson
   WHERE LOWER(cha_titre) LIKE '%silver%'
      OR LOWER(cha_texte) LIKE '%silver%'
ORDER BY cha_titre ;

Solution de l'exercice 18. 

  SELECT 'Love' AS "Love hate", (SELECT COUNT(*)
    FROM chanson
   WHERE LOWER(cha_titre) LIKE '%love%') AS nombre
UNION
  SELECT 'Hate' AS "Love hate", (SELECT COUNT(*)
    FROM chanson
   WHERE LOWER(cha_titre) LIKE '%hate%') AS nombre ;

Solution de l'exercice 19. 

  SELECT TO_CHAR(cha_modif,'DD/MM/YYYY') AS jour,COUNT(*) AS nombre
    FROM chanson
GROUP BY TO_CHAR(cha_modif,'DD/MM/YYYY')
ORDER BY COUNT(*) DESC
   LIMIT 5 ;

Solution de l'exercice 20. 

  SELECT TO_CHAR(cha_modif,'HH24') AS heure,COUNT(*) AS nombre
    FROM chanson
GROUP BY TO_CHAR(cha_modif,'HH24')
ORDER BY 1 ;

Solution de l'exercice 21. 

  SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,COUNT(*) AS nombre
    FROM artiste,ecrit
   WHERE art_num=ecr_artiste
GROUP BY art_num,art_prenom,art_nom,art_groupe
ORDER BY art_nom,art_prenom,art_groupe ;

Solution de l'exercice 22. 

  SELECT lan_nom,COUNT(*)
    FROM langue,languechanson
   WHERE lan_num=lac_langue
GROUP BY lan_nom
ORDER BY 1,2 ;

Solution de l'exercice 23. 

  SELECT dis_titre AS disque,
         TRIM(art_prenom||' '||UPPER(art_nom)||' '||art_groupe) AS artiste,
         COUNT(*) AS Nbre
    FROM disque,interprete,artiste
   WHERE dis_num=int_disque
     AND art_num=dis_artiste
GROUP BY dis_num,dis_titre,art_prenom,art_nom,art_groupe
  HAVING COUNT(DISTINCT int_numero)<5 ;

Solution de l'exercice 24. 

SELECT cha_titre
  FROM chanson
 WHERE TO_CHAR(cha_modif,'MM')::INTEGER=5 ;

Solution de l'exercice 25. 

  SELECT cha_num,cha_titre,COUNT(DISTINCT int_disque||'-'||int_numero)
    FROM chanson,interprete
   WHERE cha_num=int_chanson
GROUP BY cha_num,cha_titre
  HAVING COUNT(DISTINCT int_disque||'-'||int_numero)>=5
ORDER BY COUNT(DISTINCT int_disque||'-'||int_numero) DESC ;

Solution de l'exercice 26. 

  SELECT int_numero,cha_titre
    FROM chanson,interprete,disque
   WHERE cha_num=int_chanson
     AND int_disque=dis_num
     AND LOWER(dis_titre)='déjà vu'
ORDER BY int_numero ;

Solution de l'exercice 27. 

   (
      SELECT dis_titre AS "Discographie",dis_annee AS "Année",
             'Seul' AS "Conditions"
        FROM artiste,disque
       WHERE art_num=dis_artiste
         AND art_nom='Reed'
         AND art_prenom='Lou'
         AND art_groupe=''
   )
UNION
   (
      SELECT dis_titre AS "Discographie",dis_annee AS "Année",
             'avec '||TRIM(g.art_prenom||' '||UPPER(g.art_nom)||' '||g.art_groupe) AS "Conditions"
    FROM artiste a,artiste g,membre,disque
   WHERE g.art_num=dis_artiste
     AND a.art_nom='Reed'
     AND a.art_prenom='Lou'
     AND a.art_groupe=''
     AND g.art_num=mem_groupe
     AND a.art_num=mem_membre
   )
ORDER BY 2 ;

Solution de l'exercice 28. 

  SELECT DISTINCT art_prenom,art_nom,art_groupe
    FROM artiste,membre,interprete,chanson,genre
   WHERE art_num=mem_membre
     AND mem_groupe=int_artiste
     AND int_chanson=cha_num
     AND cha_genre=gen_num
     AND LOWER(gen_nom)='rock'
ORDER BY art_nom,art_prenom,art_groupe ;

Solution de l'exercice 29. 

   SELECT TRIM(art_prenom||' '||UPPER(art_nom)||' '||art_groupe) AS artiste,
         count(distinct lan_nom) AS "nbre de langues"
    FROM artiste,interprete,languechanson,langue
   WHERE art_num=int_artiste
     AND int_chanson=lac_chanson
     AND lac_langue=lan_num
GROUP BY art_prenom,art_nom,art_groupe
  HAVING COUNT(DISTINCT lan_num)>1
ORDER BY 1,2 ;