Queries.txt

USE task;
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
address VARCHAR(200) 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) NOT NULL,
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(order_id) REFERENCES orders(id),
FOREIGN KEY(product_id) REFERENCES products(id)
);

DESCRIBE order_items;

INSERT INTO customers (name, email, address)
VALUES
('Swetha Vanan', 'swetha@gmail.com', '29 Main st'),
('Ruban Prasath', 'ruban@gmail.com', '31 Cross st'),
('Emy Zen', 'emy@gmail.com', '22 Rail st');

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
(1, '2024-09-02', 200.00),
(2, '2024-09-14', 400.00),
(3, '2024-09-24', 600.00);

INSERT INTO products (name, price, description)
VALUES
('Product A', 50.00, 'Description of Product A'),
('Product B', 70.00, 'Description of Product B'),
('Product C', 90.00, 'Description of Product C');

INSERT INTO order_items (order_id, product_id, quantity)
VALUES
(1, 1, 2),
(2, 2, 1),
(3, 1, 1);

SELECT * FROM customers;
SELECT * FROM orders;
SELECT * FROM products;
SELECT * FROM order_items;


//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;

//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;


//Update the price of Product C to 45.00.

UPDATE products
SET price=45.00
WHERE id= 3;


//Add a new column discount to the products table.

ALTER TABLE products ADD discount DECIMAL(5,2) DEFAULT 0.00;


//Retrieve the top 3 products with the highest price.

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;   


//Get the names of customers who have ordered Product A.

SELECT DISTINCT customers.name
FROM customers
JOIN orders ON customer.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';


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

SELECT customers.name, orders.orders_date
FROM customers
JOIN orders ON customers.id=orders.customer_id;


//Retrieve the orders with a total amount greater than 150.00.

SELECT * FROM orders WHERE total_amount>150;


//Retrieve the average total of all orders.

SELECT AVG(total_amount) FROM orders;