Project 1
CREATE TABLE ADMIN(admin_id INT,admin_pw VARCHAR(100),admin_num INT,city VARCHAR(100),admin_name VARCHAR(300),Email VARCHAR(100));
INSERT INTO ADMIN VALUES(12345,"ani@123",12,"medchal","Anilkumar","[email protected]");
INSERT INTO ADMIN VALUES(12345,"ani@123",12,"medchal","Anilkumar","[email protected]");
INSERT INTO ADMIN VALUES(12345,"ani@123",12,"medchal","Anilkumar","[email protected]");
CREATE TABLE customers(cust_id INT,sal_id INT,cust_name VARCHAR(255),cust_city VARCHAR(255),cart VARCHAR(300),
delivery_add VARCHAR(300),payment VARCHAR(300),wishlist(200),membership VARCHAR(100),
cust_grade INT,FOREIGN KEY (sal_id)REFERENCES salesman(sal_id),cust_address(300),PRIMARY KEY (cust_id),orders VARCHAR(300) );
INSERT INTO customers VALUES(1,20,"anitha","medchall","A","Hyderabad medchal","online","good","yes",1,"hyderabad","books");
INSERT INTO customers VALUES(1,20,"anitha","medchall","A","Hyderabad medchal","online","good","yes",1,"hyderabad","shoes");
INSERT INTO customers VALUES(1,20,"anitha","medchall","A","Hyderabad medchal","online","good","yes",1,"hyderabad","bangles");
SELECT * FROM customers;
2.CREATE TABLE salesmans(sal_id INT,sal_name VARCHAR(255),sal_city VARCHAR(255),sal_commission DOUBLE(10,2),
sell_gen VARCHAR(100),sell_num INT,sell_pw VARCHAR(200),email VARCHAR (200),);
INSERT INTO salesmans VALUES(20,"ab","pune","10.5","female","120","ani@123","[email protected]");
INSERT INTO salesmans VALUES(20,"ab","hyd","10.5","female","130","ani@124","[email protected]");
INSERT INTO salesmans VALUES(20,"ab","wgl","10.5","female","140","ani@125","[email protected]");
ALTER orders MODIFY COLUMN FOREIGN KEY sal_id REFERENCES salesman(sal_id);
ALTER orders MODIFY COLUMN FOREIGN KEY cust_id REFERENCES customer(cust_id);
ALTER ORDER MODIFY COLUMN ord_date;
SELECT *FROM salesmans;
CREATE TABLE orders(ord_no INT,purch_amt INT,ord_date VARCHAR(255),
customer_id INT FOREIGN KEY (cust_id)REFERENCES customer(cust_id) ,sal_id INT FOREIGN KEY (sal_id) REFERENCES salesman(sal_id));
ALTER orders MODIFY COLUMN FOREIGN KEY sal_id REFERENCES salesman(sal_id);
ALTER orders MODIFY COLUMN FOREIGN KEY cust_id REFERENCES customer(cust_id);
INSERT INTO orders VALUES(1,100,2022/10/12,3002,5001);
INSERT INTO orders VALUES(2,200,2022/10/13,3007,5005);
INSERT INTO orders VALUES(3,400,2022/10/14,3004,5006);
INSERT INTO orders VALUES(4,300,2022/10/15,3008,5007);
INSERT INTO orders VALUES(5,500,2022/10/16,3009,5003);
INSERT INTO orders VALUES(6,600,2022/10/17,3003,5001);
SELECT *FROM orders;
CREATE TABLE product(pro_id INT,pro_cus_id INT,pro_desc VARCHAR(100),pro_items VARCHAR(100),pro_type VARCHAR(100),pro_num INT,pro_cost INT,pro_colour,pro_size,);
INSERT INTO product VALUES(01,10,"stationary items","java book","books",100,1000);
INSERT INTO product VALUES(02,20,"dresses","tshirts pants","cloths",200,500);
INSERT INTO product VALUES(03,30,"bangles","one gram gold","gold bangles",100,600);
INSERT INTO product VALUES(04,40,"house things","cottons","door cottons",100,400);
INSERT INTO product VALUES(05,50,"shoes","sports shoes","without lays",100,200);
INSERT INTO product VALUES(06,60,"earings","long earingis","gold",100,600);
INSERT INTO product VALUES(07,70,"watches","titanic","silver",100,700);
INSERT INTO product VALUES(08,80,"perfumes","fogg","white colour",100,800);
INSERT INTO product VALUES(09,90,"mobiles","note ten","white colour",100,9000);
SELECT * FROM product;
CREATE TABLE shopping(shp_id INT,'shp_name varchar(255)','shp_type varchar(255)','shp_desc varchar(255)',shp_date INT);
INSERT INTO shopping VALUES(001,"amezon","books","offline",12-10-2000);
INSERT INTO shopping VALUES(002,"amezon","cloths","online",13-10-2000);
INSERT INTO shopping VALUES(003,"amezon","foot wear","online",14-10-2000);
INSERT INTO shopping VALUES(004,"amezon","grocery","offline",15-10-2000);
INSERT INTO shopping VALUES(005,"amezon","earings","offline",16-10-2000);
INSERT INTO shopping VALUES(006,"amezon","bangles","online",22-10-2000);
INSERT INTO shopping VALUES(007,"amezon","rings","offline",23-10-2000);
INSERT INTO shopping VALUES(008,"amezon","mobiles","online",12-10-2000);
INSERT INTO shopping VALUES(009,"amezon","kids wears","offline",12-10-2000);
SELECT * FROM shopping;
CREATE TABLE payment(pay_id INT,pay_amt INT,pay_stu_id INT,pay_date DATE,pay_desc VARCHAR(255));
INSERT INTO payment VALUES(23,2000,20,12-10-2000,"online");
INSERT INTO payment VALUES(24,1000,21,13-11-2000,"online");
INSERT INTO payment VALUES(25,3000,22,14-12-2000,"online");
INSERT INTO payment VALUES(26,4000,23,15-6-2000,"online");
INSERT INTO payment VALUES(27,5000,24,16-5-2000,"online");
INSERT INTO payment VALUES(28,6000,25,17-4-2000,"online");
INSERT INTO payment VALUES(29,7000,26,18-8-2000,"online");
INSERT INTO payment VALUES(22,8000,27,19-9-2000,"online");
INSERT INTO payment VALUES(21,9000,28,20-2-2000,"online");
SELECT * FROM payment;
CREATE TABLE cart(cart_id INT,pro_id INT,LIST OF products VARCHAR(300));
INSERT INTO cart VALUES(1002,01,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1003,02,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1004,03,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1005,04,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1006,05,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1007,06,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1008,07,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1009,08,"books,grocery,cloths,footwears");
INSERT INTO cart VALUES(1001,09,"books,grocery,cloths,footwears");
SELECT * FROM cart;
CREATE TABLE cart_item(cust_id INT,pro_id INT,cart-item_quality VARCHAR);
INSERT INTO cart_item VALUES(3001,01,"good");
INSERT INTO cart_item VALUES(3001,01,"not good");
INSERT INTO cart_item VALUES(1,01,"ok good");
INSERT INTO cart_item VALUES(2,02,"bad");
INSERT INTO cart_item VALUES(3,03,"good");
INSERT INTO cart_item VALUES(4,04,"not bad");
INSERT INTO cart_item VALUES(5,06,"good");
INSERT INTO cart_item VALUES(6,05,"just good");
INSERT INTO cart_item VALUES(7,07,"good");
CREATE TABLE customer Details(DOB INT,cust_pw VARCHAR(100),cust_gen VARCHAR(100),Email VARCHAR(100),cust_num INT,);
INSERT INTO customer Details VALUES(12-10-2002,"abc@123","male","[email protected]",123);
INSERT INTO customer Details VALUES(12-11-2002,"abc@124","female","[email protected]",123);
INSERT INTO customer Details VALUES(12-12-2002,"abc@1235","male","[email protected]",123);
CREATE TABLE ORDER Details(ord_dt INT,delivery_dt INT,STATUS VARCHAR(100));
INSERT INTO ORDER Details VALUES(01-01-2001,02-01-2001,"medchal");
INSERT INTO ORDER Details VALUES(01-02-2001,04-02-2001,"medchal");
INSERT INTO ORDER Details VALUES(01-03-2001,02-03-2001,"medchal");
CREATE TABLE Replacements(Rep_pro_id INT,Refund INT,Replacement _id VARCHAR (200),Refund_amt VARCHAR(200));
INSERT INTO Replacements VALUES(123,1000,"abc","No");
INSERT INTO Replacements VALUES(1235,2000,"pqr","yes");
INSERT INTO Replacements VALUES(1234,3000,"xyz","yes");
CREATE TABLE Reviews(Rev_stars VARCHAR(100),Avg_rev_stars VARCHAR(200),cust_id INT FOREIGN KEY cust_id REFERENCES customer(cust_id),
sal_id INT FOREIGN KEY sal_id REFERENCES salesman(sal_id));
INSERT INTO Reviews VALUES("****","*****/100",123,120);
INSERT INTO Reviews VALUES("****","***/100",125,120);
INSERT INTO Reviews VALUES("****","****/100",124,120);
CREATE TABLE Ratings(Stars VARCHAR(100),avg_stars VARCHAR(100),cust_id INT FOREIGN KEY cust_id REFERENCES customer(cust_id),
pro_id INT FOREIGN KEY pro_id REFERENCES product(pro_id);
INSERT INTO Ratings VALUES("****","****/100",123,120);
INSERT INTO Ratings VALUES("*****","*****/100",124,130);
INSERT INTO Ratings VALUES("***","***/100",125,140);
CREATE TABLE Discount(Discount_id INT,CODE VARCHAR(100),Discount INT,Discount_per VARCHAR(100),
sell_id INT,pro_id INT FOREIGN KEY pro_id REFERENCES productDetails(pro_id));
INSERT INTO Discount VALUES(1000,10%,"abc#214");
INSERT INTO Discount VALUES(2000,20%,"abc#215");
INSERT INTO Discount VALUES(3000,30%,"abc#216");
CREATE TABLE Tags(Tag_id INT,Quetions VARCHAR(100),image_num INT,Description_num INT,popularity_index VARCHAR(100),is_featured VARCHAR(100),is_on_sale VARCHAR(100),Related_prod_id INT);,pro_id INT FOREIGN KEY pro_id REFERENCES product(pro_id),Brand_name VARCHAR(100));
INSERT TABLE Tags VALUES(1,"how is it?",15,20,"good","well runnig","yes this is good",12,"LG);
INSERT TABLE Tags VALUES(2,"how is it looking?",15,20,"good","well runnig","yes this is good",13,"SAMSANG");
INSERT TABLE Tags VALUES(3,"how is it working?",15,20,"good","well runnig","yes this is good",14,"IFB");
CREATE TABLE Product Details(supplier VARCHAR(100),subcategory_name VARCHAR(200),Brand_name VARCHAR(200),
Origin VARCHAR(100),Category_name VARCHAR(200),pro_id INT,Admin_id INT,Tag_id INT);
INSERT INTO Product Details VALUES("anitha","mobils","note 10s","MI","amazone","electronic","123","Ani122");
INSERT INTO Product Details VALUES("anitha","mobils","note 10s","MI","amazone","electronic","124","Ani122");
INSERT INTO Product Details VALUES("anitha","mobils","note 10s","MI","amazone","electronic","125","Ani122");
CREATE TABLE currentdiscount(Discount_id INT,Discount VARCHAR(200),pro_id INT);
INSERT INTO currentdiscount(1240,"20%",12);
INSERT INTO currentdiscount(1250,"30%",13);
INSERT INTO currentdiscount(1260,"40%",14);
CREATE TABLE categories(category VARCHAR(200),sub_category VARCHAR(200));
INSERT INTO categories VALUES("Books","note books",12);
INSERT INTO categories VALUES("grocery","oil",13);
INSERT INTO categories VALUES("cusmotics","fair and lovley",14);
TASK3:
1.SELECT city ,group_concat(customerName) as all_customer
FROM customers group by city;
2.ALTER TABLE product CHANGE oldcolname newcolname datatype(length);
3.SELECT COUNT (customer_code) FROM customer AS c INNER JOIN order_info AS oi ON(c.customer_code=oi.customer_code)GROUP BY customer_code HAVING COUNT(id_order)=1;
4.SELECT customer_name, COUNT(DISTINCT product_ID) as products_count FROM customer_table
INNER JOIN orders_table ON customer_table.customer_ID = orders_table.customer_ID
GROUP BY customer_table.customer_ID, HAVING customer_name
HAVING COUNT(DISTINCT product_ID) >2
5.SELECT customer_name, COUNT(DISTINCT product_ID) as products_count FROM customer_table
INNER JOIN orders_table ON customer_table.customer_ID = orders_table.customer_ID
GROUP BY customer_table.customer_ID, customer_name
HAVING COUNT(DISTINCT product_ID) > 10000;
6.create table table name (columns); like this using database
CREATE TABLE customer(cust_id INT,sal_id INT,cust_name VARCHAR(255),ciy VARCHAR(255),grade INT,FOREIGN KEY (sal_id)REFERENCES salesman(sal_id) );
insert into table name values(columns);like this values using data types.
INSERT INTO movie VALUES(915,'Titanic',1997,194,'English','1998-01-23','UK');
ALTER TABLE rating RENAME COLUMN num_o_ratings TO num_of_ratings;