Task 4 - Database

Create customer table:

create table customer(customer_id int primary key,
first_name text, 
last_name text, 
email text,
city text, 
state text);

CREATE TABLE

INSERT INTO customer (customer_id, first_name, last_name, email, city, state) VALUES 
(101, 'Arun', 'Kumar', '[e-mail]', 'Chennai', 'Tamil Nadu'),(102, 'Mani', 'Kandan', '[e-mail]', 'Coimbatore', 'Tamil Nadu'),
(103, 'Ravi', 'Shankar', '[e-mail]', 'Madurai', 'Tamil Nadu'),(104, 'Neha', 'Sharma', '[e-mail]', 'Chennai', 'Tamil Nadu'),(105, 'Vickram', 'Kumar', '[e-mail]', 'Trichy', 'Tamil Nadu');

INSERT 0 5

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    
-------------+------------+-----------+------------------+------------+------------
         101 | Arun       | Kumar     | [e-mail]  | Chennai    | Tamil Nadu
         102 | Mani       | Kandan    | [e-mail]   | Coimbatore | Tamil Nadu
         103 | Ravi       | Shankar   | [e-mail]   | Madurai    | Tamil Nadu
         104 | Neha       | Sharma    | [e-mail]   | Chennai    | Tamil Nadu
         105 | Vickram    | Kumar     | [e-mail] | Trichy     | Tamil Nadu
(5 rows)

create orders table:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    amount INTEGER,
    product_name TEXT,
    product_category TEXT,
    status TEXT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE
);

CREATE TABLE

insert into orders (order_id,customer_id, order_date, amount, product_name, product_category, status) values
(1001,101,'05-01-2024', 25000,'Laptop', 'Electronics', 'Shipped'),(1002,101,'10-01-2024', 5000,'Smartphone', 'Electronics', 'Shipped'),
(1003,102,'12-01-2024', 15000,'Chair', 'Furniture', 'Pending'),(1004,103,'15-01-2024', 20000,'Table', 'Furniture', 'Delivered'),(1005,101,'01-02-2024', 12000,'Note&Book', 'Book$stationery', 'Shipped'),
(1006,104,'05-02-2024', 8000,'Sofa', 'Furniture', 'Shipped'),(1007,102,'15-02-2024', 6000,'Smartphone', 'Electronics', 'Delivered');
(1008,104,'25-02-2024', 12000,'Desk', 'Furniture', 'Delivered')

INSERT 0 7

select * from orders;

 order_id | customer_id | order_date | amount | product_name | product_category |  status   
----------+-------------+------------+--------+--------------+------------------+-----------
     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
     1008 |         104 | 2024-02-25 |  12000 | Desk         | Furniture        | Delivered
(8 rows)

Create product table:

