Project 1

1.

2.FILE ATTACHED

3.

1.

SELECT* FROM customer ORDER BY CITY ASC;

2.

ALTER TABLE products CHANGE COLUMN ID PROD_ID INTEGER;

ALTER TABLE products CHANGE COLUMN P_NAME PROD_NAME VARCHAR(20);

ALTER TABLE products CHANGE COLUMN P_COLOR PROD_COLOR VARCHAR(10);

ALTER TABLE products CHANGE COLUMN P_SIZE PROD_SIZE INTEGER;

ALTER TABLE products CHANGE COLUMN QUANTITY PROD_QUATITY INTEGER;

ALTER TABLE products CHANGE COLUMN DISCOUNT PROD_DISCOUNT DOUBLE;

ALTER TABLE products CHANGE COLUMN TOT_RAT PROD_TOT_RAT DOUBLE;

ALTER TABLE products CHANGE COLUMN RATE_REV _PROD_RATE_REV VARCHAR(50);

3.

SELECT * FROM `ORDERTABLE` AS O LEFT JOIN customer AS C ON O .CUS_ID=C.CUS_ID
ORDER BY C.CUS_ID ASC;

4.

SELECT * FROM customer AS C INNER JOIN order_table AS O WHERE(SELECT COUNT(*) FROM order_table WHERE C.CUS_ID=O.CUS_ID GROUP BY CUS_ID HAVING COUNT(*)>1 AND CUS_NAME LIKE 'A%');

5.

SELECT * FROM ordertable WHERE STATUS="DELIVERED" AND TOT_COST>10000;

6.

CREATE TABLE CUSTOMER(CUS_ID INTEGER PRIMARY KEY NOT NULL,CUS_NAME VARCHAR(20) NOT NULL,GENDER VARCHAR(1) NOT NULL,DOB DATE NOT NULL,CUS_PHN INTEGER,MAIL VARCHAR(20),D_NUM INTEGER,STREET VARCHAR(10),CITY VARCHAR(10),STATE VARCHAR(10),ZIPCODE INTEGER,CUS_TYPE VARCHAR(10),PREV_ORDER INTEGER);
INSERT INTO CUSTOMER VALUES(2,"SAFI","M","1990-05-21",8965230147,"[email protected]",10,"TAGORE STREET","COIMBATORE","TAMILNADU",698745,"NORMAL",2),(26,"HELENA","F","1998-01-13",8505896412,"[email protected]",15,"VICTORIA STREET","MADURAI","TAMILNADU",912365,"PREMIUM",1),(66,"ANGELINAMARIA","F","2004-10-12",9630215487,"[email protected]",32,"NEHRU STREET","CHENNAI","TAMILNADU",568410,"NORMAL",3),(51,"SHARON","F","1980-01-12",9514623078,"[email protected]",30,"MPM STREET","COIMBATORE","TAMILNADU",698745,"NORMAL",1),(96,"DAVIS","M","2001-8-10",8639520147,"[email protected]",20,"RPR STREET","KOTTAYAM","KERALA",852036,"PREMUIM",3)

CREATE TABLE `seller` (SELLERID int(11) PRIMARY KEY NOT NULL,S_NAME varchar(20) DEFAULT NULL,PSWRD varchar(10) NOT NULL,S_PHN int(10) DEFAULT NULL,S_MAIL varchar(20) DEFAULT NULL);
INSERT INTO seller VALUES
(3, "SHEIK", "OPTYU", 987123, "[email protected]"),
(26, "SHARIQ", "BJUGGD", 923541, "[email protected]");

CREATE TABLE prod_category (C_ID int(11) PRIMARY KEY NOT NULL,C_NAME varchar(20) DEFAULT NULL,BRAND varchar(20) DEFAULT NULL,IMAGE tinyint(1) NOT NULL,DESCRIPTION varchar(100) DEFAULT NULL,TAGS varchar(50) NOT NULL,QUES_ANS varchar(200) DEFAULT NULL);
INSERT INTO prod_category VALUES
(1, "BOOKS", "HarperCollins", 0,"No publishing list is complete without mentioning HarperCollins", "FRESH AND NEW", "WILL ONLY THE POEMS PUBLISHED?NO,they also publish on a range of topics both fiction and non-fiction"),
( 2, "WATCHES", "Omega", 1, "The new Aqua Terra collection by Omega", "Discover the stylish WatcheS", "Why is Omega watch famous?Omega has been the official timekeeper of the Olympic Games 28 times since 1932");

CREATE TABLE PRODUCTS(ID INTEGER PRIMARY KEY NOT NULL,C_ID INTEGER,SELLERID INTEGER,P_NAME VARCHAR(20),P_COLOR VARCHAR(10),P_SIZE INTEGER,QUANTITY INTEGER,DISCOUNT DOUBLE,PROD_FROM VARCHAR(10),TOT_RAT DOUBLE,RATE_REV VARCHAR(50));

INSERT INTO products VALUES(23,1,26,"WATCHMEN","ORANGE",420,19,0.25,"USA",4.8,"THE BEST BOOK EVERYONE NEED TO READ"),(60,2,26,"KID'S WATCH","MAGENTA",75,70,0.50,"INDONESIA",5,"LIKES BY KID'S),(61,2,3,"MEN'S WATCH","BLACK",110,15,0.10,"USA",4.2,"GOOD QUALITY"),(56,1,3,"PENGUIN RANDOM HOUSE","GREEN",750,10,0.12,"INDONESIA",4.5,"THE BEST COMIC BOOK");

CREATE TABLE CART(CART_ID INTEGER PRIMARY KEY NOT NULL,CUS_ID INTEGER,ID INTEGER,P_NAME VARCHAR(20),DATE DATE,FOREIGN KEY(CUS_ID)REFERENCES customer(CUS_ID),FOREIGN KEY(ID)REFERENCES products(ID));

INSERT INTO CART VALUES(1,2,60,"KID'S WATCH","2023-05-30"),(2,66,56,"PENGUIN RANDOM HOUSE","2023-05-26"),(3,96,23,"WATCHMEN","2023-05-29"),(4,26,61,"MEN'S WATCH","2023-05-14"),(5,66,60,"KID'S WATCH","2023-06-01");

CREATE TABLE ORDER_TABLE(ORD_NUM INTEGER PRIMARY KEY NOT NULL,CART_ID INTEGER,CUS_ID INTEGER,ID INTEGER,ORD_DATE DATE,STATUS VARCHAR(10),COST INTEGER,EXP_DEL_DATE DATE,FOREIGN KEY(CART_ID)REFERENCES CART(CART_ID),FOREIGN KEY(CUS_ID)REFERENCES customer(CUS_ID),FOREIGN KEY(ID)REFERENCES products(ID));

INSERT INTO ORDER_TABLE VALUES(10,1,2,60,"2023-05-30","ORDER PLACED",550,"2023-06-02"),(15,5,66,60,"2023-6-01","DELIVERED",15050,"2023-06-02");

ALTER TABLE ORDER_TABLE ADD COLUMN QUANTITY INTEGER;

CREATE TABLE PAYMENT(PAY_ID INTEGER PRIMARY KEY,ORD_NUM INTEGER,CUS_ID INTEGER,MODE VARCHAR(10),STATUS VARCHAR(15),FOREIGN KEY(ORD_NUM)REFERENCES ORDER_TABLE(ORD_NUM),FOREIGN KEY(CUS_ID)REFERENCES CUSTOMER(CUS_ID));

INSERT INTO PAYMENT VALUES(11,15,66,"ONLINE","SUCCESS"),(12,10,2,"ONLINE","SUCCESS");