Table:1
create customer table:
movie=# CREATE TABLE Customer (
Customer_ID INT PRIMARY KEY,
First_Name VARCHAR(20),
Last_Name VARCHAR(20),
Email VARCHAR(20),
City VARCHAR(20),
State VARCHAR(20));
CREATE TABLE
Insert values to customer table:
movie=# INSERT INTO Customer(Customer_ID, First_Name, Last_Name, Email, City, State) VALUES
(101, 'Arun', 'Kumar', 'arjun@gmail.com', 'Chennai', 'Tamil Nadu'),
(102, 'Mani', 'Kandan', 'mani@yahoo.com', 'Coimbatore', 'Tamil Nadu'),
(103, 'Ravi', 'Shankar', 'ravi@yahoo.com', 'Madurai', 'Tamil Nadu'),
(104, 'Neha', 'Sharma', 'neha@gmail.com', 'Chennai', 'Tamil Nadu'),
(105, 'Vikram', 'Kumar', 'vikram@gmail.com', 'Trichy', 'Tamil Nadu');
INSERT 0 5
Customer table:
customer_id | first_name | last_name | email | city | state
-------------+------------+-----------+------------------+------------+------------
101 | Arun | Kumar | arjun@gmail.com | Chennai | Tamil Nadu
102 | Mani | Kandan | mani@yahoo.com | Coimbatore | Tamil Nadu
103 | Ravi | Shankar | ravi@yahoo.com | Madurai | Tamil Nadu
104 | Neha | Sharma | neha@gmail.com | Chennai | Tamil Nadu
105 | Vikram | Kumar | vikram@gmail.com | Trichy | Tamil Nadu
(5 rows)
Table:2
Create orders table:
movie=# CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT REFERENCES Customer(Customer_ID),
Order_Date DATE,
Amount FLOAT,
Product_Name VARCHAR(255),
Product_Category VARCHAR(20),
Status VARCHAR(20) CHECK (Status IN ('Shipped', 'Pending', 'Delivered'))
);
CREATE TABLE
Insert values to orders table:
movie=# INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Amount, Product_Name, Product_Category, Status) VALUES
(1001, 101, '2024-01-05', 25000, 'Laptop', 'Electronics', 'Shipped'),
(1002, 101, '2024-01-10', 5000, 'Smartphone', 'Electronics', 'Shipped'),
(1003, 102, '2024-01-12', 15000, 'Chair', 'Furniture', 'Pending'),
(1004, 103, '2024-01-15', 20000, 'Table', 'Furniture', 'Delivered'),
(1005, 101, '2024-02-01', 12000, 'Note&Book', 'Book & Stationery', 'Shipped'),
(1006, 104, '2024-02-05', 8000, 'Sofa', 'Furniture', 'Shipped'),
(1007, 102, '2024-02-15', 6000, 'Smartphone', 'Electronics', 'Delivered');
INSERT 0 7
Table:3
Create products table:
movie=# drop table products;
DROP TABLE
movie=# CREATE TABLE Products(
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(20),
Unit_Price FLOAT,
Category VARCHAR(20),
Manufacturer VARCHAR(20),
Stock_Quantity INT NOT NULL CHECK (Stock_Quantity >= 0),
Order_ID INT REFERENCES Orders(Order_ID) ON DELETE CASCADE);
CREATE TABLE
Insert values to products table:
movie=# INSERT INTO Products (Product_ID, Product_Name, Unit_Price, Category, Manufacturer, Stock_Quantity, Order_ID) VALUES
(201, 'Laptop', 50000, 'Electronics', 'Brand A', 10, 1001),
(202, 'Smartphone', 15000, 'Electronics', 'Brand B', 15, 1002),
(203, 'Chair', 3000, 'Furniture', 'Brand C', 20, 1003),
(204, 'Table', 8000, 'Furniture', 'Brand D', 25, 1004),
(205, 'Sofa', 15000, 'Furniture', 'Brand E', 5, 1006),
(206, 'Note&Book', 550, 'Book & Stationery', 'Brand G', 8, 1005),
(207, 'Desk', 12000, 'Furniture', 'Brand F', 12, NULL);
INSERT 0 7
Exercises:
1. Write a DDL query to create the Orders table with appropriate columns give above.
movie=# CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT REFERENCES Customer(Customer_ID),
Order_Date DATE,
Amount FLOAT,
Product_Name VARCHAR(255),
Product_Category VARCHAR(20),
Status VARCHAR(20) CHECK (Status IN ('Shipped', 'Pending', 'Delivered'))
);
CREATE TABLE
2. Write a DDL query to add a new column “Phone_Number” to the Customers table.
movie=# alter table customer add phone_number int;
ALTER TABLE
3. Write a DDL query to Insert a new customer into the Customers table.
movie=# INSERT INTO Customer(Customer_ID, First_Name, Last_Name, Email, City, State, Phone_Number)
VALUES (106, 'Guru', 'Prasanna', 'guru@gmail.com', 'Madurai', 'Tamil Nadu', 9789508514);
INSERT 0 1
4. Write a query to Count the number of orders placed by each customer in the Orders table.
movie=# SELECT Customer_ID, COUNT(*) as orders_placed
from Orders group by Customer_ID;
customer_id | orders_placed
-------------+---------------
101 | 3
103 | 1
104 | 1
102 | 2
(4 rows)
5. Retrieve all order details from order table with an amount not less than 10,000 and not greater than 25,000.
movie=# select *from orders where amount between 10000 and 25000;
order_id | customer_id | order_date | amount | product_name | product_category | status
----------+-------------+------------+--------+--------------+-------------------+-----------
1001 | 101 | 2024-01-05 | 25000 | Laptop | Electronics | Shipped
1003 | 102 | 2024-01-12 | 15000 | Chair | Furniture | Pending
1004 | 103 | 2024-01-15 | 20000 | Table | Furniture | Delivered
1005 | 101 | 2024-02-01 | 12000 | Note&Book | Book & Stationery | Shipped
(4 rows)
6. Write a DML query to Update the city of “customer_id” 101 to “Mumbai”.
movie=# update customer set city = 'Mumbai' where customer_id = 101;
UPDATE 1
7. Write a DML query to Delete the customer with “Customer_ID” 105.
movie=# delete from customer where customer_id = 105;
DELETE 1
8. Retrieve allproducts from the “Product Table” that have never been ordered.
SELECT * FROM Products WHERE Order_ID IS NULL;
product_id | product_name | unit_price | category | manufacturer | stock_quantity | order_id
------------+--------------+------------+-----------+--------------+----------------+----------
207 | Desk | 12000 | Furniture | Brand F | 12 |
(1 row)
9. Retrieve all customers details who live in Chennai and have made an order value more than 20,000.
movie=# SELECT customer.*
FROM customer
JOIN orders ON customer.customer_id = orders.customer_id
WHERE customer.city = 'Chennai' AND orders.amount > 20000;
customer_id | first_name | last_name | email | city | state | phone_number
-------------+------------+-----------+-------+------+-------+--------------
(0 rows)
10. Retrieve the “Customer_ID” and total Number of order count for each customer who has placed more than two orders
movie=# SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2;
customer_id | count
-------------+-------
101 | 3
(1 row)
11. Retrieve the top 5 customers based on the number of orders they have placed, along with their “First_name” and “email addresses”.
movie=# SELECT customer.first_name, customer.email, COUNT(*) AS order_count
FROM customer
JOIN orders ON customer.customer_id = orders.customer_id
GROUP BY customer.first_name, customer.email
ORDER BY order_count DESC
LIMIT 5;
first_name | email | order_count
------------+-----------------+-------------
Arun | arjun@gmail.com | 3
Mani | mani@yahoo.com | 2
Neha | neha@gmail.com | 1
Ravi | ravi@yahoo.com | 1
(4 rows)
DATE_PART() : To filter records based on month, year, etc.
Syntax: DATE_PART(field, source)
12. List all customers who have placed orders in the month of January 2024.
movie=# SELECT DISTINCT customer.*
FROM customer
JOIN orders ON customer.customer_id = orders.customer_id
WHERE DATE_PART('month', orders.order_date) = 1 AND DATE_PART('year', orders.order_date) = 2024;
customer_id | first_name | last_name | email | city | state | phone_number
-------------+------------+-----------+-----------------+------------+------------+--------------
101 | Arun | Kumar | arjun@gmail.com | Mumbai | Tamil Nadu |
102 | Mani | Kandan | mani@yahoo.com | Coimbatore | Tamil Nadu |
103 | Ravi | Shankar | ravi@yahoo.com | Madurai | Tamil Nadu |
(3 rows)
13. Find the total number of orders placed and the average “order amount” for each city.
movie=# select customer.city,count(orders.Order_id), avg(Orders.amount)
from customer
join orders on customer.customer_id = orders.customer_id
GROUP BY customer.city;
city | count | avg
------------+-------+-------
Chennai | 1 | 8000
Coimbatore | 2 | 10500
Madurai | 1 | 20000
Mumbai | 3 | 14000
(4 rows)
Top comments (0)