Task 2 : Database

Create cinema table for storing movie information: employee=# create table cinema(id int,movie_name varchar(20), actor varchar(20),year int,minutes int); CREATE TABLE Insert movie details into movie table: employee=# insert into cinema values(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); INSERT 0 10 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) 1) List down 8th row values from the table: employee=# 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: employee=# select movie_name from cinema where year between 2021 and 2023; movie_name ------------ Dharbar Vikram Viswasam Ayothi (4 rows) 3) List down all the movies released not between 2021 and 2023: employee=# select movie_name from cinema where year not between 2021 and 2023; movie_name ----------------- Mersal Beast Attakasam Jai Bhim Kaithi Deivathirumagan (6 rows) 4) List down first 3 movies based on released year in descending order: employee=# select movie_name from cinema order by year desc limit 3 ; movie_name ------------ Vikram Ayothi Dharbar (3 rows) 5) List down All movies by Vijay in the year 2020: employee=# select movie_name from cinema where actor='vijay' or year=2020; movie_name ------------ Mersal (1 row) 6) List down all movies where we have ‘as’ in the movie name: employee=# select movie_name from cinema where movie_name like '%as%'; movie_name ------------ Beast Viswasam Attakasam (3 rows) 7) List down all actor names without duplicates: employee=# select distinct actor from cinema; actor ----------- Sasikumar Kamal Vijay Karthi Surya Ajith Rajini Vikram (8 rows) 8) List down Ajith movies in alphabetical order: employee=# select movie_name from cinema where actor='Ajith' order by movie_name; movie_name ------------ Attakasam Viswasam (2 rows) 9) List down movies where their names start with ‘A’ and actor name starts with ‘A’: employee=# select movie_name from cinema where actor like 'A%' and movie_name like 'A%'; movie_name ------------ Attakasam (1 row) 10) List down movies if the movie name is ‘Vikram’ or the actor name is ‘Vikram’: employee=# select movie_name from cinema where actor='Vikram' or movie_name='Vikram'; movie_name ----------------- Vikram Deivathirumagan (2 rows)

Feb 13, 2025 - 14:21

Create cinema table for storing movie information:

employee=# create table cinema(id int,movie_name varchar(20), actor varchar(20),year int,minutes int);
CREATE TABLE

Insert movie details into movie table:

employee=# insert into cinema values(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);
INSERT 0 10

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)

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

employee=# 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:

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

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

employee=# select movie_name from cinema where year not between 2021 and 2023;
   movie_name    
-----------------
 Mersal
 Beast
 Attakasam
 Jai Bhim
 Kaithi
 Deivathirumagan
(6 rows)

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

employee=# select movie_name from cinema order by year desc limit 3 ;
 movie_name 
------------
 Vikram
 Ayothi
 Dharbar
(3 rows)

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

employee=# select movie_name from cinema where actor='vijay' or year=2020;
 movie_name 
------------
 Mersal
(1 row)

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

employee=# select movie_name from cinema where movie_name like '%as%'; 
 movie_name 
------------
 Beast
 Viswasam
 Attakasam
(3 rows)

7) List down all actor names without duplicates:

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

8) List down Ajith movies in alphabetical order:

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

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

employee=# select movie_name from cinema where actor like 'A%' and movie_name like 'A%';
 movie_name 
------------
 Attakasam
(1 row)

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

employee=# select movie_name from cinema where actor='Vikram' or movie_name='Vikram';
   movie_name    
-----------------
 Vikram
 Deivathirumagan
(2 rows)