Project 1

1)it is attached as file

2) create table ecommerce(
-> cus_login_id varchar(50),
-> cus_loginpassword varchar(50),
-> cus_gender varchar(15),
-> cus_captcha varchar(47),
-> cus_searchbox varchar(40));

create table customer(
-> customer_name varchar(40),
-> customer_gender varchar(10),
-> customer_dateofbirth int,
-> customer_city varchar(15),
-> customer_emailadress varchar(40),
-> customer_deliveryaddress varchar(100),
-> customer_pw varchar(40)
-> );

create table orders(
-> customer_order_id varchar(120),
-> customer_orderstatus varchar(10),
-> customer_order_delivery_status varchar(12),
-> customer_order_date varchar(12),
-> customer_delivery_date varchar(10),
-> customer_order_status varchar(15)
-> );

create table products(
-> product_name varchar(10),
-> product_color varchar(47),
-> product_seller varchar(7),
-> product_size_available varchar(7),
-> product_quantity_available varchar(41),
-> product_cost int(10),
-> product_discount int(7),
-> product_place_of_delivery varchar(40),
-> product_calculation varchar(7),
-> product_features varchar(7),
-> product_popularity varchar(41),
-> product_avg_rating varchar(4),
-> product_rating varchar(4),
-> product_review_country varchar(14),
-> product_on_sale varchar(1),
-> product_categories varchar(7),
-> product_bands varchar(4),
-> product_images varchar(1),
-> product_questions varchar(10),
-> product_description varchar(4),
-> product_tags varchar(4),
-> product_id varchar(4)
-> );

create table seller(
-> name_of_seller varchar(4),
-> mobile_num_of_seller int(10),
-> categories_of_products_sold varchar(10)
-> );

task3

1)

SELECT a.ord_no,a.purch_amt, b.cust_name,b.city FROM orders a,customer b WHERE a.customer_id=b.customer_id AND a.purch_amt BETWEEN 500 AND 2000;

2) ALTER TABLE table_name RENAME COLUMN old_column_name TO prod_;

3)

SELECT Count(item_id) order_count, customer_id, customer_name
FROM Customers
   INNER JOIN Orders ON Customers.customer_id = Orders.customer_id
   INNER JOIN Order_details ON Orders.order_id = Order_details.order_id
GROUP BY customer_id, customer_name
HAVING Count(item_id) > 1

4)SELECT Count(item_id) order_count, customer_id, customer_name
FROM Customers
   INNER JOIN Orders ON Customers.customer_id = Orders.customer_id
   INNER JOIN Order_details ON Orders.order_id = Order_details.order_id
GROUP BY customer_id, customer_name starts with A AND END with A
HAVING Count(item_id) > 1

5)SELECT customer_id, COUNT(DISTINCT ord_no), MAX(purch_amt) FROM orders GROUP BY customer_id ORDER BY 2 DESC;



6)

1. INSERT INTO table_name
2. VALUES (value1, value2, value3, ...);

1. UPDATE table_name
2. SET column1 = value1, column2 = value2, ...
3. WHERE condition;

DELETE FROM table_name WHERE condition;