Challenge

Project 1

SSafeek Raja29 Nov 2022

Task 1 - ER Diagram:

Task 2:

CREATE DATABASE eCommerceProject;

USE eCommerceProject;

CREATE TABLE customer(

cus_id INT PRIMARY KEY,

cus_name VARCHAR(30),

cus_pw VARCHAR(30),

cus_type VARCHAR(30),

cus_city VARCHAR(30),

cus_email VARCHAR(30),

cus_orders INT,

cus_address VARCHAR(50),
cus_orders INT

);

INSERT INTO customer

VALUES(01,"Antony","9876","Premium","Kallakurichi","[email protected]","No6. Vivekanandhar Strt.", 1),

(02,"Jancy","6789","Normal","Salem","[email protected]","No7. Periyar Strt.",3),

(03,"Jerome","4567","Premium","Karaikkudi","[email protected]","No8. Kaliyammal Strt.",4),

(04,"Mugil","1234","Premium","Coimbatore","[email protected]","No9. Kavitha Colony.",0);

SELECT * FROM customer;

CREATE TABLE orders(

order_id INT PRIMARY KEY,

cus_id INT,

order_cost INT,

order_date DATE,

delivery_date DATE,

order_status VARCHAR(20),

no_of_customers INT,

FOREIGN KEY (cus_id) REFERENCES customer(cus_id)

);

INSERT INTO orders(order_id,order_cost,order_date,delivery_date,order_status,no_of_customers)
VALUES(0101,12000,"2022-11-23","2022-11-30","Progressing",6),
(0102,1800,"2022-11-11","2022-11-20","Delivered",15),
(0103,25000,"2022-11-15","2022-11-23","Cancelled",1);

UPDATE orders SET cus_id = 1 WHERE order_id = 102;

UPDATE orders SET cus_id = 2 WHERE order_id = 103;

UPDATE orders SET cus_id = 3 WHERE order_id = 101;

CREATE TABLE seller(

seller_id INT PRIMARY KEY,

seller_name VARCHAR(20),

seller_pw VARCHAR(20),

mobile_no VARCHAR(20)

);

INSERT INTO seller

VALUES(10101,"Kumar","2343","9876543210"),

(10102,"Malathi","2444","7639865976"),

(10103,"Sengodan","2676","7936476244");

CREATE TABLE products(

product_id INT,

`name` VARCHAR(20),

cost INT,

isFeatured VARCHAR(10),

size VARCHAR(10),

category VARCHAR(20),

brand VARCHAR(20),

tag VARCHAR(20),

color VARCHAR(20),

quantity INT,

place_of_origin VARCHAR(20),

review VARCHAR(20)

);

INSERT INTO products

VALUES(1101,"Basket",150,"No","medium","Home_appliance","Kwality","plastic","Red",40,Salem,"Good"),

(1102,"Mobile Case",299,"Yes","Small","--","Rubit","case","Blue",30,"Chennai","Excellent");

CREATE TABLE admin(

admin_id INT PRIMARY KEY,

admin_name VARCHAR(20),

admin_pw VARCHAR(20)

);

INSERT INTO admin

VALUES(1111,"Vetri","3434"),(1112,"Velumani","1232"),(1113,"Ravi","4323");

CREATE TABLE cart(

cart_id INT,

payment INT,

tracking_id VARCHAR(20)

);

INSERT INTO cart

VALUES(10001,1299,"2343423"),(10002,1099,"3423423");

Task 3:

CREATE VIEW tempview AS (
SELECT c.*,
o.order_id, o.order_status, o.order_date, o.order_cost, o.delivery_date, o.no_of_customers
FROM customer c LEFT JOIN orders o ON c.cus_id = o.cus_id);

1. SELECT * FROM customer ORDER BY cus_city;

2. Renaming the coloumns name,

ALTER TABLE products CHANGE `cost` `pro_cost` INT;
ALTER TABLE products CHANGE `product_id` `pro_id` INT;
ALTER TABLE products CHANGE `name` `pro_name` VARCHAR(20);
ALTER TABLE products CHANGE `isFeatured` `pro_isFeatured` VARCHAR(10);
ALTER TABLE products CHANGE `size` `pro_size` VARCHAR(10);
ALTER TABLE products CHANGE `category` `pro_category` VARCHAR(20);
ALTER TABLE products CHANGE `brand` `pro_brand` VARCHAR(20);
ALTER TABLE products CHANGE `tag` `pro_tag` VARCHAR(30);
ALTER TABLE products CHANGE `color` `pro_color` VARCHAR(20);
ALTER TABLE products CHANGE `quantity` `pro_quantity` INT;
ALTER TABLE products CHANGE `place_of_origin` `pro_place_of_origin` VARCHAR(20);
ALTER TABLE products CHANGE review pro_review VARCHAR(20);

3. SELECT cus_name, cus_pw, cus_type, cus_city, cus_email, cus_address FROM tempview WHERE cus_orders > 0;

4. SELECT * FROM tempview WHERE cus_name LIKE 'A%' AND cus_orders > 1;

5. SELECT order_id, order_date, delivery_date, no_of_customers, order_cost, order_status FROM tempview WHERE order_status = "delivered" AND order_cost > 10000 AND no_of_customers > 1;

6. Statements,

create --> create table table_name(

column1 data_type,
column2 data_type
);

insert --> insert into table_name(columns) values(data);

alter --> alter table table_name
add/delete/modify column_name data_type;

Queries,