Project 1
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('venky',10,'M'),
INSERT INTO ADMIN(ad_name,ad_id,ad_gender)VALUES('harika',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 ('chandra',15-11-1980,'yes',123,'hyd',65490,'watch',9999998888,2/3/1980-hyd,'chandra456@gmail','amazon information',807,'watch');
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','large',2,35%,'good','most people buyed',3-10-2022,'red','revanth',280);
INSERT INTO seller (sel_id,sel_name,sel_mobile_no,sel_categories,prt_name,prt_id)VALUES
('teja'35,9999999999,'shirtss','shirt'480),
VALUES ('bhargav',29,8888888888,'bottom wear','phant'108);
INSERT INTO premium (pre_id, pre_password) VALUES (10,4678), VALUES (90,3578);
INSERT INTO cart (prt_name,prt_id) VALUES ('naveen',80), VALUES('vicky',29);
INSERT INTO tracking (tra_id,prt_id) VALUES (102,340), VALUES (190,56);
INSERT INTO orders (ord_date,ord_billing,ord_delivary_addres,ord_completed,ord_status_cancelled,ord_info,ord_delivary,tra_id) VALUES
(10-9-2020,'upi','3-1-238/bangalore','yes','no','correct',20-9-2020,260),
VALUES (10-5-2000,'cash','1-89-2000-pune','pending','no','not correct',24-5-2000,78);
INSERT INTO rating (cust_id,prt_id,prt_name) VALUES (2,9,'t-shirts'), VALUES(90,10,'hoodie');
INSERT INTO review (rev_id,rev_name,rev_rating,prt_id,prt_name) VALUES (467,'sravya',3,420,'bangles'), VALUES (100,'ravi',45,134,'ring');
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()
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('ram',358650,'pune','maharastra');
ALTER TABLE customers CHANGE customerName cust NAME VARCHAR(40);