Task : Database

#payilagam #database #postgres #task

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 all the movies from the year 2018:

employee=# select movie_name from cinema where year=2018;
 movie_name 
------------
 Jai Bhim
(1 row)

2) List down all the movies where their minutes greater than 120:

employee=# select movie_name from cinema where minutes>120;
   movie_name    
-----------------
 Dharbar
 Vikram
 Mersal
 Beast
 Jai Bhim
 Kaithi
 Ayothi
 Deivathirumagan
(8 rows)

3) List down all Vijay and Ajith movies:

employee=# select movie_name from cinema where actor='Vijay' or actor='Ajith';
 movie_name 
------------
 Mersal
 Beast
 Viswasam
 Attakasam
(4 rows)

4) List down all movies where their name’s last letter is ‘i’:

employee=# select movie_name from cinema where movie_name like '%i';
 movie_name 
------------
 Kaithi
 Ayothi
(2 rows)

5) List down all movies where actor name length is greater than 5:

employee=# select movie_name from cinema where length(actor)>5;
   movie_name    
-----------------
 Dharbar
 Kaithi
 Ayothi
 Deivathirumagan
(4 rows)

6) List down all movies where actor name length is greater than 5 and release after 2020:

employee=# select movie_name from cinema where length(actor)>5 and year>2020;
 movie_name 
------------
 Dharbar
 Ayothi
(2 rows)

7) List down all movies where their names start with a or b or c or d:

employee=# select movie_name from cinema where movie_name like 'A%' or movie_name like 'B%' or movie_name like 'C%' or movie_name like 'D%';
   movie_name    
-----------------
 Dharbar
 Beast
 Attakasam
 Ayothi
 Deivathirumagan
(5 rows)

8) List down all movies acted by Vikram:

employee=# select movie_name from cinema where actor='Vikram';
   movie_name    
-----------------
 Deivathirumagan
(1 row)

9) List down all movies released in 2017 or acted by Rajini:

employee=# select movie_name from cinema where year=2017 or actor='Rajini';
   movie_name    
-----------------
 Dharbar
 Kaithi
 Deivathirumagan
(3 rows)

10) List down all movies released after 2019 and acted by either Sasikumar or Vijay:

employee=# select movie_name from cinema where year>2019 and actor='Sasikumar' or actor='Vijay';
 movie_name 
------------
 Mersal
 Beast
 Ayothi
(3 rows)