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);