Project 1
ANSWER FOR TASK-1 and TASK-2
CREATE DATABASE QUERY
1.)CREATE DATABASE e-commerce;
------------------------------------------------------------------------------------------------------------------------------------
PRODUCT TABLE CREATION
2.)CREATE TABLE Product(name varchar(50),color varchar(50),seller varchar(50),sizes_available int(5),quantity_available int(200),cost int(10),current_discount int(4),place_of_origin_for_delivery varchar(150),calculations float(m,d),total_ratings int(10),review_for_ratings int(20),review_country int(30),products varchar(40),brands varchar(35),descriptions varchar(85));
-------------------------------------------------------------------------------------------------------------------------------------
CUSTOMER TABLE CREATION
3.) CREATE TABLE Customer(gender varchar(15),dob varchar(20),city varchar(25),name varchar(20),email varchar(30),pw varchar(10),delivery_addresses varchar(50),contacts number(15),current_and_previous_orders varchar(20));
-----------------------------------------------------------------------------------------------------------------------------------
ORDERS TABLE CREATION
4.) CREATE TABLE Orders(status_of_the_order varchar(20),delivery_date varchar(15),order_date varchar(15));
---------------------------------------------------------------------------------------------------------------------------------
SELLER TABLE CREATION
5.) CREATE TABLE Seller(name varchar(20),mobile_no number(15),password varchar(25),categories_of_products_provided varchar(50));
----------------------------------------------------------------------------------------------------------------------------------
1.)PRODUCTS TABLE INSERTION
INSERT INTO Product(name,color,seller,sizes_available,quantity_available,cost,current_discount,place_of_origin_for_delivery,calculations,total_ratings,review_for_ratings,review_country,brands,descriptions) VALUES ("computer","silver","Raja",12,35,49000,3000,Bangalore,2000.89,25,45,1,Dell,"12 inch displays,500gb disk,8gb RAM etc..);
------------------------------------------------------------------------------------------------------------------------------------
2.) CUSTOMER TABLE INSERTION
INSERT INTO Customer(gender,dob,city,name,email,pw,delivery_addresses,contacts) VALUES ("male","12-12-1976","Bangalore","Muthu","[email protected]","abcdefgh","hosur,old bus stand",9876543210);
------------------------------------------------------------------------------------------------------------------------------------
3.)ORDERS TABLE INSERTION
INSERT INTO Orders(status_of_the_order,delivery_date,order_date) VALUES ("Shipped","10-08-2023","01-07-2023");
---------------------------------------------------------------------------------------------------------------------------------------
4.)SELLER TABLE INSERTION
INSERT INTO Seller(name,mobile_no,password) VALUES ("Sivakumar",1234567890,"siva12345");
--------------------------------------------------------------------------------------------------------------------------------------
ANSWER FOR TASK-3
QUERIES
1.) SELECT * FROM CUSTOMERS order by city;
----------------------------------------------------------------------------------------------------------------------------------
2.) ALTER TABLE Product RENAME COLUMN name TO prod_name,RENAME COLUMN color TO prod_color,RENAME COLUMN seller TO prod_seller,RENAME COLUMN brands TO prod_brands,RENAME COLUMN descriptions TO prod_descriptions;
----------------------------------------------------------------------------------------------------------------------------------
3.) SELECT * FROM Customer WHERE purchased_products=1;
----------------------------------------------------------------------------------------------------------------------------------
4.) SELECT * FROM Customer WHERE name LIKE 'A%A' AND purchased_products > 1;
-----------------------------------------------------------------------------------------------------------------------------------
5.) SELECT * FROM Orders WHERE status_of_order='delivered' AND cost > 10000 AND purchased_product > 1;
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------**********************************-----------------------------------------
-----------------------------------------------**********************************-----------------------------------------
-----------------------------------------------**********************************-----------------------------------------