Project 1

task 3:
1) SELECT customer_id AS id, gender, dob, city, name, email, delivery_addresses AS address, contacts, current_orders AS orders, previous_orders, payment_info_saved AS payment_info FROM customers ORDER BY city ASC;

2)ALTER TABLE products CHANGE COLUMN name prod_name VARCHAR(255), CHANGE COLUMN color prod_color VARCHAR(50), CHANGE COLUMN seller_id prod_seller_id INT, CHANGE COLUMN sizes_available prod_sizes_available VARCHAR(255), CHANGE COLUMN quantity_available prod_quantity_available INT, CHANGE COLUMN cost prod_cost DECIMAL(10, 2), CHANGE COLUMN current_discount prod_current_discount DECIMAL(5, 2), CHANGE COLUMN place_of_origin prod_place_of_origin VARCHAR(100), CHANGE COLUMN calculations prod_calculations TEXT, CHANGE COLUMN is_featured prod_is_featured BOOLEAN, CHANGE COLUMN popularity_index prod_popularity_index INT, CHANGE COLUMN avg_ratings prod_avg_ratings DECIMAL(3, 2), CHANGE COLUMN total_ratings prod_total_ratings INT, CHANGE COLUMN reviews_for_ratings prod_reviews_for_ratings INT, CHANGE COLUMN review_country prod_review_country VARCHAR(50), CHANGE COLUMN is_on_sale prod_is_on_sale BOOLEAN, CHANGE COLUMN categories prod_categories VARCHAR(255), CHANGE COLUMN brands prod_brands VARCHAR(255), CHANGE COLUMN images prod_images TEXT, CHANGE COLUMN questions_with_answers prod_questions_with_answers TEXT, CHANGE COLUMN description prod_description TEXT, CHANGE COLUMN tags prod_tags TEXT, CHANGE COLUMN related_products_ids prod_related_products_ids VARCHAR(255);

3)SELECT customers.customer_id AS id, customers.gender, customers.dob, customers.city, customers.name, customers.email, customers.delivery_addresses AS address, customers.contacts, customers.current_orders AS orders, customers.previous_orders, customers.payment_info_saved AS payment_info FROM customers join orders ON customers.customer_id=orders.customer_id;





4)SELECT customers.customer_id AS id, customers.gender, customers.dob, customers.city, customers.name, customers.email, customers.delivery_addresses AS address, customers.contacts, customers.current_orders AS orders, customers.previous_orders, customers.payment_info_saved AS payment_info FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN (SELECT customer_id, COUNT(*) AS total_items_purchased FROM Orders GROUP BY customer_id HAVING COUNT(*) > 1) t ON customers.customer_id = t.customer_id WHERE customers.name LIKE 'A%a';






5)SELECT orders.* FROM (SELECT order_id FROM orders WHERE status = 'delivered' GROUP BY order_id HAVING SUM( (SELECT prod_cost FROM Products WHERE Products.product_id = orders.order_id) ) > 10000) t JOIN orders ON t.order_id = orders.order_id JOIN ( SELECT order_id FROM orders GROUP BY order_id HAVING COUNT(DISTINCT customer_id) > 1) c ON orders.order_id = c.order_id;



6)click "new" under the database, enter the table name and select the no.of tables needed .then click go.
enter the column name in the "name" box and choose the specification for your table ,then click save button.
if you want to enter the values in the table columns , choose "insert" tab at the toggler button then enter the values on the "value" box,then click "go" ,it will save automaticallly.
for alter the table , we can use the "sql" tab. it contains a "insert " and "update" button. it is user for alter the table contents easily and simply.


or we can use this sql query for table creation
CREATE TABLE IF NOT EXISTS table_name ( column_name1 datatype PRIMARY KEY , column_name2 datatype NOT NULL, column_name3 datatype NOT NULL, );


sql query for table insertion
INSERT INTO table_name (column_name1, column_name2) VALUES ('values1...', 'values2...'), ('values1', 'value2');

sql query for table alter
ALTER TABLE table_name ADD COLUMN new_column datatype;