Description

Create a movie database as follows:
Director (d-id, fname, lname, gender)
Direct (m-id, d-id)
Actor (a-id, f_name, l_name)
Cast (m-id, a-id, role)
1. Create table for above schemas. (It should include table create, drop, alter and update commands)
2. Create a view for listing all movies directed by ‘XYZ’ director. Alter this view for listing all movies of ‘XYZ’ director having rank ‘A’. Rename the view. Perform DML (insert, delete and update) operations on views.
3. Create, alter and drop index on Actor and Cast table.
4. Create sequence for required columns.

Asgn 3. Design at least 10 SQL queries for suitable database application using SQL DML statements:
Insert, Select, Update, Delete with operators, functions, and set operator.
Note: All 20 queries are mandatory.

1. Insert values into above created tables
2. Insert values into only selected columns (e.g. in Movie table insert values for only title and releasedate)
3. Select all values from Movie and Actor tables.
4. Select m-ID and title from Movie table.
5. Select details of movie directed by “Yash Chopra”.
6. Assume different actors with same first and last name and accordingly have entries into respective table. Now select list of all distinct f_name and l_name from Actor table.
8. Update (increase) the pay of actor working in “Ram-Lakhan” by 60000 Rs.

9. Delete records of all actors having pay between 40000 Rs and 70000 Rs.
10. Select all movie names where title starts with ‘K’.
11. List all actors from (“Dangal”, “War”, “PK”) movies.(IN operator)
12. What is the average pay of actors casted in “Airlift”.
13. Find total number of actors working in “Lagaan”.
14. Find Maximum and Minimum pay of actor in “PK”.
15. Find total amount spent on salaries of actors of “War”.
17. List all actor names from “Lagaan” and “Sholay” using union.
18. List all employee names (allow duplicate values for names) from “PK” and “War” using union all
19. List all actor ids casted in “Lagaan” and “PK”. (Emulate intersect)
20. List all actor ids casted in “Dangal” but not in “War” (emulate minus)