Project 1
Task 1:-
-- create database ecommerse for website
CREATE DATABASE IF NOT EXISTS Ecommerse;
-- select this database
USE Ecommerse;
-- creating customer table and putting all coloumns there
CREATE TABLE customer(
cus_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(cus_id),
NAME VARCHAR(200) NOT NULL,
email VARCHAR(200),
cus_password VARCHAR(200),
gender ENUM('f','m','o'),
phone VARCHAR(12),
pincode VARCHAR(6),
address VARCHAR(200),
city VARCHAR(100),
country VARCHAR(100)
);
-- for insertind data in customer
INSERT INTO customer(NAME,email,cus_password,gender,phone,pincode, address, city, country) VALUES
("vikas","[email protected]","asdfjkl;",'m',"9782250356","302028","323 arya campus ","Jaipur","India"),
("customer_2","[email protected]","passwordss","f","123456789","341536","13/3 andhari", "mumbai","India"),
("customer_3","[email protected]","abcdef","m","987456123","457865","123 local area", "banglore","india"),
("customer_4","[email protected]","abcdefgh","f","123445566","302027", "34/3 local streat", "chennai","India"),
("ustomer_5","[email protected]","password1234","m","9782250378","125678","air force area", "Jodhpur","India"),
("ana","[email protected]","abs","f","9788855456","302028","123 abksjfk","Jaipur","India");
-- to fetch all data from customer table
SELECT * FROM customer;
CREATE TABLE seller(
seller_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(seller_id),
NAME VARCHAR(200) NOT NULL,
email VARCHAR(200),
sel_password VARCHAR(200),
gender ENUM('f','m','o'),
phone VARCHAR(12),
pincode VARCHAR(6),
address VARCHAR(200),
city VARCHAR(100),
country VARCHAR(100)
);
-- for inserting data in seller table
INSERT INTO seller (NAME,email,sel_password,gender,phone,pincode,address,city,country) VALUES
("seller_1","[email protected]","asdfjkl;",'m',"7882250356","302028","323 arya campus ","Jaipur","India"),
("seller_2","[email protected]","passwordss","f","123756789","341536","13/3 andhari", "mumbai","India"),
("seller_3","[email protected]","abcdef","m","989856123","457865","123 local area", "banglore","india"),
("seller_4","[email protected]","abcdefgh","f","123785566","302027", "34/3 local streat", "chennai","India"),
("seller_5","[email protected]","password1234","m","1182250378","125678","air force area", "Jodhpur","India");
-- for fetching all data from seller table
SELECT * FROM seller;
-- creating admin table with its attribute
CREATE TABLE admin(
admin_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(admin_id),
NAME VARCHAR(200) NOT NULL,
adm_password VARCHAR(200)
);
-- for inserting data into admin table
INSERT INTO admin (NAME, adm_password) VALUES
("admin_1","password123"),
("admin_3","1234pass"),
("admin_2","abcd1234"),
("admin_4","name4321");
-- for fetching all the data from admin table
SELECT * FROM admin;
-- this query use to create structure of category table
CREATE TABLE category(
category_id INT(5) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(category_id),
sub_category VARCHAR(100),
description VARCHAR(100)
);
-- below query to insert data into category table
INSERT INTO category (sub_category,description) VALUES
("mans cloths","here we all the colthes like pent, shirt, t-shit for men"),
("shoes","footwear for all persons"),
("category_3","this is the desciption for category_3"),
("category_4","this is the desciption for category_4"),
("category_5","this is the desciription for category_5");
-- to see all data from category table use below query
SELECT * FROM category;
-- below query create the structrue for product table
CREATE TABLE product(
prod_id INT (5) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(prod_id),
prod_name VARCHAR(100),
prod_price INT,
prod_size VARCHAR(3),
quantity_available INT(5),
seller_id INT,
category_id INT,
color VARCHAR(50),
FOREIGN KEY(seller_id) REFERENCES seller(seller_id),
FOREIGN KEY(category_id) REFERENCES category(category_id)
);
DESCRIBE product;
-- use below query to insert data into product
INSERT INTO product (prod_name,prod_price,prod_size,quantity_available,seller_id,category_id,color) VALUES
("product_1",2000,"s",50,1,1,"black"),
("product_2",3500,"m",150,1,5,"yellow"),
("product_3",799,"m",10,2,3,"red"),
("product_4",999,"l",120,3,3,"purple"),
("product_5",15999,"xl",5,4,5,"green"),
("product_6",149,"s",99,4,4,"black"),
("product_7",299,"s",55,4,2, "orange"),
("proudct_8",499,"xxl", 100, 3,2,"red");
-- to get all data from product
SELECT * FROM product;
CREATE TABLE tags(
tag_id INT(5) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(tag_id),
tag_1 VARCHAR(50),
tag_2 VARCHAR(50),
tag_3 VARCHAR(50),
prod_id INT(5),
FOREIGN KEY(prod_id) REFERENCES product(prod_id)
);
-- use below query to insert data into tags
INSERT INTO tags (prod_id,tag_1,tag_2,tag_3) VALUES
(1,"fashinable","wearable","cotton"),
(2,"footwear","sports","shoes"),
(3,"electronic","rechargable","home"),
(4,"electronic","hair","drying"),
(5,"gadget","daily","wearable"),
(6,"daily","rechargable","wireless"),
(7,"wireless","battery","computer"),
(8,"cotton","daily","long");
-- to show all the data from tags table
SELECT * FROM tags;
-- create structure for cart table
CREATE TABLE cart(
cart_id INT(5) NOT NULL AUTO_INCREMENT,
prod_id INT (5),
quantity INT (5),
PRIMARY KEY (cart_id),
FOREIGN KEY(prod_id) REFERENCES product(prod_id)
);
-- to insert data into cart
INSERTINTO cart ( prod_id, quantity ) VALUES
(1,1),
(2,1),
(3,4),
(6,2);
-- to show all data from cart
SELECT * FROM cart;
-- create struectre for rating table
CREATE TABLE rating (
rating_id INT(5) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(rating_id),
cus_id INT(10),
num_of_stars INT(1),
feedback VARCHAR(200),
prod_id INT(5),
FOREIGN KEY(cus_id) REFERENCES customer(cus_id),
FOREIGN KEY(prod_id) REFERENCES product(prod_id)
);
-- use below query to insert data into rating table
INSERT INTO rating ( cus_id,prod_id,num_of_stars,feedback) VALUES
(1,1,9,"good very best product"),
(2,1,5," not so much good, fine under this range"),
(3,4,0,"don't like product not good"),
(5,2,8,"good product, get as aspected");
-- show data from rating
SELECT * FROM rating;
-- create sturecture for payment_info table
CREATE TABLE payment_info(
payment_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(payment_id),
card_type ENUM("debit","credit"),
card_numb VARCHAR(16),
card_name VARCHAR(100),
card_cvv VARCHAR(3),
cus_id INT (10),
FOREIGN KEY (cus_id) REFERENCES customer(cus_id)
);
ALTER TABLE payment_info ADD FOREIGN KEY (cus_id) REFERENCES customer(cus_id);
-- below query to insert data into payment_info table
INSERTINTO payment_info ( card_type, card_numb, card_name,card_cvv,cus_id) VALUES
("debit","1234567891236547","Vikas soni","789",1),
("credit","454687891518785","Person_2","179",1),
("debit","454545787989775","Person_2", "234",3),
("debit","787878787878787","Person_3", "779",2),
("credit","12121212121121","Person_4", "123",2);
-- to show all availabe details of payment_ info
SELECT * FROM payment_info;
-- create table structrue for orders table
CREATE TABLE orders(
order_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(order_id),
payment_id INT(10),
cus_id INT(10),
order_date DATE,
delivery_date DATE,
cart_id INT(5),
FOREIGN KEY(cart_id) REFERENCES cart(cart_id),
FOREIGN KEY(payment_id) REFERENCES payment_info(payment_id),
FOREIGN KEY(cus_id) REFERENCES customer(cus_id),
delivery_status ENUM("y","n"),
cost INT (10)
);
ALTER TABLE orders ADD cost INT(10);
-- use below to insert data into order table
INSERT INTO orders( payment_id,cus_id,order_date,delivery_date,cart_id, delivery_status,cost) VALUES
(1,1,'2022-11-11','2022-12-1',1,"y",10000),
(2,3,'2022-11-11','2022-11-15',2,"y",15000),
(3,2,'2022-11-12','2022-12-2',3,"n",2500),
(4,5,'2022-12-2','2022-12-10',4,"n"2999),
(3,6,'2022-10-28','2022-11-5',3,"y",349);
-- to see all data use below query
SELECT * FROM orders;
Task 3:-
-- Task 3 --
-- question 1 solution:-
SELECT * FROM customer ORDER BY city;
-- question 2 solution
ALTER TABLE product CHANGE id prod_id INT(5);
ALTER TABLE product CHANGE NAME prod_name VARCHAR(100);
ALTER TABLE product CHANGE price prod_size VARCHAR(3);
ALTER TABLE product CHANGE color prod_color VARCHAR(50);
ALTER TABLE product CHANGE quantity prod_quantity INT(5);
-- question 3 solution:-
SELECT c.name,c.email,c.phone,o.order_date,o.delivery_date
FROM customer c
INNER JOIN orders o
ON c.cus_id = o.cus_id;
-- question 4 solution:-
SELECT c.name,c.email,c.phone,o.order_date,o.delivery_date
FROM customer c
INNER JOIN orders o
ON c.cus_id = o.cus_id
WHERE c.name LIKE 'a%a';
-- question 5 solution:-
SELECT c.name, o.order_id, o.order_date, o.delivery_date, o.delivery_status, o.cost
FROM customer c
INNER JOIN orders o
ON c.cus_id = o.cus_id
WHERE delivery_status = "y" AND cost >10000
-- question 6 solution:-
-- the below statement create procedure
DELIMITER $$
CREATE PROCEDURE `ecommerse`.`runall_procedure`()
BEGIN
-- put all the quries here, i puting some here for example
CREATE TABLE customer(
cus_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(cus_id),
NAME VARCHAR(200) NOT NULL,
email VARCHAR(200),
cus_password VARCHAR(200),
gender ENUM('f','m','o'),
phone VARCHAR(12),
pincode VARCHAR(6),
address VARCHAR(200),
city VARCHAR(100),
country VARCHAR(100)
);
-- for insertind data in customer
INSERT INTO customer(NAME,email,cus_password,gender,phone,pincode, address, city, country) VALUES
("vikas","[email protected]","asdfjkl;",'m',"9782250356","302028","323 arya campus ","Jaipur","India"),
("customer_2","[email protected]","passwordss","f","123456789","341536","13/3 andhari", "mumbai","India"),
("customer_3","[email protected]","abcdef","m","987456123","457865","123 local area", "banglore","india"),
("customer_4","[email protected]","abcdefgh","f","123445566","302027", "34/3 local streat", "chennai","India"),
("ustomer_5","[email protected]","password1234","m","9782250378","125678","air force area", "Jodhpur","India"),
("ana","[email protected]","abs","f","9788855456","302028","123 abksjfk","Jaipur","India");
CREATE TABLE seller(
seller_id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(seller_id),
NAME VARCHAR(200) NOT NULL,
email VARCHAR(200),
sel_password VARCHAR(200),
gender ENUM('f','m','o'),
phone VARCHAR(12),
pincode VARCHAR(6),
address VARCHAR(200),
city VARCHAR(100),
country VARCHAR(100)
);
-- for inserting data in seller table
INSERT INTO seller (NAME,email,sel_password,gender,phone,pincode,address,city,country) VALUES
("seller_1","[email protected]","asdfjkl;",'m',"7882250356","302028","323 arya campus ","Jaipur","India"),
("seller_2","[email protected]","passwordss","f","123756789","341536","13/3 andhari", "mumbai","India"),
("seller_3","[email protected]","abcdef","m","989856123","457865","123 local area", "banglore","india"),
("seller_4","[email protected]","abcdefgh","f","123785566","302027", "34/3 local streat", "chennai","India"),
("seller_5","[email protected]","password1234","m","1182250378","125678","air force area", "Jodhpur","India");
-- some alter commands llike
ALTER TABLE seller CHANGE email seller_email VARCHAR(200);
ALTER TABLE seller CHANGE phone mobile_number varcahr(12);
END$$
DELIMITER;
-- this statement use to run procedure
CALL runall_procedure();