Project 1

TASk 1:

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(20),
address VARCHAR(200)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATETIME,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

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

CREATE TABLE reviews (
review_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
rating INT,
comment VARCHAR(500),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERTING DATA:

INSERT INTO customers (first_name, last_name, email, phone, address, city, state, country, zip_code)
VALUES ('John', 'Doe', '[email protected]', '555-1234', '123 Main St', 'Anytown', 'CA', 'USA', '12345'),
('Jane', 'Doe', '[email protected]', '555-5678', '456 Market St', 'Anytown', 'CA', 'USA', '12345');

INSERT INTO products (name, description, price, image_url)
VALUES ('Product 1', 'This is product 1', 10.00, 'https://example.com/product1.jpg'),
('Product 2', 'This is product 2', 15.00, 'https://example.com/product2.jpg');

INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (1, '2022-01-01', 20.00, 'Pending'),
(2, '2022-01-02', 30.00, 'Completed');

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (1, 1, 2, 20.00),
(2, 1, 1, 10.00),
(2, 2, 2, 30.00);

TASK 2:

1. Users

• id (int, primary key)
• name (varchar)
• email (varchar, unique)
• password (varchar)
• address (varchar)
• city (varchar)
• state (varchar)
• country (varchar)
• postal_code (varchar)
• phone (varchar)
• created_at (timestamp)

2. Products

• id (int, primary key)
• name (varchar)
• description (text)
• price (decimal)
• image_url (varchar)
• created_at (timestamp)

3. Categories

• id (int, primary key)
• name (varchar)
• description (text)
• created_at (timestamp)

4. Product Categories

• id (int, primary key)
• product_id (int, foreign key to Products)
• category_id (int, foreign key to Categories)

5. Orders

• id (int, primary key)
• user_id (int, foreign key to Users)
• status (varchar)
• total_amount (decimal)
• created_at (timestamp)

6. Order Items

• id (int, primary key)
• order_id (int, foreign key to Orders)
• product_id (int, foreign key to Products)
• quantity (int)
• price (decimal)

TASK 3:

1.Write a query to fetch the details of the customer in the order of their city

SELECT customer_id, customer_name, city, other_columns
FROM customers
ORDER BY city;

2.write a query to alter the table product by remaining all the columns by preceding them with 'prod'

ALTER TABLE product
RENAME COLUMN column_name TO prod_column_name;

3.write a query to fetch the details of customer who have purchased at least one product

SELECT c.customer_id, c.customer_name, c.other_columns
FROM customers c
WHERE EXISTS (
SELECT *
FROM orders o
WHERE o.customer_id = c.customer_id
);

SELECT DISTINCT c.customer_id, c.customer_name, c.other_columns
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

4.write a query to fetch the details of the customer who have name starting from A and ending from A and has purchased more than one item

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS num_of_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_name LIKE 'A%a'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 1;

5.WAQ to all the information about the order which are already delivered and had cost more than 10000 and also has been purchased by more than 1 customer

SELECT o.order_id, o.product_id, p.product_name, o.order_date, o.delivery_date, o.order_cost, COUNT(DISTINCT o.customer_id) AS num_of_customers
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.delivery_date IS NOT NULL AND o.order_cost > 10000
GROUP BY o.order_id, o.product_id, p.product_name, o.order_date, o.delivery_date, o.order_cost
HAVING COUNT(DISTINCT o.customer_id) > 1;

6.write a simple procedure to run all the table creation statements, insert statement and alter statement at once

CREATE PROCEDURE RunAllStatements
AS
BEGIN
-- Create tables
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
...
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
...
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
...
);

-- Insert data into tables
INSERT INTO Customers (CustomerID, CustomerName, ...)
VALUES (1, 'John Doe', ...);
INSERT INTO Customers (CustomerID, CustomerName, ...)
VALUES (2, 'Jane Smith', ...);
...
INSERT INTO Products (ProductID, ProductName, ...)
VALUES (1, 'Product 1', ...);
INSERT INTO Products (ProductID, ProductName, ...)
VALUES (2, 'Product 2', ...);
...
INSERT INTO Orders (OrderID, CustomerID, ...)
VALUES (1, 1, ...);
INSERT INTO Orders (OrderID, CustomerID, ...)
VALUES (2, 2, ...);
...

-- Alter tables
ALTER TABLE Customers
ADD Email VARCHAR(50);
ALTER TABLE Orders
ADD OrderDate DATE;
...
END;