Challenge

Project 1

PPrakash R20 Nov 2022

/********************Project -1*************************/

/***********TASK-1&TASK-2**************************/

CREATE TABLE seller(NAME VARCHAR(20),seller_id INT, PRIMARY KEY(seller_id));

SELECT * FROM SELLER;
INSERT INTO seller VALUES('JOHN WICK',101);
INSERT INTO seller VALUES('EMMA WATSON',102);
INSERT INTO seller VALUES('KEANU REEVES',103);
COMMIT;

CREATE TABLE categories(category_NAME VARCHAR(20),category_id INT,
description VARCHAR(500), PRIMARY KEY(category_id));

SELECT * FROM CATEGORIES;

INSERT INTO CATEGORIES VALUES('ELECTRONICS',10,'....');
INSERT INTO CATEGORIES VALUES('JEWEKRY',20,'....');

CREATE TABLE order_status(status_id INT, status_name VARCHAR(10),PRIMARY KEY(status_id));

SELECT * FROM ORDER_STATUS;

INSERT INTO ORDER_STATUS VALUES(11,'PEND');
INSERT INTO ORDER_STATUS VALUES(22,'SHIPPED');
INSERT INTO ORDER_STATUS VALUES(33,'CANCELLED');
INSERT INTO ORDER_STATUS VALUES(44,'DELEVIRED');

CREATE TABLE user_type(user_type VARCHAR(10),type_id INT,PRIMARY KEY(type_id));

SELECT * FROM USER_TYPE;

INSERT INTO USER_TYPE VALUES('NORMAL',44);
INSERT INTO USER_TYPE VALUES('PREMIUM',55);

COMMIT;

CREATE TABLE customer(fname VARCHAR(10),lname VARCHAR(10),email VARCHAR(30),
PASSWORD VARCHAR(20),customer_id INT,city VARCHAR(10),dob DATE,username VARCHAR(20),
gender CHAR,type_id INT,
PRIMARY KEY(customer_id),FOREIGN KEY(type_id ) REFERENCES user_type(type_id) );

SELECT * FROM CUSTOMER;

INSERT INTO CUSTOMER VALUES('EMMA','STONE','[email protected]','1234','1001','NY',SYSDATE(),'EM','F',44);
INSERT INTO CUSTOMER VALUES('EMMA','WATSON','[email protected]','1234','1002','CA',SYSDATE(),'EM','F',55);

CREATE TABLE products(product_id INT,price INT,customer_id INT,
category_id INT,COUNT INT,NAME VARCHAR(20),description VARCHAR(500),
PRIMARY KEY(product_id),FOREIGN KEY(category_id)REFERENCES categories(category_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id));

SELECT * FROM PRODUCTS;

INSERT INTO PRODUCTS VALUES(2000,10000,1001,10,3,'MOBILE','....');
INSERT INTO PRODUCTS VALUES(3000,20000,1002,20,1,'DIAMOND','....');

COMMIT;

CREATE TABLE cart(cart_id INT,product_id INT,customer_id INT, COUNT INT,
PRIMARY KEY(cart_id),FOREIGN KEY(product_id)REFERENCES products(product_id),
FOREIGN KEY(customer_id)REFERENCES customer(customer_id));

SELECT * FROM CART;

INSERT INTO CART VALUES(1,2000,1001,1);
INSERT INTO CART VALUES(2,3000,1002,2);

CREATE TABLE orders(customer_id INT,order_id INT,seller_id INT,cart_id INT,
order_date DATE,status_id INT,
PRIMARY KEY(order_id),
FOREIGN KEY(status_id) REFERENCES order_status(status_id),
FOREIGN KEY(cart_id) REFERENCES cart(cart_id),
FOREIGN KEY(customer_id) REFERENCES customer(customer_id));

SELECT * FROM ORDERS;

INSERT INTO orders VALUES(1001,200,101,1,SYSDATE(),11);
INSERT INTO orders VALUES(1002,300,102,2,SYSDATE(),22);
INSERT INTO orders VALUES(1002,400,102,2,SYSDATE(),44);
INSERT INTO orders VALUES(1002,500,102,2,SYSDATE(),44);

