کار 4 – پایگاه داده

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', '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, 'Vickram', 'Kumar', 'vikram@gmail.com', 'Trichy', 'Tamil Nadu');

INSERT 0 5

select * from customer;

 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 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu
(5 rows)

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 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)

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)

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     | 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 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu |             
(5 rows)

INSERT INTO customer (customer_id, first_name, last_name, email, city, state)                                                                         VALUES  (106, 'Lakshmi', 'Pritha', 'pritha@gmail.com', 'Chennai', 'Tamil Nadu');

INSERT 0 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         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 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | pritha@gmail.com | Chennai    | Tamil Nadu |             
(6 rows)

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)

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)

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    | 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 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | pritha@gmail.com | Chennai    | Tamil Nadu |             
         101 | Arun       | Kumar     | arjun@gmail.com  | Mumbai     | Tamil Nadu |             
(6 rows)

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    | mani@yahoo.com   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | ravi@yahoo.com   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | neha@gmail.com   | Chennai    | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | pritha@gmail.com | Chennai    | Tamil Nadu |             
         101 | Arun       | Kumar     | arjun@gmail.com  | Mumbai     | Tamil Nadu |             
(5 rows)

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)

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)

select customer_id, count(order_id) from orders group by customer_id having count(order_id)>2; 

 customer_id | count 
-------------+-------
         101 |     3
(1 row)

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       | arjun@gmail.com  |     3
         104 | Neha       | neha@gmail.com   |     2
         102 | Mani       | mani@yahoo.com   |     2
         103 | Ravi       | ravi@yahoo.com   |     1
         106 | Lakshmi    | pritha@gmail.com |     0
(5 rows)

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    | mani@yahoo.com  | Coimbatore | Tamil Nadu
         103 | Ravi       | Shankar   | ravi@yahoo.com  | Madurai    | Tamil Nadu
         101 | Arun       | Kumar     | arjun@gmail.com | Mumbai     | Tamil Nadu
         101 | Arun       | Kumar     | arjun@gmail.com | Mumbai     | Tamil Nadu
(4 rows)

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)

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    | neha@gmail.com | Chennai | Tamil Nadu
(1 row)

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)

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)

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    | neha@gmail.com
(1 row)

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)

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)

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)

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)