Task 3 - Database

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.m

Feb 16, 2025 - 11:03
 0
Task 3 - Database

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)