1030.Movie-Find the titles of all movies directed by Steven Spielberg
题目描述
Find the titles(title) of all movies directed by Steven Spielberg
注意:所有题目查询字段名称(红色标出, 大小写随意)和顺序必须和题目给出的一致,否则判错
只能使用英文字符(如 逗号, 小于号< 叹号! 等等),否则会导致Runtime Error
movie ( mID, title, year,
director )
English: There is a movie with ID number mID,
a title, a release year, and a director.
mID |
title |
year |
director |
int |
varchar |
int |
varchar |
101 |
Gone with the Wind |
1939 |
Victor Fleming |
102 |
Star Wars |
1977 |
George Lucas |
103 |
The Sound of Music |
1965 |
Robert Wise |
104 |
E.T. |
1982 |
Steven Spielberg |
105 |
Titanic |
1997 |
James Cameron |
106 |
Snow White |
1937 |
NULL |
107 |
Avatar |
2009 |
James Cameron |
108 |
Raiders of the Lost Ark |
1981 |
Steven Spielberg |
reviewer ( rID, name )
English: The reviewer with ID number rID
has a certain name.
rID |
name |
int |
varchar |
201 |
Sarah Martinez |
202 |
Daniel Lewis |
203 |
Brittany Harris |
204 |
Mike Anderson |
205 |
Chris Jackson |
206 |
Elizabeth Thomas |
207 |
James Cameron |
208 |
Ashley White |
rating ( rID, mID, stars,
ratingDate )
English: The reviewer rID gave the
movie mID a number of stars rating (1-5) on a certain ratingDate.
rID |
mID |
stars |
ratingDate |
int |
int |
int |
date |
201 |
101 |
2 |
2011-01-22 |
201 |
101 |
4 |
2011-01-27 |
202 |
106 |
4 |
NULL |
203 |
103 |
2 |
2011-01-20 |
203 |
108 |
4 |
2011-01-12 |
203 |
108 |
2 |
2011-01-30 |
204 |
101 |
3 |
2011-01-09 |
205 |
103 |
3 |
2011-01-27 |
205 |
104 |
2 |
2011-01-22 |
205 |
108 |
4 |
NULL |
206 |
107 |
3 |
2011-01-15 |
206 |
106 |
5 |
2011-01-19 |
207 |
107 |
5 |
2011-01-20 |
208 |
104 |
3 |
2011-01-02 |
SQL script:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `movie`;
CREATE TABLE `movie`
(
`mID` int(11) NULL DEFAULT NULL,
`title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
NULL DEFAULT NULL,
`year` int(11) NULL DEFAULT NULL,
`director` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE =
utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `movie` VALUES (101, 'Gone with the Wind', 1939, 'Victor Fleming');
INSERT INTO `movie` VALUES (102, 'Star Wars', 1977, 'George Lucas');
INSERT INTO `movie` VALUES (103, 'The Sound of
Music', 1965, 'Robert Wise');
INSERT INTO `movie` VALUES (104, 'E.T.', 1982, 'Steven Spielberg');
INSERT INTO `movie` VALUES (105, 'Titanic', 1997, 'James Cameron');
INSERT INTO `movie` VALUES (106, 'Snow White', 1937, NULL);
INSERT INTO `movie` VALUES (107, 'Avatar', 2009, 'James Cameron');
INSERT INTO `movie` VALUES (108, 'Raiders of the
Lost Ark', 1981, 'Steven Spielberg');
DROP TABLE IF EXISTS `rating`;
CREATE TABLE `rating`
(
`rID` int(11) NULL DEFAULT NULL,
`mID` int(11) NULL DEFAULT NULL,
`stars` int(11) NULL DEFAULT NULL,
`ratingDate` date NULL DEFAULT
NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE =
utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `rating` VALUES (201, 101, 2, '2011-01-22');
INSERT INTO `rating` VALUES (201, 101, 4, '2011-01-27');
INSERT INTO `rating` VALUES (202, 106, 4, NULL);
INSERT INTO `rating` VALUES (203, 103, 2, '2011-01-20');
INSERT INTO `rating` VALUES (203, 108, 4, '2011-01-12');
INSERT INTO `rating` VALUES (203, 108, 2, '2011-01-30');
INSERT INTO `rating` VALUES (204, 101, 3, '2011-01-09');
INSERT INTO `rating` VALUES (205, 103, 3, '2011-01-27');
INSERT INTO `rating` VALUES (205, 104, 2, '2011-01-22');
INSERT INTO `rating` VALUES (205, 108, 4, NULL);
INSERT INTO `rating` VALUES (206, 107, 3, '2011-01-15');
INSERT INTO `rating` VALUES (206, 106, 5, '2011-01-19');
INSERT INTO `rating` VALUES (207, 107, 5, '2011-01-20');
INSERT INTO `rating` VALUES (208, 104, 3, '2011-01-02');
DROP TABLE IF EXISTS `reviewer`;
CREATE TABLE `reviewer`
(
`rID` int(11) NULL DEFAULT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE =
utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `reviewer` VALUES (201, 'Sarah Martinez');
INSERT INTO `reviewer` VALUES (202, 'Daniel Lewis');
INSERT INTO `reviewer` VALUES (203, 'Brittany Harris');
INSERT INTO `reviewer` VALUES (204, 'Mike Anderson');
INSERT INTO `reviewer` VALUES (205, 'Chris Jackson');
INSERT INTO `reviewer` VALUES (206, 'Elizabeth Thomas');
INSERT INTO `reviewer` VALUES (207, 'James Cameron');
INSERT INTO `reviewer` VALUES (208, 'Ashley White');
SET FOREIGN_KEY_CHECKS = 1;