SQL 練習/電影院

關係模式

練習

1. 選擇所有電影的標題。

點選檢視解決方案

 SELECT Title 
   FROM Movies;

2. 顯示資料庫中所有不同的評分。

點選檢視解決方案

 SELECT DISTINCT Rating 
   FROM Movies;

3. 顯示所有未評分的電影。

點選檢視解決方案

 SELECT * 
   FROM Movies 
  WHERE Rating IS NULL;

4. 選擇當前沒有上映電影的所有電影院。

點選檢視解決方案

 SELECT * 
   FROM MovieTheaters 
  WHERE Movie IS NULL;

5. 從所有電影院中選擇所有資料，以及正在電影院上映的電影資料（如果有）。

點選檢視解決方案

 SELECT *
   FROM MovieTheaters JOIN Movies
   ON MovieTheaters.Movie = Movies.Code;

6. 從所有電影中選擇所有資料，如果該電影正在電影院上映，則顯示電影院的資料。

點選檢視解決方案

 SELECT *
   FROM MovieTheaters RIGHT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code;

7. 顯示當前在任何影院都沒有上映的電影的標題。

點選檢視解決方案

 /* With JOIN */
 SELECT Movies.Title
   FROM MovieTheaters RIGHT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code
   WHERE MovieTheaters.Movie IS NULL;

 /* With subquery */
 SELECT Title FROM Movies
   WHERE Code NOT IN
   (
     SELECT Movie FROM MovieTheaters
     WHERE Movie IS NOT NULL
   );

8. 新增未評分電影“One, Two, Three”。

點選檢視解決方案

 INSERT INTO Movies(Title,Rating) VALUES('One, Two, Three',NULL);

9. 將所有未評分電影的評分設定為“G”。

點選檢視解決方案

 UPDATE Movies SET Rating='G' WHERE Rating IS NULL;

10. 刪除放映評分為“NC-17”的電影的電影院。

點選檢視解決方案

 DELETE FROM MovieTheaters WHERE Movie IN
   (SELECT Code FROM Movies WHERE Rating = 'NC-17');

表建立程式碼

 CREATE TABLE Movies (
   Code INTEGER PRIMARY KEY NOT NULL,
   Title TEXT NOT NULL,
   Rating TEXT 
 );
  
 CREATE TABLE MovieTheaters (
   Code INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL,
   Movie INTEGER  
     CONSTRAINT fk_Movies_Code REFERENCES Movies(Code)
 );

點選檢視 MySQL 語法。

 CREATE TABLE Movies (
   Code INTEGER PRIMARY KEY,
   Title VARCHAR(255) NOT NULL,
   Rating VARCHAR(255) 
 );
 
 CREATE TABLE MovieTheaters (
   Code INTEGER PRIMARY KEY,
   Name VARCHAR(255) NOT NULL,
   Movie INTEGER,  
     FOREIGN KEY (Movie) REFERENCES Movies(Code)
 ) ENGINE=INNODB;

示例資料集

 INSERT INTO Movies(Code,Title,Rating) VALUES(9,'Citizen King','G');
 INSERT INTO Movies(Code,Title,Rating) VALUES(1,'Citizen Kane','PG');
 INSERT INTO Movies(Code,Title,Rating) VALUES(2,'Singin'' in the Rain','G');
 INSERT INTO Movies(Code,Title,Rating) VALUES(3,'The Wizard of Oz','G');
 INSERT INTO Movies(Code,Title,Rating) VALUES(4,'The Quiet Man',NULL);
 INSERT INTO Movies(Code,Title,Rating) VALUES(5,'North by Northwest',NULL);
 INSERT INTO Movies(Code,Title,Rating) VALUES(6,'The Last Tango in Paris','NC-17');
 INSERT INTO Movies(Code,Title,Rating) VALUES(7,'Some Like it Hot','PG-13');
 INSERT INTO Movies(Code,Title,Rating) VALUES(8,'A Night at the Opera',NULL);
 
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(1,'Odeon',5);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(2,'Imperial',1);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(3,'Majestic',NULL);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(4,'Royale',6);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(5,'Paraiso',3);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(6,'Nickelodeon',NULL);