Project 1
Dear sir/madam
I have complited project-1 kindly check.
ANS.
SELECT * FROM ecommerce.seller;
INSERT INTO seller VALUES
('rr455k', 'Arjun reddy', 92945554, 'cth8345n', 'Accessories'),
('mjhkj24', 'Veera', 752665656, 'yut64t02', 'Laptops'),
('h-56jd', 'Shah ruhk khan', 764595847, 'rea6435j', 'fashion'),
('R211_MH', 'Priya', 71349505, 'kha6590nd', 'Monitor'),
('R223_KR', 'Harini', 61467865, 'asi420f3e', 'Phones');
CREATE TABLE product(
product_id VARCHAR(100) PRIMARY KEY,
NAME VARCHAR(255),
Color VARCHAR(255),
seller_id VARCHAR(100),
Sizes INT,
Quantity INT,
Cost INT,
Discount DECIMAL(10,2),
Place_for_delevery VARCHAR(255),
Calculation_of_cost INT(10),
isFeatured VARCHAR(255),
Rating INT,
reviewCountry INT,
isOnSale VARCHAR(255),
category_Id VARCHAR(255),
brand VARCHAR(100),
description_ VARCHAR(255),
tags VARCHAR(100),
related_Product VARCHAR(255),
categoryName VARCHAR(255),
seller_name CHAR(50),
questions VARCHAR(255),
image TEXT,
FOREIGN KEY (seller_id) REFERENCES seller(seller_id)
);
SELECT * FROM ecommerce.product;
INSERT INTO product VALUES('rr455k', 'Cooling glass','blue-white', 'rr455k', 14, 2, 1000, 200, 'Chennai',1700,
'yes', 5, 4, 'yes', 'cat_291', 'Rebion', 'sunlight glasses', 'goggles', 'yellow glasses','Fashion','Arjunreddy','questions ','image' ),
('mjhkj24', 'T_Shirt', 'Yellow', 'h-56jd', 42, 2, 3000, 200, 'Nanded', 1700,'yes', 4, 8, 'yes', 'Fashion',
'being human', 'T_shirt with cap', 'Trouser', 'being humans jeans','Fashion','Shah ruhk khan','questions ','image '),
('P_1', 'lenovo', 'black', 'mjhkj24', 14, 1, 150000, 7000, 'pune', 112000,'yes', 5, 40, 'yes', 'C_301',
'Apple', 'Product is very branded and good', 'Laptop', 'Ideapad','Laptops','Veera','questions ','image '),
('P_3', 'Vivo_30', 'silver', 'R223_KR', 20, 2, 40000, 2000, 'Latur', 38000,'Welll feat', 7, 20, 'well developed', 'Phones',
'Vivo', ' good phone camera 128 ram 12 gb', 'vivo', 'vivo-27','Phones','Harini','questions ','image '),
('P_490g', 'headphone', 'white', 'rr455k', 0, 3, 700, 110, 'pune', 590,'yes', 5, 5, 'yes', 'cat_285',
'realme', 'black headphones', 'realme', 'realme ipods','Accessories','Arjunreddy','questions ','image ');
CREATE TABLE Cart(
cart_ID INT PRIMARY KEY ,
Item_Quantity INT(11),
Total_Cost INT(11)
);
INSERT INTO Cart VALUES(101, 20, 3000), (102, 10, 1200), (103, 2, 200), (104, 5, 2000), (105, 10, 70000);
CREATE TABLE customer(
cust_id VARCHAR(10) PRIMARY KEY ,
cust_name CHAR(50) NOT NULL,
cust_password VARCHAR(255)NOT NULL,
cust_gender CHAR(5)NOT NULL,
cust_dob DATETIME NOT NULL,
cust_email CHAR(50)NOT NULL,
cust_city CHAR(50)NOT NULL,
cust_Delivery_address VARCHAR(255)NOT NULL,
cust_contact INT(12) NOT NULL,
current_order VARCHAR(255) NOT NULL,
prev_orders VARCHAR(255) NOT NULL,
cart_ID INT DEFAULT NULL,
paymentinfosaved CHAR(30),
coupon_id INT REFERENCES Coupon(coupon_id),
FOREIGN KEY (cart_ID) REFERENCES cart(cart_ID)
);
INSERT INTO customer VALUES
('00124iP', 'Roman reigns','reigns123','M','1996-12-21 13:24:31', '[email protected]','Delhi', 'dadar', 784859499, 'Dress', 'lehanga',105,'yes',12361),
('213B12kr', 'Partha', 'Partha0123', 'M', '1992-11-03 13:19:36','[email protected]', 'Bangalore', 'bangalore', 928839576, 'Monitor', 'Shirts',101,'yes',245678),
('Y1235', 'Haritha','haritha123','F','1983-10-02 08:10:53', '[email protected]','Chennai', 'Motthiene', 456321789, 'Book', 'Map',103,'yes',614198);
CREATE TABLE cart_item(
cart_ID INT ,
product_id VARCHAR(100),
dateAdded DATE,
purchase VARCHAR(255),
Item_quantity INT(11),
FOREIGN KEY (cart_ID) REFERENCES cart(cart_ID),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
INSERT INTO cart_item VALUES
(101, 'rr455k', '2022-11-07', 'NO', 7),
(102, 'mjhkj24', '2022-11-22', 'Yes', 7),
(103, 'P_1', '2022-10-19', 'Yes', 3),
(104, 'P_3', '2022-11-03', 'Yes', 1),
(105, 'P_490g', '2022-11-01', 'NO', 2);
CREATE TABLE Category(
category_Id VARCHAR(100),
categoryName VARCHAR(255),
product_id VARCHAR(100),
category VARCHAR(100),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
INSERT INTO Category VALUES
('cat_285', 'Accessories', 'P_1', 'Mic'),
('cat_288', 'Laptops', 'P_1', 'Lenovo'),
('cat_291', 'Accessories', 'P_3', 'Phonescase'),
('cat_299', 'Phones', 'P_3', 'Redmi'),
('cat_301', 'Monitor', 'P_3', 'Sony');
CREATE TABLE orders(
order_id VARCHAR(255) PRIMARY KEY,
status_ VARCHAR(100),
order_date DATE,
Dilevery_Date DATE,
cust_id VARCHAR(100),
FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);
INSERT INTO orders VALUES
('02341mrk', 'shipping', '2022-11-05', '2022-11-08', '00124iP'),
('09hjwe3', 'delivered', '2022-11-01', '2022-11-05', '213B12kr'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', 'Y1235');
CREATE TABLE payment(
payment_id VARCHAR (100)PRIMARY KEY,
payment_amount INT,
payment_date DATE,
payment_type CHAR(50),
payment_status CHAR(60),
cust_id VARCHAR(100)
);
INSERT INTO payment VALUES(1 , 20000 , '2019-08-11', 'UPI' , 'successful' , '0023pkr'),
(2 , 40000 , '2019-04-15', 'credit card' , 'successful' , 'B12kr'),
(3 , 1000 , '2020-06-04', 'GPAY' , 'successful' , 'G_235'),
(4 , 900 , '2021-06-25', 'Debit card' , 'successful' , 'Tj402'),
(5 , 2000 , '2021-08-23', 'Phone pay' , 'successful' , 'V_Patil');
CREATE TABLE Coupon(
coupon_id INT PRIMARY KEY,
percentage_discount INT NOT NULL
);
INSERT INTO Coupon VALUES(123456,20),(123457,38),(123458,50);
CREATE TABLE reviewes(
rev_id INT PRIMARY KEY,
product_id VARCHAR(100) NOT NULL,
cust_id VARCHAR(10) NOT NULL REFERENCES Customer(cust_id),
rating INT NOT NULL,
average_rating INT NOT NULL,
total_rating INT NOT NULL,
review_country VARCHAR(255) NOT NULL,
FOREIGN KEY(product_id) REFERENCES Product(product_id)
);
INSERT INTO reviewes VALUES(2012,'rr455k','V_Patil',5,4,20,"India");
CREATE TABLE Wishlist(
wish_id INT PRIMARY KEY,
productname VARCHAR(255),
product_price INT NOT NULL,
product_id VARCHAR(100) NOT NULL REFERENCES product(product_id),
cust_id VARCHAR (10) NOT NULL REFERENCES customer(cust_id)
);
INSERT INTO wishlist VALUES(7001,"Samsung mobile",30000,'P_10','SFDASADSA2');
CREATE TABLE replacement (
rep_id INT PRIMARY KEY,
order_id VARCHAR(255) NOT NULL,
productname VARCHAR(255) NOT NULL,
order_status VARCHAR(255) NOT NULL,
cust_id VARCHAR(10) NOT NULL REFERENCES customer(cust_id),
FOREIGN KEY(order_id) REFERENCES orders(order_id)
);
INSERT INTO replacement VALUES(2001,'09hjwe3',"ASUS mobile",'returned','0023pkr');
CREATE TABLE shipmentinvoices(
invoice_id INT PRIMARY KEY,
cust_id VARCHAR (10) NOT NULL REFERENCES customer1(cust_id),
product_id VARCHAR(100) NOT NULL REFERENCES product(pro_id),
order_id VARCHAR(255)NOT NULL,
productname VARCHAR(255) NOT NULL,
paymentway VARCHAR(255) DEFAULT "CASH",
FOREIGN KEY(order_id) REFERENCES orders(order_id)
);
INSERT INTO shipmentinvoices VALUES(6576575,'Tj402','P_10','09hjwe3',"Trouser","Gpay");
CREATE TABLE ADMIN(
Admin_id INT,
NAME VARCHAR(255) NOT NULL,
PASSWORD VARCHAR(255) UNIQUE NOT NULL,
mobile_no VARCHAR(17) NOT NULL,
city VARCHAR(255) NOT NULL
);
INSERT INTO ADMIN VALUES(101,'Ragul raj','2022','949345678','Chennai'),
(102,'Mythili','20022','623899821','Mumbai');
Task-3,
Q-1, to fetch the details of the customer in the order of their city
ANS-;
SELECT * FROM customer ORDER BY cust_city;
Q-2, To alter the table product by renaming all the columns by preceding them with 'prod_'
ANS-:
-- Rename columns in the product table
ALTER TABLE product
RENAME COLUMN product_id TO prod_product_id,
RENAME COLUMN NAME TO prod_NAME,
RENAME COLUMN Color TO prod_Color,
RENAME COLUMN seller_id TO prod_seller_id,
RENAME COLUMN Sizes TO prod_Sizes,
RENAME COLUMN Quantity TO prod_Quantity,
RENAME COLUMN Cost TO prod_Cost,
RENAME COLUMN Discount TO prod_Discount,
RENAME COLUMN Place_for_delevery TO prod_Place_for_delevery,
RENAME COLUMN Calculation_of_cost TO prod_Calculation_of_cost,
RENAME COLUMN isFeatured TO prod_isFeatured,
RENAME COLUMN Rating TO prod_Rating,
RENAME COLUMN reviewCountry TO prod_reviewCountry,
RENAME COLUMN isOnSale TO prod_isOnSale,
RENAME COLUMN category_Id TO prod_category_Id,
RENAME COLUMN brand TO prod_brand,
RENAME COLUMN description_ TO prod_description_,
RENAME COLUMN tags TO prod_tags,
RENAME COLUMN related_Product TO prod_related_Product,
RENAME COLUMN categoryName TO prod_categoryName,
RENAME COLUMN seller_name TO prod_seller_name,
RENAME COLUMN questions TO prod_questions,
RENAME COLUMN image TO prod_image;
Q-3, To fetch the details of customer who have purchased at last one product .
ANS -:;
select c.cust_name, c.cust_id, count(o.order_id) total_orders from customer c inner join orders o on
c.cust_id = o.cust_id group by c.cust_id having count(o.order_id) >1;
Q-4, To fetch details of the customer who have
name starting from A and ending from A and has purchased more than one item;
ANS -
select c.cust_name, c.cust_id, count(o.order_id) total_orders from customer c inner join orders o on
c.cust_id = o.cust_id group by c.cust_id having count(o.order_id)>1 and c.cust_name like "a%a";
Q-5, WAQ to all the information about the orders are already delivered and has cost more
then 10000 and also has been purchased by more then 1 customer;
SELECT c.cust_name,c.cust_id,SUM(o.order_cost) "Purchesed amount" FROM orders o LEFT JOIN customer c
ON c.cust_id=o.cust_id WHERE o.status_="Delivered" AND o.order_cost>10000
AND c.cust_id HAVING COUNT(o.order_id)>1;
Q-6, write a simple procedure to run all the table creation statement insert statement and alter statement at once;
ANS -:
CREATE PROCEDURE Create_All_Tables_and_puting_insertion()BEGIN;