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

SQL Queries Interview Questions - Oracle Analytical Functions

SQL Queries Interview Questions - Oracle Analytical Functions

Analytic functions compute aggregate values based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. Most of the SQL developers won't use analytical functions because of its cryptic syntax or uncertainty about its logic of operation. Analytical functions saves lot of time in writing queries and gives better performance when compared to native SQL.

Before starting with the interview questions, we will see the difference between the aggregate functions and analytic functions with an example. I have used SALES TABLE as an example to solve the interview questions. Please create the below sales table in your oracle database.

CREATE TABLE SALES

(

SALE_ID INTEGER,

PRODUCT_ID INTEGER,

YEAR INTEGER,

Quantity INTEGER,

PRICE INTEGER

);

INSERT INTO SALES VALUES ( 1, 100, 2008, 10, 5000);

INSERT INTO SALES VALUES ( 2, 100, 2009, 12, 5000);

INSERT INTO SALES VALUES ( 3, 100, 2010, 25, 5000);

INSERT INTO SALES VALUES ( 4, 100, 2011, 16, 5000);

INSERT INTO SALES VALUES ( 5, 100, 2012, 8, 5000);

INSERT INTO SALES VALUES ( 6, 200, 2010, 10, 9000);

INSERT INTO SALES VALUES ( 7, 200, 2011, 15, 9000);

INSERT INTO SALES VALUES ( 8, 200, 2012, 20, 9000);

INSERT INTO SALES VALUES ( 9, 200, 2008, 13, 9000);

INSERT INTO SALES VALUES ( 10,200, 2009, 14, 9000);

INSERT INTO SALES VALUES ( 11, 300, 2010, 20, 7000);

INSERT INTO SALES VALUES ( 12, 300, 2011, 18, 7000);

INSERT INTO SALES VALUES ( 13, 300, 2012, 20, 7000);

INSERT INTO SALES VALUES ( 14, 300, 2008, 17, 7000);

INSERT INTO SALES VALUES ( 15, 300, 2009, 19, 7000);

COMMIT;

SELECT * FROM SALES;

SALE_ID PRODUCT_ID YEAR QUANTITY PRICE

--------------------------------------

1 100 2008 10 5000

2 100 2009 12 5000

3 100 2010 25 5000

4 100 2011 16 5000

5 100 2012 8 5000

6 200 2010 10 9000

7 200 2011 15 9000

8 200 2012 20 9000

9 200 2008 13 9000

10 200 2009 14 9000

11 300 2010 20 7000

12 300 2011 18 7000

13 300 2012 20 7000

14 300 2008 17 7000

15 300 2009 19 7000

Difference Between Aggregate and Analytic Functions:

Q. Write a query to find the number of products sold in each year?

The SQL query Using Aggregate functions is

SELECT Year,

COUNT(1) CNT

FROM SALES

GROUP BY YEAR;

YEAR CNT

---------

2009 3

2010 3

2011 3

2008 3

2012 3

The SQL query Using Aanalytic functions is

SELECT SALE_ID,

PRODUCT_ID,

Year,

QUANTITY,

PRICE,

COUNT(1) OVER (PARTITION BY YEAR) CNT

FROM SALES;

SALE_ID PRODUCT_ID YEAR QUANTITY PRICE CNT

------------------------------------------

9 200 2008 13 9000 3

1 100 2008 10 5000 3

14 300 2008 17 7000 3

15 300 2009 19 7000 3

2 100 2009 12 5000 3

10 200 2009 14 9000 3

11 300 2010 20 7000 3

6 200 2010 10 9000 3

3 100 2010 25 5000 3

12 300 2011 18 7000 3

4 100 2011 16 5000 3

7 200 2011 15 9000 3

13 300 2012 20 7000 3

5 100 2012 8 5000 3

8 200 2012 20 9000 3

From the ouputs, you can observe that the aggregate functions return only one row per group whereas analytic functions keeps all the rows in the gorup. Using the aggregate functions, the select clause contains only the columns specified in group by clause and aggregate functions whereas in analytic functions you can specify all the columns in the table.

