Tricky Query Interview Questions

Create following tables:

CREATE TABLE PRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2(30)

);

CREATE TABLE SALES

(

SALE_ID INTEGER,

PRODUCT_ID INTEGER,

YEAR INTEGER,

Quantity INTEGER,

PRICE INTEGER

);

INSERT INTO PRODUCTS VALUES ( 100, 'Nokia');

INSERT INTO PRODUCTS VALUES ( 200, 'IPhone');

INSERT INTO PRODUCTS VALUES ( 300, 'Samsung');

INSERT INTO PRODUCTS VALUES ( 400, 'LG');

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

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

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

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

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

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

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

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

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

COMMIT;

SELECT * FROM PRODUCTS;

PRODUCT_ID PRODUCT_NAME

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

100 Nokia

200 IPhone

300 Samsung

SELECT * FROM SALES;

SALE_ID PRODUCT_ID YEAR QUANTITY PRICE

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

1 100 2010 25 5000

2 100 2011 16 5000

3 100 2012 8 5000

4 200 2010 10 9000

5 200 2011 15 9000

6 200 2012 20 9000

7 300 2010 20 7000

8 300 2011 18 7000

9 300 2012 20 7000

1. Write a SQL query to find the products which have continuous increase in sales every year?

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 whose sales decreased in 2012 compared to 2011?

SELECT P.PRODUCT_NAME

FROM PRODUCTS P,

SALES S_2012,

SALES S_2011

WHERE P.PRODUCT_ID = S_2012.PRODUCT_ID

AND S_2012.YEAR = 2012

AND S_2011.YEAR = 2011

AND S_2012.PRODUCT_ID = S_2011.PRODUCT_ID

AND S_2012.QUANTITY < S_2011.QUANTITY;

3. 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;

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?

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

);

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

Using self join:

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

Using DECODE:

select year,max(decode(product_name,'IPhone',quantity)) Iphone_QUAN,

max(decode(product_name,'Samsung',quantity)) SAM_QUAN,

max(decode(product_name,'IPhone',price)) Iphone_PRICE,

max(decode(product_name,'Samsung',price)) SAM_PRICE

from sales s, products p where s.product_id=p.product_id

group by year

Using PIVOT (from oracle 11g onwards):

select * from (

select year,quantity,price,product_name

from sales s, products p where s.product_id=p.product_id

)

pivot (max(quantity) AS QUAN,max(price) AS PRICE FOR product_name in ('IPhone','Samsung'))

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

Using DECODE:

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;

Using PIVOT (from oracle 11g onwards):

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));

7. Write a query to generate sequence numbers from 1 to the specified number N?

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=&N;

8. Write a query to display only friday dates from Jan, 2000 to till now?

select d_date,to_char(d_date,'DY') as Day from (

select to_date('01-JAN-2000','DD-MON-YYYY')+Level-1 as d_date from dual

connect by level < = (SYSDATE - to_date('01-JAN-2000','DD-MON-YYYY')+1)

) where to_char(d_date,'DY') = 'FRI'

9. Write a query to duplicate each row based on the value in the repeat column? The input table data looks like as below

Products, Repeat

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

A, 3

B, 5

C, 2

Now in the output data, the product A should be repeated 3 times, B should be repeated 5 times and C should be repeated 2 times. The output will look like as below

Products, Repeat

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

A, 3

A, 3

A, 3

B, 5

B, 5

B, 5

B, 5

B, 5

C, 2

C, 2

SELECT PRODUCTS,

REPEAT

FROM T,

( SELECT LEVEL L FROM DUAL

CONNECT BY LEVEL <= (SELECT MAX(REPEAT) FROM T)

) A

WHERE T.REPEAT >= A.L

ORDER BY T.PRODUCTS;

10. Write a query to display each letter of the word "IPHONE" in a separate row?

I

P

H

O

N

E

SELECT SUBSTR('IPHONE',LEVEL,1) A

FROM DUAL

CONNECT BY LEVEL <=LENGTH('IPHONE');

11. Convert the string "IPHONE" to Ascii values? The output should look like as 73,80,72,79,78,69. Where 73 is the ascii value of I and so on.

Using WM_CONCAT() which is not official & documented:

select wm_concat(A) from(

SELECT ascii(SUBSTR('IPHONE',LEVEL,1)) A

FROM DUAL

CONNECT BY LEVEL <=LENGTH('IPHONE'))

Using DUMP:

SELECT SUBSTR(DUMP('IPHONE'),15)

FROM DUAL;

Using LISTAGG():

select LISTAGG(A,',') within group (order by B) from(

SELECT ascii(SUBSTR('IPHONE',LEVEL,1)) A ,LEVEL B

FROM DUAL

CONNECT BY LEVEL <=LENGTH('IPHONE'))

12. I have a table which has only one column and one row. The numeric column contains a 6 digit number. My requirements is to find out the product of the 3 consecutive digits and display them in separate rows.

Sample input data output are shown below:

Source Table Name: Digits

Value

-----------

123456

Output:

product

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

6

24

60

120

select (substr(Value,level,1)*substr(Value,level+1,1)*substr(Value,level+2,1)) as product from Digits

connect by level <= length(Value)-2

13. Write a query to print star triangle.

select rpad( '* ', level*2, '* ' ) from dual connect by level <= 3

14. How to create test data in bulk ?

Refer following queries :

Insert into test(id, name) select level, dbms_random.string('ss',4) from dual connect by level<=1000

SELECT LEVEL empl_id,

MOD (ROWNUM, 50000) dept_id,

TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,

DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender,

TO_DATE ( ROUND (DBMS_RANDOM.VALUE (1, 28))

|| '-'

|| ROUND (DBMS_RANDOM.VALUE (1, 12))

|| '-'

|| ROUND (DBMS_RANDOM.VALUE (1900, 2010)),

'DD-MM-YYYY'

) dob,

DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address

FROM DUAL

CONNECT BY LEVEL < 10000;

15. One of the columns in the table is of varchar data type. It contains both strings, numbers and mix of char and numeric values. My problem is to print only the values that contains only numeric digits.

Sample data in the columne is shown below:

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

Ora123

786

database

92db

The output should contain the following values:

786

select * from test where regexp_like(a,'^[0-9]+$')

select * from test where regexp_like(a,'^[0-9]*$')

select * from test where regexp_like(a,'^[[:digit:]]+$')

select * from test where upper(a)=lower(a) --will fail for special characters

select * from test where LTRIM (a, '0123456789') IS NULL

select * from test where translate(a,'0123456789',' ') IS NULL