Project 1

1)

SELECT `coupon`.`code`, `coupon`.`percentage`, `coupon`.`discountRule`
FROM `coupon`

2)

SELECT `orders`.`id`, `orders`.`status`, `orders`.`invoiceId`
FROM `orders`

3)

SELECT `customer`.`name`, `product`.`id` AS `product id`, `product`.`name` AS `product on wishlist`
FROM `customer`
INNER JOIN `wishlist` ON `wishlist`.`cId` = `customer`.`id`
INNER JOIN `product` ON `wishlist`.`pId` = `product`.`id`;

4)

SELECT id,
CASE replacement
WHEN 'y' THEN 'YES'
WHEN 'n' THEN 'NO'
END AS `replacement status`
FROM orders;

5)

SELECT `product`.`id`, `product`.`name`, `reviews`.`review`
FROM `reviews`
INNER JOIN `product n reviews` ON `product n reviews`.`r_id` = `reviews`.`id`
INNER JOIN `product` ON `product n reviews`.`p_id` = `product`.`id`;

6)

SELECT `product`.*, `category`.`categories`, `qna`.`questions`, `qna`.`answer`, `coupon`.`percentage`, `reviews`.`review`
FROM `category`
INNER JOIN `product categories` ON `product categories`.`caId` = `category`.`id`
INNER JOIN `product` ON `product categories`.`pId` = `product`.`id`
INNER JOIN `product n coupoun` ON `product n coupoun`.`p_id` = `product`.`id`
INNER JOIN `coupon` ON `product n coupoun`.`c_id` = `coupon`.`id`
INNER JOIN `product n qna` ON `product n qna`.`p_id` = `product`.`id`
INNER JOIN `qna` ON `product n qna`.`q_id` = `qna`.`id`
INNER JOIN `product n reviews` ON `product n reviews`.`p_id` = `product`.`id`
INNER JOIN `reviews` ON `product n reviews`.`r_id` = `reviews`.`id`;

7)

SELECT `x`.id, x.name, x.email, x.dob, x.gender, x.address, x.city, x.contacts, x.orderId,
CASE `status`
WHEN 'delivered' THEN 'prev order'
WHEN 'cancelled' then 'Prev order'
ELSE 'current order'
END AS prevorcurrent
FROM (
SELECT `customer`.*, `orders`.`status`, `orders`.`id` AS orderId
FROM orders
INNER JOIN `customer order` ON `customer order`.`oId` = `orders`.`id`
INNER JOIN `customer` ON `customer order`.`cId` = `customer`.`id`
)x;

8)

SELECT `orders`.`id`, `orders`.`status`, `orders`.`orderdate`, `orders`.`deliverydate`
FROM `orders`

9)

SELECT `seller`.*, `category`.`categories` AS `categories of products provided`
FROM `category`
INNER JOIN `seller category` ON `seller category`.`caId` = `category`.`id`
INNER JOIN `seller` ON `seller category`.`sId` = `seller`.`id`;

The Report and the SQL file has been attached