Aller au contenu principal

Travaux dirigés

Sélection de colonnes​

Afficher les titres et identifiants des albums.​

select title, album_id from album;
titlealbum_id
Heart of the Night38
For Those About To Rock We Salute You1
Balls to the Wall2

Afficher les colonnes prénom, nom et ville de la table clients.​

select first_name, last_name, city from customer;
first_namelast_namecity
LuísGonçalvesSão José dos Campos
LeonieKöhlerStuttgart
FrançoisTremblayMontréal
BjørnHansenOslo

Afficher le nom complet des clients.​

Le nom complet est la concaténation dans une seule colonne du prénom et du nom. Donner le titre NomComplet à cette colonne.

L'opérateur || est l'opérateur de concaténation de chaîne équivalent à + mais pour le texte.

Le mot clé as permet de spécifier ou renommer le titre d'une colonne.

select first_name || ' ' || last_name as nom_complet, city from customer;
nom_completcity
Luís GonçalvesSão José dos Campos
Leonie KöhlerStuttgart
François TremblayMontréal
Bjørn HansenOslo

Tri​

Affichez les noms des artistes triés par ordre alphabétique.​

select * from artist order by name asc;

Filtre​

Afficher toutes les pistes (Name) dont la durée (Milliseconds) est supérieure à 5 minutes.​

select * from track where milliseconds > 5 * 60 * 1000

Afficher les clients dont l'âge est inférieur à 25 ans.​

select * from customer where age < 25;

Afficher les clients dont l'âge est entre 30 et 40 ans.​

select * from customer where age between 30 and 40;

Afficher les clients vivant au Brésil.​

select * from customer where upper(country) = 'BRAZIL';
attention

Le respect des majuscules n'est pas obligatoire pour le nom des tables et colonnes mais cela l'est pour les valeurs.
brazil est différent de Brazil.

Afficher les clients dont l'age est inférieur à 25 ans et qui vivent aux USA.​

select * from customer where age < 25 and upper(country) = 'USA'

L'opérateur and effectue un ET logique entre 2 conditions.

Ici aussi il faut respecter les majuscules pour la valeur USA.

Afficher les albums qui sont sortis entre 1984 et 1989.​

select * from album where year between 1984 and 1989;

équivalent à

select * from album where year >= 1984 and year <= 1989;

L'opérateur between combine supérieur ou égal >= ET inférieur ou égal <= dans une seule commande

Afficher les employés qui ne sont pas de la ville de Calgary.​

select * from employee where city <> 'Calgary';

Il est possible d'utiliser l'opérateur != à la place de <> mais ce n'est pas l'opérateur standard. Il n'est pas reconnu par tous les systèmes.

select * from employee where not city = 'Calgary';

Afficher les clients de France, Belgique et Allemagne (France, Belgium, Germany)​

select * from customer where upper(country) in ('FRANCE', 'BELGIUM', 'GERMANY');
select * from customer where upper(country) = 'FRANCE'
    or upper(country) = 'BELGIUM'
    or  upper(country) ='GERMANY';

Afficher les clients dont le nom commence par 'M'​

select * from customer where last_name like 'M%';

Les dates​

Récupérez toutes les factures (InvoiceId, InvoiceDate, Total) émises en 2010.​

Lister le total des factures en détaillant dans des colonnes séparées l'année iso, le mois, le n° de semaine et le n° de jour (iso) de la semaine​

select total,
  extract('isoyear' from invoice_date),
  extract('month' from invoice_date),
  extract('week' from invoice_date),
  extract('isodow' from invoice_date)
from invoice

Afficher les jours de la semaine avec leur nom (lundi, mardi, mercredi, ...) plutôt qu'avec un nombre.​

