Ecommerce table

TASK 1:

USE Ecommerce;

CREATE TABLE Cart(
Cart_id VARCHAR(7),
date_added VARCHAR(8),
quantity_wished VARCHAR(1),
PRIMARY KEY(Cart_id));

CREATE TABLE Customer(
cust_id VARCHAR(6)NOT NULL,
cust_fname VARCHAR(10)NOT NULL,
cust_lname VARCHAR(20)NOT NULL,
cust_email VARCHAR(20)NOT NULL,
cust_del_address VARCHAR(20)NOT NULL,
cust_dob DATE,
cust_password VARCHAR(7),
cust_gender VARCHAR(1),
cust_city VARCHAR(20),
PRIMARY KEY(cust_id),
Cart_id VARCHAR(7)NOT NULL,
FOREIGN KEY(Cart_id)REFERENCES Cart(Cart_id)
);
INSERT INTO customer VALUES(111,'Kailash', 'Rajkumar', '[email protected]' ,'btmlayout banglore', 14-02-1999, 6789,'m');

SELECT * FROM customer;

CREATE TABLE Seller(
Seller_id VARCHAR(6)NOT NULL,
seller_name VARCHAR(20)NOT NULL,
seller_email VARCHAR(10)NOT NULL,
seller_contact number INT(10),NOT NULL,
PRIMARY KEY(seller_id)
);
INSERT INTO seller VALUES(112, 'nitish', '[email protected]', 9894989488);
SELECT * FROM Seller;

CREATE TABLE Product(
product_id INT,
product_name VARCHAR(20),
prod_cus_id INT,
product_items INT,
product_color VARCHAR(8),
product_size VARCHAR(5),
product_cost INT,
product_quantity INT,
pro_type VARCHAR(20),
product_total_ratings VARCHAR(20),
product_reviews INT,
product_origin VARCHAR(10),
PRIMARY KEY(product_id));

INSERT INTO Product VALUES(222, 'samsung s22' , 324, 121, 'black', 'five inches', 35000, 1, 'Dual', 'four stars', 45, 'china');
SELECT * FROM product;

CREATE TABLE Payment(
pay_id INT,
pay_amt INT,
pay_date DATE,
PRIMARY KEY(pay_id));

INSERT INTO Payment VALUES(345,35000,13_06_2022);
SELECT * FROM payment;

CREATE TABLE Orders(
ord_id INT,
order_status VARCHAR(50),
order_delivery_expected VARCHAR(20),
ord_desc VARCHAR(10),
ord_type VARCHAR(10),
PRIMARY KEY(ord_id));

INSERT INTO Orders VALUES(545,'cancelled/ordered/packed/shipped/delivered','today/tomorrow','50%','cod');
SELECT * FROM Orders;

CREATE TABLE shipping address(
shipping_id INT,
billing_name VARCHAR(20),
billing_number INT,
street,colony VARCHAR(50),
phone_number INT,
pin INT,
PRIMARY KEY(shipping_id));

INSERT INTO shipping address VALUES(647,'ABCD',768,'BTMLAYOUT', 'BANGLORE', 9898878798,500084);
SELECT * FROM shipping address;

TASK 3:

1)SELECT customers.customer_id,customers_city FROM ORDER of the city INNER JOIN Orders ON customers.customer_city=order.orders_city;

2)ALTER TABLE table_name product BY RENAME COLUMN product TO prod_;

3)SELECT * FROM Customers WHERE CustID IN (SELECT CustID FROM Purchases WHERE productID='prod1');

4)SELECT *FROM customers c,orders o,order_details od,WHERE c.customer_id=o.customer_id AND o.order_id=od.order_id

GROUP BY od.order_id HAVING COUNT(od.item_id)>1;

5)SELECT customer_id,MAX(order_price)AS highestPurchase FROM order_details GROUP BY customer_id ORDER BY MAX(order_price)>1;

6)INSERT INTO table_name (column_list1,column_list2)VALUES(column_values1,column_values2);

INSERT INTO table_name VALUES(column_values);

INSERT INTO destination_table(column_list,..)
SELECT column_list FROM source_table WHERE CONDITION;

INSERT INTO Customers(Customers_name,contact_num,address,city)VALUES('nitish',767898678,'karur','tn');
SELECT * FROM customers;

INSERT INTO country(country_name,country_code)VALUES(india,+91);
SELECT * FROM country;

INSERT INTO city(city_name,city_code)VALUES('tn',123);
SELECT * FROM city;