Question

Given a relational database that consists of the following relations:

Performer (pid: integer, pname: string, years_of_experience: integer, age: integer) Movie (mname: string, genre: string, minutes: integer, release_year: integer, did: integer) Acted (pid: integer, mname: string)
Director (did: integer, dname: string, earnings: real)

Do the following using your Azure SQL database:
a) Use SQL statements to create the relations.
b) Populate the relations using SQL statements with the given data posted on Canvas. c) Implement the SQL queries for the following:

1. Display all the data you store in the database to verify that you have populated the relations correctly.

2. Find the names of all Action movies.

3. For each genre, display the genre and the average length (minutes) of movies for that genre.

4. Find the names of all performers with at least 20 years of experience who have acted in a movie

   directed by Black.

5. Find the age of the oldest performer who is either named “Hanks” or has acted in a movie named

   “The Departed”.

6. Find the names of all movies that are either a Comedy or have had more than one performer act in

   them.

7. Find the names and pid's of all performers who have acted in at least two movies that have the same

   genre.

8. Decrease the earnings of all directors who directed “Up” by 10%.

9. Delete all movies released in the 70's and 80's (1970 <= release_year <= 1989).

Answer 1

SOLUTION A:

CREATE RELATION:

Perfomer Relation:

create table Performer (pid integer , pname varchar2(20), years_of_experience integer, age integer,primary key(pid))

Movie Relation:

create table Movie (mname varchar2(20), genre varchar2(20), minutes integer, release_year integer, did integer, primary key(mname),foreign key(did) references Director(did))

Actor Relation:

create table Acted (pid integer, mname varchar2(20),foreign key (pid) references Performer(pid),foreign key (mname) references Movie(mname))

Director Relation:

create table Director (did integer, dname varchar2(20), earnings real,primary key(did))

SOLUTION B:

NOTE : I have mentioned the sample data which is less and i have inserted some more data for the output clear

INSERTION INTO MOVIES RELATION:

insert into Movie values ("Romantic2","Romantic",190,2019,1000)
insert into Movie values ("Romantic3","Romantic",190,2019,1002)
insert into Movie values ("Romantic4","Romantic",190,2019,1002)

INSERTION INTO Director RELATION:

insert into Director values(1000,"RGV",100000)
insert into Director values(1001,"Trivikram",100000)
insert into Director values(1002,"black",100000)

INSERTION INTO Performer RELATION:

insert into Performer values (2001,"Hanks",30,60)
insert into Performer values (2004,"Vin0",10,60)
insert into Performer values (2005,"Vin",10,60)

INSERTION INTO Acted RELATION:

insert into Acted values (2004,"Romantic3")
insert into Acted values (2005,"Romantic2")

SOLUTION C:

NOTE: Mentioning questions since the sub-questions numbers are not mentioned

a)Display all the data you store in the database to verify that you have populated the relations correctly.

SELECT * FROM Performer;

SELECT * FROM Movies;

SELECT * FROM Acted;

SELECT * FROM Director;

b)Find the names of all Action movies.

SOLUTION:

SELECT mname FROM Movie WHERE genre=="Action"

EXPLANATION:

Apply where Condition on genre as Action and Project Movie name

c)For each genre, display the genre and the average length (minutes) of movies for that genre.

SOLUTION:

SELECT genre,AVG(minutes) AS  averagelength
FROM Movie
GROUP BY genre

EXPLANATION:

Group all the same genre by using GROUP BY, Then apply aggregate function AVG on the minutes

Project the mname,AVG(minutes)

d)Find the names of all performers with at least 20 years of experience who have acted in a movie directed by Black.

SOLUTION:

SELECT pname
FROM Acted A,Director D,Performer P, Movie M
WHERE dname=="black" AND M.did==D.did AND M.mname==A.mname AND P.pid==A.pid AND years_of_experience>=20

EXPLANATION:

Step 1: First find out director with dname then find the movies directed by him using the Movies table

Step 2: Get the all movies directed by the black and Join with Acted to get the pid

Step 3: Join with the Performer table to the pnames

e)Find the age of the oldest performer who is either named “Hanks” or has acted in a movie named “The Departed”.

SOLUTION:

SELECT P.pname,max(age) AS AGE
FROM Acted A,Performer P, Movie M
WHERE (pname =="Hanks" OR M.mname =="The Departed") AND M.mname==A.mname AND A.pid==P.pid

EXPLANATION :

Step 1: Find out the mname As the Departed or pname as the hanks

Step 2: Join the Movies table with the Acted to get the Pid of the performer

Step 3: Now join with the Performer table using the pid

Step 3: Use the aggregate functions to get the max age

f)Find the names of all movies that are either a Comedy or have had more than one performer act in them

​​​​​​​SOLUTION:

SELECT mname
FROM Movie
WHERE genre=="Comedy"
UNION
SELECT A.mname
FROM Acted A, Movie M
WHERE M.mname==A.mname
GROUP BY A.mname
HAVING count(A.mname)>=1

EXPLANATION:

Step 1: Find the movies which has genre As comedy

Step 2: Find the movies that has more than one performer by grouping the mname and checking whether it has more than one actor using the Having Clause

Step 3: Use Union to Get the complete Result

g)Find the names and pid's of all performers who have acted in at least two movies that have the same

​​​​​​​SOLUTION:

SELECT pname,P.pid
FROM Acted A, Movie M,Performer p
WHERE M.mname==A.mname AND A.pid==P.pid
GROUP BY M.genre,P.pid,pname
HAVING count(*)>=2

EXPLANATION:

Step 1: Join Movies table with Acted table so that we can get genre

Step 2: Join the previous one with the Performer table to get the mapping of the performer with movies table

Step 3: Group the data based on the genre

Step 4: Filter the data based on the number Of rows of each genre for performer based on the HAVING clause condition

h)Decrease the earnings of all directors who directed “Up” by 10%.

NOTE : the Way I have understood the Question is like for the movie Up all the directors income shoukd be reduced by 10%

Excuse me if the way i have understood the question is Wrong

​​​​​​​SOLUTION:

UPDATE Director
SET earnings=(earnings*(0.90))
where Director.did IN (SELECT did FROM Movie WHERE mname="Up")

EXPLANATION:

Step 1: Check for the did of all the directors who directed up and then update their earnings by 10%

i)Delete all movies released in the 70's and 80's (1970 <= release_year <= 1989).

SOLUTION:

DELETE FROM Movie
Where release_year>=1970 AND release_year<=1989