select total,
  extract('isoyear' from invoice_date),
  extract('month' from invoice_date),
  extract('week' from invoice_date),
  extract('isodow' from invoice_date),
  case  extract('dow' from invoice_date)
    when 0 then 'Dimanche'
    when 1 then 'Lundi'
    when 2 then 'Mardi'
    when 3 then 'Mercredi'
    when 4 then 'Jeudi'
    when 5 then 'Vendredi'
    when 6 then 'Samedi'
  end
from invoice
to_char(invoice_date, 'TMDay')

Top​

Pour afficher un top il suffit de trier par ordre décroissant DESC et de limiter le nombre de lignes.

Afficher les 10 pistes les plus longues en durée.​

Afficher les 5 factures les plus importantes par montant total.​

Null​

[!WARNING] Null est égal à rien même pas à lui même. Il faut utiliser les opérateurs IS NULL ou IS NOT NULL

  • Listez les employés (FirstName, LastName) qui reportent à un manager (ReportsTo non nul).
  • Le client qui n'a pas de numéro de téléphone
ALTER TABLE IF EXISTS album
    ADD COLUMN year integer;

update album set year = 1999 where album_id = 1;
update album set year = 1995 where album_id = 2;
update album set year = 1997 where album_id = 3;
update album set year = 2008 where album_id = 4;
update album set year = 1980 where album_id = 5;
update album set year = 1999 where album_id = 6;
update album set year = 1974 where album_id = 7;
update album set year = 1987 where album_id = 8;
update album set year = 1987 where album_id = 9;
update album set year = 1992 where album_id = 10;
update album set year = 1974 where album_id = 11;
update album set year = 2001 where album_id = 12;
update album set year = 2003 where album_id = 13;
update album set year = 1984 where album_id = 14;
update album set year = 1987 where album_id = 15;
update album set year = 2000 where album_id = 16;
update album set year = 1982 where album_id = 17;
update album set year = 2009 where album_id = 18;
update album set year = 1987 where album_id = 19;
update album set year = 1988 where album_id = 20;
update album set year = 1970 where album_id = 21;
update album set year = 1992 where album_id = 22;
update album set year = 1979 where album_id = 23;
update album set year = 1991 where album_id = 24;
update album set year = 1995 where album_id = 25;
update album set year = 2001 where album_id = 26;
update album set year = 1985 where album_id = 27;
update album set year = 2011 where album_id = 28;
update album set year = 2019 where album_id = 29;
update album set year = 1975 where album_id = 30;
update album set year = 2009 where album_id = 31;
update album set year = 1973 where album_id = 32;
update album set year = 1990 where album_id = 33;
update album set year = 1985 where album_id = 34;
update album set year = 2007 where album_id = 35;
update album set year = 1997 where album_id = 36;
update album set year = 2004 where album_id = 37;
update album set year = 1997 where album_id = 38;
update album set year = 1995 where album_id = 39;
update album set year = 1994 where album_id = 40;
update album set year = 1987 where album_id = 41;
update album set year = 1975 where album_id = 42;
update album set year = 2005 where album_id = 43;
update album set year = 2014 where album_id = 44;
update album set year = 2018 where album_id = 45;
update album set year = 1980 where album_id = 46;
update album set year = 1975 where album_id = 47;
update album set year = 1995 where album_id = 48;
update album set year = 2009 where album_id = 49;
update album set year = 2018 where album_id = 50;
update album set year = 2003 where album_id = 51;
update album set year = 1978 where album_id = 52;
update album set year = 1995 where album_id = 53;
update album set year = 2002 where album_id = 54;
update album set year = 2011 where album_id = 55;
update album set year = 1998 where album_id = 56;
update album set year = 1977 where album_id = 57;
update album set year = 1987 where album_id = 58;
update album set year = 2001 where album_id = 59;
update album set year = 1992 where album_id = 60;
update album set year = 1986 where album_id = 61;
update album set year = 2011 where album_id = 62;
update album set year = 1989 where album_id = 63;
update album set year = 1997 where album_id = 64;
update album set year = 2011 where album_id = 65;
update album set year = 1991 where album_id = 66;
update album set year = 2010 where album_id = 67;
update album set year = 1993 where album_id = 68;
update album set year = 1995 where album_id = 69;
update album set year = 1977 where album_id = 70;
update album set year = 1978 where album_id = 71;
update album set year = 1975 where album_id = 72;
update album set year = 2009 where album_id = 73;
update album set year = 2010 where album_id = 74;
update album set year = 2002 where album_id = 75;
update album set year = 1976 where album_id = 76;
update album set year = 1988 where album_id = 77;
update album set year = 2018 where album_id = 78;
update album set year = 1985 where album_id = 79;
update album set year = 1983 where album_id = 80;
update album set year = 1991 where album_id = 81;
update album set year = 1974 where album_id = 82;
update album set year = 1988 where album_id = 83;
update album set year = 2012 where album_id = 84;
update album set year = 2004 where album_id = 85;
update album set year = 1998 where album_id = 86;
update album set year = 1985 where album_id = 87;
update album set year = 1972 where album_id = 88;
update album set year = 1984 where album_id = 89;
update album set year = 1977 where album_id = 90;
update album set year = 1995 where album_id = 91;
update album set year = 1983 where album_id = 92;
update album set year = 1971 where album_id = 93;
update album set year = 1970 where album_id = 94;
update album set year = 1973 where album_id = 95;
update album set year = 2004 where album_id = 96;
update album set year = 1972 where album_id = 97;
update album set year = 1983 where album_id = 98;
update album set year = 2007 where album_id = 99;
update album set year = 1981 where album_id = 100;
update album set year = 1979 where album_id = 101;
update album set year = 1999 where album_id = 102;
update album set year = 1980 where album_id = 103;
update album set year = 1996 where album_id = 104;
update album set year = 2018 where album_id = 105;
update album set year = 1998 where album_id = 106;
update album set year = 1996 where album_id = 107;
update album set year = 1976 where album_id = 108;
update album set year = 1984 where album_id = 109;
update album set year = 2011 where album_id = 110;
update album set year = 1994 where album_id = 111;
update album set year = 2011 where album_id = 112;
update album set year = 2020 where album_id = 113;
update album set year = 2014 where album_id = 114;
update album set year = 1977 where album_id = 115;
update album set year = 1992 where album_id = 116;
update album set year = 1977 where album_id = 117;
update album set year = 1988 where album_id = 118;
update album set year = 2003 where album_id = 119;
update album set year = 1997 where album_id = 120;
update album set year = 1997 where album_id = 121;
update album set year = 1979 where album_id = 122;
update album set year = 2014 where album_id = 123;
update album set year = 2020 where album_id = 124;
update album set year = 1995 where album_id = 125;
update album set year = 1988 where album_id = 126;
update album set year = 2005 where album_id = 127;
update album set year = 2011 where album_id = 128;
update album set year = 1975 where album_id = 129;
update album set year = 1991 where album_id = 130;
update album set year = 1984 where album_id = 131;
update album set year = 2008 where album_id = 132;
update album set year = 1980 where album_id = 133;
update album set year = 1981 where album_id = 134;
update album set year = 2001 where album_id = 135;
update album set year = 1994 where album_id = 136;
update album set year = 2003 where album_id = 137;
update album set year = 1998 where album_id = 138;
update album set year = 1970 where album_id = 139;
update album set year = 1998 where album_id = 140;
update album set year = 1995 where album_id = 141;
update album set year = 2007 where album_id = 142;
update album set year = 2002 where album_id = 143;
update album set year = 1997 where album_id = 144;
update album set year = 2013 where album_id = 145;
update album set year = 1976 where album_id = 146;
update album set year = 1994 where album_id = 147;
update album set year = 1970 where album_id = 148;
update album set year = 1985 where album_id = 149;
update album set year = 1992 where album_id = 150;
update album set year = 2010 where album_id = 151;
update album set year = 2012 where album_id = 152;
update album set year = 1984 where album_id = 153;
update album set year = 1973 where album_id = 154;
update album set year = 1982 where album_id = 155;
update album set year = 1998 where album_id = 156;
update album set year = 1971 where album_id = 157;
update album set year = 1997 where album_id = 158;
update album set year = 1996 where album_id = 159;
update album set year = 1985 where album_id = 160;
update album set year = 1989 where album_id = 161;
update album set year = 1971 where album_id = 162;
update album set year = 2020 where album_id = 163;
update album set year = 1989 where album_id = 164;
update album set year = 1974 where album_id = 165;
update album set year = 2009 where album_id = 166;
update album set year = 1997 where album_id = 167;
update album set year = 2014 where album_id = 168;
update album set year = 2018 where album_id = 169;
update album set year = 1975 where album_id = 170;
update album set year = 2019 where album_id = 171;
update album set year = 1982 where album_id = 172;
update album set year = 2004 where album_id = 173;
update album set year = 1990 where album_id = 174;
update album set year = 2006 where album_id = 175;
update album set year = 1987 where album_id = 176;
update album set year = 2004 where album_id = 177;
update album set year = 1992 where album_id = 178;
update album set year = 1973 where album_id = 179;
update album set year = 1998 where album_id = 180;
update album set year = 1987 where album_id = 181;
update album set year = 1983 where album_id = 182;
update album set year = 2003 where album_id = 183;
update album set year = 2002 where album_id = 184;
update album set year = 1996 where album_id = 185;
update album set year = 1993 where album_id = 186;
update album set year = 2017 where album_id = 187;
update album set year = 1990 where album_id = 188;
update album set year = 2000 where album_id = 189;
update album set year = 1994 where album_id = 190;
update album set year = 1990 where album_id = 191;
update album set year = 1973 where album_id = 192;
update album set year = 2002 where album_id = 193;
update album set year = 1979 where album_id = 194;
update album set year = 2015 where album_id = 195;
update album set year = 1997 where album_id = 196;
update album set year = 2011 where album_id = 197;
update album set year = 1976 where album_id = 198;
update album set year = 1975 where album_id = 199;
update album set year = 1987 where album_id = 200;
update album set year = 1975 where album_id = 201;
update album set year = 1978 where album_id = 202;
update album set year = 2013 where album_id = 203;
update album set year = 2003 where album_id = 204;
update album set year = 1999 where album_id = 205;
update album set year = 2008 where album_id = 206;
update album set year = 1979 where album_id = 207;
update album set year = 1993 where album_id = 208;
update album set year = 1997 where album_id = 209;
update album set year = 2017 where album_id = 210;
update album set year = 1999 where album_id = 211;
update album set year = 2005 where album_id = 212;
update album set year = 2007 where album_id = 213;
update album set year = 1998 where album_id = 214;
update album set year = 1994 where album_id = 215;
update album set year = 1986 where album_id = 216;
update album set year = 2008 where album_id = 217;
update album set year = 2011 where album_id = 218;
update album set year = 1977 where album_id = 219;
update album set year = 1988 where album_id = 220;
update album set year = 1989 where album_id = 221;
update album set year = 2010 where album_id = 222;
update album set year = 2014 where album_id = 223;
update album set year = 1996 where album_id = 224;
update album set year = 1989 where album_id = 225;
update album set year = 1978 where album_id = 226;
update album set year = 2019 where album_id = 227;
update album set year = 1986 where album_id = 228;
update album set year = 1972 where album_id = 229;
update album set year = 1993 where album_id = 230;
update album set year = 2003 where album_id = 231;
update album set year = 1979 where album_id = 232;
update album set year = 1970 where album_id = 233;
update album set year = 1973 where album_id = 234;
update album set year = 2008 where album_id = 235;
update album set year = 1976 where album_id = 236;
update album set year = 2002 where album_id = 237;
update album set year = 2015 where album_id = 238;
update album set year = 1980 where album_id = 239;
update album set year = 2010 where album_id = 240;
update album set year = 1976 where album_id = 241;
update album set year = 1984 where album_id = 242;
update album set year = 1991 where album_id = 243;
update album set year = 1982 where album_id = 244;
update album set year = 2018 where album_id = 245;
update album set year = 1979 where album_id = 246;
update album set year = 2005 where album_id = 247;
update album set year = 1996 where album_id = 248;
update album set year = 2003 where album_id = 249;
update album set year = 2015 where album_id = 250;
update album set year = 1983 where album_id = 251;
update album set year = 2005 where album_id = 252;
update album set year = 1971 where album_id = 253;
update album set year = 2008 where album_id = 254;
update album set year = 1999 where album_id = 255;
update album set year = 1989 where album_id = 256;
update album set year = 1988 where album_id = 257;
update album set year = 1977 where album_id = 258;
update album set year = 1993 where album_id = 259;
update album set year = 1998 where album_id = 260;
update album set year = 1988 where album_id = 261;
update album set year = 1982 where album_id = 262;
update album set year = 1993 where album_id = 263;
update album set year = 2016 where album_id = 264;
update album set year = 2001 where album_id = 265;
update album set year = 2019 where album_id = 266;
update album set year = 1977 where album_id = 267;
update album set year = 1977 where album_id = 268;
update album set year = 2002 where album_id = 269;
update album set year = 2020 where album_id = 270;
update album set year = 1984 where album_id = 271;
update album set year = 2005 where album_id = 272;
update album set year = 1973 where album_id = 273;
update album set year = 2013 where album_id = 274;
update album set year = 1984 where album_id = 275;
update album set year = 2017 where album_id = 276;
update album set year = 1971 where album_id = 277;
update album set year = 2003 where album_id = 278;
update album set year = 2020 where album_id = 279;
update album set year = 1993 where album_id = 280;
update album set year = 1972 where album_id = 281;
update album set year = 1998 where album_id = 282;
update album set year = 1970 where album_id = 283;
update album set year = 2014 where album_id = 284;
update album set year = 2005 where album_id = 285;
update album set year = 2014 where album_id = 286;
update album set year = 1979 where album_id = 287;
update album set year = 1978 where album_id = 288;
update album set year = 2016 where album_id = 289;
update album set year = 1987 where album_id = 290;
update album set year = 1997 where album_id = 291;
update album set year = 1982 where album_id = 292;
update album set year = 1978 where album_id = 293;
update album set year = 2013 where album_id = 294;
update album set year = 1987 where album_id = 295;
update album set year = 1983 where album_id = 296;
update album set year = 2009 where album_id = 297;
update album set year = 1979 where album_id = 298;
update album set year = 1972 where album_id = 299;
update album set year = 1989 where album_id = 300;
update album set year = 1984 where album_id = 301;
update album set year = 2012 where album_id = 302;
update album set year = 2014 where album_id = 303;
update album set year = 2011 where album_id = 304;
update album set year = 2019 where album_id = 305;
update album set year = 1976 where album_id = 306;
update album set year = 1986 where album_id = 307;
update album set year = 1988 where album_id = 308;
update album set year = 1998 where album_id = 309;
update album set year = 1971 where album_id = 310;
update album set year = 2012 where album_id = 311;
update album set year = 2018 where album_id = 312;
update album set year = 1994 where album_id = 313;
update album set year = 1983 where album_id = 314;
update album set year = 1981 where album_id = 315;
update album set year = 1992 where album_id = 316;
update album set year = 1974 where album_id = 317;
update album set year = 1973 where album_id = 318;
update album set year = 2010 where album_id = 319;
update album set year = 1994 where album_id = 320;
update album set year = 1986 where album_id = 321;
update album set year = 2004 where album_id = 322;
update album set year = 2010 where album_id = 323;
update album set year = 1986 where album_id = 324;
update album set year = 1976 where album_id = 325;
update album set year = 1978 where album_id = 326;
update album set year = 2004 where album_id = 327;
update album set year = 2006 where album_id = 328;
update album set year = 1985 where album_id = 329;
update album set year = 2020 where album_id = 330;
update album set year = 1988 where album_id = 331;
update album set year = 2015 where album_id = 332;
update album set year = 1987 where album_id = 333;
update album set year = 1985 where album_id = 334;
update album set year = 1994 where album_id = 335;
update album set year = 1990 where album_id = 336;
update album set year = 2011 where album_id = 337;
update album set year = 1974 where album_id = 338;
update album set year = 1980 where album_id = 339;
update album set year = 1974 where album_id = 340;
update album set year = 2017 where album_id = 341;
update album set year = 1995 where album_id = 342;
update album set year = 2017 where album_id = 343;
update album set year = 2006 where album_id = 344;
update album set year = 2005 where album_id = 345;
update album set year = 2019 where album_id = 346;
update album set year = 1996 where album_id = 347;

