Products & Categories:
Display product name and category name.
cinemas=# select p.product_name, c.category_name
cinemas-# from products p
cinemas-# join categories c
cinemas-# on p.category_id = c.category_id;
O/P:
product_name | category_name
-------------------------+-----------------
Wireless Mouse | Electronics
Bluetooth Speaker | Electronics
Dell Laptop | Laptops
HP Laptop | Laptops
iPhone 15 | Mobile Phones
Samsung Galaxy | Mobile Phones
Office Chair | Furniture
Computer Table | Furniture
Java Programming Book | Books
Python Programming Book | Books
Air Conditioner | Home Appliances
Microwave Oven | Home Appliances
(12 rows)
Display product name, price and category name.
cinemas=# select p.product_name, p.price, c.category_name
from products p
join categories c
on p.category_id = c.category_id;
O/P:
product_name | price | category_name
-------------------------+----------+-----------------
Wireless Mouse | 799.00 | Electronics
Bluetooth Speaker | 1499.00 | Electronics
Dell Laptop | 65000.00 | Laptops
HP Laptop | 58000.00 | Laptops
iPhone 15 | 65000.00 | Mobile Phones
Samsung Galaxy | 42000.00 | Mobile Phones
Office Chair | 7500.00 | Furniture
Computer Table | 12000.00 | Furniture
Java Programming Book | 850.00 | Books
Python Programming Book | 950.00 | Books
Air Conditioner | 45000.00 | Home Appliances
Microwave Oven | 12000.00 | Home Appliances
(12 rows)
Display product name, category name and stock.
cinemas=# select p.product_name, c.category_name, p.stock
from products p
join categories c
on p.category_id = c.category_id;
O/P:
product_name | category_name | stock
-------------------------+-----------------+-------
Wireless Mouse | Electronics | 50
Bluetooth Speaker | Electronics | 25
Dell Laptop | Laptops | 10
HP Laptop | Laptops | 8
iPhone 15 | Mobile Phones | 15
Samsung Galaxy | Mobile Phones | 20
Office Chair | Furniture | 12
Computer Table | Furniture | 5
Java Programming Book | Books | 30
Python Programming Book | Books | 20
Air Conditioner | Home Appliances | 7
Microwave Oven | Home Appliances | 9
(12 rows)
Display products belonging to Electronics.
cinemas=# select p.product_name
cinemas-# from products p
cinemas-# join categories c
cinemas-# on p.category_id = c.category_id
cinemas-# where c.category_name = 'Electronics';
O/P:
product_name
-------------------
Wireless Mouse
Bluetooth Speaker
(2 rows)
Display products whose price is greater than Rs. 50,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where p.price > 50000;
O/P:
product_name
--------------
Dell Laptop
HP Laptop
iPhone 15
(3 rows)
Display products from Laptops category costing more than Rs. 60,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Laptops' and p.price > 60000;
O/P:
product_name
--------------
Dell Laptop
(1 row)
Display products whose stock is less than 10.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where p.stock < 10;
O/P:
product_name
-----------------
HP Laptop
Computer Table
Air Conditioner
Microwave Oven
(4 rows)
Display products from Books category with price less than Rs. 1,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Books' and p.price < 1000;
O/P:
product_name
-------------------------
Java Programming Book
Python Programming Book
(2 rows)
Display all products from Mobile Phones category costing more than Rs. 40,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Mobile Phones' and p.price > 40000;
O/P:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Level 2:
Display products from Electronics costing more than Rs. 1,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Electronics' and p.price > 1000;
O/P:
product_name
-------------------
Bluetooth Speaker
(1 row)
Display products from Furniture costing less than Rs. 10,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Furniture' and p.price < 10000;
O/P:
product_name
--------------
Office Chair
(1 row)
Display products from Mobile Phones having stock greater than 10.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Mobile Phones' and p.price > 10;
O/P:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Display products from Home Appliances costing more than Rs. 20,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Home Appliances' and p.price > 20000;
O/P:
product_name
-----------------
Air Conditioner
(1 row)
Display products whose price is between Rs. 10,000 and Rs. 50,000, along with their category.
cinemas=# select p.product_name, c.category_name
from products p
join categories c
on p.category_id = c.category_id
where p.price between 10000 and 50000;
O/P:
product_name | category_name
-----------------+-----------------
Samsung Galaxy | Mobile Phones
Computer Table | Furniture
Air Conditioner | Home Appliances
Microwave Oven | Home Appliances
(4 rows)
Display products from Books where stock is greater than 20.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Books' and p.price > 20;
O/P:
product_name
-------------------------
Java Programming Book
Python Programming Book
(2 rows)
Display products from Laptops where price is less than Rs. 60,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Laptops' and p.price < 60000;
O/P:
product_name
--------------
HP Laptop
(1 row)
Level 3:
Display products whose price is greater than ₹40,000 and stock is greater than 10.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where p.price > 40000 and p.stock > 10;
O/P:
product_name
----------------
Samsung Galaxy
iPhone 15
(2 rows)
Display products belonging to either Laptops or Mobile Phones.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Laptops' or c.category_name = 'Mobile Phones';
O/P:
product_name
----------------
Dell Laptop
HP Laptop
iPhone 15
Samsung Galaxy
(4 rows)
Display products belonging to either Books or Electronics, with price greater than ₹1,000.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name in ('Books', 'Electronics')
cinemas-# and p.price > 1000;
O/P:
product_name
-------------------
Bluetooth Speaker
(1 row)
Display products whose category is not Books.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name not in ('Books');
O/P:
product_name
-------------------
Wireless Mouse
Bluetooth Speaker
Dell Laptop
HP Laptop
iPhone 15
Samsung Galaxy
Office Chair
Computer Table
Air Conditioner
Microwave Oven
(10 rows)
Display products whose category name starts with 'M'.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name like 'M%';
O/P:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Display products whose category name contains the word 'Phone'.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where c.category_name like '%Phones';
O/P:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Display products priced between ₹10,000 and ₹60,000 and having stock greater than 8.
cinemas=# select p.product_name
from products p
join categories c
on p.category_id = c.category_id
where (p.price between 10000 and 60000) and p.stock > 8;
O/P:
product_name
----------------
Samsung Galaxy
Microwave Oven
(2 rows)
Top comments (0)