Project 1
Task1:
TABLE CREATION
CREATE TABLE admin(
ad_name VARCHAR(20),
ad_id INT,
ad_gender VARCHAR(20),
PRIMARY KEY(ad_name)
);
CREATE TABLE customer(
cust_name CHAR(20),
cust_dob DATE,
cust_premium CHAR(20),
cust_normal CHAR(20),
cust_city CHAR(20),
cust_password INT,
cust_order INT,
cust_contact INT,
cust_deliva_address CHAR(20),
cust_email CHAR(20),
cust_info CHAR(20),
prt_name CHAR(20),
prt_id INT,
PRIMARY KEY(cust_name),
FOREIGN KEY(prt_name)REFERENCES products (prt_name),
FOREIGN KEY (prt_id) REFERENCES products(prt_id)
);
CREATE TABLE products(
prt_name CHAR(20),
prt_size CHAR(20),
prt_quentity CHAR(20),
prt_discount CHAR(20),
prt_rev_name CHAR(20),
prt_rating INT(20),
prt_popularity CHAR(20),
prt_delivary CHAR(10),
prt_colour CHAR(5),
sel_name CHAR(5),
sel_id INT,
PRIMARY KEY(prt_name),
FOREIGN KEY(sel_name)REFERENCES seller(sel_name),
FOREIGN KEY(sel_id)REFERENCES seller(sel_id),
);
CREATE TABLE seller(
sel_id INT,
sel_name CHAR(20),
sel_mobile_no INT,
sel_categories CHAR(20),
prt_id INT,
prt_name CHAR(5),
FOREIGN KEY(prt_id)REFERENCES products(prt_id),
FOREIGN KEY(prt_name)REFERENCES products(prt_name)
);
CREATE TABLE premium(
pre_id INT,
pre_passaword INT,
PRIMARY KEY(pre_id)
);
CREATE TABLE cart(
prt_name CHAR(10),
prt_id INT,
PRIMARY KEY(prt_name)
);
CREATE TABLE traking(
tra_id INT,
prt_id INT,
PRIMARY KEY(tra_id)
);
CREATE TABLE orders(
ord_date DATE,
ord_billing CHAR(20),
ord_delivary_address CHAR(10),
ord_completed CHAR(10),
ord_satus_cancelled CHAR(20),
ord_info CHAR(20),
tra_id INT,
ord_delivary_date CHAR(20);
PRIMARY KEY(ord_date),
FOREIGN KEY(tra_id)REFERENCES traking(tra_id)
);
CREATE TABLE rating(
cust_id INT,
prt_id INT,
prt_name CHAR(5),
PRIMARY KEY(cust_id),
FOREIGN KEY(prt_id) REFERENCES products(prt_id),
FOREIGN KEY(prt_name)REFERENCES products(prt_name)
);
CREATE TABLE review(
rev_id INT,
rev_name CHAR(5),
rev_rating INT,
prt_id INT,
prt_name CHAR(10),
PRIMARY KEY (rev_id),
FOREIGN KEY(prt_id)REFERENCES products(prt_id),
FOREIGN KEY(prt_name)REFERENCES products(prt_name)
);
Task2:
INSERTING DATA INTO THE TABLES
INSERT INTO admin(ad_name,ad_id,ad_gender)VALUES('Deepu',10,'M'),
INSERT INTO admin(ad_name,ad_id,ad_gender)VALUES('Hope',11,'F');
INSERT INTO customer(cust_name,cust_dob,cust_premium,cust_normal,cust_city,cust_password,cust_order_cust_contact,cust_delivary_addres,cust_email,cust_infr,prt_id,prt_name) VALUES ('Deepu',11-02-1999,'amazon'-111,123,'vijayawada'433,'watch',4356789356,24-56/'vijayanagar','deepu@123','amazon information',155,'raj');
INSERT INTO products (prt_name,prt_size,prt_quantity,prt_discount,prt_rating,prt_popularity,prt_delivary,prt_colour,sel_name,sel_id) VALUES ('shirt','Medium',4,20%,'good','high selling product',2-7-2000,'prakash',39);
INSERT INTO seller (sel_id,sel_name,sel_mobile_no,sel_categories,prt_name,prt_id)VALUES('prakash'39,234567890,'product cast quality','shirt'40), VALUES ('mukesh',41,456789023,'cost','t-shirt'38);
INSERT INTO premium (pre_id, pre_password) VALUES (56,1106), VALUES (47,4106);
INSERT INTO cart (prt_name,prt_id) VALUES ('mamatha',78), VALUES('nissi',42);
INSERT INTO tracking (tra_id,prt_id) VALUES (222,39), VALUES (234,44);
INSERT INTO orders (ord_date,ord_billing,ord_delivary_addres,ord_completed,ord_status_cancelled,ord_info,ord_delivary,tra_id) VALUES (12-06-1997,'money',32-18/'varangal','yes','no','correct',13-6-2002,53), VALUES (13-07-1998,'cash',52-19/'maharastra','no','no','wrong',16-2-2003,58);
INSERT INTO rating (cust_id,prt_id,prt_name) VALUES (66,77,'shirts'), VALUES(88,99,'chains');
INSERT INTO review (rev_id,rev_name,rev_rating,prt_id,prt_name) VALUES (156,'ghanesh',5,2424,'saares'), VALUES (173,'kamala',7,6789,'bags');
QUERIES
1] SELECT *FROM customers ORDER BY cust_city;
2]ALTER TABLE product
RENAME COLUMN prt_name TO prod_name;
RENAME COLUMN id TO prod_id;
RENAME COLUMN city TO prod_city;
RENAME COLUMN size TO prod_size;
RENAME COLUMN discount TO prod_discount;
RENAME COLUMN ratimg TO prod_rating;
RENAME COLUMN popularity TO prod_popularity;
RENAME COLUMN delivary TO prod_delivary;
3]SELECT Customers.cust_name,orders.order_name,Customers.cust_id FROM customers INNER JOIN ORDER ON customer.cust_id=order.cust_id;
4]SELECT Customers.cust_nam,cust_lname,cust_fname FROM customers INNER JOIN orders ON customer.cust_id=order.cust_id WHERE customer.cust_lname,cust_fname LIKE 'A%A';
5]SELECT*FROM ORDER GROUP BY cust_id HAVING COUNT(cust_id)>1 WHERE order_amt>10000 AND STATUS='delivered';
USE ecommercedatabase;
6]DELIMITER $$
CREATE PROCEDURE GetCustomersNew()
BEIGN
CREATE TABLE customer(
cust_name VARCHAR(20),
cust_id INT,
cust_city VARCHAR(20),
cust_state VARCHAR(20),
PRIMARY KEY(cust_name)
);
INSERT INTO customer(cust_name,cust_id,cust_city,cust_state)VALUES('ramesh',808080,'banglur','UK');
ALTER TABLE customers CHANGE customerName cust NAME VARCHAR(40);
END$$