Project 1

Task 1.

Customer Table :

CREATE TABLE customer (
Customerid varchar(10) NOT NULL PRIMARY KEY,
Name varchar(200) NOT NULL,
email varchar(100) NOT NULL,
gender varchar(50) NOT NULL,
dob datetime NOT NULL,
city varchar(100) NOT NULL,
diliveryAddress varchar(500) NOT NULL,
contact int(11) NOT NULL,
Currentorder varchar(100) NOT NULL,
PrevOrder varchar(100) NOT NULL,
Cartid varchar(100) DEFAULT NULL
)

ALTER TABLE customer
ADD FOREIGN KEY (Cartid) REFERENCES cart (Cartid);

INSERT INTO customer (Customerid, Name, email, gender, dob, city, deliveryAddress, contact, Currentorder, PrevOrder, Cartid) VALUES
('023pkr', 'Aishwarya', '[email protected]', 'female', '2003-11-14 15:23:33', 'mumbai', 'dadar', 888859944, 'Dress', 'lehanga', 'c_105'),
('B12kr', 'Abhi', '[email protected]', 'male', '1995-11-02 15:19:36', 'Bangalore', 'bangalore', 988849445, 'Headphone', 'Shirts', 'c_103'),
('G_235', 'Ganesh', '[email protected]', 'male', '2022-11-05 08:11:53', 'Pune', 'Hadapsar Pune', 456777653, 'T_Shirt', 'Cap', 'c_102'),
('Tj402', 'Vankatesh', 'vanki@[email protected]', 'male', '2002-11-21 15:19:36', 'Chennai', 'chennai', 78906483, 'Homethreater', 'sounds', 'c_104'),
('V_Patil', 'Vaibhav', '[email protected]', 'Male', '2000-11-12 12:41:53', 'Nanded', 'Latur', 845905668, 'Macbook', 'HP Laptop', 'c_101');

Cart Table :

CREATE TABLE
cart (Cartid varchar(100) NOT NULL PRIMARY KEY, Itemquantity int(11) NOT NULL, Total cost int(11) NOT NULL);

INSERT INTO cart (Cartid, Itemquantity,Total cost) VALUES('c_101', 20, 3000),
('c_102', 10, 1200),
('c_103', 2, 200),
('c_104', 5, 2000),
('c_105', 10, 70000);


Cart_Item Table :

CREATE TABLE cartitem (Cartid varchar(100) NOT NULL PRIMARY KEY,Productid varchar(100) NOT NULL,
DateAdded date NOT NULL, purchased varchar(3) DEFAULT 'NO',itemquantity int(11) DEFAULT NULL, customerid varchar(100));


ALTER TABLE cartitem ADD FOREIGN KEY (Cartid) REFERENCES cart (Cartid), ADD FOREIGN KEY (Productid) RFERENCES Product, ADD FOREIGN KEY (customerid) REFERENCES customer;

INSERT INTO cartitem (Cartid,Productid,DateAdded,purchased,itemquantity,customerid) VALUES
('c_101', 'P_3', '2022-11-07', 'NO', 7),
('c_102', 'P_1', '2022-11-22', 'y', 3),
('c_103', 'P_1', '2022-10-19', 'Y', 5),
('c_104', 'P_3', '2022-11-03', 'Y', 6),
('c_105', 'P_1', '2022-11-01', 'NO', 3);


Category Table :

CREATE TABLE category (categoryid varchar(100) NOT NULL PRIMARY Key,categoryname varchar(100) NOT NULL,
Productid varchar(100) NOT NULL,subCategory varchar(100) NOT NULL);

ALTER TABLE category ADD FOREIGN KEY (Productid) REFERENCES Product (Productid);

INSERT INTO category (categoryid, categoryname,Productid,subCategory) VALUES
('cat_285', 'Accessories', 'P_1', 'Headphones'),
('cat_288', 'Laptops', 'P_1', 'Apple'),
('cat_291', 'Accessories', 'P_3', 'Phones'),
('cat_299', 'Phones', 'P_3', 'Vivo'),
('cat_301', 'Fashion', 'P_3', 'Mens');


Orders Table :

CREATE TABLE orders (orderid varchar(100) NOT NULL PRIMARY KEY,status varchar(100) NOT NULL, orderdate date NOT NULL, deiverydate date NOT NULL,customerid varchar(100), productid varchar(100));

ALTER TABLE orders ADD FOREIGN KEY (customerid) REFERENCES customer (Customerid);