CREATE TABLE payment(payment_id INT,product_id INT,quantity INT,
amount INT,payment_date DATE,customer_id INT,
PRIMARY KEY(payment_id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
FOREIGN KEY(customer_id) REFERENCES customer(customer_id));

SELECT * FROM PAYMENT;

INSERT INTO PAYMENT VALUES(60,2000,2,5999,SYSDATE(),1001);
INSERT INTO PAYMENT VALUES(70,3000,1,9999,SYSDATE(),1002);

COMMIT;

CREATE TABLE payment_offer (
id INT,
product_id INT,
card_type VARCHAR(20) ,
coupon_code VARCHAR(10) ,
discount_value INT,
create_date TIMESTAMP,
valid_from TIMESTAMP,
valid_until TIMESTAMP,
category_id INT,
PRIMARY KEY (id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
FOREIGN KEY(category_id) REFERENCES categories(category_id)
);

SELECT * FROM PAYMENT_OFFER;

INSERT INTO PAYMENT_OFFER VALUES(999,2000,'DEBIT','GCV67H',9990,SYSDATE(),SYSDATE(),SYSDATE(),10);
INSERT INTO PAYMENT_OFFER VALUES(777,3000,'CREDIT','OPL8XC',4440,SYSDATE(),SYSDATE(),SYSDATE(),20);

COMMIT;

/****************TASK-3**********************/
A)
SELECT * FROM CUSTOMER
ORDER BY CITY;

SELECT * FROM CUSTOMER;
SELECT * FROM PRODUCTS;

C)
SELECT A.* FROM CUSTOMER A,PRODUCTS B
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID;

D)
SELECT A.* FROM CUSTOMER A,PRODUCTS B
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID AND (A.USERNAME LIKE 'A%' AND A.USERNAME LIKE '%A')
HAVING COUNT(*)>1;

E)
SELECT DISTINCT A.*,C.USERNAME,K.STATUS_NAME FROM PRODUCTS A INNER JOIN ORDERS B
ON A.CUSTOMER_ID=B.CUSTOMER_ID
JOIN CUSTOMER C
ON B.CUSTOMER_ID=C.CUSTOMER_ID
JOIN PAYMENT D
ON C.CUSTOMER_ID=D.CUSTOMER_ID
JOIN ORDER_STATUS K
ON B.STATUS_ID=K.STATUS_ID
WHERE A.PRICE>10000
AND K.status_name='DELEVIRED'
HAVING COUNT(C.CUSTOMER_ID)>1;

F)
DELIMITER |
CREATE PROCEDURE SP_CREATE_TABLE_ECOMMERCE ()
BEGIN

CREATE TABLE categories(category_NAME VARCHAR(20),category_id INT,
description VARCHAR(500), PRIMARY KEY(category_id));

CREATE TABLE order_status(status_id INT, status_name VARCHAR(10),
PRIMARY KEY(status_id));

CREATE TABLE user_type(user_type VARCHAR(10),type_id INT,
PRIMARY KEY(type_id));

/**Populate data into table**/

INSERT INTO seller VALUES('JOHN WICK',101);
INSERT INTO seller VALUES('EMMA WATSON',102);
INSERT INTO seller VALUES('KEANU REEVES',103);

INSERT INTO CATEGORIES VALUES('ELECTRONICS',10,'....');
INSERT INTO CATEGORIES VALUES('JEWEKRY',20,'....');

INSERT INTO USER_TYPE VALUES('NORMAL',44);
INSERT INTO USER_TYPE VALUES('PREMIUM',55);

INSERT INTO PRODUCTS VALUES(2000,10000,1001,10,3,'MOBILE','....');
INSERT INTO PRODUCTS VALUES(3000,20000,1002,20,1,'DIAMOND','....');

INSERT INTO CART VALUES(1,2000,1001,1);
INSERT INTO CART VALUES(2,3000,1002,2);

INSERT INTO PAYMENT VALUES(60,2000,2,5999,SYSDATE(),1001);
INSERT INTO PAYMENT VALUES(70,3000,1,9999,SYSDATE(),1002);

END;

CALL SP_CREATE_TABLE_ECOMMERCE;

B)/***CHANGE COLUMN NAME FROM TABLE**/

SELECT * FROM PRODUCTS;

ALTER TABLE rating CHANGE stars STAR INT;

/***SOME COLUMN NAME WE ARE NOT ABLE TO CHANGE IT HENCE SOME COLUMNS ARE FOREIGN KEY**/

ALTER TABLE PRODUCTS CHANGE PRODUCT_ID PROD_ID INT;
ALTER TABLE PRODUCTS CHANGE PRICE PROD_PRICE INT;
ALTER TABLE PRODUCTS CHANGE CUSTOMER_ID PROD_CUSTOMER_ID INT;
ALTER TABLE PRODUCTS CHANGE CATEGORY_ID PROD_CATEGORY_ID INT;
ALTER TABLE PRODUCTS CHANGE COUNT PROD_COUNT INT;
ALTER TABLE PRODUCTS CHANGE NAME PROD_NAME VARCHAR(50);
ALTER TABLE PRODUCTS CHANGE DESCRIPTION PROD_DESCRIPTION VARCHAR(500);

Project 1

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.