Posted: September 13th, 2017

Write Queries for 15 Mysql Questions

Paper, Order, or Assignment Requirements

 

 

PLEASE USE MySQL Workbench 6.0 Community Edition

First read instruction then run script NetFilms.

Question1

How many films does NetFilms offer?

 

SELECT COUNT(*) FROM Film;

 

1 row returned;

 

Question2

List all films of genre ‘Documentary’

 

SELECT * FROM Film,Genre

WHERE Genre.Name = “Documentary”;

 

4292 rows returned

 

Question3

How many films in our list were released during the 1990s?

 

SELECT COUNT(*) FROM Film WHERE ReleaseYear LIKE ‘19%’;

1 row returned

 

Question4

List all of our R-rated comedy films. Show the title and year of release of each film, and order by title.

 

SELECT Film.Title, Film.ReleaseYear FROM Film, Genre

WHERE Film.Classification=’R’ AND Genre.Name = ‘Comedy’

ORDER BY Film.Title;

1741 rows returned

——————————————————————————–

 

Question5

List all films directed by Steven Spielberg

 

Result Duplicating

SELECT * FROM Film, Role, CastAndCrew

WHERE CastAndCrew.FirstName= ‘Steven’

AND CastAndCrew.LastName = ‘Spielberg’ AND Role.Name = ‘Director’;

 

Question6

List all of the directors and how many films each has made.

 

SELECT CastAndCrew.FirstName, CastAndCrew.LastName

FROM CastAndCrew, Role, Film

WHERE Role.Name=’Director’

GROUP BY `Film`.`id`;

 

 

Question7

Who has acted in the most films?

 

SELECT Distinct CastAndCrew.FirstName, CastAndCrew.LastName

FROM CastAndCrew, Role, Film

WHERE Role.Name = ‘Actor’

ORDER BY Film.id LIMIT 1;

 

Question8

Which are the 5 most-watched films?

 

SELECT * FROM Watch, Film

WHERE Watch.Film = Film.id

ORDER BY ‘Watch.id’

LIMIT 5;

 

 

5 rows returned

 

Question9

Which customers have not viewed any films yet?

 

SELECT Customer.FirstName, Customer.LastName

FROM Customer, Watch

WHERE Watch.Customer != Customer.id;

 

49500 rows returned

 

Question10

On which day of the week do most people watch films?

 

SELECT DAY(`WhenViewed`) as day FROM `Watch`

WHERE GROUP BY `Watch`.`WhenViewed` ORDER BY Watch`.`Rating` DESC LIMIT 1;

 

Question11

How many different films have been watched by customers in postcode 3053?

 

SELECT COUNT(Distinct Film.Title) FROM Film, Customer

WHERE Customer.PostCode = 3053;

1 row returned

 

Question12

List the top 8 films as ranked by our customers. For each, show the title, and average rank, and order the list by rank. Only include films that have been ranked by at least 5 customers.

 

SELECT DISTINCT Film.Title, Watch.Rating as Rank

FROM Watch, Film WHERE 1 ORDER BY Watch.Rating DESC LIMIT 8;

 

Question13

Which films has customer Steven Kloves watched? List the films along with how many times he has watched them.

 

Question13(a):

SELECT COUNT(*) FROM Customer, Film

WHERE Customer.FirstName=Steven AND Customer.LastName=Kloves;

 

SELECT COUNT(*) FROM `Customer`,`Film` WHERE     `Customer`.`FirstName`=’Steven’ AND `Customer`.`LastName`=’Kloves’;

 

Question13(b).

SELECT DISTINCT Film.Title,

COUNT(Film.Title) AS Number of Times

FROM Customer, Film WHERE Customer.FirstName=Steven

AND Customer.LastName=Kloves;

 

SELECT DISTINCT `Film`.`Title`, COUNT(`Film`.`Title`) AS ‘Number of Times’ FROM `Customer`,`Film` WHERE `Customer`.`FirstName`=’Steven’ AND `Customer`.`LastName`=’Kloves’;

 

Question14:

Which genres of films have been watched the most in postcode 3053? List the top 3 genres for that postcode.

 

SELECT DISTINCT Genre.Name FROM Genre, Film, Customer

WHERE Customer.PostCode = 3053

ORDER BY Film.id DESC LIMIT 3;

 

Question15

Which films are making a profit for NetFilms? (Profit is the net takings from viewings minus the wholesale cost of the film.)

 

SELECT * FROM Film, Watch

WHERE (Film.PricePerView*Watch.Film -Film.CostWholesale)>0

ORDER BY Film.id;

 

SELECT * FROM `Film`,`Watch` WHERE (`Film`.`PricePerView`*`Watch`.`Film`-`Film`.`CostWholesale`)>0 ORDER BY `Film`.`id`;