Project 1

Ans-

create database project1;
use project1;

CREATE TABLE Customers (
cust_id INT PRIMARY KEY,
cust_fname VARCHAR(50),
cust_lname VARCHAR(50),
cust_address VARCHAR(255)
);

CREATE TABLE Products (
pro_id INT PRIMARY KEY,
pro_name VARCHAR(255),
pro_price DECIMAL(10, 2),
pro_quantity INT
);

CREATE TABLE Cart (
cust_id INT,
pro_id INT,
pro_price int,
pro_quantity INT,
FOREIGN KEY (cust_id) REFERENCES Customers(cust_id),
FOREIGN KEY (pro_id) REFERENCES Products(pro_id)
);

create table orders(
ord_id int primary key,
cust_id int,
pro_id int ,
ord_total_amount int ,
ord_address varchar(255),
FOREIGN KEY (cust_id) REFERENCES Customers(cust_id),
FOREIGN KEY (pro_id) REFERENCES Products(pro_id)
);

create table seller(
ord_id int,
ord_date int,
cust_address varchar(255),
FOREIGN KEY (ord_id) REFERENCES orders(ord_id)
);

INSERT INTO Customers (cust_id, cust_fname, cust_lname, cust_address)
VALUES (1, 'John', 'Doe', '123 Main St'),
(2, 'Jane', 'Doe', '456 Elm St'),
(3, 'Bob', 'Smith', '789 Oak St');

-- Insert some products
INSERT INTO Products (pro_id, pro_name, pro_price, pro_quantity)
VALUES (1, 'Product A', 10.99, 50),
(2, 'Product B', 5.99, 100),
(3, 'Product C', 8.99, 75);

-- Add some items to the cart
INSERT INTO Cart (cust_id, pro_id, pro_price, pro_quantity)
VALUES (1, 1, 10.99, 2),
(1, 2, 5.99, 1),
(2, 1, 10.99, 1),
(2, 2, 5.99, 2),
(3, 3, 8.99, 3);
INSERT INTO Orders (ord_id, cust_id, pro_id, ord_total_amount, ord_address)
VALUES (1, 1, 1, 21.98, '123 Main St'),
(2, 2, 2, 17.97, '456 Elm St'),
(3, 1, 3, 26.97, '123 Main St'),
(4, 3, 1, 10.99, '789 Oak St'),
(5, 2, 3, 26.97, '456 Elm St');

SELECT cust_id, cust_fname, cust_lname, cust_address
FROM Customers
ORDER BY cust_address;

ALTER TABLE Products
RENAME COLUMN pro_id TO prod_id,
RENAME COLUMN pro_name TO prod_name,
RENAME COLUMN pro_price TO prod_price,
RENAME COLUMN pro_quantity TO prod_quantity;

SELECT c.*
FROM Customers c
INNER JOIN Cart ct ON c.cust_id = ct.cust_id
GROUP BY c.cust_id
HAVING COUNT(DISTINCT ct.pro_id) >= 1;

SELECT c.*
FROM Customers c
INNER JOIN Cart ct ON c.cust_id = ct.cust_id
WHERE c.cust_fname LIKE 'A%' AND c.cust_lname LIKE '%A'
GROUP BY c.cust_id
HAVING COUNT(DISTINCT ct.pro_id) > 1;

SELECT o.*
FROM Orders o
INNER JOIN Cart ct ON o.pro_id = ct.pro_id
WHERE o.ord_status = 'delivered' AND o.ord_total_amount > 10000
GROUP BY o.ord_id
HAVING COUNT(DISTINCT ct.cust_id) > 1;