#PROBLEM STATEMENT
Data requirements of movie industry are captured.
Each movie is identified by title and year of release. Each movie has length in
minutes and classified under one genres (like action, horror etc.). Each movie
has a plot outline. Production companies are identified by name and each has an
address. A production company produces one or more movies. Actors are
identified by id. Other details like name and date of birth of actors are also stored.
Each actor acts in one or more movies. Each actor has a role in movie.
Directors are identified by id. Other details like name and date of birth of
directors are also stored. Each director directs one or more movies. Each movie
has one or more actors and one or more directors and is produced by a
production company. Queries:
a) List the details of horror movies released in
2012 and directed by more than 2 directors.
b) List the details of actors who acted in movies
having same titles but released before 2000 and after 2010.
c) List the details of production companies
producing maximum movies.
d) List the details of movies where director and
actor have same date of birth.
e) Retrieve the names of directors directed all the
movies produced by any one production company.
………………………………………..CREATE TABLES………………………….
create table production
(
pname varchar(20) primary key,
address varchar(20)
);
create table actor
(
aid varchar(5) primary key,
aname varchar(20),
a_dob date
);
create table director
(
did varchar(5) primary key,
dname varchar(20),
d_dob date
);
create table movie
(
mtitle varchar(20),
year number(4),
primary key(mtitle,year),
length number(6),
genre varchar(20),
plot varchar(10),
pname varchar(20),
aid varchar(5),
did varchar(5),
foreign key(pname) references production(pname),
foreign key(aid) references actor(aid),
foreign key(did) references director(did)
);
create table directed_by
(
mtitle varchar(20),
year number(4),
did varchar(5),
primary key(mtitle,year,did),
foreign key(mtitle,year) references movie(mtitle,year),
foreign key(did) references director(did)
);
create table acts_in
(
aid varchar(5),
mtitle varchar(20),
foreign key(aid) references actor(aid)
);
create table produced_by
(
pname varchar(20),
mtitle varchar(20),
foreign key(pname) references production(pname)
);
…………………………………………INSERT INTO TABLE……………………
insert into production values('Dharma','Mumbai');
insert into production values('Universal','Mumbai');
insert into production values('Crouching Tiger','Mumbai');
insert into production values('Amir Khan','Mumbai');
insert into actor values
('a1','Shahrukh','01-jan-70');
insert into actor values ('a2','Ajay','01-feb-70');
insert into actor values
('a3','Hrithik','03-mar-80');
insert into actor values
('a4','Akshay','01-jan-90');
insert into actor values
('a5','Jackie','03-mar-60');
insert into actor values ('a6','Yash','02-jan-85');
insert into actor values ('a7','Vivek','02-jan-85');
insert into actor values ('a8','Govinda','02-jan-85');
insert into actor values
('a9','Akhtar','02-jan-85');
insert into actor values
('a10','Mohan','02-jan-85');
insert into director values('d1','Allu
Arvind','01-jan-70');
insert into director values('d2','Sanjay
Bhansali','02-jan-60');
insert into director values('d3','David
Dhavan','03-mar-80');
insert into director values('d4','Farah
Khan','01-oct-70');
insert into director values('d5','Gouri
Khan','02-nov-80');
insert into director values ('d6','Karan
Johar','01-feb-70');
insert into director values ('d7','Rohit
Shetty','02-jan-85');
insert into movie
values('Raaz','2012','70','Horror','Horror','Dharma','a1','d1');
insert into movie
values('DDLJ','2012','70','Love','Love','Amir Khan','a2','d2');
insert into movie
values('Titanic','2012','70','Love','Love','Dharma','a3','d3');
insert into movie
values('Rebel','2012','70','Action','Action','Crouching Tiger','a4','d1');
insert into movie
values('Aarya','1999','70','Love','Action','Dharma','a5','d2');
insert into movie
values('RHTDM','2012','70','Love','Love','Amir Khan','a1','d4');
insert into movie
values('Elaan','2014','70','Action','Action','Universal','a1','d1');
insert into movie
values('Kaal','2012','70','Thriller','Action','Dharma','a4','d1');
insert into movie
values('Aashiqui','2012','70','Romantic','Love','Amir Khan','a1','d2');
insert into movie
values('Dhoom','2013','70','Action','Action','Dharma','a1','d3');
insert into directed_by values('Raaz','2012','d1');
insert into directed_by values('DDLJ','2012','d2');
insert into directed_by
values('Titanic','2012','d1');
insert into directed_by values('Rebel','2012','d2');
insert into directed_by values('Aarya','1999','d1');
insert into directed_by values('Elaan','2012','d2');
insert into directed_by values('Raaz','2012','d2');
insert into directed_by values('Raaz','2012','d3');
insert into produced_by values('Dharma','Raaz');
insert into produced_by values('Universal','Elaan');
insert into produced_by values('Amir
Khan','Aashiqui');
insert into produced_by values('Dharma','Dhoom');
insert into produced_by values('Dharma','Titanic');
insert into produced_by values('Crouching
Tiger','Rebel');
…………………………………..QUERIES……………………………………….
1.List the details of the horror movies released in
2012 and directed by more than 2 directors.
select m.mtitle
from movie m
where m.year='2012' and
m.genre='Horror' and
m.mtitle in
(
select d.mtitle from directed_by d
where d.year='2012'
group by d.mtitle
having count(d.mtitle)>1
);
SQL> select m.mtitle
2 from movie m
3 where m.year='2012' and
4 m.genre='Horror' and
5 m.mtitle in
6 (
7 select d.mtitle from directed_by d
8 where d.year='2012'
9 group by d.mtitle
10 having count(d.mtitle)>1
11 );
MTITLE
--------------------
Raaz
2.List the details of actors who acted in movies
having same titles but released before 2000 and after 2010.
select a.aid,a.aname
from actor a,movie m
where m.aid=a.aid and
m.year not between 2000 and 2010
group by a.aid,aname
having count(a.aid)>1;
SQL> select a.aid,a.aname
2 from actor a,movie m
3 where m.aid=a.aid and
4 m.year not between 2000 and 2010
5 group by a.aid,aname
6 having count(a.aid)>1;
AID ANAME
----- --------------------
a1 Shahrukh
a4 Akshay
3.List the details of production companies producing
maximum movies.
select max(pname)
from movie m;
select m.pname
from movie m
group by m.pname
having count(m.pname)>=ALL(select count(*)
from movie
group by pname);
SQL> select m.pname
2 from movie m
3 group by m.pname
4 having count(m.pname)>=ALL(select count(*)
5 from movie
6 group by pname);
PNAME
--------------------
Dharma
4.List the details of movies where director and
actor have same date of birth.
select mtitle,year
from movie m,actor a, director d
where d.d_dob=a.a_dob and
m.aid=a.aid and
m.did=d.did;
SQL> select mtitle,year
2 from movie m,actor a, director d
3 where d.d_dob=a.a_dob and
4 m.aid=a.aid and
5 m.did=d.did;
MTITLE YEAR
-------------------- ----------
Elaan 2014
Raaz
2012
Titanic 2012
5.Retreive the names of directors directed all the
movies produced by any one production company.
select dname
from director d, movie m
where d.did=m.did and
pname='&pname';
SQL> select dname
2 from director d, movie m
3 where d.did=m.did and
4 pname='&pname';
Enter value for pname: Dharma
old 4:
pname='&pname'
new 4:
pname='Dharma'
DNAME
--------------------
Allu Arvind
Allu Arvind
Sanjay Bhansali
David Dhavan
David Dhavan
select d.dname
from
director d, movie m
where
d.did=m.did and
pname='&pname'
group by(d.dname)
No comments:
Post a Comment