BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 1

SSaravanan T4 Dec 2022

//Task 1, Task 2

CREATE DATABASE Ecommerce;

USE Ecommerce;

CREATE TABLE Ecommerce_Store(
Admin_ID INT PRIMARY KEY,
Customer_ID INT UNIQUE KEY NOT NULL,
Seller_ID INT UNIQUE KEY NOT NULL
);

DESCRIBE Ecommerce_Store;

INSERT INTO Ecommerce_Store VALUES(1001,2001,3001),
(1002,2002,3002),
(1003,2003,3003),
(1004,2004,3004),
(1005,2005,3005);

SELECT * FROM Ecommerce_Store;

CREATE TABLE Admin(
Admin_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
PASSWORD VARCHAR(10) UNIQUE NOT NULL,
Mobile_Number VARCHAR(15) NOT NULL,
City VARCHAR(255) NOT NULL,
FOREIGN KEY(Admin_ID) REFERENCES Ecommerce_Store(Admin_ID)
);

SHOW TABLES;

DESCRIBE Admin;

INSERT INTO Admin VALUES(1001,'Saravana','10sara','0123456789','TRICHY'),
(1002,'Balu','10balu','0123456789','TRICHY'),
(1003,'Priya','10priya','0123456789','TRICHY'),
(1004,'Anu','10anu','0123456789','TRICHY'),
(1005,'Bharathi','10bhara','0123456789','TRICHY');

SELECT * FROM Admin;

CREATE TABLE Customer(
Customer_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
PASSWORD VARCHAR(255) NOT NULL,
DOB DATE NOT NULL,
Gender CHAR(1) NOT NULL,
City CHAR(50) NOT NULL,
Mobile_Number VARCHAR(15) NOT NULL,
email CHAR(50) DEFAULT "Unassigned",
Address VARCHAR(255) NOT NULL,
Coupon_ID INT REFERENCES Coupon(Coupon_ID),
FOREIGN KEY(Customer_ID) REFERENCES Ecommerce_Store(Customer_ID)
);

DESCRIBE Customer;

INSERT INTO Customer VALUES(2001,'Karthi','10sara','1993-03-05','M','Ramanad','0000000000','[email protected]','1 100 Valluvar STREET Ramanad',12345),
(2002,'Abu','10abu','1993-06-13','M','Trichy','1111111111','[email protected]','2 200 Star STREET Trichy',12346),
(2003,'Sundar','10sunda','1993-03-26','M','Thanjore','2222222222','[email protected]','3 300 New STREET Thanjore',NULL),
(2004,'Praveen','10prav','1993-05-26','M','Mannargudi','3333333333','[email protected]','4 400 Old STREET Mannargudi',12347),
(2005,'Thiru','10thiru','1993-07-28','M','Chennai','4444444444','[email protected]','5 500 Solai STREET Chennai',NULL);

SELECT * FROM Customer;

CREATE TABLE Seller(
Seller_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
PASSWORD VARCHAR(50) UNIQUE NOT NULL,
Gender CHAR(10) NOT NULL,
Mobile_Number VARCHAR(15) NOT NULL,
City CHAR(50) NOT NULL,
Categry_of_Products CHAR(50) NOT NULL,
FOREIGN KEY(Seller_ID) REFERENCES Ecommerce_Store(Seller_ID)
);

DESCRIBE Seller;

INSERT INTO Seller VALUES(3001,'Kumar','10kumar','M','5555555555','Thiruvalur','Electronic'),
(3002,'Mani','10mani','M','6666666666','Thiruppur','clothes'),
(3003,'Guna','10guna','M','7777777777','Chennai','Electronic'),
(3004,'Ram','10ram','M','8888888888','Coimbatore','Furniture'),
(3005,'Sadha','10sadha','M','9999999999','Madurai','Furniture');

SELECT * FROM Seller;

