Project 1

PROJECT 1

TASK : 1 & TASK : 2

USE ecommercewebsite;

CREATE TABLE customer(
cust_id INT PRIMARY KEY,
gender VARCHAR(50),
DOB DATE,
city VARCHAR(50),
email VARCHAR(255),
delivery_address VARCHAR(255),
current_order INT,
previous_order INT,
payment_info VARCHAR(255)
);

INSERT INTO customer VALUES
( 1001 , 'Female' , 23/09/1897 , 'Chennai', '[email protected]','ABC chitra nager chennai', 1 ,9 , 'Online payment ');
INSERT INTO customer VALUES
( 1002, 'Male', 07/08/1998 , 'madurai', ' [email protected] ','xyz kvs apartment madurai', 2 , 24 , 'Cash on Delivery');

CREATE TABLE payment(
payment_id INT PRIMARY KEY,
user_id INT,
payment_type VARCHAR(50),
account_no VARCHAR(50) ,
FOREIGN KEY(user_id) REFERENCES user_table(user_id)
);

INSERT INTO payment VALUES( 6789 , 101 , 'Online Payment', 'ABCD67796847989' );
INSERT INTO payment VALUES( 9876 , 102 , 'Cash on Delivery' , 'XYZG78543654469 ');

CREATE TABLE seller(
seller_name INT,
mobile_number DOUBLE,
seller VARCHAR(50),
categories VARCHAR(255),
FOREIGN KEY(categories) REFERENCES product(categories)
);

INSERT INTO seller ( 'Kiran', 09876543321 , 'Galaxy & iphone ','All items');
INSERT INTO seller ( ' sam' , 90765445342 , 'iphone', 'All items');

CREATE TABLE product(
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
size INT,
seller_id INT,
quantity FLOAT,
cost INT,
current_discount INT ,
order_Status VARCHAR(50),
avg_rating INT,
total_rating INT,
review_rating INT,
category VARCHAR(255),
brand VARCHAR(50),
discription VARCHAR(255),
tags INT
);

INSERT INTO product VALUES
( 907 , 'Iphone', 13 , 123456 , 0.567 , 14500 , 24 , 10 , 'Pending ' , 24 , 39 , 2 , 'Phone items' ,'Apple', 'Use it easily ', 1 );
INSERT INTO product VALUES
( 908 , 'Glaxy', 12 , 098767 , 2 , 15600 , 34 , 8 , 10 , 'delivery', 76, 65 , 43 , 'gloasary items ','Galaxy ', 'nice product', 2 );

CREATE TABLE orders(
order_id INT PRIMARY KEY,
order_status VARCHAR(50),
delivery_date DATE,
order_date DATE
);

INSERT INTO orders VALUES ( 101,'pending', 22/11/2022 , 12/11/2022);
INSERT INTO orders VALUES ( 102, 'Delivered', 21/11/2022 , 13/11/2022);

CREATE TABLE order_status(
order_status VARCHAR(50),
order_id INT,FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

CREATE TABLE coupon_table(
coupon_id INT PRIMARY KEY,
discount INT,
discount_type TIMESTAMP
);

INSERT INTO coupon_table VALUES( 9087 , 23 , 'coupon ');
INSERT INTO coupon_table VALUES ( 9088 , 24 , 'coupon type ');

CREATE TABLE coupon_code_table(
coupon_id INT PRIMARY KEY,
discount INT,
discount_type VARCHAR(50),
FOREIGN KEY(coupon_id) REFERENCES coupon_code_table(coupon_id)
);


CREATE TABLE shipments(
order_id INT,
shipment_id INT PRIMARY KEY,
payment_id INT,
shopping_address VARCHAR(255),
discount VARCHAR(50)
);

INSERT INTO shipments VALUES(101, 9090 , 65 , 'chennnai, tamilnadu' , '23% ');
INSERT INTO shipments VALUES(102, 9091 , 66 , ' white field,bangalore', ' 11%');

CREATE TABLE invoices(
shipment_id INT,
order_id INT,
invoice_date DATE,
shipment_id REFERENCES shipments(shipment_id);
);

CREATE TABLE user_table(
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_password VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
phone_number DOUBLE
);

INSERT INTO user_table VALUES ( 101,'kavipriya','abc@12','M','kavi', 9087654321);
INSERT INTO user_table VALUES ( 102, 'ramcharan','vbn@3','v','ram', 3214567890);

CREATE TABLE user_wishList(
user_id INT PRIMARY KEY,
FOREIGN KEY(user_id) REFERENCES user_wishList(user_id)
);

--------------------------------------------------------------------------------------------------------------

TASK : 3

1)

• SELECT orders.order_id , orders.order_status , orders.order_date, orders.delivery_date,
• customer.cust_id, customer.gender, customer.DOB, customer.city, customer.email,
• customer.deliver_address, customer.current_order, customer.previous_order,
• customer.payment_info
• FROM customer , orders
• WHERE customer.city = orders.order_id

2)

• RENAME TABLE product COLUMN order_id TO prod_product_id;
• RENAME TABLE product COLUMN order_id TO prod_product_name;
• RENAME TABLE product COLUMN order_id TO prod_size;
• RENAME TABLE product COLUMN order_id TO prod_seller;
• RENAME TABLE product COLUMN order_id TO prod_quantity;
• RENAME TABLE product COLUMN order_id TO prod_cost;
• RENAME TABLE product COLUMN order_id TO prod_current_discount;
• RENAME TABLE product COLUMN order_id TO prod_order_status;
• RENAME TABLE product COLUMN order_id TO prod_avg_rating;
• RENAME TABLE product COLUMN order_id TO prod_total_rating;
• RENAME TABLE product COLUMN order_id TO prod_review_rating;
• RENAME TABLE product COLUMN order_id TO prod_category;
• RENAME TABLE product COLUMN order_id TO prod_brand;
• RENAME TABLE product COLUMN order_id TO prod_description;
• RENAME TABLE product COLUMN order_id TO prod_tags;
• SELECT *
  FROM product ;

3)

• SELECT customer.Cust_id,customer.cust_name
• FROM customer
• LEFT JOIN orders on customer.cust_id=orders.cust_id
• HAVING Count(order_id)>=1;

4)

• SELECT *
• FROM customer
• WHERE cust_name LIKE 'A%A' AND current_order >= 1

6)

CREATE STATEMENT :

• create a table statement is used to create a new table in a database
• Syntax:
• CREATE TABLE table_name(
• column1 datatype,
• column2 datatype, ...
• );

INSERT STATEMENT :

• the INSERT INTO statement is used to insert new records in a table
• SYNTAX :
• INSERT INTO table_name(column1,column2,...) VALUES (value1,values2,...)
• INSERT INTO table_name VALUES (value1, value2,...)

ALTER STATEMENT :

• The alter table is used to add, delete or modify columns in a existing table
• ALTER TABLE -ADD COLUMN
• ALTER TABLE table_name ADD column_name datatype;
• ALTER TABLE- DROP COLUMN
• ALTER TABLE table_name DROP COLUMN column_name;
• ALTER TABLE -MODIFY COLUMN
• ALTER TABLE teble_name MODIFY COLUMN column_name datatype;

==================================================================