Exercices (requêtes SELECT) sur la base TeniRaq

La « pensée » du lundi 20 novembre 2017, 23h52m55s :
Le 1er janvier 1945 à Hiroshima les gens s'étaient souhaité une bonne et heureuse année.
  -- Le Chat (Philippe Geluck)
  1. Les rues accompagnées de leurs villes en ordre alphabétique. (132 lignes.)
    SELECT  typevoirie||' '||nomvoirie AS rue,vo.codepostal,ville
    FROM  voiries vo,villes vi,typevoiries t
    WHERE  vo.codepostal=vi.codepostal
    AND  reftypevoirie=codetypevoirie
    ORDER BY  ville ;
  2. Liste des joueurs, dans l'ordre alphabétique des noms, prénoms, ainsi que leur adresse (numéro, rue, code postal, ville). (292 lignes.)
    SELECT  nom,prenom,adressenum,typevoirie,nomvoirie,vo.codepostal,ville
    FROM  badges b,joueurs j,voiries vo,typevoiries t,villes vi
    WHERE  b.numbadge=j.numbadge
    AND  refvoirie=codevoirie
    AND  reftypevoirie=codetypevoirie
    AND  vi.codepostal=vo.codepostal
    ORDER BY  1,2 ;
  3. Les noms et prénoms des personnes qui habitent à Lille. (23 lignes.)
    SELECT  nom,prenom
    FROM  badges b,joueurs j,voiries,villes
    WHERE  b.numbadge=j.numbadge
    AND  refvoirie=codevoirie
    AND  voiries.codepostal=villes.codepostal
    AND  LOWER(ville)='lille'
    ORDER BY  nom,prenom ;
  4. Classement des points défis, du joueur le plus fort au joueur le moins fort. (292 lignes.)
    SELECT  nom,prenom,pointsdefis
    FROM  badges b,joueurs j
    WHERE  b.numbadge=j.numbadge
    ORDER  BY 3 DESC,1,2 ;
  5. Liste des joueurs battus par « Jules LHERMITTE ». (4 lignes.)
    SELECT  b.nom,b.prenom,b.numbadge
    FROM  badges b,badges jl,matchs
    WHERE  jl.nom='Lhermitte'
    AND  jl.prenom='Jules'
    AND  refvainqueur=jl.numbadge
    AND  refperdant=b.numbadge
    ORDER BY  1,2,3 ;
  6. Afficher les noms, prénoms des 10 meilleurs joueurs (ceux qui ont gagné le plus de matchs) en matchs défis. (10 lignes.)
    SELECT  nom,prenom,COUNT(*)
    FROM  matchs,badges
    WHERE  refvainqueur=numbadge
    GROUP BY  nom,prenom
    ORDER BY  3 DESC
    LIMIT  10 ;
  7. Afficher le numéro des badges qui ont réservé il y a 21 jours sur le court C. (0 ligne.)
    SELECT  refreservant
    FROM  reservations
    WHERE  datereservation=CURRENT_DATE-21
    AND  court='C' ;
  8. Liste (nom, prénom) des badges logistiques. (8 lignes.)
    SELECT  nom,prenom
    FROM  badges
    WHERE  numbadge NOT IN (SELECT numbadge FROM joueurs)
    ORDER BY  1,2 ;
  9. Le nombre de personnes par ville. (36 lignes.)
    SELECT  ville,count(*)
    FROM  joueurs,voiries vo,villes vi
    WHERE  vo.codepostal=vi.codepostal
    AND  refvoirie=codevoirie
    GROUP BY  ville
    ORDER BY  ville ;
  10. Les noms et prénoms des 20 personnes qui sont venues le plus souvent. (20 lignes.)
    SELECT  nom,prenom,count(*)
    FROM  badges,acces
    WHERE  numbadge=refbadge
    GROUP BY  numbadge,nom,prenom
    ORDER BY  count(*) DESC
    LIMIT 20 ;
  11. Les noms et prénoms des personnes qui n'ont jamais fait de réservation. (9 lignes.)
    SELECT  nom,prenom
    FROM  badges
    WHERE  numbadge
     NOT IN (SELECT refreservant FROM reservations)
    ORDER BY  nom,prenom ;
  12. Les 5 noms les plus courants. (10 lignes.)
    SELECT  nom,COUNT(*)
    FROM  badges
    GROUP  BY nom
    ORDER  BY 2,1 DESC
    LIMIT  10 ;
  13. Les 5 prénoms les moins courants. (10 lignes.)
    SELECT  prenom,COUNT(*)
    FROM  badges
    GROUP  BY prenom
    ORDER  BY 2,1
    LIMIT  10 ;
  14. Nombre de réservations par joueur. (291 lignes.)
    SELECT  j.numbadge,nom,prenom,COUNT(*)
    FROM  joueurs j,reservations r,badges b
    WHERE  j.numbadge=refreservant
    AND  j.numbadge=b.numbadge
    GROUP BY  j.numbadge,nom,prenom
    ORDER BY   2,3 ;
  15. Proportions de matchs nuls par rapport au nombre de matchs total (en pourcentage). (1 ligne.)
    SELECT 100*(SELECT  COUNT(*)
    FROM  matchs
    WHERE  scorevainqueur=scoreperdant)
     /
    (SELECT  COUNT(*)
    FROM  matchs)
     ||'%' ;
  16. Les jours où il y a eu plus de 7 matchs défis. (14 lignes.)
    SELECT  datematch,COUNT(*)
    FROM  matchs
    GROUP BY  datematch
    HAVING  COUNT(*)>7
    ORDER BY  2 DESC ;
  17. Nombre de personnes concernées pour chaque niveau d'autorisation. (4 lignes.)
    SELECT  niveau,autorisation,COUNT(*)
    FROM  niveauautorisations,badges
    WHERE  codeniveauautorisation=refniveauautorisation
    GROUP BY  codeniveauautorisation,niveau,autorisation
    ORDER BY  niveau DESC ;
  18. Nombre de joueurs par année de naissance, rangés par ordre chronologique inverse. (70 lignes.)
    SELECT  TO_CHAR(datenaiss,'YYYY'),COUNT(*)
    FROM  joueurs
    GROUP  BY TO_CHAR(datenaiss,'YYYY')
    ORDER BY  1 DESC ;
  19. Les joueurs qui ont gagné au moins 10 matchs défis. (9 lignes.)
    SELECT  numbadge,nom,prenom,COUNT(*)
    FROM  badges,matchs
    WHERE  numbadge=refvainqueur
    GROUP BY  numbadge,nom,prenom
    HAVING  COUNT(*)>=10
    ORDER BY  4 DESC,2,3 ;
  20. Les couples (nom,prénom) qui désignent des homonymes, dans l'ordre alphabétique, et combien d'homonymes désignent-ils. (17 lignes.)
    SELECT  nom,prenom,COUNT(*)
    FROM  badges
    GROUP BY  nom,prenom
    HAVING  COUNT(*)>1
    ORDER BY  nom,prenom ;
  21. Moyenne des scores des vainqueurs et moyenne des scores des perdants dans les matchs défis. (1 ligne.)
    SELECT  AVG(scorevainqueur) AS "Moyenne des vainqueurs",
     AVG(scoreperdant) AS "Moyenne des perdants"
    FROM  matchs ;
  22. Journée où il y a eu le plus d'accès. (1 ligne.)
    SELECT  instantacces::DATE,COUNT(*)
    FROM  acces
    GROUP BY  instantacces::DATE
    ORDER BY  2 DESC
    LIMIT  1 ;
  23. Moyenne du nombre de personnes par rue pour chaque chaque ville. (36 lignes.)
    SELECT  ville,AVG(nbre)
    FROM  (
       SELECT  ville,codevoirie,COUNT(*) AS nbre
       FROM  villes vi,voiries vo,joueurs
       WHERE  refvoirie=codevoirie
       AND  vi.codepostal=vo.codepostal
       GROUP BY  ville,codevoirie
     ) XX
    GROUP BY  ville
    ORDER BY  2 DESC,1 ;
  24. La ville dont les habitants ont gagné le plus de points en étant vainqueurs dans des matchs défis. (1 ligne.)
    SELECT  SUM(scorevainqueur),ville
    FROM  matchs,joueurs,voiries vo,villes vi
    WHERE  refvainqueur=numbadge
    AND  refvoirie=codevoirie
    AND  vo.codepostal=vi.codepostal
    GROUP BY  ville
    ORDER BY  1 DESC
    LIMIT  1 ;
  25. La ville dont les habitants ont gagné le moins de points (vainqueurs et perdants) dans des matchs défis. (1 ligne.)
    SELECT  V.ville,vainqueur+perdant
    FROM  (
       SELECT  SUM(scorevainqueur) AS vainqueur,ville
       FROM  matchs,joueurs,voiries vo,villes vi
       WHERE  refvainqueur=numbadge
       AND  refvoirie=codevoirie
       AND  vo.codepostal=vi.codepostal
       GROUP BY  ville
     ) V,
     (
       SELECT  SUM(scoreperdant) AS perdant,ville
       FROM  matchs,joueurs,voiries vo,villes vi
       WHERE  refvainqueur=numbadge
       AND  refvoirie=codevoirie
       AND  vo.codepostal=vi.codepostal
       GROUP BY  ville
     ) P
    WHERE  P.ville=V.ville
    ORDER BY  2
    LIMIT  1 ;
  26. La ville la plus représentée parmi les joueurs (sans utiliser LIMIT). (1 ligne.)
    SELECT  ville
    FROM  villes vi,voiries vo,joueurs
    WHERE  refvoirie=codevoirie
    AND  vi.codepostal=vo.codepostal
    GROUP  BY ville
    HAVING  COUNT(*) = (SELECT MAX(n) FROM  (SELECT  COUNT(*) AS n
       FROM  villes vi,voiries vo,joueurs
       WHERE  refvoirie=codevoirie
       AND  vi.codepostal=vo.codepostal
       GROUP BY  ville) XX
       ) ;
  27. Afficher la date de dimanche dernier. (1 ligne.)
    SELECT CURRENT_DATE - TO_CHAR(CURRENT_DATE,'D')::INTEGER +1 ;
  28. Numéro de badge, nom et prénom des joueurs, accompagnés de leur date de naissance, leur âge au jour près et leur âge en années, du plus vieux au plus jeune. (292 lignes.)
    SELECT  j.numbadge,nom,prenom,datenaiss,
     AGE(CURRENT_DATE,datenaiss),
     EXTRACT(YEAR FROM AGE(CURRENT_DATE,datenaiss))
    FROM  joueurs j,badges b
    WHERE  j.numbadge=b.numbadge
    ORDER BY  datenaiss ;
  29. Numéro de badge, nom et prénom des joueurs, accompagnés de leur âge et leur catégorie. (292 lignes.)
    SELECT  j.numbadge,nom,prenom,datenaiss,
     EXTRACT(YEAR FROM AGE(CURRENT_DATE,datenaiss)),nomcategorie
    FROM  joueurs j,badges b,categories
    WHERE  j.numbadge=b.numbadge
    AND  EXTRACT(YEAR FROM AGE(CURRENT_DATE,datenaiss)) BETWEEN agemini AND agemaxi-1
    ORDER BY  datenaiss,nom,prenom ;
  30. Liste de tous les matchs du mois d'août 2007, en ordre chronologique, sur trois colonnes « Jour du mois,NOM Prénom du vainqueur (Score), NOM prénom du perdant (score) ». (98 lignes.)
    SELECT  TO_CHAR(datematch,'DD') AS jour,
     UPPER(bv.nom)||' '||bv.prenom||' ('||scorevainqueur||')' AS vainqueur,
     UPPER(bp.nom)||' '||bp.prenom||' ('||scoreperdant||')' AS perdant
    FROM  matchs, badges bv,badges bp
    WHERE  bv.numbadge=refvainqueur
    AND  bp.numbadge=refperdant
    AND  datematch BETWEEN '2007-08-01' AND '2007-08-31'
    ORDER BY  1,2 ;
  31. Statistiques par jour (en toutes lettres et en français) de la semaine et par court du nombre de réservations. ( ligne.)
    SELECT  CASE
     WHEN TO_CHAR(datereservation,'D')=1 THEN 'dimanche'
     WHEN TO_CHAR(datereservation,'D')=2 THEN 'lundi'
     WHEN TO_CHAR(datereservation,'D')=3 THEN 'mardi'
     WHEN TO_CHAR(datereservation,'D')=4 THEN 'mercredi'
     WHEN TO_CHAR(datereservation,'D')=5 THEN 'jeudi'
     WHEN TO_CHAR(datereservation,'D')=6 THEN 'vendredi'
     ELSE 'samedi' END AS "Jour",
     court,COUNT(*) AS "Réservations"
    FROM  reservations
    GROUP BY  TO_CHAR(datereservation,'D'),court
    ORDER BY  TO_CHAR(datereservation,'D'),2 ;
  32. Date de naissance, âge au jour près, âge en années des joueurs, du plus vieux au plus jeune. (292 lignes.)
    SELECT  nom,prenom,
     TO_CHAR(datenaiss,'DD MM YYYY') AS naissance,
     AGE(CURRENT_DATE,datenaiss) AS "age complet",
     EXTRACT(YEAR FROM AGE(CURRENT_DATE,datenaiss)) AS age
    FROM  badges b,joueurs j
    WHERE  b.numbadge=j.numbadge
    ORDER  BY datenaiss,nom,prenom ;
  33. Nombre de matchs gagnés et de matchs perdus par joueur (en une seule requête). (292 lignes.)
    SELECT  j.numbadge,nom,prenom,
     (SELECT COUNT(*)  FROM matchs
       WHERE refvainqueur=j.numbadge)
       AS "Matchs gagnés",
     (SELECT COUNT(*)  FROM matchs
       WHERE refperdant=j.numbadge)
       AS "Matchs perdus"
    FROM  joueurs j,badges b
    WHERE  b.numbadge=j.numbadge
    ORDER  BY 4 DESC,5 DESC,2,3 ;
  34. Un vol a été commis dans la salle dans la soirée du 26 septembre 2007. Afficher la liste des joueurs (nom, prénom, accès) qui sont entrés ce jour là après 17 heures. (6 lignes.)
    SELECT  nom,prenom,instantacces
    FROM  joueurs j,badges b,acces
    WHERE  j.numbadge=b.numbadge
    AND  b.numbadge=refbadge
    AND  EXTRACT(HOUR FROM instantacces)>=17
    AND  instantacces::DATE='26-09-2007'
    ORDER BY  3 ;
  35. Afficher la liste des matchs qui se sont déroulés le 1er septembre 2007, sous la forme « Michel DUPOND a battu Marcel DURANT par 5 à 2, à 17 heures ». (6 lignes.)
    SELECT  v.prenom||' '||UPPER(v.nom)
     ||' a battu '||p.prenom||' '||UPPER(p.nom)
     ||' par '||scorevainqueur
     ||' à '||scoreperdant
     ||', à '||heurematch||' heures.' AS "Compte-rendu"
    FROM  matchs,badges v,badges p
    WHERE  refvainqueur=v.numbadge
    AND  refperdant=p.numbadge
    AND  datematch='2007-09-01'
    ORDER BY  heurematch ;
  36. Liste des joueurs (nom, prénom, numéro de badge) qui ont le même classement officiel que Kimberley TARTINN. (14 lignes.)
    SELECT  b.nom,b.prenom,b.numbadge
    FROM  joueurs j, joueurs jef,badges b,badges bef
    WHERE  b.numbadge=j.numbadge
    AND  bef.numbadge=jef.numbadge
    AND  j.classementofficiel=jef.classementofficiel
    AND  LOWER(bef.nom)='tartinn'
    AND  LOWER(bef.prenom)='kimberley'
    ORDER  BY 1,2,3 ;
  37. Nom, prénom et numéro de badge du (des) joueur(s) qui a (ont) le meilleur score en matchs défis. (Ne pas utiliser de vues.) (168 lignes.)
     SELECT  nom,prenom,numbadge
     FROM  badges,matchs
     WHERE  refvainqueur=numbadge
     AND  scorevainqueur=(SELECT MAX(scorevainqueur) FROM matchs)
    UNION
     SELECT  nom,prenom,numbadge
     FROM  badges,matchs
     WHERE  refperdant=numbadge
     AND  scoreperdant=(SELECT MAX(scorevainqueur) FROM matchs)
    ORDER BY  1,2,3 ;
  38. Afficher le nom et le prénom du (des) joueur(s) le(s) plus âgé(s) qui a (ont) joué en matchs défis. (Ne pas utiliser de vues.) (1 ligne.)
    SELECT DISTINCT  nom,prenom,j.numbadge
    FROM  badges b,matchs,joueurs j
    WHERE  j.numbadge=b.numbadge
    AND  (refvainqueur=j.numbadge
    OR  refperdant=j.numbadge)
    AND  datenaiss=(
       SELECT  MIN(datenaiss)
       FROM  joueurs,matchs
       WHERE  refvainqueur=numbadge
       OR  refperdant=numbadge
     ) ;
  39. Nombre de joueurs par catégorie. (2 lignes.)
    SELECT  nomcategorie, COUNT(*)
    FROM  joueurs,categories
    WHERE  EXTRACT(YEAR FROM AGE(CURRENT_DATE,datenaiss)) >= agemini
    AND  EXTRACT(YEAR FROM AGE(CURRENT_DATE,datenaiss)) < agemaxi
    GROUP BY  nomcategorie,agemini
    ORDER BY  agemini ;
Dernière modification : 25/8/2016