Create cinema table:
employee=# select * from cinema; id | movie_name | actor | year | minutes ----+-----------------+-----------+------+--------- 1 | Dharbar | Rajini | 2021 | 121 2 | Vikram | Kamal | 2023 | 125 3 | Mersal | Vijay | 2020 | 123 4 | Beast | Vijay | 2019 | 134 5 | Viswasam | Ajith | 2021 | 117 6 | Attakasam | Ajith | 2006 | 119 7 | Jai Bhim | Surya | 2018 | 127 8 | Kaithi | Karthi | 2017 | 125 9 | Ayothi | Sasikumar | 2023 | 124 10 | Deivathirumagan | Vikram | 2017 | 121 (10 rows) create c_ratings table:
employee=# create table c_ratings(id int,ImDBRating float,FanRating float,CritiqueRating float); CREATE TABLE employee=# insert into c_ratings values(1,7.2,9.1,7.7),(2,8.1,9.3,7.3),(3,6.5,9.2,7.3),(4,6.2,8.7,6),(5,5.1,6.6,6),(6,7.6,8.8,9),(7,8.9,9.7,9.7),(8,4.5,7,6.5),(9,5.3,6.5,6),(10,8.3,8.7,8.2); INSERT 0 10 employee=# select * from c_ratings; id | imdbrating | fanrating | critiquerating ----+------------+-----------+---------------- 1 | 7.2 | 9.1 | 7.7 2 | 8.1 | 9.3 | 7.3 3 | 6.5 | 9.2 | 7.3 4 | 6.2 | 8.7 | 6 5 | 5.1 | 6.6 | 6 6 | 7.6 | 8.8 | 9 7 | 8.9 | 9.7 | 9.7 8 | 4.5 | 7 | 6.5 9 | 5.3 | 6.5 | 6 10 | 8.3 | 8.7 | 8.2 (10 rows) 1) Find ImDB Rating and Critique Rating for each movie:
employee=# select cinema.movie_name, c_ratings.imdbrating, c_ratings.critiquerating from c_ratings inner join cinema on c_ratings.id=cinema.id; movie_name | imdbrating | critiquerating -----------------+------------+---------------- Dharbar | 7.2 | 7.7 Vikram | 8.1 | 7.3 Mersal | 6.5 | 7.3 Beast | 6.2 | 6 Viswasam | 5.1 | 6 Attakasam | 7.6 | 9 Jai Bhim | 8.9 | 9.7 Kaithi | 4.5 | 6.5 Ayothi | 5.3 | 6 Deivathirumagan | 8.3 | 8.2 (10 rows) 2) Find Movies that have better ImDB rating than critique rating:
employee=# select cinema.movie_name, c_ratings.imdbrating, c_ratings.critiquerating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating > c_ratings.critiquerating; movie_name | imdbrating | critiquerating -----------------+------------+---------------- Vikram | 8.1 | 7.3 Beast | 6.2 | 6 Deivathirumagan | 8.3 | 8.2 (3 rows) 3) List down all movies based on their ImDB Rating in ascending order:
employee=# select cinema.movie_name,c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id order by c_ratings.imdbrating; movie_name | imdbrating -----------------+------------ Kaithi | 4.5 Viswasam | 5.1 Ayothi | 5.3 Beast | 6.2 Mersal | 6.5 Dharbar | 7.2 Attakasam | 7.6 Vikram | 8.1 Deivathirumagan | 8.3 Jai Bhim | 8.9 (10 rows) 4) List down all movies for which ImDB rating and Fan Rating are greater than 8:
employee=# select cinema.movie_name, c_ratings.imdbrating, c_ratings.fanrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating>8 and c_ratings.fanrating>8; movie_name | imdbrating | fanrating -----------------+------------+----------- Vikram | 8.1 | 9.3 Jai Bhim | 8.9 | 9.7 Deivathirumagan | 8.3 | 8.7 (3 rows) 5) List down all movies released in the year 2017,2018 and 2019 and have >8 as ImDB Value:
employee=# select cinema.movie_name,cinema.year, c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating>8 and cinema.year between 2017 and 2019; movie_name | year | imdbrating -----------------+------+------------ Jai Bhim | 2018 | 8.9 Deivathirumagan | 2017 | 8.3 (2 rows) 6) List down all movies for which actor name contains the letter ‘j’ and have ImDB rating (>8):
employee=# select cinema.movie_name,cinema.actor, c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating>8 and cinema.actor='%j%'; movie_name | actor | imdbrating ------------+-------+------------ (0 rows) 7) List down all movies with less than 7 ImDB and Critique rating released between 2010 – 2020:
employee=# select cinema.movie_name,cinema.year, c_ratings.imdbrating,c_ratings.critiquerating from c_ratings inner join cinema on c_ratings.id=cinema.id where (c_ratings.imdbrating<7 and c_ratings.critiquerating<7) and (cinema.year between 2010 and 2020); movie_name | year | imdbrating | critiquerating ------------+------+------------+---------------- Beast | 2019 | 6.2 | 6 Kaithi | 2017 | 4.5 | 6.5 (2 rows) 8) List down all movies with less than 120 Minutes and have Fan Rating greater than 8.5:
employee=# select cinema.movie_name, cinema.minutes, c_ratings.fanrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.fanrating>8.5 and cinema.minutes<120; movie_name | minutes | fanrating ------------+---------+----------- Attakasam | 119 | 8.8 (1 row) 9) List down all movies based on their ImDB Rating in descending order and year in ascending:
employee=# select cinema.movie_name,cinema.year,c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id order by c_ratings.imdbrating desc,cinema.year asc; movie_name | year | imdbrating -----------------+------+------------ Jai Bhim | 2018 | 8.9 Deivathirumagan | 2017 | 8.3 Vikram | 2023 | 8.1 Attakasam | 2006 | 7.6 Dharbar | 2021 | 7.2 Mersal | 2020 | 6.5 Beast | 2019 | 6.2 Ayothi | 2023 | 5.3 Viswasam | 2021 | 5.1 Kaithi | 2017 | 4.5 (10 rows) 10) List down all movies where both Actor name and Movie name starts with same letter with their ImDB value in descending order:
employee=# select cinema.movie_name, cinema.actor, c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id where left(cinema.actor,1)=left(cinema.movie_name,1) order by c_ratings.imdbrating desc; movie_name | actor | imdbrating ------------+--------+------------ Attakasam | Ajith | 7.6 Kaithi | Karthi | 4.5 (2 rows) 
Top comments (0)