SQL Queries Interview Questions
Solve the below examples by writing SQL queries.
1. In the SALES table quantity of each product is stored in rows for every year. Now write a query to transpose the quantity for each product and display it in columns? The output should look like as
PRODUCT_NAME QUAN_2010 QUAN_2011 QUAN_2012
------------------------------------------
IPhone 10 15 20
Samsung 20 18 20
Nokia 25 16 8
Solution:
Oracle 11g provides a pivot function to transpose the row data into column data. The SQL query for this is
SELECT * FROM
(
SELECT P.PRODUCT_NAME,
S.QUANTITY,
S.YEAR
FROM PRODUCTS P,
SALES S
WHERE (P.PRODUCT_ID = S.PRODUCT_ID)
)A
PIVOT ( MAX(QUANTITY) AS QUAN FOR (YEAR) IN (2010,2011,2012));
If you are not running oracle 11g database, then use the below query for transposing the row data into column data.
SELECT P.PRODUCT_NAME,
MAX(DECODE(S.YEAR,2010, S.QUANTITY)) QUAN_2010,
MAX(DECODE(S.YEAR,2011, S.QUANTITY)) QUAN_2011,
MAX(DECODE(S.YEAR,2012, S.QUANTITY)) QUAN_2012
FROM PRODUCTS P,
SALES S
WHERE (P.PRODUCT_ID = S.PRODUCT_ID)
GROUP BY P.PRODUCT_NAME;
2. Write a query to compare the products sales of "IPhone" and "Samsung" in each year? The output should look like as
YEAR IPHONE_QUANT SAM_QUANT IPHONE_PRICE SAM_PRICE
---------------------------------------------------
2010 10 20 9000 7000
2011 15 18 9000 7000
2012 20 20 9000 7000
Solution:
By using self-join SQL query we can get the required result. The required SQL query is
SELECT S_I.YEAR,
S_I.QUANTITY IPHONE_QUANT,
S_S.QUANTITY SAM_QUANT,
S_I.PRICE IPHONE_PRICE,
S_S.PRICE SAM_PRICE
FROM PRODUCTS P_I,
SALES S_I,
PRODUCTS P_S,
SALES S_S
WHERE P_I.PRODUCT_ID = S_I.PRODUCT_ID
AND P_S.PRODUCT_ID = S_S.PRODUCT_ID
AND P_I.PRODUCT_NAME = 'IPhone'
AND P_S.PRODUCT_NAME = 'Samsung'
AND S_I.YEAR = S_S.YEAR
3. Write a query to find the ratios of the sales of a product?
Solution:
The ratio of a product is calculated as the total sales price in a particular year divide by the total sales price across all years. Oracle provides RATIO_TO_REPORT analytical function for finding the ratios. The SQL query is
SELECT P.PRODUCT_NAME,
S.YEAR,
RATIO_TO_REPORT(S.QUANTITY*S.PRICE)
OVER(PARTITION BY P.PRODUCT_NAME ) SALES_RATIO
FROM PRODUCTS P,
SALES S
WHERE (P.PRODUCT_ID = S.PRODUCT_ID);
PRODUCT_NAME YEAR RATIO
-----------------------------
IPhone 2011 0.333333333
IPhone 2012 0.444444444
IPhone 2010 0.222222222
Nokia 2012 0.163265306
Nokia 2011 0.326530612
Nokia 2010 0.510204082
Samsung 2010 0.344827586
Samsung 2012 0.344827586
Samsung 2011 0.310344828
4. Write a query to find the products whose quantity sold in a year should be greater than the average quantity of the product sold across all the years?
Solution:
This can be solved with the help of correlated query. The SQL query for this is
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
);
PRODUCT_NAME YEAR QUANTITY
--------------------------
Nokia 2010 25
IPhone 2012 20
Samsung 2012 20
Samsung 2010 20
5. Write a query to find the number of products sold in each year?
Solution:
To get this result we have to group by on year and the find the count. The SQL query for this question is
SELECT YEAR,
COUNT(1) NUM_PRODUCTS
FROM SALES
GROUP BY YEAR;
YEAR NUM_PRODUCTS
------------------
2010 3
2011 3
2012 3
