Task:
Create Table: Cinema
movie=# create table cinema(id int,Movie_name varchar(20),Actor varchar(20),year int,Minutes int);
CREATE TABLE
movie=# insert into cinema(id,Movie_name,Actor,year,Minutes) 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
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 all the movies from the year 2018
movie=# select movie_name,year from cinema where year=2018;
movie_name | year
------------+------
Jai Bhim | 2018
(1 row)
2) List down all the movies where their minutes greater than 120
movie=# select movie_name,minutes from cinema where minutes>120;
movie_name | minutes
-----------------+---------
Dharbar | 121
Vikram | 125
Mersal | 123
Beast | 134
Jai Bhim | 127
Kaithi | 125
Ayothi | 124
Deivathirumagan | 121
(8 rows)
3) List down all Vijay and Ajith movies
movie=# select movie_name,actor from cinema where actor='Ajith' or actor='Vijay';
movie_name | actor
------------+-------
Mersal | Vijay
Beast | Vijay
Viswasam | Ajith
Attakasam | Ajith
(4 rows)
4) List down all movies where their name’s last letter is ‘i’
movie=# 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.
movie=# select movie_name,actor from cinema where length(actor)>5;
movie_name | actor
-----------------+-----------
Dharbar | Rajini
Kaithi | Karthi
Ayothi | Sasikumar
Deivathirumagan | Vikram
(4 rows)
6) List down all movies where actor name length is greater than 5 and release after 2020.
movie=# select movie_name,actor,year from cinema where length(actor)>5 and year>2020;
movie_name | actor | year
------------+-----------+------
Dharbar | Rajini | 2021
Ayothi | Sasikumar | 2023
(2 rows)
SIMILAR TO
--> SIMILAR TO works like LIKE but follows SQL-standard regular expressions.
--> It supports | (OR), () (grouping), % (any characters), and _ (single character).
--> If you have many conditions,instead of using multiple LIKE conditions with OR, you can use SIMILAR TO for a cleaner query and shorter.
7) List down all movies where their names start with a or b or c or d.
movie=# select movie_name from cinema where movie_name similar to '(A%|B%|C%|D%)';
movie_name
-----------------
Dharbar
Beast
Attakasam
Ayothi
Deivathirumagan
(5 rows)
8) List down all movies acted by Vikram
movie=# select movie_name,actor from cinema where actor in ('Vikram');
movie_name | actor
-----------------+--------
Deivathirumagan | Vikram
(1 row)
9) List down all movies released in 2017 or acted by Rajini.
movie=# select movie_name,actor,year from cinema where year=2017 or actor='Rajini';
movie_name | actor | year
-----------------+--------+------
Dharbar | Rajini | 2021
Kaithi | Karthi | 2017
Deivathirumagan | Vikram | 2017
(3 rows)
10) List down all movies released after 2019 and acted by either Sasikumar or Vijay.
movie=# select movie_name,actor,year from cinema where year>2019 and (actor ='Sasikumar' or actor='Vijay');
movie_name | actor | year
------------+-----------+------
Mersal | Vijay | 2020
Ayothi | Sasikumar | 2023
(2 rows)
Top comments (0)