DEV Community

Lakshmi Pritha Nadesan
Lakshmi Pritha Nadesan

Posted on

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

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

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

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

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

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

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

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

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

8) List down all movies acted by Vikram:

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

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

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

Top comments (0)