Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL queries on FILM Database

  • Last Updated : 29 May, 2020

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:

Attention reader! Don’t stop learning now.  Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.

Learn all GATE CS concepts with Free Live Classes on our youtube channel.

SELECT * FROM ARTIST;



Art_idArt_NameArt_Gender
101AMITM
102PRITAMM
103SREYAF
104SUJATAF

SELECT * FROM PRODUCER;

Prod_idProd_NameProd_Phone
200ADITYA6735835863
201FARAN8654297433
202YASH8765421567
203NIRAJ7654321986

SELECT * FROM FILMS;

Film_idFilm_TitleFilm_YearFilm_LangProd_id
11WAR 22017ENGLISH201
12MOMENTS2015HINDI203
13THE MAY2019ENGLISH201
14BHANUMATI2014TELEGU200

SELECT * FROM CASTING;

Art_idFilm_idPart
10112ACTOR
10111ACTOR
10313ACTRESS
10312GUEST
10414ACTRESS

SELECT * FROM REVIEW;



Film_idStars
114
122
135
144

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_NAMEFILM_TITLEFILM_YEAR
AMITWAR 22017

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_TITLESTARS
THE MAY5
BHANUMATI4
WAR 24
MOMENTS2

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_idStars
114
125
135
144

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!