SQL, PL/SQL Interview Questions

1. Write a query to insert/update Col2’s values to look exactly opposite to Col1’s values.

Col1	Col2
1	0
0	1
0	1
0	1
1	0
0	1
1	0
1	0

Answer:

Update <TableName> Set Col2 = Case When Col1 = 1 Then 0 Else 1 End

For numeric columns you can use below Query

Update <TableName> Set Col2 = 1 - Col1

2. Given the following table.

A
2
-2
4
-4
-3
0
2

Write a single query to calculate the sum of all positive values of A and the sum of all negative values of A.

Answer:

Select Sum (Case When A>0 Then A Else 0 End) SumPos, Sum (Case When A<0 Then A Else 0 End) SumNeg From A;

3. Given the table Mass table

Weight

5.67

34.567

365.253

34

Write a query that produces the output:

Weight	KG	GMS
5.67	5	67
34.567	34	567
365.253	365	253
34	34	0

Answer:

Select Weight, Trunc(Weight) As KG, NVL(Substr(Weight - Trunc(Weight), 2), 0) As GMS

From Mass_Table;

4. How to write SQL query for the below scenario
Input: ORACLE splitting into multiple columns a string using SQL.

Output:
O
R
A
C
L
E

Answer:

Select Substr(‘ORACLE’,Level,1) From Dual Connect By Level<= Length(‘ORACLE’);

5. How to display following using query?

*

**

***

Answer:

Here dual table can not be used so will have to use any other table having required number of rows in it.

 SELECT LPAD (‘*’, ROWNUM,’*’) FROM Employees WHERE ROWNUM <4;

6. How to display 1 to 100 Numbers with query?

Answer:

Select level from dual connect by level <=100;

7. What is the difference between DECODE and CASE?

Answer: Click here

8. What is the difference between ROWNUM and ROWID?

Answer: Click here

9. How SQL query gets executed and what are different phases?

Answer: Click here

10. What is the difference between Analytical and Aggregate Functions? Differentiate with examples.

Answer: Click here

More examples: Click here