1030.Movie-Find the titles of all movies directed by Steven Spielberg

时间限制: 1000 MS    内存限制: 32768 KB
提交: 191    解决: 142

题目描述

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;

来源

Unknown