Description
I attached the files
Unformatted Attachment Preview
=========================================================================
• Query to create tables and insert values are given in “MidExam.txt”
• As a submission you need to submit a word document with the answers and
output screenshots
============================================================================================
Consider the following relations in a movie database.
Movie (title:char(25), year:int, length:float, language:char(15), type:char(1), directorName: char(30))
MovieStar (name: char (15), country:varchar(40), gender:char(1), birthdate: date)
StarsIn (movieTitle:char(25), movieYear:int, starname:char(15), role:varchar(15))
Theater (theaterName: char (20), country: varchar (40), city: varchar (20), capacity: int)
Show (movieTitle: char (25), theaterName: char (20), datetime: datetime, ticketPrice: real, spectators: int)
The attributes of the Movie relation are title of the movie, year it was made, language, filmtype which may
be ‘F’ or ‘D’ for feature or documentary respectively and the name of the director of the movie. The
MovieStar relation has attributes to record the name, country, gender (‘M’ or ‘F’), and birthday of stars.
StarsIn relation associates the movie with stars that acted in them and contains the role (‘lead’, ‘support’, or
‘other’) they played. The Theater relation has attributes to store the name, country, city and the capacity of
theaters. Show relation stores information movies shown in theaters. It stores the title of the movie shown,
theater where the movie is shown, price of the ticket to see the movie and the number of specters who are
there to see the movie. The primary keys of all relations are underlined.
Write SQL statements to answer the following queries
1.
Find the names of the directors who had worked with American stars. Assume that if an actor/actress
stars in a movie the directed by a director the actor/actress works with that director.
2.
Find the movies in English for which all seats are booked in a theater.
3.
Display the names of stars who have acted in 3 or more movies in any year between 2017 and 2018.
4.
Find the names of male stars who had only starred in lead roles in 2018.
5.
Find the names of the stars who has appeared in same movie with ‘Robert Downey’.
6.
Find the names of feature movies which is viewed by at least 1 Million spectators in total.
7.
Find the total income of each movie shown in theaters in America
8.
Find the name of the theaters located in ‘New York’ which shows both ‘The Passenger’ and ‘Jurassic
World’ on 1st January-2018.
9.
Find the feature movies for which all shows have more than 200 spectators.
10. Find the name of the most popular movie. The most popular movie is the movie viewed by most
spectators.
Movie
title
Avengers
Black Panther
Ghost in Shell
Jurrasic World
Passengers
Spider-man
year
2018
2018
2017
2018
2016
2018
length language filmtype
3 English
F
3 English
F
2.5 English
D
2.75 English
D
2.75 English
F
2.5 English
F
MovieStar
name
Bryce Howard
Chadwick Boseman
Chris Pratt
Jennifer Lawrence
Robert Downey
Scarlett Johansson
Tom Holland
country
America
America
England
America
America
America
England
Theater
theaterName
Beverly
Cinnemaworld
Electric
Grand Rex
Nitehawk
country
America
Australia
England
France
America
gender
F
M
M
F
M
F
M
city
LA
Melbourne
London
Paris
New York
DirectorName
Anthony Russo
Ryan Coogler
Rupert Sanders
Colin Trevorrow
Morten Tyldum
Jon Watts
birthdate
3/2/1981
11/29/1977
6/21/1979
8/15/1990
4/4/1963
11/22/1984
6/1/1996
capacity
300
250
275
300
200
StarsIn
movieTitle
Avengers
Avengers
Avengers
Black Panther
Black Panther
Ghost in Shell
Jurrasic World
Jurrasic World
Passengers
Passengers
Spider-man
Spider-man
Show
movieTitle
Spider-man
Spider-man
Avengers
Black Panther
Black Panther
Passengers
Ghost in Shell
Black Panther
Jurrasic World
Black Panther
Jurrasic World
movieYear starname
2018
Chadwick Boseman
2018
Robert Downey
2018
Scarlett Johansson
2018
Chadwick Boseman
2018
Robert Downey
2017
Scarlett Johansson
2018
Bryce Howard
2018
Chris Pratt
2016
Chris Pratt
2016
Jennifer Lawrence
2018
Robert Downey
2018
Tom Holland
theaterName
Electric
Grand Rex
Grand Rex
Beverly
Grand Rex
Nitehawk
Cinnemaworld
Grand Rex
Cinnemaworld
Nitehawk
Nitehawk
datetime
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
00:00.0
role
lead
lead
lead
lead
support
lead
lead
lead
lead
lead
support
lead
ticketPrice spectators
200
275
200
200
200
98
200
205
300
300
200
219
200
101
200
200
200
188
200
219
200
176
Create table Movie
(
title char(30),
year int,
length float,
language char(15),
filmtype char(1),
DirectorName varchar(15)
primary key (title, year)
)
insert into Movie values(‘Spider-man’,2018,2.5,’English’,’F’,’Jon Watts’)
insert into Movie values(‘Black Panther’,2018,3,’English’,’F’,’Ryan Coogler’)
insert into Movie values(‘Avengers’,2018,3,’English’,’F’,’Anthony Russo’)
insert into Movie values(‘Ghost in Shell’, 2017, 2.5,’English’,’D’,’Rupert Sanders’)
insert into Movie values(‘Jurrasic World’,2018,2.75,’English’,’D’,’Colin Trevorrow’)
insert into Movie values(‘Passengers’,2016,2.75,’English’,’F’,’Morten Tyldum’)
Create table MovieStar
(
name char(25) primary key,
country varchar(40),
gender char(1),
birthdate date
)
Insert into MovieStar values(‘Tom Holland’,’England’,’M’,’1-Jun-1996′)
Insert into MovieStar values(‘Robert Downey’,’America’,’M’,’4-Apr-1963′)
Insert into MovieStar values(‘Chadwick Boseman’,’America’,’M’,’29-Nov-1977′)
Insert into MovieStar values(‘Scarlett Johansson’,’America’,’F’,’22-Nov-1984′)
Insert into MovieStar values(‘Chris Pratt’,’England’,’M’,’21-Jun-1979′)
Insert into MovieStar values(‘Bryce Howard’,’America’,’F’,’2-Mar-1981′)
Insert into MovieStar values(‘Jennifer Lawrence’,’America’,’F’,’15-Aug-1990′)
Create table StarsIn
(
movieTitle char(30),
movieYear int,
starname char(25),
role varchar(15),
primary key (movieTitle,movieYear,starname),
foreign key (movieTitle,movieYear) references Movie,
foreign key (starname) references MovieStar
)
insert into StarsIn values(‘Spider-man’,2018,’Tom Holland’,’lead’)
insert into StarsIn values(‘Spider-man’,2018,’Robert Downey’,’support’)
insert into StarsIn values(‘Black Panther’,2018,’Chadwick Boseman’,’lead’)
insert into StarsIn values(‘Black Panther’,2018,’Robert Downey’,’support’)
insert into StarsIn values(‘Avengers’,2018,’Robert Downey’,’lead’)
insert into StarsIn values(‘Avengers’,2018,’Chadwick Boseman’,’lead’)
insert into StarsIn values(‘Avengers’,2018,’Scarlett Johansson’,’lead’)
insert into StarsIn values(‘Ghost in Shell’,2017,’Scarlett Johansson’,’lead’)
insert into StarsIn values(‘Jurrasic World’,2018,’Chris Pratt’,’lead’)
insert into StarsIn values(‘Jurrasic World’,2018,’Bryce Howard’,’lead’)
insert into StarsIn values(‘Passengers’,2016,’Chris Pratt’,’lead’)
insert into StarsIn values(‘Passengers’,2016,’Jennifer Lawrence’,’lead’)
create table Theater
(
theaterName varchar(20) primary key,
country varchar (40),
city varchar (20),
capacity int
)
insert into Theater values(‘Beverly’,’America’,’LA’,300)
insert into Theater values(‘Electric’,’England’,’London’,275)
insert into Theater values(‘Grand Rex’,’France’,’Paris’,300)
insert into Theater values(‘Nitehawk’,’America’,’New York’,200)
insert into Theater values(‘Cinnemaworld’,’Australia’,’Melbourne’,250)
Create table Show
(
showId int primary key,
movieTitle char (30),
theaterName varchar(20),
datetime datetime,
ticketPrice real,
spectators int
)
insert into Show values(1,’Spider-man’,’Electric’,’1-Jan-2018′,200,275)
insert into Show values(2,’Spider-man’,’Grand Rex’,’1-Jan-2018′,200,200)
insert into Show values(3,’Avengers’,’Grand Rex’,’1-Apr-2018′,200,98)
insert into Show values(4,’Black Panther’,’Beverly’,’1-Jan-2018′,200,205)
insert into Show values(5,’Black Panther’,’Grand Rex’,’1-Jan-2018′,300,300)
insert into Show values(6,’Passengers’,’Nitehawk’,’1-Jan-2018′,200,176)
insert into Show values(7,’Jurrasic World’,’Nitehawk’,’1-Jan-2018′,200,176)
insert into Show values(8,’Black Panther’,’Nitehawk’,’1-Jan-2018′,200,219)
insert into Show values(9,’Ghost in Shell’,’Cinnemaworld’,’1-Jan-2018′,200,101)
insert into Show values(10,’Black Panther’,’Grand Rex’,’1-Jan-2018′,200,200)
insert into Show values(11,’Jurrasic World’,’Cinnemaworld’,’1-Jan-2018′,200,188)
create table Booking
(
showId int,
custName varchar(25),
numTickets int,
primary key(showId,custName),
Foreign key (showId) references Show(showId)
)
Purchase answer to see full
attachment
