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 TELEGU 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

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.