MOVIE DATABASE
Experiment 3
Latharani T R, Asst. Professor, CS&E, JITD
1
Consider the schema for Movie Database:
Latharani T R, Asst. Professor, CS&E, JITD
2
Entity-Relationship Diagram
Latharani T R, Asst. Professor, CS&E, JITD
3
Latharani T R, Asst. Professor, CS&E, JITD
4
create table actor
(
act_id int,
act_name varchar(10),
act_gender char(1),
primary key(act_id)
);
Latharani T R, Asst. Professor, CS&E, JITD
5
SQL> desc actor;
Name Null? Type
------------------------------------------------------------------
ACT_ID NOT NULL INT
ACT_NAME VARCHAR(10)
ACT_GENDER CHAR(1)
Latharani T R, Asst. Professor, CS&E, JITD
6
insert into actor values(301,'Anushka','F');
insert into actor values(302,'Prabhas','M');
insert into actor values(303,'Punith','M');
insert into actor values(304,'Jermy','M');
insert into actor values(305,'yash','M');
select *from actor;
ACT_ID ACT_NAME ACT_G
---------- ---------------- ----------
301 Anushka F
302 Prabhas M
303 Punith M
304 Jermy M
305 yash M
Latharani T R, Asst. Professor, CS&E, JITD
7
create table director
(
dir_id int,
dir_name varchar(20),
dir_phone bigint,
primary key(dir_id)
Latharani T R, Asst. Professor, CS&E, JITD
8
SQL> desc director;
Name Null? Type
----------------------------------------- -------- -----------
DIR_ID NOT NULL INT
DIR_NAME VARCHAR(20)
DIR_PHONE INT
Latharani T R, Asst. Professor, CS&E, JITD
9
insert into director values (70,'Hitchcock',9487563255);
insert into director values (71,'Rajamouli',8948562346);
insert into director values (72,'steven spielberg',9823654125);
insert into director values (73,'Faran',9786542356);
insert into director values (74,'Martin',8974563214);
Latharani T R, Asst. Professor, CS&E, JITD
10
SQL> select *from director;
DIR_ID DIR_NAME DIR_PHONE
---------- ----------------- ------------
70 Hitchcock 9487563255
71 Rajamouli 8948562346
72 stevenspielberg 9823654125
73 Faran 9786542356
74 Martin 8974563214
Latharani T R, Asst. Professor, CS&E, JITD
11
create table movies
(
mov_id int,
mov_title varchar(10),
mov_year int,
mov_lang varchar(10),
dir_id int,
primary key(mov_id),
foreign key(dir_id) references director(dir_id)
);
Latharani T R, Asst. Professor, CS&E, JITD
12
desc movies;
Name Null? Type
----------------------------------------- -------- --------------
MOV_ID NOT NULL INT
MOV_TITLE VARCHAR2(10)
MOV_YEAR INT
MOV_LANG VARCHAR2(10)
DIR_ID INT
Latharani T R, Asst. Professor, CS&E, JITD
13
insert into movies values (1001,'Bahubali-2',2017,'telugu',70);
insert into movies values (1002,'Singham',2008,'hindi',70);
insert into movies values (1003,'Golmaal-2',2011,'telugu',70);
insert into movies values (1004,'Allthebest',2015,'hindi',70);
insert into movies values (1005,'Akash',2009,'kannada',70);
Latharani T R, Asst. Professor, CS&E, JITD
14
select *from movies;
MOV_ID MOV_TITLE MOV_YEAR MOV_LANG DIR_ID
---------- ---------- ---------- ---------- ----------
1001 Bahubali-2 2017 telugu 70
1002 Singham 2008 hindi 71
1003 Golmaal-2 2011 telugu 72
1005 Akash 2009 kannada 74
1004 Allthebest 2015 hindi 73
Latharani T R, Asst. Professor, CS&E, JITD
15
create table moviecast
(
act_id int,
mov_id int,
role varchar(10),
primary key(act_id,mov_id),
foreign key(act_id) references actor(act_id),
foreign key(mov_id) references movies(mov_id)
);
Latharani T R, Asst. Professor, CS&E, JITD
16
desc moviecast;
Name Null? Type
----------------------------------------- -------- --------------
ACT_ID NOT NULL INT
MOV_ID NOT NULL INT
ROLE VARCHAR(10)
Latharani T R, Asst. Professor, CS&E, JITD
17
insert into moviecast values (301,1001,'Heroine');
insert into moviecast values(302,1002,'Hero');
insert into moviecast values (303,1003,'Guest');
insert into moviecast values(304,1004,'Hero');
insert into moviecast values (305, 1005, 'Heroine');
Latharani T R, Asst. Professor, CS&E, JITD
18
select *from moviecast;
ACT_ID MOV_ID ROLE
---------- ---------- ----------
301 1001 Heroine
302 1002 Hero
303 1003 Guest
304 1004 Hero
305 1005 Heroine
Latharani T R, Asst. Professor, CS&E, JITD
19
create table rating
(
mov_id int,
rev_stars int,
primary key(mov_id),
foreign key(mov_id) references movies (mov_id)
);
Latharani T R, Asst. Professor, CS&E, JITD
20
desc rating;
Name Null? Type
MOV_ID NOT NULL INT
REV_STARS INT
Latharani T R, Asst. Professor, CS&E, JITD
21
insert into rating values(1001,4);
insert into rating values(1002,2);
insert into rating values(1003,5);
insert into rating values(1004,3);
insert into rating values(1005,4);
Latharani T R, Asst. Professor, CS&E, JITD
22
select *from rating;
MOV_ID REV_STARS
---------- ----------
1001 4
1002 2
1003 5
1004 3
1005 4
Latharani T R, Asst. Professor, CS&E, JITD
23
1. List the titles of all movies directed by ‘Hitchcock’.
select mov_title
from movies m, director d
where m.dir_id = d.dir_id and d.dir_name ='Hitchcock';
Latharani T R, Asst. Professor, CS&E, JITD
24
2. Find the movie names where one or more actors acted in two or more movies.
SELECT MOV_TITLE
FROM MOVIES M, MOVIECAST MV
WHERE M.MOV_ID=MV.MOV_ID AND
ACT_ID IN (SELECT ACT_ID
FROM MOVIECAST GROUP BY
ACT_ID HAVING COUNT (ACT_ID)>1)
GROUP BY MOV_TITLE
HAVING COUNT (*)>1;
Latharani T R, Asst. Professor, CS&E, JITD
25
3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).
SELECT ACT_NAME, MOV_TITLE, MOV_YEAR FROM ACTOR A
JOIN MOVIECAST C
ON A.ACT_ID=C.ACT_ID
JOIN MOVIES M
ON C.MOV_ID=M.MOV_ID
WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
Latharani T R, Asst. Professor, CS&E, JITD
26
4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.
Select mov_title, max(rev_stars)
from movies m, rating r
where m.mov_id = r.mov_id group by (m.mov_id)
having max(rev_stars)>=1
order by m.mov_title;
Latharani T R, Asst. Professor, CS&E, JITD
27
5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.
update rating set rev_stars=5
where mov_id in
(select m.mov_id
from movies m, director d
where m.dir_id = d.dir_id and d.dir_name='stevenspielberg');
Latharani T R, Asst. Professor, CS&E, JITD
28