Queries.txt

show databases;
create database ecommerce;
use ecommerce;

create table customers(
id INT auto_increment PRIMARY KEY,
name varchar(100) NOT NULL,
email varchar(100) UNIQUE,
address varchar(255) NOT NULL 
);
DESCRIBE customers;

create table orders(
id INT auto_increment PRIMARY KEY,
customer_id INT  ,
order_date DATE,
total_amount DECIMAL(10,2),
FOREIGN KEY(customer_id) REFERENCES customers(id)
);
DESCRIBE orders;

create table products(
id INT auto_increment PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
description TEXT
);
DESCRIBE products;

CREATE TABLE order_items(
id INT auto_increment PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY(product_id) REFERENCES orders(id),
FOREIGN KEY(product_id) REFERENCES products(id)
);
DESCRIBE order_items;

INSERT INTO customers(name,email,address)
VALUES
('leo','leo@gmail.com','chennai'),
('rio','rio@gmail.com','trichy'),
('joe','joe@gmail.com','madurai');

INSERT INTO products(name,price,description)
VALUES
('Product A','11.00','A has balls'),
('Product B','22.00','B has bat'),
('Product C','33.00','C has box');

INSERT INTO orders(customer_id,order_date,total_amount)
VALUES
(1,'2024-09-25',120.00),
(2,'2024-09-20',90.00),
(3,'2024-09-05',100.00);

INSERT INTO order_items(order_id,product_id,quantity)
VALUES
(1,1,1),
(2,2,1),
(3,3,1);
 
SELECT * from customers;
SELECT * from orders;
SELECT * from products; 
SELECT * from order_items;

# 1.Retrieve all customers who have placed an order in the last 30 days.
SELECT DISTINCT customers.name 
FROM customers	 
JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_date >= curdate() - interval 30 DAY;

# 2.Get the total amount of all orders placed by each customer.
SELECT customers.name,SUM(orders.total_amount) AS total_spent
FROM customers 
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

SET SQL_SAFE_UPDATES = 1; #used to enable or disable safe mode in mysql

# 3. Update the price of Product C to 45.00.
UPDATE products
set price = 45.00 
WHERE name = 'Product C';
SELECT * FROM products;

# 4. Add a new column discount to the products table.
ALTER TABLE products
ADD discounts DECIMAL(5,2) DEFAULT 0.00;
SELECT * FROM products;

# 5. Retrieve the top 3 products with the highest price.
SELECT name,price FROM products ORDER BY price DESC LIMIT 3;

# 6. Get the names of customers who have ordered Product A.
SELECT DISTINCT customers.name
FROM customers 
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id
WHERE products.name = 'Product A' ;

# 7. Join the orders and customers tables to retrieve the customer's name and order date for each order. 
SELECT customers.name,orders.order_date
FROM customers
JOIN orders ON customers.id = orders.customer_id;

# 8. Retrieve the orders with a total amount greater than 150.00.
SELECT * FROM orders WHERE total_amount>150;

# 9. Retrieve the average total of all orders.
SELECT avg(total_amount) FROM orders;

# Normalize the database by creating a separate table for order items and updating the orders table to reference the order_items table.
added screenshot my DB design