Project 1
-- 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();