List of Practice SQL Queries With Answers For Product Table
In this blog post you can find list of practice SQL Queries With Answers For Product Table. If you are working closely with the Sales Team, they would always ask the Database Developer Questions related to Product Sales and Quantity. This List of practice SQL Queries for Product Table can help you overcome such problems.
As a database developer, writing SQL queries, PLSQL code is part of daily life. Having a good knowledge on SQL is really important. Here i am posting some practical examples on SQL queries.
1
S.YEAR,
S.QUANTITY,
LEAD(S.QUANTITY,1,0) OVER (
PARTITION BY P.PRODUCT_ID
ORDER BY S.YEAR DESC
) QUAN_PREV_YEAR
FROM PRODUCTS P,
SALES S
WHERE P.PRODUCT_ID = S.PRODUCT_ID;
FROM
(
SELECT P.PRODUCT_NAME,
S.QUANTITY –
LEAD(S.QUANTITY,1,0) OVER (
PARTITION BY P.PRODUCT_ID
ORDER BY S.YEAR DESC
) QUAN_DIFF
FROM PRODUCTS P,
SALES S
WHERE P.PRODUCT_ID = S.PRODUCT_ID
)A
GROUP BY PRODUCT_NAME
HAVING MIN(QUAN_DIFF) >= 0;
2
Write a SQL query to find the products which have continuous increase in sales every year?
FROM PRODUCTS P
LEFT OUTER JOIN
SALES S
ON (P.PRODUCT_ID = S.PRODUCT_ID);
WHERE S.QUANTITY IS NULL
FROM PRODUCTS P
WHERE P.PRODUCT_ID NOT IN
(SELECT DISTINCT PRODUCT_ID FROM SALES);
FROM PRODUCTS P
WHERE NOT EXISTS
(SELECT 1 FROM SALES S WHERE S.PRODUCT_ID = P.PRODUCT_ID);
3
Write a SQL query to find the products whose sales decreased in 2018 compared to 2017?
FROM PRODUCTS P,
SALES S_2018,
SALES S_2017
WHERE P.PRODUCT_ID = S_2012.PRODUCT_ID
AND S_2012.YEAR = 2018
AND S_2011.YEAR = 2017
AND S_2012.PRODUCT_ID = S_2017.PRODUCT_ID
AND S_2018.QUANTITY < S_2017.QUANTITY;
4
Write a query to select the top product sold in each year?
SELECT PRODUCT_NAME,
YEAR
FROM
(
SELECT P.PRODUCT_NAME,
S.YEAR,
RANK() OVER (
PARTITION BY S.YEAR
ORDER BY S.QUANTITY DESC
) RNK
FROM PRODUCTS P,
SALES S
WHERE P.PRODUCT_ID = S.PRODUCT_ID
) A
WHERE RNK = 1;
5
Write a query to find the total sales of each product.?
SELECT P.PRODUCT_NAME,
NVL( SUM( S.QUANTITY*S.PRICE ), 0) TOTAL_SALES
FROM PRODUCTS P
LEFT OUTER JOIN
SALES S
ON (P.PRODUCT_ID = S.PRODUCT_ID)
GROUP BY P.PRODUCT_NAME;
6
Write a query to find the products whose quantity sold in a year should be greater than the average quantity sold across all the years?
SELECT P.PRODUCT_NAME,
S.YEAR,
S.QUANTITY
FROM PRODUCTS P,
SALES S
WHERE P.PRODUCT_ID = S.PRODUCT_ID
AND S.QUANTITY >
(SELECT AVG(QUANTITY)
FROM SALES S1
WHERE S1.PRODUCT_ID = S.PRODUCT_ID
);
7
Write a query to find the number of products sold in each year?
SELECT YEAR,
COUNT(1) NUM_PRODUCTS
FROM SALES
GROUP BY YEAR;