INSERT INTO orders (orderid,status,orderdate,deliverydate,customerid,productid) VALUES
('02341mrk', 'shipping', '2022-11-05', '2022-11-08', 'V_Patil','krt455'),
('09hjwe3', 'delivered', '2022-11-01', '2022-11-05', '0023pkr','P_1'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', 'G_235'),
('klr892', 'dileverd soon', '2022-11-04', '2022-11-06', 'B12kr','P_3'),
('ormh26', 'shiiping', '2022-11-02', '2022-11-07', 'Tj402');


Payment Table :

CREATE TABLE payment ( paymentid varchar(100) NOT NULL PRIMARY KEY,paymentdate date NOT NULL,Paymenttype varchar(200) NOT NULL,Customerid varchar(100) NOT NULL,Cartid varchar(100));


ALTER TABLE payment ADD FOREIGN KEY (Customerid) REFERENCES customer (Customerid), ADD FOREIGN KEY (Cartid) REFERENCES cart (Cartid);

INSERT INTO payment (paymentid,paymentdate,Paymenttype,Customerid,Cartid) VALUES
('0023449', '2022-11-03', 'reference-0394j34', 'B12kr', 'c_104'),
('0294738484', '2022-11-01', 'UPI', '0023pkr', 'c_102'),
('488443565', '2022-11-02', 'phonepay', 'G_235', 'c_103'),
('4930980', '2022-11-04', 'bharatPay', 'V_Patil', 'c_101'),
('9849344', '2022-11-04', 'upi', 'Tj402', 'c_104');

Product Table :

CREATE TABLE Product(Productid varchar(50) NOT NULL PRIMARY KEY,Name varchar(200) NOT NULL,color varchar(50), sellerid varchar(100) NOT NULL,size int(11) NOT NULL,quantity int(11) NOT NULL,cost int(11) NOT NULL,discount int(11) NOT NULL,placefordelivery varchar(200) NOT NULL,isFeatured varchar(10) NOT NULL,rating int(11) NOT NULL,reviews int(11) NOT NULL,isOnScale varchar(100) NOT NULL, Categoryid varchar(100) NOT NULL,brand varchar(100) NOT NULL, description varchar(300) NOT NULL, tags varchar(100) NOT NULL, relatedproduct varchar(300) NOT NULL);


ALTER TABLE Product ADD FOREIGN KEY (sellerid) REFERENCES seller(sellerid);

INSERT INTO Product(Productid,Name,color,sellerid,size,quantity,cost,discount,placefordelivery,isFeatured,rating,reviews,isOnScale,Categoryid,brand,description,tags,relatedproduct) VALUES
('Hag3943r', 'Rebion Goggals', 'red-black', 'S123_KR', 0, 1, 2000, 300, 'Latur', 'yes', 5, 4, 'yes', 'cat_291', 'Rebion', 'sunlight glasses', 'goggles', 'yellow glasses'),
('krt455', 'T_Shirt', 'Yellow', 'S111_MH', 42, 2, 3000, 200, 'Nanded', '', 4, 8, 'yes', 'Fashion', 'being human', 'T_shirt with cap', '', 'being humans jeans'),
('P_1', 'Macbook', 'black', 'S123_KR', 14, 1, 120000, 7000, 'pune', 'yes', 5, 40, 'very good product', 'C_301', 'Apple', 'Product is very branded and good', 'Laptop', 'IPad'),
('P_3', 'Vivo_30', 'silver', 'S111_MH', 20, 2, 40000, 2000, 'Latur', 'Welll feat', 7, 20, 'well developed', 'Phones', 'Vivo', 'Very good phone camera 64 ram 8 gb', 'vivo', 'vivo-28'),
('P_490g', 'headphone', 'white', 'S111_MH', 0, 3, 700, 110, 'pune', 'yes', 5, 5, 'yes', 'cat_285', 'realme', 'white headphones', 'realme', 'realme ipods');


Seller Table :

CREATE TABLE seller(sellerid varchar(100) NOT NULL PRIMARY KEY,Name varchar(200) NOT NULL,Mobileno int(11) NOT NULL,password varchar(200) NOT NULL,ProductCategory varchar(100) NOT NULL);


INSERT INTO seller(sellerid,Name,Mobileno,password,ProductCategory) VALUES
('er455k', 'Jully', 59545554, 'li9345n', 'Accessories'),
('kjhkj54', 'Danni', 345665656, 'ggj54t02', 'Laptop'),
('S-66jd', 'Javed', 483595847, 'ghd3435j', 'fashion'),
('S111_MH', 'Abhi', 8549505, 'V4590nd', 'Accessories'),
('S123_KR', 'Vinay', 84564865, '90hf3e', 'Phones');

Task 3 :

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

==> SELECT * FROM customers ORDER BY city;

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_productid,

RENAME COLUMN color TO prod_color,

RENAME COLUMN seller_id TO prod_seller_id,

RENAME COLUMN size TO prod_size,

RENAME COLUMN quantity TO prod_quantity,

RENAME COLUMN cost TO prod_cost,

RENAME COLUMN discount TO prod_discount,

RENAME COLUMN placeofdelivery TO prod_placeofdelivery,

RENAME COLUMN isfeatured To prod_isfeatured,

RENAME COLUMN rating To prod_rating,

RENAME COLUMN reviews TO prod_reviews,

RENAME COLUMN isOnScale TO prod_isOnScale,

RENAME COLUMN categoryid TO prod_category,

RENAME COLUMN brand TO prod_brand,

RENAME COLUMN description TO prod_description,

RENAME COLUMN tags TO prod_tags,

RENAME COLUMN relatedproduct TO prod_relatedproduct ;

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

==> SELECT * FROM customers c INNER JOIN order o ON c.customerid=o.customerid WHERE o.customerid IS NOT NULL;

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 * FROM customer c INNER JOIN cart_item ct ON c.customerid=ct.customerid WHERE c.name LIKE '%AA%' AND ct.purchased='y' AND ct.itemquantity>1;

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 * FROM orders o INNER JOIN product p ON o.productid=p.productid WHERE o.status='delivered' AND p.cost=(SELECT productid FROM product WHERE cost> 10000);

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

==>

DELIMITER $$

CREATE PROCEDURE 'ecommerce'

BEGIN

CREATE TABLE customer (
Customerid varchar(10) NOT NULL PRIMARY KEY,
Name varchar(200) NOT NULL,
email varchar(100) NOT NULL,
gender varchar(50) NOT NULL,
dob datetime NOT NULL,
city varchar(100) NOT NULL,
diliveryAddress varchar(500) NOT NULL,
contact int(11) NOT NULL,
Currentorder varchar(100) NOT NULL,
PrevOrder varchar(100) NOT NULL,
Cartid varchar(100) DEFAULT NULL
)

ALTER TABLE customer
ADD FOREIGN KEY (Cartid) REFERENCES cart (Cartid);

INSERT INTO customer VALUES
('0023pkr', 'Aishwarya', '[email protected]', 'female', '2003-11-14 15:23:33', 'mumbai', 'dadar', 888859944, 'Dress', 'lehanga', 'c_105'),
('B12kr', 'Abhi', '[email protected]', 'male', '1995-11-02 15:19:36', 'Bangalore', 'bangalore', 988849445, 'Headphone', 'Shirts', 'c_103'),
('G_235', 'Ganesh', '[email protected]', 'male', '2022-11-05 08:11:53', 'Pune', 'Hadapsar Pune', 456777653, 'T_Shirt', 'Cap', 'c_102'),
('Tj402', 'Vankatesh', 'vanki@[email protected]', 'male', '2002-11-21 15:19:36', 'Chennai', 'chennai', 78906483, 'Homethreater', 'sounds', 'c_104'),
('V_Patil', 'Vaibhav', '[email protected]', 'Male', '2000-11-12 12:41:53', 'Nanded', 'Latur', 845905668, 'Macbook', 'HP Laptop', 'c_101');

CREATE TABLE
cart (Cartid varchar(100) NOT NULL PRIMARY KEY, Itemquantity int(11) NOT NULL, Total cost int(11) NOT NULL);

INSERT INTO cart VALUES('c_101', 20, 3000),
('c_102', 10, 1200),
('c_103', 2, 200),
('c_104', 5, 2000),
('c_105', 10, 70000);

CREATE TABLE cartitem (Cartid varchar(100) NOT NULL PRIMARY KEY,Productid varchar(100) NOT NULL,
DateAdded date NOT NULL, purchased varchar(3) DEFAULT 'NO',itemquantity int(11) DEFAULT NULL, customerid varchar(100));

ALTER TABLE cartitem ADD FOREIGN KEY (Cartid) REFERENCES cart (Cartid), ADD FOREIGN KEY (Productid) RFERENCES Product, ADD FOREIGN KEY (customerid) REFERENCES customer;

INSERT INTO cartitem VALUES
('c_101', 'P_3', '2022-11-07', 'NO', 7),
('c_102', 'P_1', '2022-11-22', 'y', 3),
('c_103', 'P_1', '2022-10-19', 'Y', 5),
('c_104', 'P_3', '2022-11-03', 'Y', 6),
('c_105', 'P_1', '2022-11-01', 'NO', 3);

CREATE TABLE category (categoryid varchar(100) NOT NULL PRIMARY Key,categoryname varchar(100) NOT NULL,
Productid varchar(100) NOT NULL,subCategory varchar(100) NOT NULL);

ALTER TABLE category ADD FOREIGN KEY (Productid) REFERENCES Product (Productid);

INSERT INTO category VALUES
('cat_285', 'Accessories', 'P_1', 'Headphones'),
('cat_288', 'Laptops', 'P_1', 'Apple'),
('cat_291', 'Accessories', 'P_3', 'Phones'),
('cat_299', 'Phones', 'P_3', 'Vivo'),
('cat_301', 'Fashion', 'P_3', 'Mens');

CREATE TABLE orders (orderid varchar(100) NOT NULL PRIMARY KEY,status varchar(100) NOT NULL, orderdate date NOT NULL, deiverydate date NOT NULL,customerid varchar(100), productid varchar(100));

ALTER TABLE orders ADD FOREIGN KEY (customerid) REFERENCES customer (Customerid);

INSERT INTO orders VALUES
('02341mrk', 'shipping', '2022-11-05', '2022-11-08', 'V_Patil','krt455'),
('09hjwe3', 'delivered', '2022-11-01', '2022-11-05', '0023pkr','P_1'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', 'G_235'),
('klr892', 'dileverd soon', '2022-11-04', '2022-11-06', 'B12kr','P_3'),
('ormh26', 'shiiping', '2022-11-02', '2022-11-07', 'Tj402');

CREATE TABLE payment ( paymentid varchar(100) NOT NULL PRIMARY KEY,paymentdate date NOT NULL,Paymenttype varchar(200) NOT NULL,Customerid varchar(100) NOT NULL,Cartid varchar(100));

ALTER TABLE payment ADD FOREIGN KEY (Customerid) REFERENCES customer (Customerid), ADD FOREIGN KEY (Cartid) REFERENCES cart (Cartid);

INSERT INTO payment VALUES
('0023449', '2022-11-03', 'reference-0394j34', 'B12kr', 'c_104'),
('0294738484', '2022-11-01', 'UPI', '0023pkr', 'c_102'),
('488443565', '2022-11-02', 'phonepay', 'G_235', 'c_103'),
('4930980', '2022-11-04', 'bharatPay', 'V_Patil', 'c_101'),
('9849344', '2022-11-04', 'upi', 'Tj402', 'c_104');

CREATE TABLE Product(Productid varchar(50) NOT NULL PRIMARY KEY,Name varchar(200) NOT NULL,color varchar(50), sellerid varchar(100) NOT NULL,size int(11) NOT NULL,quantity int(11) NOT NULL,cost int(11) NOT NULL,discount int(11) NOT NULL,placefordelivery varchar(200) NOT NULL,isFeatured varchar(10) NOT NULL,rating int(11) NOT NULL,reviews int(11) NOT NULL,isOnScale varchar(100) NOT NULL, Categoryid varchar(100) NOT NULL,brand varchar(100) NOT NULL, description varchar(300) NOT NULL, tags varchar(100) NOT NULL, relatedproduct varchar(300) NOT NULL);

ALTER TABLE Product ADD FOREIGN KEY (sellerid) REFERENCES seller(sellerid);

INSERT INTO Product VALUES
('Hag3943r', 'Rebion Goggals', 'red-black', 'S123_KR', 0, 1, 2000, 300, 'Latur', 'yes', 5, 4, 'yes', 'cat_291', 'Rebion', 'sunlight glasses', 'goggles', 'yellow glasses'),
('krt455', 'T_Shirt', 'Yellow', 'S111_MH', 42, 2, 3000, 200, 'Nanded', '', 4, 8, 'yes', 'Fashion', 'being human', 'T_shirt with cap', '', 'being humans jeans'),
('P_1', 'Macbook', 'black', 'S123_KR', 14, 1, 120000, 7000, 'pune', 'yes', 5, 40, 'very good product', 'C_301', 'Apple', 'Product is very branded and good', 'Laptop', 'IPad'),
('P_3', 'Vivo_30', 'silver', 'S111_MH', 20, 2, 40000, 2000, 'Latur', 'Welll feat', 7, 20, 'well developed', 'Phones', 'Vivo', 'Very good phone camera 64 ram 8 gb', 'vivo', 'vivo-28'),
('P_490g', 'headphone', 'white', 'S111_MH', 0, 3, 700, 110, 'pune', 'yes', 5, 5, 'yes', 'cat_285', 'realme', 'white headphones', 'realme', 'realme ipods');

CREATE TABLE seller(sellerid varchar(100) NOT NULL PRIMARY KEY,Name varchar(200) NOT NULL,Mobileno int(11) NOT NULL,password varchar(200) NOT NULL,ProductCategory varchar(100) NOT NULL);

INSERT INTO seller VALUES
('er455k', 'Jully', 59545554, 'li9345n', 'Accessories'),
('kjhkj54', 'Danni', 345665656, 'ggj54t02', 'Laptop'),
('S-66jd', 'Javed', 483595847, 'ghd3435j', 'fashion'),
('S111_MH', 'Abhi', 8549505, 'V4590nd', 'Accessories'),
('S123_KR', 'Vinay', 84564865, '90hf3e', 'Phones');

END$$

DELIMETER;