PostgreSql Tasks - Case study:3

#payilagam #postgres #sql #database

Create Ratings Table and add values to the table

movie=# creat table ratings (id int primary key references cinema(id),imdb_rating float,fan_rating float,critique_rating float);
CREATE TABLE

movie=# insert into ratings (id, imdb_rating, fan_rating, critique_rating) 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.0),
(5, 5.1, 6.6, 6.0),
(6, 7.6, 8.8, 9.0),
(7, 8.9, 9.7, 9.7),
(8, 4.5, 7.0, 6.5),
(9, 5.3, 6.5, 6.0),
(10, 8.3, 8.7, 8.2);
INSERT 0 10


movie=# select * from ratings;
 id | imdb_rating | fan_rating | critique_rating 
----+-------------+------------+-----------------
  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.0 |             6.5
  9 |         5.3 |        6.5 |               6
 10 |         8.3 |        8.7 |             8.2
(10 rows)

Combined table of cinema and ratings:

movie=# select cinema.id,cinema.movie_name,cinema.actor, cinema.year,cinema.minutes,ratings.imdb_rating,ratings.fan_rating,ratings.critique_rating
from cinema
full join ratings on cinema.id = ratings.id;
 id |   movie_name    |   actor   | year | minutes | imdb_rating | fan_rating | critique_rating 
----+-----------------+-----------+------+---------+-------------+------------+-----------------
  1 | Dharbar         | Rajini    | 2021 |     121 |         7.2 |        9.1 |             7.7
  2 | Vikram          | Kamal     | 2023 |     125 |         8.1 |        9.3 |             7.3
  3 | Mersal          | Vijay     | 2020 |     123 |         6.5 |        9.2 |             7.3
  4 | Beast           | Vijay     | 2019 |     134 |         6.2 |        8.7 |             6.0
  5 | Viswasam        | Ajith     | 2021 |     117 |         5.1 |        6.6 |             6.0
  6 | Attakasam       | Ajith     | 2006 |     119 |         7.6 |        8.8 |             9.0
  7 | Jai Bhim        | Surya     | 2018 |     127 |         8.9 |        9.7 |             9.7
  8 | Kaithi          | Karthi    | 2017 |     125 |         4.5 |        7.0 |             6.5
  9 | Ayothi          | Sasikumar | 2023 |     124 |         5.3 |        6.5 |             6.0
 10 | Deivathirumagan | Vikram    | 2017 |     121 |         8.3 |        8.7 |             8.2
(10 rows)

Tasks:

1) Find ImDB Rating and Critique Rating for each movie

movie=# select cinema.movie_name,ratings.imdb_rating,ratings.critique_rating from cinema join ratings on cinema.id = ratings.id;
   movie_name    | imdb_rating | critique_rating 
-----------------+-------------+-----------------
 Dharbar         |         7.2 |             7.7
 Vikram          |         8.1 |             7.3
 Mersal          |         6.5 |             7.3
 Beast           |         6.2 |             6.0
 Viswasam        |         5.1 |             6.0
 Attakasam       |         7.6 |             9.0
 Jai Bhim        |         8.9 |             9.7
 Kaithi          |         4.5 |             6.5
 Ayothi          |         5.3 |             6.0
 Deivathirumagan |         8.3 |             8.2
(10 rows)

2) Find Movies that have better ImDB rating than critique rating

movie=# select cinema.movie_name, ratings.imdb_rating, ratings.critique_rating from cinema
join ratings on cinema.id = ratings.id
where ratings.imdb_rating > ratings.critique_rating;
   movie_name    | imdb_rating | critique_rating 
-----------------+-------------+-----------------
 Vikram          |         8.1 |             7.3
 Beast           |         6.2 |             6.0
 Deivathirumagan |         8.3 |             8.2
(3 rows)

3) List down all movies based on their ImDB Rating in ascending order

movie=# select cinema.movie_name,ratings.imdb_rating from cinema join ratings on cinema.id = ratings.id order by ratings.imdb_rating;

   movie_name    | imdb_rating 
-----------------+-------------
 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.

movie=# select cinema.movie_name,ratings.imdb_rating,ratings.fan_rating from cinema join ratings on cinema.id = ratings.id where imdb_rating > 8 and fan_rating > 8;

   movie_name    | imdb_rating | fan_rating 
-----------------+-------------+------------
 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

movie=# select cinema.movie_name,cinema.year,ratings.imdb_rating from cinema join ratings on cinema.id = ratings.id where cinema.year in (2017,2018,2019) and ratings.imdb_rating > 8; 

   movie_name    | year | imdb_rating 
-----------------+------+-------------
 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).

movie=# select cinema.movie_name,cinema.actor,ratings.imdb_rating from cinema join ratings on cinema.id = ratings.id where cinema.actor like '%j%' and ratings.imdb_rating > 8;

 movie_name | actor | imdb_rating 
------------+-------+-------------
(0 rows)

7) List down all movies with less than 7 ImDB and Critique rating released between 2010 – 2020.

movie=# select cinema.movie_name,cinema.year,ratings.imdb_rating,ratings.critique_rating from cinema join ratings on cinema.id = ratings.id where ratings.imdb_rating <7 and ratings.critique_rating < 7 and cinema.year between 2010 and 2020;

 movie_name | year | imdb_rating | critique_rating 
------------+------+-------------+-----------------
 Beast      | 2019 |         6.2 |             6.0
 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

movie=# select cinema.movie_name,cinema.minutes,ratings.fan_rating from cinema join ratings on cinema.id = ratings.id where cinema.minutes < 120 and ratings.fan_rating > 8.5;
 movie_name | minutes | fan_rating 
------------+---------+------------
 Attakasam  |     119 |        8.8
(1 row)

9) List down all movies based on their ImDB Rating in descending order and year in ascending.

movie=# select cinema.movie_name, cinema.year, ratings.imdb_rating
from cinema
join ratings on cinema.id = ratings.id
order by ratings.imdb_rating desc, cinema.year asc;

   movie_name    | year | imdb_rating 
-----------------+------+-------------
 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)

**LEFT()**

The LEFT() function extracts a specified number of characters from the beginning of a string.

Syntax: LEFT(string, number_of_characters)

10) List down all movies where both Actor name and Movie name starts with same letter with their ImDB value in descending order.

movie=# select cinema.movie_name,cinema.actor,ratings.imdb_rating
from cinema 
join ratings on cinema.id = ratings.id where left(cinema.actor,1) = left(cinema.movie_name,1) 
order by ratings.imdb_rating desc; 

 movie_name | actor  | imdb_rating 
------------+--------+-------------
 Attakasam  | Ajith  |         7.6
 Kaithi     | Karthi |         4.5
(2 rows)