Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table actor (act_id integer primary key, act_name varchar(20), act_gender char(1));
- create table director (dir_id integer primary key, dir_name varchar(20), dir_phone varchar(20));
- create table movies (mov_id integer primary key, mov_title varchar(25), mov_year integer, mov_lang varchar(15), dir_id integer, foreign key (dir_id) references director (dir_id));
- create table movie_cast (act_id integer, mov_id integer, 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));
- create table rating (mov_id integer primary key, rev_stars varchar(25), foreign key (mov_id) references movies (mov_id));
- insert into actor (act_id, act_name, act_gender) values
- (101, 'Rahul', 'M'),(102, 'Ankitha', 'F'),(103, 'Radhika', 'F'),
- (104, 'Chethan', 'M'),(105, 'Vivan', 'M');
- insert into director (dir_id, dir_name, dir_phone) values
- (201, 'Anoop', '9181818181'),(202, 'Hitchcock', '9181818182'),
- (203, 'Shashank', '9181818183'),(204, 'Steven Spielberg', '9181818184'),
- (205, 'Anand', '9181818185');
- insert into movies (mov_id, mov_title, mov_year, mov_lang, dir_id) values
- (1001, 'MANASU', 2017, 'Kannada', 201),(1002, 'AAKASHAM', 2015, 'Telugu', 202),
- (1003, 'KALIYONA', 2008, 'Kannada', 201),(1004, 'WAR HORSE', 2011, 'English', 204),
- (1005, 'HOME', 2012, 'English', 205);
- insert into movie_cast (act_id, mov_id, role) values
- (101, 1002, 'Hero'),(101, 1001, 'Hero'),(103, 1003, 'Heroine'),
- (103, 1002, 'Count'),(104, 1004, 'Hero'),(105, 1005, 'Hero');
- insert into rating (mov_id, rev_stars) values
- (1001, 4),(1002, 3),(1003, 5),(1004, 4),(1005, 3);
- select mov_title from movies where dir_id = (select dir_id from director where dir_name = 'Hitchcock');
- select m.mov_title from movies m join movie_cast mc on m.mov_id = mc.mov_id group by m.mov_id having count(mc.act_id) >= 2;
- select act_name from actor a join movie_cast mc on a.act_id = mc.act_id join movies m on mc.mov_id = m.mov_id where m.mov_year not between 2000 and 2015;
- select m.mov_title, max(r.rev_stars) from movies m inner join rating r using (mov_id) group by m.mov_title having max(r.rev_stars) > 0 order by m.mov_title;
- update rating set rev_stars = 5 where mov_id in (select mov_id from movies where dir_id in (select dir_id from director where dir_name = 'Steven Spielberg'));
- select * from rating;
- Output:
- +-----------+
- | mov_title |
- +-----------+
- | AAKASHAM |
- +-----------+
- +-----------+
- | mov_title |
- +-----------+
- | AAKASHAM |
- +-----------+
- +----------+
- | act_name |
- +----------+
- | Rahul |
- +----------+
- +-----------+------------------+
- | mov_title | max(r.rev_stars) |
- +-----------+------------------+
- | AAKASHAM | 3 |
- | HOME | 3 |
- | KALIYONA | 5 |
- | MANASU | 4 |
- | WAR HORSE | 4 |
- +-----------+------------------+
- +--------+-----------+
- | mov_id | rev_stars |
- +--------+-----------+
- | 1001 | 4 |
- | 1002 | 3 |
- | 1003 | 5 |
- | 1004 | 5 |
- | 1005 | 3 |
- +--------+-----------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
