PostgreSql Tasks - Case 2

Table: movie=# 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) Exercise: 1) List down 8th row values from the table movie=# select * from cinema where id in (8); id | movie_name | actor | year | minutes ----+------------+--------+------+--------- 8 | Kaithi | Karthi | 2017 | 125 (1 row) (Or) OFFSET The OFFSET clause in SQL is used to skip a specific number of rows before starting to return the results. It is commonly used with LIMIT to retrieve a specific row. movie=# SELECT * from cinema limit 1 offset 7; id | movie_name | actor | year | minutes ----+------------+--------+------+--------- 8 | Kaithi | Karthi | 2017 | 125 (1 row) 2) List down all the movies released between 2021 and 2023 movie=# select movie_name,year from cinema where year between 2021 and 2023; movie_name | year ------------+------ Dharbar | 2021 Vikram | 2023 Viswasam | 2021 Ayothi | 2023 (4 rows) 3) List down all the movies released not between 2021 and 2023 movie=# select movie_name,year from cinema where year not between 2021 and 2023; movie_name | year -----------------+------ Mersal | 2020 Beast | 2019 Attakasam | 2006 Jai Bhim | 2018 Kaithi | 2017 Deivathirumagan | 2017 (6 rows) 4) List down first 3 movies based on released year in descending order movie=# select movie_name,year from cinema order by year desc limit 3; movie_name | year ------------+------ Vikram | 2023 Ayothi | 2023 Dharbar | 2021 (3 rows) 5) List down All movies by Vijay in the year 2020. movie=# select movie_name,actor,year from cinema where actor='Vijay' and year=2020 ; movie_name | actor | year ------------+-------+------ Mersal | Vijay | 2020 (1 row) 6) List down all movies where we have ‘as’ in the movie name. movie=# select movie_name,year from cinema where movie_name like '%as%'; movie_name | year ------------+------ Beast | 2019 Viswasam | 2021 Attakasam | 2006 (3 rows) 7) List down all actor names without duplicates. movie=# select distinct actor from cinema; actor ----------- Sasikumar Kamal Vijay Karthi Surya Ajith Rajini Vikram (8 rows) 8) List down Ajith movies in alphabetical order movie=# select movie_name,actor from cinema where actor= 'Ajith' order by movie_name; movie_name | actor ------------+------- Attakasam | Ajith Viswasam | Ajith (2 rows) 9) List down movies where their names start with ‘A’ and actor name starts with ‘A’. movie=# select movie_name,actor from cinema where actor like 'A%' and movie_name like'A%'; movie_name | actor ------------+------- Attakasam | Ajith (1 row) 10) List down movies if the movie name is ‘Vikram’ or the actor name is ‘Vikram’. movie=# select movie_name,actor from cinema where actor like 'Vikram' or movie_name like 'Vikram'; movie_name | actor -----------------+-------- Vikram | Kamal Deivathirumagan | Vikram (2 rows)

Feb 13, 2025 - 16:02

Table:

movie=# 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)

Exercise:
1) List down 8th row values from the table

movie=# select * from cinema where id in (8);
 id | movie_name | actor  | year | minutes 
----+------------+--------+------+---------
  8 | Kaithi     | Karthi | 2017 |     125
(1 row)

(Or)

OFFSET

The OFFSET clause in SQL is used to skip a specific number of rows before starting to return the results. It is commonly used with LIMIT to retrieve a specific row.

movie=# SELECT * from cinema limit 1 offset 7;
 id | movie_name | actor  | year | minutes 
----+------------+--------+------+---------
  8 | Kaithi     | Karthi | 2017 |     125
(1 row)

2) List down all the movies released between 2021 and 2023

movie=# select movie_name,year from cinema where year between 2021 and 2023;
 movie_name | year 
------------+------
 Dharbar    | 2021
 Vikram     | 2023
 Viswasam   | 2021
 Ayothi     | 2023
(4 rows)

**
3) List down all the movies released not between 2021 and 2023**

movie=# select movie_name,year from cinema where year not between 2021 and 2023;   movie_name    | year 
-----------------+------
 Mersal          | 2020
 Beast           | 2019
 Attakasam       | 2006
 Jai Bhim        | 2018
 Kaithi          | 2017
 Deivathirumagan | 2017
(6 rows)

4) List down first 3 movies based on released year in descending order

movie=# select movie_name,year from cinema order by year desc limit 3;
 movie_name | year 
------------+------
 Vikram     | 2023
 Ayothi     | 2023
 Dharbar    | 2021
(3 rows)

5) List down All movies by Vijay in the year 2020.

movie=# select movie_name,actor,year from cinema where actor='Vijay' and year=2020 ;
 movie_name | actor | year 
------------+-------+------
 Mersal     | Vijay | 2020
(1 row)

6) List down all movies where we have ‘as’ in the movie name.

movie=# select movie_name,year from cinema where movie_name like '%as%';
 movie_name | year 
------------+------
 Beast      | 2019
 Viswasam   | 2021
 Attakasam  | 2006
(3 rows)

7) List down all actor names without duplicates.

movie=# select distinct actor from cinema;
   actor   
-----------
 Sasikumar
 Kamal
 Vijay
 Karthi
 Surya
 Ajith
 Rajini
 Vikram
(8 rows)

8) List down Ajith movies in alphabetical order

movie=# select movie_name,actor from cinema where actor= 'Ajith' order by movie_name;
 movie_name | actor 
------------+-------
 Attakasam  | Ajith
 Viswasam   | Ajith
(2 rows)

9) List down movies where their names start with ‘A’ and actor name starts with ‘A’.

movie=# select movie_name,actor from cinema where actor like 'A%' and movie_name like'A%';
 movie_name | actor 
------------+-------
 Attakasam  | Ajith
(1 row)

10) List down movies if the movie name is ‘Vikram’ or the actor name is ‘Vikram’.

movie=# select movie_name,actor from cinema where actor like 'Vikram' or movie_name like 'Vikram';
   movie_name    | actor  
-----------------+--------
 Vikram          | Kamal
 Deivathirumagan | Vikram
(2 rows)