Queries.txt

mysql> CREATE DATABASE ecommerce;
Query OK, 1 row affected (9.21 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ecommerce          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (1.27 sec)

1.customer table create

mysql>  CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY ,name VARCHAR(255) NOT NULL,email VARCHAR(255) UNIQUE,address VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (4.83 sec)

mysql> describe customers;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   |     | NULL    |                |
| email   | varchar(255) | NO   | UNI | NULL    |                |
| address | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

2.Orders Table create 

mysql>  CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,FOREIGN KEY (customer_id) REFERENCES customers(id) ,order_date INT ,total_amount INT );
Query OK, 0 rows affected (15.83 sec)

mysql> describe orders;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| customer_id  | int         | YES  | MUL | NULL    |                |
| order_date   | varchar(50) | NO   |     | NULL    |                |
| total_amount | int         | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.product table create 

mysql> CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY ,name VARCHAR(255) NOT NULL ,price INT ,description VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.83 sec)

mysql> describe products;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | NO   |     | NULL    |                |
| price       | int          | YES  |     | NULL    |                |
| description | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

INSERT DATA INTO TABLES ;

1. CUSTOMERS :

mysql> INSERT INTO customers (name,email,address) VALUES ('ajith','ajith@gmail.com','chennai'),('vijay','vijay@gmail.com','chennai'),('vikram','vikram@gmail.com','madurai'),('surya','surya@gmail.com','coimbatore'),('dhanush','dhanush@gmail.com','trichy'),('kamal','kamal@gmail.com','delhi');
Query OK, 6 rows affected (0.44 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from customers;
+----+---------+-------------------+------------+
| id | name    | email             | address    |
+----+---------+-------------------+------------+
|  1 | ajith   | ajith@gmail.com   | chennai    |
|  2 | vijay   | vijay@gmail.com   | chennai    |
|  3 | vikram  | vikram@gmail.com  | madurai    |
|  4 | surya   | surya@gmail.com   | coimbatore |
|  5 | dhanush | dhanush@gmail.com | trichy     |
|  6 | kamal   | kamal@gmail.com   | delhi      |
+----+---------+-------------------+------------+
6 rows in set (0.00 sec)

2.ORDERS : 

mysql> INSERT INTO orders (customer_id,order_date,total_amount) VALUES (1,'2025-05-13',1000);
Query OK, 1 row affected (0.12 sec)

INSERT INTO orders (customer_id,order_date,total_amount) VALUES (2,'2025-05-14',1000), (3,'2025-05-15',500), (4,'2025-05-16',200), (5,'2025-05-17',45), (6,'2025-05-18',100), (1,'2025-05-19',500), (2,'2025-05-20',200),(3,'2025-05-21',150),(5,'2025-05-22',100), (6,'2025-05-23',45),(1,'2025-05-24',1000),(2,'2025-05-25',200),(3,'2025-05-26',100),(4,'2025-05-27',500),(6,'2025-05-28',1000),(1,'2025-05-29',200),(2,'2025-05-30',45),(3,'2025-05-31',100),(4,'2025-06-01',150),(5,'2025-06-02',1000),(6,'2025-06-03',150),(1,'2025-06-04',1000),(2,'2025-06-05',500),(3,'2025-06-06',200),(4,'2025-06-07',45),(5,'2025-06-08',100),(6,'2025-06-09',150),(1,'2025-06-10',500),(2,'2025-06-11',1000),(3,'2025-06-12',200),(4,'2025-06-13',1000),(5,'2025-06-14',500),(6,'2025-06-15',1000);
Query OK, 33 rows affected (0.44 sec)
Records: 33  Duplicates: 0  Warnings: 0

mysql> select * from orders;
+-----+-------------+------------+--------------+
| id  | customer_id | order_date | total_amount |
+-----+-------------+------------+--------------+    
| 103 |           2 | 2025-05-14 |         1000 |
| 104 |           3 | 2025-05-15 |          500 |
| 105 |           4 | 2025-05-16 |          200 |
| 106 |           5 | 2025-05-17 |           45 |
| 107 |           6 | 2025-05-18 |          100 |
| 108 |           1 | 2025-05-19 |          500 |
| 109 |           2 | 2025-05-20 |          200 |
| 110 |           3 | 2025-05-21 |          150 |
| 111 |           5 | 2025-05-22 |          100 |
| 112 |           6 | 2025-05-23 |           45 |
| 113 |           1 | 2025-05-24 |         1000 |
| 114 |           2 | 2025-05-25 |          200 |
| 115 |           3 | 2025-05-26 |          100 |
| 116 |           4 | 2025-05-27 |          500 |
| 117 |           6 | 2025-05-28 |         1000 |
| 118 |           1 | 2025-05-29 |          200 |
| 119 |           2 | 2025-05-30 |           45 |
| 120 |           3 | 2025-05-31 |          100 |
| 121 |           4 | 2025-06-01 |          150 |
| 122 |           5 | 2025-06-02 |         1000 |
| 123 |           6 | 2025-06-03 |          150 |
| 124 |           1 | 2025-06-04 |         1000 |
| 125 |           2 | 2025-06-05 |          500 |
| 126 |           3 | 2025-06-06 |          200 |
| 127 |           4 | 2025-06-07 |           45 |
| 128 |           5 | 2025-06-08 |          100 |
| 129 |           6 | 2025-06-09 |          150 |
| 130 |           1 | 2025-06-10 |          500 |
| 131 |           2 | 2025-06-11 |         1000 |
| 132 |           3 | 2025-06-12 |          200 |
| 133 |           4 | 2025-06-13 |         1000 |
| 134 |           5 | 2025-06-14 |          500 |
| 135 |           6 | 2025-06-15 |         1000 |
+-----+-------------+------------+--------------+
33 rows in set (0.00 sec)

3.PRODUCTS:

mysql> INSERT INTO products (name,price,description) VALUES ('A',150,'airpods'),('B',100,'battery'),('C','50','chain'),('D',200,'dice'),('E',500,'rice'),('F',1000,'toy');
Query OK, 6 rows affected (0.44 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from products;
+----+------+-------+-------------+
| id | name | price | description |
+----+------+-------+-------------+
|  1 | A    |   150 | airpods     |
|  2 | B    |   100 | battery     |
|  3 | C    |    50 | chain       |
|  4 | D    |   200 | dice        |
|  5 | E    |   500 | rice        |
|  6 | F    |  1000 | toy         |
+----+------+-------+-------------+
6 rows in set (0.02 sec)


Question :- 1 

Retrieve all customers who have placed an order in the last 30 days.

Answer :
mysql> SELECT customers.id,customers.name,orders.order_date FROM customers INNER JOIN orders ON orders.customer_id=customers.id order by order_date desc limit 30;
+----+---------+------------+
| id | name    | order_date |
+----+---------+------------+
|  6 | kamal   | 2025-06-15 |
|  5 | dhanush | 2025-06-14 |
|  4 | surya   | 2025-06-13 |
|  3 | vikram  | 2025-06-12 |
|  2 | vijay   | 2025-06-11 |
|  1 | ajith   | 2025-06-10 |
|  6 | kamal   | 2025-06-09 |
|  5 | dhanush | 2025-06-08 |
|  4 | surya   | 2025-06-07 |
|  3 | vikram  | 2025-06-06 |
|  2 | vijay   | 2025-06-05 |
|  1 | ajith   | 2025-06-04 |
|  6 | kamal   | 2025-06-03 |
|  5 | dhanush | 2025-06-02 |
|  4 | surya   | 2025-06-01 |
|  3 | vikram  | 2025-05-31 |
|  2 | vijay   | 2025-05-30 |
|  1 | ajith   | 2025-05-29 |
|  6 | kamal   | 2025-05-28 |
|  4 | surya   | 2025-05-27 |
|  3 | vikram  | 2025-05-26 |
|  2 | vijay   | 2025-05-25 |
|  1 | ajith   | 2025-05-24 |
|  6 | kamal   | 2025-05-23 |
|  5 | dhanush | 2025-05-22 |
|  3 | vikram  | 2025-05-21 |
|  2 | vijay   | 2025-05-20 |
|  1 | ajith   | 2025-05-19 |
|  6 | kamal   | 2025-05-18 |
|  5 | dhanush | 2025-05-17 |
+----+---------+------------+
30 rows in set (0.00 sec)

Question :- 2

Get the total amount of all orders placed by each customer.

Answer :

mysql> SELECT customers.id,customers.name,count(*) as order_count,SUM(total_amount) as total_amount from customers INNER JOIN orders ON orders.customer_id=customers.id group by id;
+----+---------+-------------+--------------+
| id | name    | order_count | total_amount |
+----+---------+-------------+--------------+
|  1 | ajith   |           5 |         3200 |
|  2 | vijay   |           6 |         2945 |
|  3 | vikram  |           6 |         1250 |
|  4 | surya   |           5 |         1895 |
|  5 | dhanush |           5 |         1745 |
|  6 | kamal   |           6 |         2445 |
+----+---------+-------------+--------------+
6 rows in set (0.00 sec)

Question :- 3

Update the price of Product C to 45.00.

Answer :

mysql> UPDATE products SET price = 45 where id=3;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from products;
+----+------+-------+-------------+
| id | name | price | description |
+----+------+-------+-------------+
|  1 | A    |   150 | airpods     |
|  2 | B    |   100 | battery     |
|  3 | C    |    45 | chain       |
|  4 | D    |   200 | dice        |
|  5 | E    |   500 | rice        |
|  6 | F    |  1000 | toy         |
+----+------+-------+-------------+
6 rows in set (0.00 sec)

Question :- 4

Add a new column discount to the products table.

Answer :

mysql> ALTER TABLE products ADD COLUMN discount INT;
Query OK, 0 rows affected (2.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM products ;
+----+------+-------+-------------+----------+
| id | name | price | description | discount |
+----+------+-------+-------------+----------+
|  1 | A    |   150 | airpods     |     NULL |
|  2 | B    |   100 | battery     |     NULL |
|  3 | C    |    45 | chain       |     NULL |
|  4 | D    |   200 | dice        |     NULL |
|  5 | E    |   500 | rice        |     NULL |
|  6 | F    |  1000 | toy         |     NULL |
+----+------+-------+-------------+----------+
6 rows in set (0.04 sec)

after updating discount initial 0 

+----+------+-------+-------------+----------+
| id | name | price | description | discount |
+----+------+-------+-------------+----------+
|  1 | A    |   150 | airpods     |        0 |
|  2 | B    |   100 | battery     |        0 |
|  3 | C    |    45 | chain       |        0 |
|  4 | D    |   200 | dice        |        0 |
|  5 | E    |   500 | rice        |        0 |
|  6 | F    |  1000 | toy         |        0 |
+----+------+-------+-------------+----------+
6 rows in set (0.00 sec)

Question :- 5

Retrieve the top 3 products with the highest price.

Answer :

mysql> SELECT id,name,price FROM products ORDER BY price DESC LIMIT 3;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  6 | F    |  1000 |
|  5 | E    |   500 |
|  4 | D    |   200 |
+----+------+-------+
3 rows in set (0.01 sec)

Question :- 6

Get the names of customers who have ordered Product A.

Answer :


mysql> SELECT DISTINCT customers.id,customers.name,products.name FROM orders INNER JOIN order_items ON order_items.order_id = orders.id INNER JOIN customers ON orders.customer_id=customers.id INNER JOIN products ON order_items.product_id=products.id WHERE product_id=1;
+----+--------+------+
| id | name   | name |
+----+--------+------+
|  3 | vikram | A    |
|  4 | surya  | A    |
|  6 | kamal  | A    |
+----+--------+------+
3 rows in set (0.00 sec)


Question :- 7 

Join the orders and customers tables to retrieve the customer's name and order date for each order.

Answer :

mysql> select orders.id, customers.name,orders.order_date from customers inner join orders on orders.customer_id=customers.id order by orders.id ;
+-----+---------+------------+
| id  | name    | order_date |
+-----+---------+------------+
| 103 | vijay   | 2025-05-14 |
| 104 | vikram  | 2025-05-15 |
| 105 | surya   | 2025-05-16 |
| 106 | dhanush | 2025-05-17 |
| 107 | kamal   | 2025-05-18 |
| 108 | ajith   | 2025-05-19 |
| 109 | vijay   | 2025-05-20 |
| 110 | vikram  | 2025-05-21 |
| 111 | dhanush | 2025-05-22 |
| 112 | kamal   | 2025-05-23 |
| 113 | ajith   | 2025-05-24 |
| 114 | vijay   | 2025-05-25 |
| 115 | vikram  | 2025-05-26 |
| 116 | surya   | 2025-05-27 |
| 117 | kamal   | 2025-05-28 |
| 118 | ajith   | 2025-05-29 |
| 119 | vijay   | 2025-05-30 |
| 120 | vikram  | 2025-05-31 |
| 121 | surya   | 2025-06-01 |
| 122 | dhanush | 2025-06-02 |
| 123 | kamal   | 2025-06-03 |
| 124 | ajith   | 2025-06-04 |
| 125 | vijay   | 2025-06-05 |
| 126 | vikram  | 2025-06-06 |
| 127 | surya   | 2025-06-07 |
| 128 | dhanush | 2025-06-08 |
| 129 | kamal   | 2025-06-09 |
| 130 | ajith   | 2025-06-10 |
| 131 | vijay   | 2025-06-11 |
| 132 | vikram  | 2025-06-12 |
| 133 | surya   | 2025-06-13 |
| 134 | dhanush | 2025-06-14 |
| 135 | kamal   | 2025-06-15 |
+-----+---------+------------+
33 rows in set (0.04 sec)

Question :- 8

Retrieve the orders with a total amount greater than 150.00.

Answer :

mysql> select * from orders where total_amount>150 ;
+-----+-------------+------------+--------------+
| id  | customer_id | order_date | total_amount |
+-----+-------------+------------+--------------+
| 103 |           2 | 2025-05-14 |         1000 |
| 104 |           3 | 2025-05-15 |          500 |
| 105 |           4 | 2025-05-16 |          200 |
| 108 |           1 | 2025-05-19 |          500 |
| 109 |           2 | 2025-05-20 |          200 |
| 113 |           1 | 2025-05-24 |         1000 |
| 114 |           2 | 2025-05-25 |          200 |
| 116 |           4 | 2025-05-27 |          500 |
| 117 |           6 | 2025-05-28 |         1000 |
| 118 |           1 | 2025-05-29 |          200 |
| 122 |           5 | 2025-06-02 |         1000 |
| 124 |           1 | 2025-06-04 |         1000 |
| 125 |           2 | 2025-06-05 |          500 |
| 126 |           3 | 2025-06-06 |          200 |
| 130 |           1 | 2025-06-10 |          500 |
| 131 |           2 | 2025-06-11 |         1000 |
| 132 |           3 | 2025-06-12 |          200 |
| 133 |           4 | 2025-06-13 |         1000 |
| 134 |           5 | 2025-06-14 |          500 |
| 135 |           6 | 2025-06-15 |         1000 |
+-----+-------------+------------+--------------+
20 rows in set (0.17 sec)

Question :- 9

Normalize the database by creating a separate table for order items and updating the orders table to reference the order_items table.

Answer :

mysql> create table order_items ( order_id INT ,product_id INT, constraint order_id foreign key (order_id) references orders (id), constraint product_id foreign key (product_id) references products (id));
Query OK, 0 rows affected (1.96 sec)

mysql> describe order_items;
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| order_id   | int  | YES  | MUL | NULL    |       |
| product_id | int  | YES  | MUL | NULL    |       |
+------------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Question :- 10

Retrieve the average total of all orders.

Answer :

mysql> select SUM(total_amount) as total_avg_amount from orders;
+------------------+
| total_avg_amount |
+------------------+
|            13480 |
+------------------+
1 row in set (0.05 sec)