SQL Queries Interview Questions - Oracle Part 2

SQL Queries Interview Questions - Oracle Part 2

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

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

Solution:

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

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

Solution:

SELECT C_DATE,

TO_CHAR(C_DATE,'DY')

FROM

(

SELECT TO_DATE('01-JAN-2000','DD-MON-YYYY')+LEVEL-1 C_DATE

FROM DUAL

CONNECT BY LEVEL <=

(SYSDATE - TO_DATE('01-JAN-2000','DD-MON-YYYY')+1)

)

WHERE TO_CHAR(C_DATE,'DY') = 'FRI';

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

Solution:

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;

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

S

M

I

L

E

Solution:

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

FROM DUAL

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

7. Convert the string "SMILE" to Ascii values? The output should look like as 83,77,73,76,69. Where 83 is the ascii value of S and so on.
The ASCII function will give ascii value for only one character. If you pass a string to the ascii function, it will give the ascii value of first letter in the string. Here i am providing two solutions to get the ascii values of string.

Solution1:

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

FROM DUAL;

Solution2:

SELECT WM_CONCAT(A)

FROM

(

SELECT ASCII(SUBSTR('SMILE',LEVEL,1)) A

FROM DUAL

CONNECT BY LEVEL <=LENGTH('SMILE')

);

8. Consider the following friends table as the source

Name, Friend_Name

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

sam, ram

sam, vamsi

vamsi, ram

vamsi, jhon

ram, vijay

ram, anand

Here ram and vamsi are friends of sam; ram and jhon are friends of vamsi and so on. Now write a query to find friends of friends of sam. For sam; ram,jhon,vijay and anand are friends of friends. The output should look as

Name, Friend_of_Firend

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

sam, ram

sam, jhon

sam, vijay

sam, anand

Solution:

SELECT f1.name,

f2.friend_name as friend_of_friend

FROM friends f1,

friends f2

WHERE f1.name = 'sam'

AND f1.friend_name = f2.name;