DEV Community

Lakshmi Pritha Nadesan
Lakshmi Pritha Nadesan

Posted on

2

Task 3 - Database

#database #postgres #task #payilagam

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
profile
Checkly
 Promoted

Playwright CLI Flags Tutorial

5 Playwright CLI Flags That Will Transform Your Testing Workflow

  • 0:56 --last-failed: Zero in on just the tests that failed in your previous run
  • 2:34 --only-changed: Test only the spec files you've modified in git
  • 4:27 --repeat-each: Run tests multiple times to catch flaky behavior before it reaches production
  • 5:15 --forbid-only: Prevent accidental test.only commits from breaking your CI pipeline
  • 5:51 --ui --headed --workers 1: Debug visually with browser windows and sequential test execution

Learn how these powerful command-line options can save you time, strengthen your test suite, and streamline your Playwright testing experience. Click on any timestamp above to jump directly to that section in the tutorial!

Watch Full Video šŸ“¹ļø

Top comments (0)

profile
AWS
 Promoted

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

šŸ‘‹ Kindness is contagious

Please show some love ā¤ļø or share a kind word in the comments if you found this useful!

Got it!