Database Project 1

e_commerce

1.SELECT * FROM customers ORDER BY city;

2.ALTER TABLE product
RENAME COLUMN column1 TO prod_column1,
RENAME COLUMN column2 TO prod_column2,
qRENAME COLUMN column TO prod_column;

3.SELECT * FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

4.SELECT c.* FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.customer_name LIKE 'A%A'
GROUP BY c.customer_id
HAVING COUNT(DISTINCT oi.product_id) > 1;

5.CREATE PROCEDURE RunAllStatements()
BEGIN
-- Table creation statements
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(100)
);

CREATE TABLE product (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
status VARCHAR(20),
cost DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insert statements
INSERT INTO customers (customer_id, customer_name, city)
VALUES (1, 'abc123', 'New York'),
(2, 'abc456', 'Los Angeles'),
(3, 'abc789', 'London');

INSERT INTO product (product_id, product_name, price)
VALUES ('pro1', 'Product 1', 10.99),
('pro2', 'Product 2', 20.99),
('pro3', 'Product 3', 15.99);

INSERT INTO orders (order_id, customer_id, status, cost)
VALUES (1, 1, 'delivered', 500.00),
(2, 2, 'pending', 10000.00),
(3, 1, 'delivered', 1500.00);

-- Alter statements
ALTER TABLE product
RENAME COLUMN product_id TO prod_product_id,
RENAME COLUMN product_name TO prod_product_name,
RENAME COLUMN price TO prod_price;
END;