STUDENTS AND COURSES:
Learning Objective: Inner Join
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
course_id INT REFERENCES courses(course_id)
);
Sample Data:
INSERT INTO courses VALUES (101, 'Java'), (102, 'Python'), (103, 'Software Testing');
INSERT INTO students VALUES (1, 'Viyan', 101), (2, 'Kavin', 102), (3, 'Mathi', 101),
(4, 'Arul', 103);
Questions:
• Display student name and course name.
• Display only students who are enrolled in Java.
• Display students enrolled in Python.
• Display student name and course name ordered by student name.
Level 2 — Employee & Department
CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50));
CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), salary NUMERIC(10,2), dept_id INT REFERENCES departments(dept_id));
Add appropriate insert queries.
Practice Questions:
• Display employee name and department name.
• Display employees working in IT.
• Display employees earning more than Rs. 50,000 along with their department.
• Display employees sorted by department name.
• Display employee name, salary and department name.
- Products & Categories CREATE TABLE categories (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL); CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category_id INT, price NUMERIC(10,2), stock INT, FOREIGN KEY (category_id) REFERENCES categories(category_id)); INSERT INTO categories (category_id, category_name) VALUES (1, 'Electronics'),(2, 'Laptops'),(3, 'Mobile Phones'),(4, 'Furniture'),(5, 'Books'),(6, 'Home Appliances'); INSERT INTO products (product_id, product_name, category_id, price, stock) VALUES (101, 'Wireless Mouse', 1, 799, 50), (102, 'Bluetooth Speaker', 1, 1499, 25), (103, 'Dell Laptop', 2, 65000, 10), (104, 'HP Laptop', 2, 58000, 8), (105, 'iPhone 15', 3, 65000, 15), (106, 'Samsung Galaxy', 3, 42000, 20), (107, 'Office Chair', 4, 7500, 12), (108, 'Computer Table', 4, 12000, 5), (109, 'Java Programming Book', 5, 850, 30), (110, 'Python Programming Book', 5, 950, 20), (111, 'Air Conditioner', 6, 45000, 7), (112, 'Microwave Oven', 6, 12000, 9); Queries to Practice: • Display product name and category name. • Display product name, price and category name. • Display product name, category name and stock. • Display products belonging to Electronics. • Display products whose price is greater than Rs. 50,000. • Display products from Laptops category costing more than Rs. 60,000. • Display products whose stock is less than 10. • Display products from Books category with price less than Rs. 1,000. • Display all products from Mobile Phones category costing more than Rs. 40,000. Level 2: • Display products from Electronics costing more than Rs. 1,000. • Display products from Furniture costing less than Rs. 10,000. • Display products from Mobile Phones having stock greater than 10. • Display products from Home Appliances costing more than Rs. 20,000. • Display products whose price is between Rs. 10,000 and Rs. 50,000, along with their category. • Display products from Books where stock is greater than 20. • Display products from Laptops where price is less than Rs. 60,000.
vishwa@vishwa-VivoBook-ASUSLaptop-X421EAY-X413EA:~$ suod -i -u postgres;
Command 'suod' not found, did you mean:
command 'sudo' from deb sudo (1.9.15p5-3ubuntu5.24.04.2)
command 'sudo' from deb sudo-ldap (1.9.15p5-3ubuntu5.24.04.2)
Try: sudo apt install <deb name>
vishwa@vishwa-VivoBook-ASUSLaptop-X421EAY-X413EA:~$ sudo -i -u postgres;
[sudo] password for vishwa:
postgres@vishwa-VivoBook-ASUSLaptop-X421EAY-X413EA:~$ psql
psql (16.14 (Ubuntu 16.14-0ubuntu0.24.04.1))
Type "help" for help.
postgres=# \c cinemas
You are now connected to database "cinemas" as user "postgres".
cinemas=# select * from departments;
dept_id | dept_name
---------+-----------
1 | cse
2 | ece
3 | eee
4 | mech
(4 rows)
cinemas=# select * from employeess;
emp_id | emp_name | salary | dept_id
--------+----------+-----------+---------
101 | appu | 500000.00 | 1
102 | ragul | 400000.00 | 2
103 | vishwa | 600000.00 | 3
104 | vinoth | 700000.00 | 4
(4 rows)
cinemas=# select * from departments inner join employeess on departments.dept_id=employeess.dept_id;
dept_id | dept_name | emp_id | emp_name | salary | dept_id
---------+-----------+--------+----------+-----------+---------
1 | cse | 101 | appu | 500000.00 | 1
2 | ece | 102 | ragul | 400000.00 | 2
3 | eee | 103 | vishwa | 600000.00 | 3
4 | mech | 104 | vinoth | 700000.00 | 4
(4 rows)
cinemas=# select dept_name,emp_name from departments inner join employeess on departments.dept_id=employeess.dept_id;
dept_name | emp_name
-----------+----------
cse | appu
ece | ragul
eee | vishwa
mech | vinoth
(4 rows)
cinemas=# select * from employeess inner join departments on departments.dept_id=employeess.dept_id where dept_name = 'eee';
emp_id | emp_name | salary | dept_id | dept_id | dept_name
--------+----------+-----------+---------+---------+-----------
103 | vishwa | 600000.00 | 3 | 3 | eee
(1 row)
cinemas=# select emp_name,dept_name from employeess inner join departments on departments.dept_id=employeess.dept_id having salary > 500000;
ERROR: column "employeess.emp_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select emp_name,dept_name from employeess inner join departm...
^
cinemas=# select emp_name,dept_name from employeess inner join departments on employeess.dept_id=departments.dept_id having salary > 500000;
ERROR: column "employeess.emp_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select emp_name,dept_name from employeess inner join departm...
^
cinemas=# ^C
cinemas=# ^C
cinemas=# select dept_name,emp_name,salary from departments inner join employeess on departments.dept_id=employeess.dept_id having salary>500000;
ERROR: column "departments.dept_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select dept_name,emp_name,salary from departments inner join...
^
cinemas=# select dept_name,emp_name,salary from departments inner join employeess on departments.dept_id=employeess.dept_id where salary>500000;
dept_name | emp_name | salary
-----------+----------+-----------
eee | vishwa | 600000.00
mech | vinoth | 700000.00
(2 rows)
cinemas=# select dept_name,emp_name from departments inner join employeess on departments.dept_id=employeess.dept_id where salary>500000;
dept_name | emp_name
-----------+----------
eee | vishwa
mech | vinoth
(2 rows)
cinemas=# select emp_name from employeess inner join departments on employeess.dept_id=departments.dept_id order by dept_name;
emp_name
----------
appu
ragul
vishwa
vinoth
(4 rows)
cinemas=# select dept_name,emp_name,salary from departments inner join employeess on departments.dept_id=employeess.dept_id;
dept_name | emp_name | salary
-----------+----------+-----------
cse | appu | 500000.00
ece | ragul | 400000.00
eee | vishwa | 600000.00
mech | vinoth | 700000.00
(4 rows)
cinemas=# CREATE TABLE categories (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL);
CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category_id INT, price NUMERIC(10,2), stock INT, FOREIGN KEY (category_id) REFERENCES categories(category_id));
CREATE TABLE
ERROR: relation "products" already exists
cinemas=# [200~CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category_id INT, price NUMERIC(10,2), stock INT, FOREIGN KEY (category_id) REFERENCES categories(category_id));
ERROR: syntax error at or near "["
LINE 1: [200~CREATE TABLE products (product_id INT PRIMARY KEY, prod...
^
cinemas=# select * from categories;
category_id | category_name
-------------+---------------
(0 rows)
cinemas=# CREATE TABLE product (product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category_id INT, price NUMERIC(10,2), stock INT, FOREIGN KEY (category_id) REFERENCES categories(category_id));
CREATE TABLE
cinemas=# INSERT INTO categories (category_id, category_name) VALUES (1, 'Electronics'),(2, 'Laptops'),(3, 'Mobile Phones'),(4, 'Furniture'),(5, 'Books'),(6, 'Home Appliances');
INSERT INTO product (product_id, product_name, category_id, price, stock) VALUES (101, 'Wireless Mouse', 1, 799, 50), (102, 'Bluetooth Speaker', 1, 1499, 25), (103, 'Dell Laptop', 2, 65000, 10), (104, 'HP Laptop', 2, 58000, 8), (105, 'iPhone 15', 3, 65000, 15), (106, 'Samsung Galaxy', 3, 42000, 20), (107, 'Office Chair', 4, 7500, 12), (108, 'Computer Table', 4, 12000, 5), (109, 'Java Programming Book', 5, 850, 30), (110, 'Python Programming Book', 5, 950, 20), (111, 'Air Conditioner', 6, 45000, 7), (112, 'Microwave Oven', 6, 12000, 9);
INSERT 0 6
INSERT 0 12
cinemas=# select * from categories;
category_id | category_name
-------------+-----------------
1 | Electronics
2 | Laptops
3 | Mobile Phones
4 | Furniture
5 | Books
6 | Home Appliances
(6 rows)
cinemas=# select * from product;
product_id | product_name | category_id | price | stock
------------+-------------------------+-------------+----------+-------
101 | Wireless Mouse | 1 | 799.00 | 50
102 | Bluetooth Speaker | 1 | 1499.00 | 25
103 | Dell Laptop | 2 | 65000.00 | 10
104 | HP Laptop | 2 | 58000.00 | 8
105 | iPhone 15 | 3 | 65000.00 | 15
106 | Samsung Galaxy | 3 | 42000.00 | 20
107 | Office Chair | 4 | 7500.00 | 12
108 | Computer Table | 4 | 12000.00 | 5
109 | Java Programming Book | 5 | 850.00 | 30
110 | Python Programming Book | 5 | 950.00 | 20
111 | Air Conditioner | 6 | 45000.00 | 7
112 | Microwave Oven | 6 | 12000.00 | 9
(12 rows)
cinemas=# select * from categories inner join product on categories.^C
cinemas=# select * from categories inner join product on categories.category_id=product.category_id;
category_id | category_name | product_id | product_name | category_id | price | stock
-------------+-----------------+------------+-------------------------+-------------+----------+-------
1 | Electronics | 101 | Wireless Mouse | 1 | 799.00 | 50
1 | Electronics | 102 | Bluetooth Speaker | 1 | 1499.00 | 25
2 | Laptops | 103 | Dell Laptop | 2 | 65000.00 | 10
2 | Laptops | 104 | HP Laptop | 2 | 58000.00 | 8
3 | Mobile Phones | 105 | iPhone 15 | 3 | 65000.00 | 15
3 | Mobile Phones | 106 | Samsung Galaxy | 3 | 42000.00 | 20
4 | Furniture | 107 | Office Chair | 4 | 7500.00 | 12
4 | Furniture | 108 | Computer Table | 4 | 12000.00 | 5
5 | Books | 109 | Java Programming Book | 5 | 850.00 | 30
5 | Books | 110 | Python Programming Book | 5 | 950.00 | 20
6 | Home Appliances | 111 | Air Conditioner | 6 | 45000.00 | 7
6 | Home Appliances | 112 | Microwave Oven | 6 | 12000.00 | 9
(12 rows)
cinemas=# select product_name,category_name from product inner join categories on product.category_id=categories.category_id;
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)
cinemas=# select product_name,price,category_name, from product inner join categories on product.category_id=categories.category_id;
ERROR: syntax error at or near "from"
LINE 1: select product_name,price,category_name, from product inner ...
^
cinemas=# select product_name,price,category_name from product inner join categories on product.category_id=categories.category_id;
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)
cinemas=# select product_name,category_name,stock from product inner join categories on product.category_id=categories.category_id;
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)
cinemas=# select product_name product inner join categories on roduct.category_id=categories.category_id where category_name = 'Electronics';
ERROR: syntax error at or near "inner"
LINE 1: select product_name product inner join categories on roduct....
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name = 'Electronics';
product_name
-------------------
Wireless Mouse
Bluetooth Speaker
(2 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where price>500000;
product_name
--------------
(0 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where price>50000;
product_name
--------------
Dell Laptop
HP Laptop
iPhone 15
(3 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptop' having laptop > 60000;
ERROR: column "laptop" does not exist
LINE 1: ...s.category_id where category_name='Laptop' having laptop > 6...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptop' having Laptop>60000;
ERROR: column "laptop" does not exist
LINE 1: ...s.category_id where category_name='Laptop' having Laptop>600...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where Laptop>60000;
ERROR: column "laptop" does not exist
LINE 1: ... product.category_id=categories.category_id where Laptop>600...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where price>500000;
product_name
--------------
(0 rows)
cinemas=# elect product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptop';
ERROR: syntax error at or near "elect"
LINE 1: elect product_name from product inner join categories on pr...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptop';
product_name
--------------
(0 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptops';
product_name
--------------
Dell Laptop
HP Laptop
(2 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptops' having Laptops>60000 ;
ERROR: column "laptops" does not exist
LINE 1: ....category_id where category_name='Laptops' having Laptops>60...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where Laptops>60000;
ERROR: column "laptops" does not exist
LINE 1: ... product.category_id=categories.category_id where Laptops>60...
^
cinemas=# select product_name,category_name from product inner join categories on product.category_id=categories.category_id where Laptops>60000;
ERROR: column "laptops" does not exist
LINE 1: ... product.category_id=categories.category_id where Laptops>60...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptops' and price>60000;
product_name
--------------
Dell Laptop
(1 row)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where stock<10;
product_name
-----------------
HP Laptop
Computer Table
Air Conditioner
Microwave Oven
(4 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Books' and price<1000;
product_name
-------------------------
Java Programming Book
Python Programming Book
(2 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Mobile Phones' and price>40000;
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Electronics' and price>1000;
product_name
-------------------
Bluetooth Speaker
(1 row)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Furniture' and price>10000;
product_name
----------------
Computer Table
(1 row)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Mobile Phones' and stock>10;
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name=' Home Appliances' and price>20000;
product_name
--------------
(0 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Home Appliances' and price>20000;
product_name
-----------------
Air Conditioner
(1 row)
cinemas=# select product_name,category_name from product inner join categories on product.category_id=categories.category_id where price between 10000 and 50000;
product_name | category_name
-----------------+-----------------
Samsung Galaxy | Mobile Phones
Computer Table | Furniture
Air Conditioner | Home Appliances
Microwave Oven | Home Appliances
(4 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Books' and stock>20;
product_name
-----------------------
Java Programming Book
(1 row)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name='Laptops' and price<60000;
product_name
--------------
HP Laptop
(1 row)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where price>40000 and stock>10;
product_name
----------------
Samsung Galaxy
iPhone 15
(2 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name= 'Laptops' or 'Mobile Phones';
ERROR: invalid input syntax for type boolean: "Mobile Phones"
LINE 1: ...ies.category_id where category_name= 'Laptops' or 'Mobile Ph...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where c.category_name = 'Laptops' or c.category_name = 'Mobile Phones';
ERROR: missing FROM-clause entry for table "c"
LINE 1: ... product.category_id=categories.category_id where c.category...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name = 'Laptops' or category_name = 'Mobile Phones';
product_name
----------------
Dell Laptop
HP Laptop
iPhone 15
Samsung Galaxy
(4 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name = 'Books' or category_name = 'Electronics' and price>1000;
product_name
-------------------------
Bluetooth Speaker
Java Programming Book
Python Programming Book
(3 rows)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name = 'Books' price > 1000 or category_name = 'Electronics' and price>1000;
ERROR: syntax error at or near "price"
LINE 1: ...egories.category_id where category_name = 'Books' price > 10...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where c.category_name in ('Books' , 'Electronics') and price>1000;
ERROR: missing FROM-clause entry for table "c"
LINE 1: ... product.category_id=categories.category_id where c.category...
^
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name in ('Books' , 'Electronics') and price>1000;
product_name
-------------------
Bluetooth Speaker
(1 row)
cinemas=# select product_name from product inner join categories on product.category_id=categories.category_id where category_name not in ('Books');
product_name
-------------------
Wireless Mouse
Bluetooth Speaker
Dell Laptop
HP Laptop
iPhone 15
Samsung Galaxy
Office Chair
Computer Table
Air Conditioner
Microwave Oven
(10 rows)
cinemas=# select category_name from categories inner join product on categories.category_id=product.category_id where name like 'M%';
ERROR: column "name" does not exist
LINE 1: ... categories.category_id=product.category_id where name like ...
^
cinemas=# select product__name from categories inner join product on categories.category_id=product.category_id where category_name like 'M%';
ERROR: column "product__name" does not exist
LINE 1: select product__name from categories inner join product on c...
^
HINT: Perhaps you meant to reference the column "product.product_name".
cinemas=# select category__name from categories inner join product on categories.category_id=product.category_id where category_name like 'M%';
ERROR: column "category__name" does not exist
LINE 1: select category__name from categories inner join product on ...
^
HINT: Perhaps you meant to reference the column "categories.category_name".
cinemas=# select * from categories inner join product on categories.category_id=product.category_id where category_name like 'M%';
category_id | category_name | product_id | product_name | category_id | price | stock
-------------+---------------+------------+----------------+-------------+----------+-------
3 | Mobile Phones | 105 | iPhone 15 | 3 | 65000.00 | 15
3 | Mobile Phones | 106 | Samsung Galaxy | 3 | 42000.00 | 20
(2 rows)
cinemas=# select product_name from categories inner join product on categories.category_id=product.category_id where category_name like 'M%';
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
cinemas=# select product_name from categories inner join product on categories.category_id=product.category_id where category_name like 'Phone%';
product_name
--------------
(0 rows)
cinemas=# select product_name from categories inner join product on categories.category_id=product.category_id where category_name like '%Phone%';
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
cinemas=# select product_name from categories inner join product on categories.category_id=product.category_id where price>10000 and price<60000 ;
product_name
-----------------
HP Laptop
Samsung Galaxy
Computer Table
Air Conditioner
Microwave Oven
(5 rows)
cinemas=# select product_name from categories inner join product on categories.category_id=product.category_id where price>10000 and price<60000 and stock>8;
product_name
----------------
Samsung Galaxy
Microwave Oven
(2 rows)
Top comments (0)