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;

SELECT * FROM PRODUCER;

SELECT * FROM FILMS;

SELECT * FROM CASTING;

SELECT * FROM REVIEW;

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:

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:

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:

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;