L1 informatique : Introduction aux bases de données

TM n°5 : sous requêtes, modification de table

Exercice 1

On considère les tables dept et emp de la base de données shared de https://pw.lacl.fr/gbd.

 CREATE TABLE dept(
  ND int(11) NOT NULL,
  NOM varchar(14),
  VILLE varchar(13),
  PRIMARY KEY(ND)
) ;
CREATE TABLE emp (
  NE int(11) NOT NULL,
  NOM varchar(10),
  JOB varchar(9),
  DATEMB date,
  SAL float(7,2),
  COMM float(7,2),
  ND int(11),
  NEchef int(11),
  PRIMARY KEY(NE),
  FOREIGN KEY(ND) REFERENCES dept(ND),
  FOREIGN KEY(NEchef) REFERENCES emp(NE)
);


Cherchez les questions et testez vos réponses avec phpMyAdmin.
Donner les requêtes en utilisant des sous requêtes pour trouver :
  1. le nom et le salaire des employés du département RESEARCH ;
  2. le nom des employés dont le chef est BLAKE ;
  3. les noms et les salaires des employés qui gagnent plus que le salaire moyen ;
  4. le nom des employés qui ont le plus grand salaire ;
  5. le nom des départements dans lesquels il n'y a pas d'employé ;
  6. le nom et le salaire des employés qui ont un salaire strictement supérieur à tous les employés du département SALES en utilisant ALL ;
  7. le nom des départements dans lesquels il existe un employé qui a un salaire inférieur à une des commissions avec ANY ;
  8. le nom des employés qui ont un salaire plus élévé que celui d'un MANAGER ;
  9. le nom des employés qui ont un salaire plus élevé que leur chef.

Exercice 2

On considère les tables suivantes :

players
id player
 1  Adam
2 Boris
3 Christopher
4 Dennis

scores
id
 player1 player2 date winner
1 1 2 2007-07-03 1
2 2 4 2008-01-04 4
3 2 3 2008-03-22 2
4 1 4 2008-05-21 4
Donner et tester avec phpMyAdmin les requêtes pour :
  1. créer la table players1 avec les mêmes attributs que players ;
  2. modifier la table players1 et ajouter la contrainte AUTO_INCREMENT pour id et la clé primaire id ;
  3. créer la table scores1similaire à scores avec la clé primaire id et les clés étrangères player1 et player2 ;
  4. modifier la table scores1 et ajouter la clé étrangère winner ;
  5. ajouter trois joueuses Alice, Ava, Sabrina  et Sophie ;
  6. ajouter un match entre Ava et Sophie, gagné par Sophie le 21 novembre 2019 ;
  7. modifier la date du match, la nouvelle date est 23 novembre 2019 en utilisant la valeur de la clé primaire du match ;
  8. modifier le nom de Sophie et le remplacer par Sofia dans toute la table ;
  9. effacer Ava  en utilisant la valeur de la clé primaire ; (Est-il possible d'exécuter cette requête quand on active la vérification des clés étrangères ?)
  10. effacer le match entre Sofia et Ava le 23 novembre 2019 ; (Réessayer d'effacer Ava.)
  11. effacer  les joueurs dont le nom commence par S ;
  12. effacer Alice ;
  13. effacer la table players1 ; (Est-il possible d'exécuter cette requête quand on active la vérification des clés étrangères ?)
  14. effacer la table scores1. (Réessayer d'effacer players1.)