Task 3 - Database

[MyrinNew]

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
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
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)











More...