ALTER TABLE IF EXISTS customer
    ADD COLUMN age integer;

update customer set age = 47 where customer_id = 1;
update customer set age = 30 where customer_id = 2;
update customer set age = 24 where customer_id = 3;
update customer set age = 45 where customer_id = 4;
update customer set age = 30 where customer_id = 5;
update customer set age = 40 where customer_id = 6;
update customer set age = 22 where customer_id = 7;
update customer set age = 32 where customer_id = 8;
update customer set age = 40 where customer_id = 9;
update customer set age = 44 where customer_id = 10;
update customer set age = 41 where customer_id = 11;
update customer set age = 47 where customer_id = 12;
update customer set age = 31 where customer_id = 13;
update customer set age = 38 where customer_id = 14;
update customer set age = 37 where customer_id = 15;
update customer set age = 42 where customer_id = 16;
update customer set age = 21 where customer_id = 17;
update customer set age = 55 where customer_id = 18;
update customer set age = 79 where customer_id = 19;
update customer set age = 77 where customer_id = 20;
update customer set age = 64 where customer_id = 21;
update customer set age = 62 where customer_id = 22;
update customer set age = 52 where customer_id = 23;
update customer set age = 18 where customer_id = 24;
update customer set age = 78 where customer_id = 25;
update customer set age = 72 where customer_id = 26;
update customer set age = 67 where customer_id = 27;
update customer set age = 47 where customer_id = 28;
update customer set age = 39 where customer_id = 29;
update customer set age = 38 where customer_id = 30;
update customer set age = 39 where customer_id = 31;
update customer set age = 19 where customer_id = 32;
update customer set age = 43 where customer_id = 33;
update customer set age = 59 where customer_id = 34;
update customer set age = 32 where customer_id = 35;
update customer set age = 65 where customer_id = 36;
update customer set age = 38 where customer_id = 37;
update customer set age = 67 where customer_id = 38;
update customer set age = 23 where customer_id = 39;
update customer set age = 54 where customer_id = 40;
update customer set age = 25 where customer_id = 41;
update customer set age = 33 where customer_id = 42;
update customer set age = 27 where customer_id = 43;
update customer set age = 52 where customer_id = 44;
update customer set age = 34 where customer_id = 45;
update customer set age = 20 where customer_id = 46;
update customer set age = 22 where customer_id = 47;
update customer set age = 43 where customer_id = 48;
update customer set age = 43 where customer_id = 49;
update customer set age = 51 where customer_id = 50;
update customer set age = 73 where customer_id = 51;
update customer set age = 22 where customer_id = 52;
update customer set age = 25 where customer_id = 53;
update customer set age = 64 where customer_id = 54;
update customer set age = 19 where customer_id = 55;
update customer set age = 29 where customer_id = 56;
update customer set age = 47 where customer_id = 57;
update customer set age = 52 where customer_id = 58;
update customer set age = 66 where customer_id = 59;