SQL Queries Interview Questions

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