CREATE TABLE Product(
Category_ID INT NOT NULL,
Category_Name CHAR(50)NOT NULL REFERENCES Seller(categery_name),
Product_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
Product_Color CHAR(50) NOT NULL,
Product_Cost INT NOT NULL,
Size_available DECIMAL(10,2) NOT NULL,
Quantity_available INT NOT NULL,
Seller_Name CHAR(50) NOT NULL,
Discount INT DEFAULT 0,
Place_origing_delivery VARCHAR(255) NOT NULL,
Rating INT NOT NULL,
isonsale CHAR(10) NOT NULL,
Brands CHAR(50) NOT NULL,
Images VARCHAR(255) NOT NULL,
Questions VARCHAR(255) NOT NULL
);

DESCRIBE Product;

INSERT INTO Product VALUES(101,'Electronic',01,'Vivo mobile','Gray',30000,6.1,1500,'Kumar',1000,'INDIA',4,'YES','Vivo','vivo.jpg','Nothing'),
(101,'Electronic',02,'Oppo mobile','Blue',20000,6.5,2000,'Mani',2000,'INDIA',5,'YES','Oppo','oppo.jpg','Nothing'),
(101,'Electronic',03,'Apple phone','White',80000,5.0,800,'Guna',5000,'INDIA',5,'YES','Apple','apple.jpg','Nothing'),
(101,'Electronic',04,'Redmi mobile','Voilet',25000,6.7,3000,'Ram',3000,'INDIA',4,'YES','Redmi','redmi.jpg','Nothing'),
(101,'Electronic',05,'Samasung mobile','Red',15000,6.0,1800,'Sadha',1000,'INDIA',4,'YES','Samsung','samsung.jpg','Nothing'),
(102,'Furniture',06,'Table','Brown',5000,10.5,150,'Kumar',1000,'INDIA',4,'YES','Nilkamal','nilkamal.jpg','Nothing'),
(102,'Furniture',07,'Chair','Black',1000,5.5,5000,'Mani',200,'INDIA',5,'YES','Nilkamal','nilkamal.jpg','Nothing'),
(102,'Furniture',08,'Fan','Blue',3000,4.5,1000,'Guna',500,'INDIA',5,'YES','Crompton','crompton.jpg','Nothing'),
(102,'Furniture',09,'Sofa','Black',40000,55.5,30,'Ram',5000,'INDIA',4,'YES','Damro','damro.jpg','Nothing'),
(102,'Furniture',10,'Grinder','White',8000,15.5,500,'Sadha',1000,'INDIA',5,'YES','Preethi','preethi.jpg','Nothing'),
(103,'cloths',11,'Shirt','White',1000,40.0,25,'Kumar',50,'INDIA',4,'YES','Otto','otto.jpg','Nothing'),
(103,'cloths',12,'Phant','Black',2000,34.0,30,'Mani',50,'INDIA',4,'YES','Cherokie','cherokie.jpg','Nothing'),
(103,'cloths',13,'Track phant','Gray',800,42.0,75,'Guna',20,'INDIA',4,'YES','Max','max.jpg','Nothing'),
(103,'cloths',14,'T_shirt','Yellow',500,40.0,100,'Ram',80,'INDIA',4,'YES','Ajio','ajio.jpg','Nothing'),
(103,'cloths',15,'Jeans','Blue',1500,34.0,50,'Sadha',30,'INDIA',4,'YES','Ajio','ajio.jpg','Nothing');

SELECT * FROM Product;

CREATE TABLE Orders(
Order_ID INT PRIMARY KEY,
Customer_ID INT NOT NULL,
Product_ID INT NOT NULL,
Product_Cost INT NOT NULL,
Order_Status VARCHAR(200) DEFAULT "ORDERED",
Order_Date DATE NOT NULL,
Delivery_Date DATE NOT NULL,
How_many_Product INT NOT NULL
);

DESCRIBE Orders;

