SQL queries on FILM Database

Consider the following tables in Film Database:

ARTIST (Art_id, Art_Name, Art_Gender) 
PRODUCER (Prod_id, Prod_Name, Prod_Phone)
FILMS (Film_id, Film_Title, Film_Year, Film_Lang, Prod_id) 
CASTING (Art_id, Film_id, Part) 
REVIEW (Film_id, Stars) 

The data content of these five tables are shown below:

SELECT * FROM ARTIST;

Art_id	Art_Name	Art_Gender
101	AMIT	M
102	PRITAM	M
103	SREYA	F
104	SUJATA	F

SELECT * FROM PRODUCER;

Prod_id	Prod_Name	Prod_Phone
200	ADITYA	6735835863
201	FARAN	8654297433
202	YASH	8765421567
203	NIRAJ	7654321986

SELECT * FROM FILMS;

Film_id	Film_Title	Film_Year	Film_Lang	Prod_id
11	WAR 2	2017	ENGLISH	201
12	MOMENTS	2015	HINDI	203
13	THE MAY	2019	ENGLISH	201
14	BHANUMATI	2014	TELUGU	200

SELECT * FROM CASTING;

Art_id	Film_id	Part
101	12	ACTOR
101	11	ACTOR
103	13	ACTRESS
103	12	GUEST
104	14	ACTRESS

SELECT * FROM REVIEW;

Film_id	Stars
11	4
12	2
13	5
14	4

Query-1: Find the name of all the Films whose producer is “NIRAJ”. First we will find the Producer ID for the Producer Named “NIRAJ” and for this we use the PRODUCER table.

SELECT PROD_ID 
FROM PRODUCER 
WHERE PROD_NAME = ‘NIRAJ’

The above query will return the Prod_id “203”. Now we will find the title of films whose pros_id is “203”. For doing this we use the table FILMS and use the above query as a sub query.

SELECT FILM_TITLE 
FROM FILMS 
WHERE PROD_ID IN (SELECT PROD_ID 
FROM PRODUCER 
WHERE PROD_NAME = ‘NIRAJ’);

Output:

FILM_TITLE
MOMENTS

Query-2: Display all artists who acted in a film between 2016 and 2018. For finding this we need to use the ARTIST_NAME field of ARTIST table as well as the FILM_YEAR field of FILMS table. For joining these two tables we use a third table CASTING using Art_id and Film_id.After joining we use the BETWEEN operator for checking the FILM_YEAR in between 2016 and 2018.

SELECT A.ART_NAME,  F.FILM_TITLE, F.FILM_YEAR 
FROM ARTIST A, CASTING C, FILMS F
WHERE A.ART_ID=C.ART_ID 
AND C.FILM_ID=F.FILM_ID 
AND F.FILM_YEAR  BETWEEN 2016 AND 2018;

Output:

ART_NAME	FILM_TITLE	FILM_YEAR
AMIT	WAR 2	2017

Query-3: Display the names of films with the stars received and sort the result on the basis of stars. For this we need FILM_TITLE field of FILMS table as well as STARS field of REVIEW table. For joining these two tables we will use the common field which is the FILM_ID. And after joining we display the result in the order of increasing STARS by using ORDER BY clause.

SELECT F.FILM_TITLE, R_STARS
FROM FILMS F, REVIEW R
WHERE F.FILM_ID=R.FILM_ID
ORDER BY R.STARS DESC

Output:

FILM_TITLE	STARS
THE MAY	5
BHANUMATI	4
WAR 2	4
MOMENTS	2

Query-4: Update the stars of all films whose producer is ‘NIRAJ’ to 5. First we find the PRODUCER ID of the PRODUCER named “NIRAJ” by using this query:

SELECT PROD_ID 
FROM PRODUCER 
WHERE PROD_NAME = ‘NIRAJ’

Then find the FILM_ID from the FILMS table using this PROD_ID.

SELECT FILM_ID FROM FILMS 
WHERE PROD_ID IN (SELECT PROD_ID 
FROM PRODUCER 
WHERE PROD_NAME = ‘NIRAJ’)

And then this FILM_ID helps in Updating the value of STARS in REVIEW table using UPDATE command.

UPDATE REVIEW 
SET STARS=5 
WHERE FILM_ID IN (SELECT FILM_ID FROM FILMS 
WHERE PROD_ID IN (SELECT PROD_ID 
FROM PRODUCER 
WHERE PROD_NAME = ‘NIRAJ’));

Output:

1 row updated.

To observe the changes we can use SELECT * command in REVIEW table.

SELECT * FROM REVIEW;

Film_id	Stars
11	4
12	5
13	5
14	4