nsi

Structure d’une table

1. id_citation, texte & id_film
2. id_citation : int, texte: text & id_film : int
3. nom : text, prenom : text & age : int

Contenu d’une table

1. SELECT texte FROM citations;

2. SELECT id_citation,texte FROM citations

3. +---------+--------------+------+-----+---------+-------+
   | Field   | Type         | Null | Key | Default | Extra |
   +---------+--------------+------+-----+---------+-------+
   | id_film | int(11)      | NO   | PRI | NULL    |       |
   | titre   | varchar(100) | YES  |     | NULL    |       |
   | annee   | int(11)      | YES  |     | NULL    |       |
   +---------+--------------+------+-----+---------+-------+

4. SELECT * FROM films

5. SELECT * FROM citations WHERE id_citation < 6;

6. SELECT * FROM citations WHERE texte LIKE '%je%';

7. SELECT * FROM citations WHERE texte LIKE '%je%' AND texte LIKE '%!';

8. SELECT * FROM citations WHERE texte LIKE texte LIKE '%est%' AND id_citation < 11;

9. Ça marche 😱😱😱

10. UPDATE citations SET texte = 'C'est dans ses rêves que l'homme trouve la liberté, cela fut, est, et restera la vérité.' WHERE id_citation = 5;

11. INSERT INTO citations VALUES (16, 'Je suis ton père', 0);

12. INSERT INTO citations VALUES (16, 'Je suis ton père', 0);

13. DELETE FROM citations WHERE id_citation = 16,

Clé primaire, clé étrangère

1. La clef primaire de la table films s’appelle id_film.
2. Il exite plusieurs entrées id_film de même valeur sur la table citation, hors cela est impossible pour une clef primaire.

3. INSERT INTO citations VALUES (12, 'Star Wars V', 1980);

4. UPDATE citations SET id_film = 12 WHERE id_film = 0;

Jointure

1. SELECT texte, titre, annee FROM citations JOIN films ON citations.id_film = films.id_film  WHERE films.annee < 2000;

Compléments

1. SELECT titre, annee FROM films ORDER BY titre ASC;

2. SELECT texte, titre FROM citations JOIN films ON citations.id_film = films.id_film ORDER BY films.annee ASC;

Exercies

9. +--------+--------------+------+-----+---------+-------+
   | Field  | Type         | Null | Key | Default | Extra |
   +--------+--------------+------+-----+---------+-------+
   | rank   | int(11)      | NO   | PRI | NULL    |       |
   | artist | varchar(100) | YES  |     | NULL    |       |
   | title  | varchar(100) | YES  |     | NULL    |       |
   | year   | int(11)      | YES  |     | NULL    |       |
   +--------+--------------+------+-----+---------+-------+

10. SELECT * from songs ORDER BY rank ASC;

11. SELECT artist, title from songs WHERE rank = 100;

12. SELECT * from songs WHERE artist = "Aretha Franklin";

13. SELECT * from songs WHERE year >= 2000 ORDER BY year ASC;

14. SELECT count(*) from songs WHERE year <= 2000 AND year >= 1900;

15. SELECT * from songs WHERE title LIKE '%rock%';

16. SELECT * from songs WHERE year = 1971;

17. SELECT count(*) AS nb, year from songs GROUP BY year ORDER BY nb DESC;

18. SELECT count(*) AS nb, artist from songs GROUP BY artist ORDER BY nb DESC;