INSERT INTO Orders VALUES(10001,2004,21,10000,'Cancelled','2022-06-13','2022-06-17',1),
(10002,2004,22,50000,'Delivered','2022-07-22','2022-07-25',1),
(10003,2004,22,1000,'Delivered','2022-10-03','2022-10-06',1),
(10004,2001,26,6000,'Ordered','2022-11-23','2022-11-26',1),
(10005,2002,35,9999,'Delivered','2022-01-12','2022-01-15',1),
(10006,2003,35,1500,'Shiped','2022-11-30','2022-12-02',1);

SELECT * FROM Orders;

CREATE TABLE Coupon(
Coupon_ID INT PRIMARY KEY,
Percentage_Discount INT NOT NULL
);

DESCRIBE Coupon;

INSERT INTO Coupon VALUES(12345,10),(12346,15),(12347,30);

SELECT * FROM Coupon;

CREATE TABLE Reviewes(
Rev_ID INT PRIMARY KEY,
Product_ID INT NOT NULL,
Customer_ID INT NOT NULL REFERENCES Customer(Customer_ID),
Rating INT NOT NULL,
Average_Rating INT NOT NULL,
Total_Rating INT NOT NULL,
Review_Country VARCHAR(255) NOT NULL
);

DESCRIBE Reviewes;

INSERT INTO Reviewes VALUES(2012,22,2002,5,12,2000,'INDIA');

SELECT * FROM Reviewes;

CREATE TABLE Wishlist(
Wish_ID INT PRIMARY KEY,
Product_Name VARCHAR(255),
Product_Cost INT NOT NULL,
Product_ID INT NOT NULL REFERENCES Product(Product_ID),
Customer_ID INT NOT NULL REFERENCES Customer(Customer_ID)
);

DESCRIBE Wishlist;

INSERT INTO wishlist VALUES(7001,'Vivo mobile',30000,21,2002);

SELECT * FROM Wishlist;

CREATE TABLE Shipment(
Shipment_ID INT PRIMARY KEY,
Customer_ID INT NOT NULL REFERENCES Customer(Customer_ID),
Product_ID INT NOT NULL REFERENCES Product(Product_ID),
Order_ID INT NOT NULL,
Product_Name CHAR(50) NOT NULL,
Payment_Way VARCHAR(255) DEFAULT 'CASH',
FOREIGN KEY(Order_ID) REFERENCES Orders(Order_ID)
);

DESCRIBE Shipment;

INSERT INTO Shipment VALUES(790012,2002,18,10005,'Coatsuit','Cash on Delivery');

SELECT * FROM Shipment;

// Task 3

1. SELECT * FROM Customer ORDER BY City;

