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

Write a SQL query to find the products which have continuous increase in sales every year?
Step 1: Get the previous year sales for each product Using the Following SQL Query.
SELECT P.PRODUCT_NAME,
                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;
Step 2: Find the difference between the quantities of a product with its previous year’s quantity.
SELECT PRODUCT_NAME 
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?

Method 1: Using left outer join
SELECT P.PRODUCT_NAME
            FROM PRODUCTS P
            LEFT OUTER JOIN
            SALES S
ON (P.PRODUCT_ID = S.PRODUCT_ID);
WHERE S.QUANTITY IS NULL
Method2Using the NOT IN operator
SELECT P.PRODUCT_NAME
FROM PRODUCTS P
             WHERE P.PRODUCT_ID NOT IN
             (SELECT DISTINCT PRODUCT_ID FROM SALES);
Method3: Using the NOT EXISTS operator.
SELECT P.PRODUCT_NAME 
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?

Solution:
SELECT P.PRODUCT_NAME
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?

Solution:

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.?

Solution:

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?

Solution:

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?

Solution:

SELECT YEAR,

       COUNT(1) NUM_PRODUCTS

FROM   SALES

GROUP BY YEAR;

http://techhowdy.com/wp-content/uploads/2018/05/List-of-Practice-SQL-Queries-With-Answers-For-Product-Table-1024x614.pnghttp://techhowdy.com/wp-content/uploads/2018/05/List-of-Practice-SQL-Queries-With-Answers-For-Product-Table-150x150.pngDemonDatabase Programminginterview questions sql queries product table,product table queries in sql,sql command for product table,sql queries based on product table,sql queries examples on product table,sql queries for product and department table,sql queries for product table,sql queries on product and department table,sql queries on product table pdf,sql queries product department table,sql queries product table,sql queries using product table,sql queries with product table,sql query examples for product table,sql query for creating product table,sql query for product table,sql query product table,sql query to create product table,sql server queries on product tableIn 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...Latest technology news