Project 1

Q 1 WAQ to fetch the details of the customers in the order of their city

SELECT * FROM customer ORDER BY city;

Q 2 WAQ to alter the table product by renaming all the columns by preceding them with 'prod-' .

ALTER TABLE product RENAME COLUMN productID TO prod_ID;
ALTER TABLE product RENAME COLUMN product_name TO prod_name;
ALTER TABLE product RENAME COLUMN quant TO prod_quant;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN size TO prod_size;
ALTER TABLE product RENAME COLUMN Actual_cost TO prod_actual_cost;
ALTER TABLE product RENAME COLUMN Discount_Coupon TO prod_Disc_coupon;
ALTER TABLE product RENAME COLUMN Discount_Amt TO prod_Disc_amt;
ALTER TABLE product RENAME COLUMN Total_cost TO prod_total_cost;
ALTER TABLE product RENAME COLUMN customerID TO prod_customerID;
ALTER TABLE product RENAME COLUMN sellerID TO prod_SellerID;
ALTER TABLE product RENAME COLUMN brand TO prod_brand;
ALTER TABLE product RENAME COLUMN Discription TO prod_Discription;
ALTER TABLE product RENAME COLUMN tags TO prod_tags;
ALTER TABLE product RENAME COLUMN cust_reviws TO prod_cust_review;

Q 3 WAQ to fetch the details of customer who have purchased at least one product

SELECT c.customerID,c.customer_name,COUNT(o.orderID)TOTAL_ORDERS FROM
customer c INNER JOIN orders o ON c.customerID = o.customerID GROUP BY c.customerID HAVING COUNT(o.orderID)>=1;

Q 4 WAQ 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.customerID,c.customer_name,COUNT(o.orderID)TOTAL_ORDERS FROM
customer c INNER JOIN orders o ON c.customerID = o.customerID GROUP BY c.customerID HAVING COUNT(o.orderID)>1 AND c.customer_name LIKE "A%A";

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

SELECT c.customerID,c.customer_name,SUM(order_cost)"Purchased Amount" FROM
orders o LEFT JOIN customer c ON c.customerID = o.customerID WHERE o.order_status ="Delivered" AND order_cost>10000 AND c.customerID HAVING COUNT(o.orderID)>1;

Q 6. Write a simple procedure to run all the table creation statements. insert statements and alter statements at once.

CREATE TABLE admin_(
adminID INT,admin_Pass VARCHAR(255)UNIQUE NOT NULL,admin_name VARCHAR(255)NOT NULL,EmailID VARCHAR(255),
contact_No VARCHAR(255)
);

insert into admin (adminID,admin_Pass,admin_name,EmailID ,contact_No ),

values(1,8977789,"kiran","kiranboskhe1999",8605642068);

create table seller(
sellerID INT PRIMARY KEY NOT NULL,
seller_Pass VARCHAR(255),
seller_Name VARCHAR(255),
Email VARCHAR(255),
contact_No VARCHAR(10)
);

CREATE TABLE customer(
customerID INT PRIMARY KEY NOT NULL,
customer_Pass VARCHAR(100),
cust_type VARCHAR (100),
customer_name CHAR(50) ,
DOB DATE,
gender CHAR(50),
contact_no CHAR(10),
del_address VARCHAR(255),
city CHAR(50),
email VARCHAR(50),
current_order VARCHAR(255) NOT NULL,
prev_order VARCHAR(255) NOT NULL,
paymentsavedinfo CHAR(50)
);

CREATE TABLE product(
productID INT PRIMARY KEY NOT NULL,
product_name CHAR(50),
quant INT,
color CHAR(20),
size CHAR(20),
Actual_cost DECIMAL(7,2),
Discount_Coupon CHAR(20),
Discount_Amt DECIMAL(7,2),
Total_cost DECIMAL(7,2),
customerID INT,
FOREIGN KEY(customerID) REFERENCES customer(customerID),
sellerID INT,
FOREIGN KEY(sellerID) REFERENCES seller(sellerID),
brand CHAR(50),
Discription VARCHAR(100),
tags VARCHAR(255),
cust_reviws CHAR(20)
);

CREATE TABLE categories (
categoriesID INT NOT NULL,
categorieName VARCHAR(255),
productID INT,
FOREIGN KEY (productID)REFERENCES product(productID)
);

CREATE TABLE subcategories(
subcateID INT ,
subcateNames VARCHAR(100),
categoriesID INT,
productID INT REFERENCES categories(categoriesID),
FOREIGN KEY (productID) REFERENCES product(productID)
);

CREATE TABLE orders(
orderID INT ,
order_Date DATE,
order_status VARCHAR(255),
order_cost DECIMAL(7,2) REFERENCES product(Total_cost),
customerID INT,
FOREIGN KEY (customerID) REFERENCES customer(customerID),
productID INT ,
FOREIGN KEY (productID)REFERENCES product(productID)
);

CREATE TABLE payment(
paymentID INT PRIMARY KEY ,
paymentCategorie VARCHAR(100),
customerID INT REFERENCES customer(customerID),
orderID INT REFERENCES orders(orderID)
);

CREATE TABLE shipment(
shipmentID INT ,
shipmentDate DATE,
shipment_city VARCHAR(100),
invoice VARCHAR(100),
orderID INT REFERENCES orders(orderID),
paymentID INT REFERENCES payment(paymentID),
customerID INT REFERENCES customers(customerID)
);

ALTER TABLE product RENAME COLUMN productID TO prod_ID;
ALTER TABLE product RENAME COLUMN product_name TO prod_name;
ALTER TABLE product RENAME COLUMN quant TO prod_quant;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN size TO prod_size;
ALTER TABLE product RENAME COLUMN Actual_cost TO prod_actual_cost;
ALTER TABLE product RENAME COLUMN Discount_Coupon TO prod_Disc_coupon;
ALTER TABLE product RENAME COLUMN Discount_Amt TO prod_Disc_amt;
ALTER TABLE product RENAME COLUMN Total_cost TO prod_total_cost;
ALTER TABLE product RENAME COLUMN customerID TO prod_customerID;
ALTER TABLE product RENAME COLUMN sellerID TO prod_SellerID;
ALTER TABLE product RENAME COLUMN brand TO prod_brand;
ALTER TABLE product RENAME COLUMN Discription TO prod_Discription;
ALTER TABLE product RENAME COLUMN tags TO prod_tags;
ALTER TABLE product RENAME COLUMN cust_reviws TO prod_cust_review;