2. ALTER TABLE Product RENAME COLUMN Category_ID TO prod_Category_ID;
ALTER TABLE Product RENAME COLUMN Category_Name TO prod_Category_Name;
ALTER TABLE Product RENAME COLUMN Product_ID TO prod_ID;
ALTER TABLE Product RENAME COLUMN Name TO prod_Name;
ALTER TABLE Product RENAME COLUMN Product_Color TO prod_Color;
ALTER TABLE Product RENAME COLUMN Product_Cost TO prod_Cost;
ALTER TABLE Product RENAME COLUMN Size_available TO prod_Size_available;
ALTER TABLE Product RENAME COLUMN Quantity_available TO prod_Quantity_available;
ALTER TABLE Product RENAME COLUMN Seller_Name TO prod_Seller_Name;
ALTER TABLE Product RENAME COLUMN Discount TO prod_Discount;
ALTER TABLE Product RENAME COLUMN Place_origing_delivery TO prod_Place_origing_delivery;
ALTER TABLE Product RENAME COLUMN Rating TO prod_Rating;
ALTER TABLE Product RENAME COLUMN isonsale TO prod_isonsale;
ALTER TABLE Product RENAME COLUMN Brands TO prod_Brands;
ALTER TABLE Product RENAME COLUMN Images TO prod_Images;
ALTER TABLE Product RENAME COLUMN Questions TO prod_Questions;
SELECT * FROM Product;
3.
SELECT C.Name, C.Customer_ID, COUNT(O.Order_ID)TOTAL_Orders FROM Customer C INNER JOIN Orders O ON
C.Customer_ID = O.Customer_ID GROUP BY C.Customer_ID HAVING COUNT(O.Order_ID) >= 1;
4.
SELECT C.Name,C.Customer_ID,COUNT(O.Order_ID) TOTAL_Orders FROM Customer C INNER JOIN Orders O ON
C.Customer_ID = O.Customer_ID GROUP BY C.Customer_ID HAVING COUNT(O.Order_ID)>1 AND C.Name LIKE 'A%A';
5.
SELECT C.Name,C.Customer_ID,O.Order_Status, SUM(Product_Cost) 'Purchesed amount' FROM Orders O LEFT JOIN Customer C
ON C.Customer_ID = O.Customer_ID WHERE O.Order_Status = 'Delivered' AND Product_Cost > 10000
GROUP BY C.Customer_ID HAVING COUNT(O.Order_ID) > 1;
6.
DELIMITER$$
CREATE PROCEDURE Create_All_Tables()
BEGIN
CREATE TABLE Ecommerce_Store(
Admin_ID INT PRIMARY KEY,
Customer_ID INT UNIQUE KEY NOT NULL,
Seller_ID INT UNIQUE KEY NOT NULL
);
INSERT INTO Ecommerce_Store VALUES(1001,2001,3001),
(1002,2002,3002),
(1003,2003,3003),
(1004,2004,3004),
(1005,2005,3005);
CREATE TABLE Admin(
Admin_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
PASSWORD VARCHAR(10) UNIQUE NOT NULL,
Mobile_Number VARCHAR(15) NOT NULL,
City VARCHAR(255) NOT NULL,
FOREIGN KEY(Admin_ID) REFERENCES Ecommerce_Store(Admin_ID)
);
INSERT INTO Admin VALUES(1001,'Saravana','10sara','0123456789','TRICHY'),
(1002,'Balu','10balu','0123456789','TRICHY'),
(1003,'Priya','10priya','0123456789','TRICHY'),
(1004,'Anu','10anu','0123456789','TRICHY'),
(1005,'Bharathi','10bhara','0123456789','TRICHY');
CREATE TABLE Customer(
Customer_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
PASSWORD VARCHAR(255) NOT NULL,
DOB DATE NOT NULL,
Gender CHAR(1) NOT NULL,
City CHAR(50) NOT NULL,
Mobile_Number VARCHAR(15) NOT NULL,
email CHAR(50) DEFAULT "Unassigned",
Address VARCHAR(255) NOT NULL,
Coupon_ID INT REFERENCES Coupon(Coupon_ID),
FOREIGN KEY(Customer_ID) REFERENCES Ecommerce_Store(Customer_ID)
);
INSERT INTO Customer VALUES(2001,'Karthi','10sara','1993-03-05','M','Ramanad','0000000000','[email protected]','1 100 Valluvar STREET Ramanad',12345),
(2002,'Abu','10abu','1993-06-13','M','Trichy','1111111111','[email protected]','2 200 Star STREET Trichy',12346),
(2003,'Sundar','10sunda','1993-03-26','M','Thanjore','2222222222','[email protected]','3 300 New STREET Thanjore',NULL),
(2004,'Praveen','10prav','1993-05-26','M','Mannargudi','3333333333','[email protected]','4 400 Old STREET Mannargudi',12347),
(2005,'Thiru','10thiru','1993-07-28','M','Chennai','4444444444','[email protected]','5 500 Solai STREET Chennai',NULL);
CREATE TABLE Seller(
Seller_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
PASSWORD VARCHAR(50) UNIQUE NOT NULL,
Gender CHAR(10) NOT NULL,
Mobile_Number VARCHAR(15) NOT NULL,
City CHAR(50) NOT NULL,
Categry_of_Products CHAR(50) NOT NULL,
FOREIGN KEY(Seller_ID) REFERENCES Ecommerce_Store(Seller_ID)
);
INSERT INTO Seller VALUES(3001,'Kumar','10kumar','M','5555555555','Thiruvalur','Electronic'),
(3002,'Mani','10mani','M','6666666666','Thiruppur','clothes'),
(3003,'Guna','10guna','M','7777777777','Chennai','Electronic'),
(3004,'Ram','10ram','M','8888888888','Coimbatore','Furniture'),
(3005,'Sadha','10sadha','M','9999999999','Madurai','Furniture');
CREATE TABLE Product(
Category_ID INT NOT NULL,
Category_Name CHAR(50)NOT NULL REFERENCES Seller(categery_name),
Product_ID INT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
Product_Color CHAR(50) NOT NULL,
Product_Cost INT NOT NULL,
Size_available DECIMAL(10,2) NOT NULL,
Quantity_available INT NOT NULL,
Seller_Name CHAR(50) NOT NULL,
Discount INT DEFAULT 0,
Place_origing_delivery VARCHAR(255) NOT NULL,
Rating INT NOT NULL,
isonsale CHAR(10) NOT NULL,
Brands CHAR(50) NOT NULL,
Images VARCHAR(255) NOT NULL,
Questions VARCHAR(255) NOT NULL
);
INSERT INTO Product VALUES(101,'Electronic',01,'Vivo mobile','Gray',30000,6.1,1500,'Kumar',1000,'INDIA',4,'YES','Vivo','vivo.jpg','Nothing'),
(101,'Electronic',02,'Oppo mobile','Blue',20000,6.5,2000,'Mani',2000,'INDIA',5,'YES','Oppo','oppo.jpg','Nothing'),
(101,'Electronic',03,'Apple phone','White',80000,5.0,800,'Guna',5000,'INDIA',5,'YES','Apple','apple.jpg','Nothing'),
(101,'Electronic',04,'Redmi mobile','Voilet',25000,6.7,3000,'Ram',3000,'INDIA',4,'YES','Redmi','redmi.jpg','Nothing'),
(101,'Electronic',05,'Samasung mobile','Red',15000,6.0,1800,'Sadha',1000,'INDIA',4,'YES','Samsung','samsung.jpg','Nothing'),
(102,'Furniture',06,'Table','Brown',5000,10.5,150,'Kumar',1000,'INDIA',4,'YES','Nilkamal','nilkamal.jpg','Nothing'),
(102,'Furniture',07,'Chair','Black',1000,5.5,5000,'Mani',200,'INDIA',5,'YES','Nilkamal','nilkamal.jpg','Nothing'),
(102,'Furniture',08,'Fan','Blue',3000,4.5,1000,'Guna',500,'INDIA',5,'YES','Crompton','crompton.jpg','Nothing'),
(102,'Furniture',09,'Sofa','Black',40000,55.5,30,'Ram',5000,'INDIA',4,'YES','Damro','damro.jpg','Nothing'),
(102,'Furniture',10,'Grinder','White',8000,15.5,500,'Sadha',1000,'INDIA',5,'YES','Preethi','preethi.jpg','Nothing'),
(103,'cloths',11,'Shirt','White',1000,40.0,25,'Kumar',50,'INDIA',4,'YES','Otto','otto.jpg','Nothing'),
(103,'cloths',12,'Phant','Black',2000,34.0,30,'Mani',50,'INDIA',4,'YES','Cherokie','cherokie.jpg','Nothing'),
(103,'cloths',13,'Track phant','Gray',800,42.0,75,'Guna',20,'INDIA',4,'YES','Max','max.jpg','Nothing'),
(103,'cloths',14,'T_shirt','Yellow',500,40.0,100,'Ram',80,'INDIA',4,'YES','Ajio','ajio.jpg','Nothing'),
(103,'cloths',15,'Jeans','Blue',1500,34.0,50,'Sadha',30,'INDIA',4,'YES','Ajio','ajio.jpg','Nothing');
CREATE TABLE Orders(
Order_ID INT PRIMARY KEY,
Customer_ID INT NOT NULL,
Product_ID INT NOT NULL,
Product_Cost INT NOT NULL,
Order_Status VARCHAR(200) DEFAULT "ORDERED",
Order_Date DATE NOT NULL,
Delivery_Date DATE NOT NULL,
How_many_Product INT NOT NULL
);
INSERT INTO Orders VALUES(10001,2004,21,10000,'Cancelled','2022-06-13','2022-06-17',1),
(10002,2004,22,50000,'Delivered','2022-07-22','2022-07-25',1),
(10003,2004,22,1000,'Delivered','2022-10-03','2022-10-06',1),
(10004,2001,26,6000,'Ordered','2022-11-23','2022-11-26',1),
(10005,2002,35,9999,'Delivered','2022-01-12','2022-01-15',1),
(10006,2003,35,1500,'Shiped','2022-11-30','2022-12-02',1);
CREATE TABLE Coupon(
Coupon_ID INT PRIMARY KEY,
Percentage_Discount INT NOT NULL
);
INSERT INTO Coupon VALUES(12345,10),(12346,15),(12347,30);
CREATE TABLE Reviewes(
Rev_ID INT PRIMARY KEY,
Product_ID INT NOT NULL,
Customer_ID INT NOT NULL REFERENCES Customer(Customer_ID),
Rating INT NOT NULL,
Average_Rating INT NOT NULL,
Total_Rating INT NOT NULL,
Review_Country VARCHAR(255) NOT NULL
);
INSERT INTO Reviewes VALUES(2012,22,2002,5,12,2000,'INDIA');
CREATE TABLE Wishlist(
Wish_ID INT PRIMARY KEY,
Product_Name VARCHAR(255),
Product_Cost INT NOT NULL,
Product_ID INT NOT NULL REFERENCES Product(Product_ID),
Customer_ID INT NOT NULL REFERENCES Customer(Customer_ID)
);
INSERT INTO wishlist VALUES(7001,'Vivo mobile',30000,21,2002);
CREATE TABLE Shipment(
Shipment_ID INT PRIMARY KEY,
Customer_ID INT NOT NULL REFERENCES Customer(Customer_ID),
Product_ID INT NOT NULL REFERENCES Product(Product_ID),
Order_ID INT NOT NULL,
Product_Name CHAR(50) NOT NULL,
Payment_Way VARCHAR(255) DEFAULT 'CASH',
FOREIGN KEY(Order_ID) REFERENCES Orders(Order_ID)
);
INSERT INTO Shipment VALUES(790012,2002,18,10005,'Coatsuit','Cash on Delivery');
ALTER TABLE Product RENAME COLUMN Category_ID TO prod_Category_ID;
ALTER TABLE Product RENAME COLUMN Category_Name TO prod_Category_Name;
ALTER TABLE Product RENAME COLUMN Product_ID TO prod_ID;
ALTER TABLE Product RENAME COLUMN NAME TO prod_Name;
ALTER TABLE Product RENAME COLUMN Product_Color TO prod_Color;
ALTER TABLE Product RENAME COLUMN Product_Cost TO prod_Cost;
ALTER TABLE Product RENAME COLUMN Size_available TO prod_Size_available;
ALTER TABLE Product RENAME COLUMN Quantity_available TO prod_Quantity_available;
ALTER TABLE Product RENAME COLUMN Seller_Name TO prod_Seller_Name;
ALTER TABLE Product RENAME COLUMN Discount TO prod_Discount;
ALTER TABLE Product RENAME COLUMN Place_origing_delivery TO prod_Place_origing_delivery;
ALTER TABLE Product RENAME COLUMN Rating TO prod_Rating;
ALTER TABLE Product RENAME COLUMN isonsale TO prod_isonsale;
ALTER TABLE Product RENAME COLUMN Brands TO prod_Brands;
ALTER TABLE Product RENAME COLUMN Images TO prod_Images;
ALTER TABLE Product RENAME COLUMN Questions TO prod_Questions;
END$$
DELIMITER;
Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.