Project 1

Task.3: Write the SQL Queries for the below scenarios:

1. WAQ to fetch the details of the customers in the order of their city

Query: select city from customer;

2. WAQ to alter the table product by renaming all the columns by preceding them with 'prod_'

Query: ALTER TABLE product
rename column product_id to prod_id,
rename column product_name to prod_name,
rename column product_color to prod_color,
rename column sizesAvailable to prod_sizes,
rename column seller_details to prod_sizes,
rename column quantityOfProducts to prod_sizes,
rename column price to prod_price,
rename column discount to prod_discount,
rename column placeOfOrigin to prod_placeOfOrigin,
rename column avgRatings to prod_avgRatings,
rename column totalRatings to prod_totalRatings,
rename column reviewCountry to prod_reviewCountry,
rename column reviewsForRatings to prod_reviewsForRatings;

3. WAQ to fetch the details of customer who have purchased at least one product

Query: SELECT orders.order_id, customer.customer_name FROM orders,
customer WHERE orders.customer_id = customer.customer_id;

4. WAQ to fetch the details of the customer who have name starting from A and ending from A and has purchased more than one item

Query: select customer.* from customer customer, orders orders, product products
where customer.customer_id = orders.customer_id
and orders.order_id = products.product_id
group by products.product_id having count(products.product_id) > 1
and customer_name like 'A%A';

5. WAQ to all the information about the orders which are already delivered and has cost more than 10000 and also has been puchased by more than 1 customer.

Query: SELECT order_id, orderDate, product_id
FROM orders,product
WHERE order_status = 1
and cost > 10000
and 1<
(SELECT COUNT(*)
FROM customer
WHERE product_id=product.product_id) ;

6. Write a sipmle procedure to run all the table creation statements, insert statements and alter statements at once.

Answer- Create Syntax: CREATE TABLE table_name(column1 datatype, column2 datatype.........);

Insert Syntax: INSERT INTO table_name VALUES(value1, value2...............);

Alter Syntax: ALTER TABLE table_name RENAME old_table_name TO new_table_name;