CREATE TABLE product (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    unit_price INTEGER,
    category TEXT,
    manufacturer TEXT,
    stock_quantity INTEGER,
    order_id INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

CREATE TABLE

insert into product (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,1008);

INSERT 0 7

select* from product;

 product_id | product_name | unit_price |    category     | manufacturer | stock_quantity | order_id 
------------+--------------+------------+-----------------+--------------+----------------+----------
        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 |     1008
(7 rows)

1. Write a DDL query to create the Orders table with appropriate columns give above:

select * from orders;

 order_id | customer_id | order_date | amount | product_name | product_category |  status   
----------+-------------+------------+--------+--------------+------------------+-----------
     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
     1008 |         104 | 2024-02-25 |  12000 | Desk         | Furniture        | Delivered
(8 rows)

2. Write a DDL query to add a new column “Phone_Number” to the Customers table:

alter table customer add phone_number int;

ALTER TABLE

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         101 | Arun       | Kumar     | [e-mail]  | Chennai    | Tamil Nadu |             
         102 | Mani       | Kandan    | [e-mail]   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | [e-mail]   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | [e-mail]   | Chennai    | Tamil Nadu |             
         105 | Vickram    | Kumar     | [e-mail] | Trichy     | Tamil Nadu |             
(5 rows)

3. Write a DDL query to Insert a new customer into the Customers table:

INSERT INTO customer (customer_id, first_name, last_name, email, city, state)                                                                         VALUES  (106, 'Lakshmi', 'Pritha', '[e-mail]', 'Chennai', 'Tamil Nadu');

INSERT 0 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         101 | Arun       | Kumar     | [e-mail]  | Chennai    | Tamil Nadu |             
         102 | Mani       | Kandan    | [e-mail]   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | [e-mail]   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | [e-mail]   | Chennai    | Tamil Nadu |             
         105 | Vickram    | Kumar     | [e-mail] | Trichy     | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | [e-mail] | Chennai    | Tamil Nadu |             
(6 rows)

4. Write a query to Count the number of orders placed by each customer in the Orders table:

select customer_id, count(order_id) from orders group by customer_id order by customer_id ;

 customer_id | count 
-------------+-------
         101 |     3
         102 |     2
         103 |     1
         104 |     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:

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
     1008 |         104 | 2024-02-25 |  12000 | Desk         | Furniture        | Delivered
(5 rows)

6. Write a DML query to Update the city of “customer_id” 101 to “Mumbai”:

update customer  set city='Mumbai' where customer_id=101;

UPDATE 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         102 | Mani       | Kandan    | [e-mail]   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | [e-mail]   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | [e-mail]   | Chennai    | Tamil Nadu |             
         105 | Vickram    | Kumar     | [e-mail] | Trichy     | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | [e-mail] | Chennai    | Tamil Nadu |             
         101 | Arun       | Kumar     | [e-mail]  | Mumbai     | Tamil Nadu |             
(6 rows)

7. Write a DML query to Delete the customer with “Customer_ID” 105:

delete from customer where customer_id=105;

DELETE 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         102 | Mani       | Kandan    | [e-mail]   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | [e-mail]   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | [e-mail]   | Chennai    | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | [e-mail] | Chennai    | Tamil Nadu |             
         101 | Arun       | Kumar     | [e-mail]  | Mumbai     | Tamil Nadu |             
(5 rows)

8. Retrieve allproducts from the “Product Table” that have never been ordered:

SELECT product.product_id, product.product_name
FROM product
LEFT JOIN orders  ON product.order_id = orders.order_id
WHERE orders.order_id IS  NULL;

 product_id | product_name 
------------+--------------
(0 rows)

9. Retrieve all customers details who llive in Chennai and have made an order value more than 20,000:

select customer.customer_id, customer.first_name, customer.last_name, customer.email, customer.city, customer.state from customer left 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 
-------------+------------+-----------+-------+------+-------
(0 rows)

10. Retrieve the “Customer_ID” and total Number of order count for each customer who has placed more than two orders:

select customer_id, count(order_id) from orders group by customer_id having count(order_id)>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”:

select customer.customer_id,customer.first_name, customer.email, count(order_id) from customer join orders on customer.customer_id= orders.customer_id group by customer.customer_id order by count desc limit 5;

 customer_id | first_name |      email       | count 
-------------+------------+------------------+-------
         101 | Arun       | [e-mail]  |     3
         104 | Neha       | [e-mail]   |     2
         102 | Mani       | [e-mail]   |     2
         103 | Ravi       | [e-mail]   |     1
         106 | Lakshmi    | [e-mail] |     0
(5 rows)

12. List all customers who have placed orders in the month of January 2024:

select customer.customer_id, customer.first_name, customer.last_name, customer.email, customer.city, customer.state from customer join orders on customer.customer_id=orders.customer_id where order_date between '01-01-2024' and '31-01-2024';

 customer_id | first_name | last_name |      email      |    city    |   state    
-------------+------------+-----------+-----------------+------------+------------
         102 | Mani       | Kandan    | [e-mail]  | Coimbatore | Tamil Nadu
         103 | Ravi       | Shankar   | [e-mail]  | Madurai    | Tamil Nadu
         101 | Arun       | Kumar     | [e-mail] | Mumbai     | Tamil Nadu
         101 | Arun       | Kumar     | [e-mail] | Mumbai     | Tamil Nadu
(4 rows)

13. Find the total number of orders placed and the average “order amount” for each city:

select customer.city, count(order_id), avg(round(amount)) from customer join orders on customer.customer_id=orders.customer_id group by customer.city;

    city    | count |  avg  
------------+-------+-------
 Mumbai     |     3 | 14000
 Chennai    |     2 | 10000
 Coimbatore |     2 | 10500
 Madurai    |     1 | 20000
(4 rows)

14. Identity the products which are ordered on weekends, And the customer details who had ordered them:

select orders.product_name, orders.order_date, customer.customer_id, customer.first_name, customer.last_name, customer.email, customer.city, customer.state from customer join orders on customer.customer_id=orders.customer_id WHERE EXTRACT(DOW FROM orders.order_date) IN (0, 6);

 product_name | order_date | customer_id | first_name | last_name |     email      |  city   |   state    
--------------+------------+-------------+------------+-----------+----------------+---------+------------
 Desk         | 2024-02-25 |         104 | Neha       | Sharma    | [e-mail] | Chennai | Tamil Nadu
(1 row)

15. Find the most expensive product in the along with the price in the Products table and the customer details(Customer_ID,First_Name) who have ordered it:

select product.product_name, product.unit_price, customer.customer_id, customer.first_name from product join orders on product.order_id=orders.order_id join customer on orders.customer_id=customer.customer_id order by product.unit_price desc limit 1;

 product_name | unit_price | customer_id | first_name 
--------------+------------+-------------+------------
 Laptop       |      50000 |         101 | Arun
(1 row)

16. List all customers along with the count of their orders in the format “Customer (Order Count)”. Example Output: ARUN Kumar (3):

select concat(customer.first_name,' ', customer.last_name, '(', count(orders.order_id), ')' ) from customer join orders on customer.customer_id=orders.customer_id group by customer.customer_id order by customer.first_name;
     concat      
-----------------
 Arun Kumar(3)
 Mani Kandan(2)
 Neha Sharma(2)
 Ravi Shankar(1)
(4 rows)

17. Write an SQL query to select all customers who live in Chennai and have ordered the second most expensive product:

select customer.customer_id, customer.first_name, customer.last_name, customer.email from customer join orders on customer.customer_id=orders.customer_id join product on product.order_id=orders.order_id where customer.city='Chennai' and product.unit_price=(select distinct unit_price from product order by unit_price desc limit 1 offset 1);

 customer_id | first_name | last_name |     email      
-------------+------------+-----------+----------------
         104 | Neha       | Sharma    | [e-mail]
(1 row)

18. Get the total revenue generated from each customer, and label the customer as ‘High Spender’ if they have spent more than 50,000, otherwise ‘Low Spender’:

select customer.customer_id, customer.first_name,customer.last_name, sum(orders.amount) as totalrevenue, case when sum(orders.amount) > 50000 then 'High Spender' else 'Low Spender' end from customer join orders on customer.customer_id= orders.customer_id group by customer.customer_id order by totalrevenue desc;

 customer_id | first_name | last_name | totalrevenue |    case     
-------------+------------+-----------+--------------+-------------
         101 | Arun       | Kumar     |        42000 | Low Spender
         102 | Mani       | Kandan    |        21000 | Low Spender
         103 | Ravi       | Shankar   |        20000 | Low Spender
         104 | Neha       | Sharma    |        20000 | Low Spender
(4 rows)

19. Retrieve the total number of orders placed by each customer for each month in the year:

select customer.customer_id,customer.first_name, customer. last_name, EXTRACT(month from orders.order_date) as ordermonth, EXTRACT(year from orders.order_date) as orderyear, count(order_id) as total from customer join orders on customer.customer_id = orders.customer_id GROUP BY customer.customer_id, EXTRACT(month from orders.order_date), EXTRACT(year from orders.order_date) ORDER BY customer.customer_id, orderyear, ordermonth;

 customer_id | first_name | last_name | ordermonth | orderyear | total 
-------------+------------+-----------+------------+-----------+-------
         101 | Arun       | Kumar     |          1 |      2024 |     2
         101 | Arun       | Kumar     |          2 |      2024 |     1
         102 | Mani       | Kandan    |          1 |      2024 |     1
         102 | Mani       | Kandan    |          2 |      2024 |     1
         103 | Ravi       | Shankar   |          1 |      2024 |     1
         104 | Neha       | Sharma    |          2 |      2024 |     2
(6 rows)

20. Write an SQL query to retrieve all customers who satisfy the following conditions
Condition 1: The city is not either ‘chennai’ or Trichy’.
Condition 2: The email domain is ‘@yahoo.com’.
Condition 3: The customer has at least one order.
Sort them in alphabetical order by last name in descending order, followed by first name in ascending order.

select customer.customer_id,customer.first_name, customer. last_name, customer.city, count(orders.order_id) from customer join orders on customer.customer_id=orders.customer_id where customer.city not in('Chennai','Trichy') and customer.email like '@yahoo.com' GROUP BY customer.customer_id having count(orders.order_id)>=1 ORDER BY customer.last_name desc, customer.first_name asc;

 customer_id | first_name | last_name | city | count 
-------------+------------+-----------+------+-------
(0 rows)

21. Find customers who have not ordered any products in the Furniture category.

select customer.customer_id,customer.first_name, customer. last_name, customer.city from customer where  NOT EXISTS (SELECT 1                                                                                                                                             
FROM orders WHERE orders.customer_id = customer.customer_id
AND orders.product_category = 'Furniture' GROUP BY customer.customer_id);

 customer_id | first_name | last_name |  city   
-------------+------------+-----------+---------
         106 | Lakshmi    | Pritha    | Chennai
         101 | Arun       | Kumar     | Mumbai
(2 rows)