Project 1

Ans :- 1

SELECT orders.customer_id,orders.order_id,
address.city AS City, COUNT(address.city) City_Order_Total_Count
FROM orders INNER JOIN address
USING(address_id)
GROUP BY address.city;

Ans :-2
ALTER TABLE product
CHANGE pro_name prod_name CHAR(50);
ALTER TABLE product
CHANGE pro_brand prod_brand CHAR(50),
CHANGE pro_num prod_num INT,
CHANGE pro_amount prod_amount DECIMAL(10,2),
CHANGE pro_desc prod_desc CHAR(100),
CHANGE pro_type prod_type CHAR(50);

Ans :-3

SELECT
person_info.fname,
person_info.lname,
person_info.gender,
person_info.email,
person_info.phone_number,
person_info.address_id,
orders.order_id
FROM person_info
JOIN customer
ON person_info.person_id = customer.info_id
JOIN orders
ON orders.customer_id = customer.customer_id;

Ans :-4
SELECT
person_info.fname,
person_info.lname,
person_info.gender,
person_info.email,
person_info.phone_number,
person_info.address_id,
orders.order_id
FROM person_info
JOIN customer
ON person_info.person_id = customer.info_id
JOIN orders
ON orders.customer_id = customer.customer_id
WHERE fname LIKE 'A%';

Ans :- 5

SELECT product.prod_amount,
product.prod_name,
product.product_id,
cart.pro_qty,
(product.prod_amount*cart.pro_qty) AS Total_Amount
FROM product
JOIN cart
ON product.product_id = cart.product_id
JOIN orders
ON orders.cart_id = cart.cart_id
WHERE (product.prod_amount*cart.pro_qty) > 10000;

Ans :-6

DELIMITER $$
CREATE PROCEDURE `Ecommerce`.`test`()
BEGIN
SELECT * FROM customer;
END$$
DELIMITER;

CALL test();

DELIMITER $$
CREATE PROCEDURE `Ecommerce`.`info`(IN info_id INT)
BEGIN
SELECT * FROM person_info WHERE person_id=info_id;
END$$
DELIMITER ;

CALL info(11);