Project 1

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

ANS:

SELECT * FROM customers ORDER BY city;

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

ANS:

ALTER TABLE product RENAME COLUMN 'name','color','seller','sizes_available','quantity' TO 'prod_name','prod_color',prod_seller','prod_sizes_available','pro_quantity';

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

ANS:

SELECT name,geneder,dob,city FROM customers WHERE currentAndPrevOders > 0;

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

ANS:
SELECT name,geneder,dob,city FROM customers WHERE (name LIKE '%A' OR 'A%) AND ( currentAndPrevOders > 0);

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

ANS:
SELECT * FROM orders INNER JOIN customers ON orders.order_ID = customers.customer_ID WHERE status = "dellivered" AND cost >10000 AND currentAndPrevOders >1;

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

Ans:

DELIMITER$$

CREATE PROCEDURE create_table (tablename VARCHAR(50))

BEGIN

CREATE TABLE tablename;

END$$

DELIMITER;

---------------------------------------------------------------------------------------------------------

DELIMITER$$

CREATE PROCEDURE insert_statement(tablename VARCHAR(50),values VARCHAR(255))

BEGIN

INSERT INTO tablename VALUES values;

END$$

DELIMITER;