Queries.sql

--1) Select all columns and all rows from one table
SELECT * FROM PRODUCT;

--2) Select five columns and all rows from one table
SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS, DOB FROM CUSTOMER;

--3) Select all columns from all rows from one view
Select * From Wishlist_View;

--4) Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product 

SELECT * 
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID;

--5) Select and order data retrieved from one table
SELECT *
FROM CUSTOMER
ORDER BY DOB;

--6) Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows

SELECT P.PRODUCT_ID, O.ORDER_ID, P.PRODUCT_NAME, O.ORDER_DATE, OI.QUANTITY
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
INNER JOIN PRODUCT P
ON  OI.PRODUCT_ID = P.PRODUCT_ID
FETCH FIRST 10 ROWS ONLY;

--7) Select distinct rows using joins on 3 tables

SELECT DISTINCT P.PRODUCT_ID, O.ORDER_ID, P.PRODUCT_NAME, O.ORDER_DATE, OI.QUANTITY
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
INNER JOIN PRODUCT P
ON  OI.PRODUCT_ID = P.PRODUCT_ID;

--8) Use GROUP BY and HAVING in a select statement using one or more tables
SELECT O.ORDER_DATE, OI.QUANTITY
FROM ORDERS O INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
GROUP BY O.ORDER_DATE, OI.QUANTITY
HAVING OI.QUANTITY > 2;

--9) Use IN clause to select data from one or more tables
SELECT ORDER_ID, PRODUCT_ID
FROM ORDER_ITEMS
WHERE QUANTITY IN (5 ,6, 7);

--10) Select length of one column from one table (use LENGTH function)
SELECT LENGTH(ARTICLE_DESCRIPTION) 
FROM ARTICLE;

/*--11) Delete one record from one table. 
Use select statements to demonstrate the table contents before and after the DELETE statement. 
Make sure you use ROLLBACK afterwards so that the data will not be physically removed*/

Select * from Customer;

Delete from Customer
where Customer_ID = 202372;

Select * from Customer;

Rollback;


/*--12) Update one record from one table.
Use select statements to demonstrate the table contents before and after the UPDATE statement.
Make sure you use ROLLBACK afterwards so that the data will not be physically removed */

Select * from Customer;

Update Customer
Set LAST_Name = 'Brooks'
where Customer_ID = 202372;

Select * from Customer;

Rollback;

--13) List all the articles that are linked to atleast one product.
SELECT A.ARTICLE_ID, COUNT(*)
FROM ARTICLE A INNER JOIN PRODUCT P
ON A.ARTICLE_ID = P.ARTICLE_ID
GROUP BY A.ARTICLE_ID
ORDER BY COUNT(*) DESC; 

--14)List all the products that have previous_count greater than the average previous_count of all the products.
SELECT PRODUCT_ID, PREVIOUS_COUNT, (SELECT ROUND(AVG(PREVIOUS_COUNT)) FROM PRODUCT) AS AVERAGE
FROM PRODUCT
WHERE PREVIOUS_COUNT >=(SELECT ROUND(AVG(PREVIOUS_COUNT)) FROM PRODUCT);

--15)LIST DETAILS OF CUSTOMERS WHO HAVE ORDERED PRODUCTS WHICH ARE SPONSORED AN ARTICLE ID 100.
SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, O.ORDER_ID FROM CUSTOMER C
LEFT JOIN ORDERS O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
LEFT JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
LEFT JOIN PRODUCT P
ON P.PRODUCT_ID = OI.PRODUCT_ID
WHERE P.PRODUCT_ID = ANY(SELECT P.PRODUCT_ID FROM PRODUCT P JOIN ARTICLE A ON P.ARTICLE_ID = A.ARTICLE_ID WHERE A.ARTICLE_ID = 100);


--16)LIST DETAILS OF CUSTOMERS WHO HAVE ORDERED PRODUCTS WHICH ARE NOT SPONSORED BY ANY ARTICLE.
SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, O.ORDER_ID FROM CUSTOMER C
LEFT JOIN ORDERS O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
LEFT JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
LEFT JOIN PRODUCT P
ON P.PRODUCT_ID = OI.PRODUCT_ID
WHERE P.PRODUCT_ID = (SELECT PRODUCT_ID FROM PRODUCT WHERE ARTICLE_ID IS NULL);

--17)LIST DETAILS OF CUSTOMERS WHO HAVE PRODUCTS IN THEIR WISHLIST THAT WERE ORDERED ATLEAST ONCE PREVIOUSLY, PRINT PREVIOUS COUNT AND NUMBER OF PRODUCTS.
SELECT P.PREVIOUS_COUNT,C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME,COUNT(*) AS COUNT
FROM CUSTOMER C INNER JOIN WISHLIST W
ON C.CUSTOMER_ID = W.CUSTOMER_ID
INNER JOIN PRODUCT P
ON W.PRODUCT_ID = P.PRODUCT_ID
GROUP BY P.PREVIOUS_COUNT,C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
HAVING P.PREVIOUS_COUNT>0
ORDER BY P.PREVIOUS_COUNT;

--18)SELECT CUSTOMER AND ORDER DETAILS OF ALL ORDERS PLACED AFTER 2021.

SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, O.ORDER_ID, O.ORDER_DATE, P.PRODUCT_NAME, P.BRAND, OI.QUANTITY
FROM CUSTOMER C
LEFT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
LEFT JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
LEFT JOIN PRODUCT P ON P.PRODUCT_ID = OI.PRODUCT_ID
WHERE EXTRACT(YEAR FROM O.ORDER_DATE) >= 2021
ORDER BY O.ORDER_DATE DESC;

--19)Displaying the total revenue earned from each product type

SELECT P.PRODUCT_TYPE, SUM(OI.QUANTITY * OI.DISCOUNT * P.PREVIOUS_COUNT) AS REVENUE FROM PRODUCT P
JOIN ORDER_ITEMS OI ON P.PRODUCT_ID = OI.PRODUCT_ID 
JOIN ORDERS O ON OI.ORDER_ID = O.ORDER_ID 
GROUP BY P.PRODUCT_TYPE 
ORDER BY REVENUE DESC;

--20)Customer and Product details of coustomers who ordered MORE THAN 5 in the month of Sept 2023

SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, P.PRODUCT_NAME, P.BRAND FROM CUSTOMER C 
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID 
JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID 
JOIN PRODUCT P ON P.PRODUCT_ID = OI.PRODUCT_ID WHERE C.CUSTOMER_ID IN 
(SELECT CUSTOMER_ID FROM ORDERS
WHERE EXTRACT(YEAR FROM O.ORDER_DATE)= 2023 AND EXTRACT(MONTH FROM O.ORDER_DATE)= 09 
GROUP BY CUSTOMER_ID 
HAVING SUM(O.ORDER_TOTAL) >= 5);