The PARTITION BY clause is similar to GROUP By clause, it specifies the window of rows that the analytic funciton should operate on.

I hope you got some basic idea about aggregate and analytic functions. Now lets start with solving the Interview Questions on Oracle Analytic Functions.

1. Write a SQL query using the analytic function to find the total sales(QUANTITY) of each product?

Solution:

SUM analytic function can be used to find the total sales. The SQL query is

SELECT PRODUCT_ID,

QUANTITY,

SUM(QUANTITY) OVER( PARTITION BY PRODUCT_ID ) TOT_SALES

FROM SALES;

PRODUCT_ID QUANTITY TOT_SALES

-----------------------------

100 12 71

100 10 71

100 25 71

100 16 71

100 8 71

200 15 72

200 10 72

200 20 72

200 14 72

200 13 72

300 20 94

300 18 94

300 17 94

300 20 94

300 19 94

2. . Write a SQL query to find the Median of sales of a product?

Solution:

The SQL query for calculating the median is

SELECT PRODUCT_ID,

QUANTITY,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY QUANTITY ASC)

OVER (PARTITION BY PRODUCT_ID) MEDIAN

FROM SALES;

PRODUCT_ID QUANTITY MEDIAN

--------------------------

100 8 12

100 10 12

100 12 12

100 16 12

100 25 12

200 10 14

200 13 14

200 14 14

200 15 14

200 20 14

300 17 19

300 18 19

300 19 19

300 20 19

300 20 19

3. Write a SQL query to find the sum of sales of current row and previous 2 rows in a product group? Sort the data on sales and then find the sum.

Solution:

The sql query for the required ouput is

SELECT PRODUCT_ID,

QUANTITY,

SUM(QUANTITY) OVER(

PARTITION BY PRODUCT_ID

ORDER BY QUANTITY DESC

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) CALC_SALES

FROM SALES;

PRODUCT_ID QUANTITY CALC_SALES

------------------------------

100 25 25

100 16 41

100 12 53

100 10 38

100 8 30

200 20 20

200 15 35

200 14 49

200 13 42

200 10 37

300 20 20

300 20 40

300 19 59

300 18 57

300 17 54

The ROWS BETWEEN clause specifies the range of rows to consider for calculating the SUM.

4 Write a SQL query to find the cumulative sum of sales(QUANTITY) of each product? Here first sort the QUANTITY in ascendaing order for each product and then accumulate the QUANTITY.
Cumulative sum of QUANTITY for a product = QUANTITY of current row + sum of QUANTITIES all previous rows in that product.

Solution:

We have to use the option "ROWS UNBOUNDED PRECEDING" in the SUM analytic function to get the cumulative sum. The SQL query to get the ouput is

SELECT PRODUCT_ID,

QUANTITY,

SUM(QUANTITY) OVER( PARTITION BY PRODUCT_ID

ORDER BY QUANTITY ASC

ROWS UNBOUNDED PRECEDING) CUM_SALES

FROM SALES;

PRODUCT_ID QUANTITY CUM_SALES

-----------------------------

100 8 8

100 10 18

100 12 30

100 16 46

100 25 71

200 10 10

200 13 23

200 14 37

200 15 52

200 20 72

300 17 17

300 18 35

300 19 54

300 20 74

300 20 94

The ORDER BY clause is used to sort the data. Here the ROWS UNBOUNDED PRECEDING option specifies that the SUM analytic function should operate on the current row and the pervious rows processed.

5. Write a SQL query to find the minimum sales of a product without using the group by clause.

Solution:

The SQL query is

SELECT PRODUCT_ID,

YEAR,

QUANTITY

FROM

(

SELECT PRODUCT_ID,

YEAR,

QUANTITY,

ROW_NUMBER() OVER(PARTITION BY PRODUCT_ID

ORDER BY QUANTITY ASC) MIN_SALE_RANK

FROM SALES

) WHERE MIN_SALE_RANK = 1;

PRODUCT_ID YEAR QUANTITY

------------------------

100 2012 8

200 2010 10

300 2008 17