SQL, PL/SQL Interview Questions

1.Query to find Second Highest Salary of Employee? Do the same nth highest salary.

Answer: Click here for answer

2.Query to find and remove duplicate rows in table?

Answer: Click here for answer

3. How Can I create table with same structure of Employee table?

Answer: Create table Employee_1 as Select * from Employee where 1=2;

4. Display first 50% records from Employee table?

Answer:  Select Rownum, E.* from Employee E where Rownum<= (Select count (*/2) from Employee);

5.How To fetch ALTERNATE records from a table. (EVEN NUMBERED/ODD Numbered)

Answer:

EVEN.NUMBERED:
SELECT * From EMPLOYEES Where

ROWID IN (SELECT DECODE(MOD(ROWNUM,2),0, ROWID, NULL) From EMPLOYEES);

Another way using Analytical SQL is:

Select * From

(

Select *,  Row_Number() Over (Order By EmpNo) As Rn From EMPLOYEES

) E

Where E.Rn%2=0;

ODD.NUMBERED:

SELECT * From EMPLOYEES Where

ROWID IN (SELECT DECODE(MOD(ROWNUM,2),0, NULL, ROWID) From EMPLOYEES);

Another way using Analytical SQL is:

Select * From

(

Select *,  Row_Number() Over (Order By EmpNo) As Rn From EMPLOYEES

) E

Where E.Rn%2=1;

6. How to get distinct records from the table without using distinct keyword.

Answer:

Select * from Employee a where Rowid = (Select max(Rowid) from Employee b where a.Employee_no=b.Employee_no);

7. You have a composite index of three columns, and you only provide the value of two columns in WHERE clause of a select query? Will Index be used for this operation? 

Answer:

For example, if Index is on EmpId, EmpFirstName, and EmpSecondName and you write query like

SELECT * FROM Employee WHERE EmpId=1813 and EmpFirstName='Ravi'

If the given two columns are secondary index column then the index will not invoke, but if the given 2 columns contain the primary index (first column while creating index) then the index will invoke. In this case, Index will be used because EmpId and EmpFirstName are primary columns.

8.How can we view last record added to a table?

Answer:

Following Query will display the last record added into Table:

SELECT * From (SELECT * From EMPLOYEES ORDER BY ROWNUM DESC)

Where ROWNUM<2;

9. Given a table Employees, such as the one below, that has m = male and f = female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.

Id Name Sex Salary

1    A            M    2500

2   B             F     1500

3   C             M    5500

4   D             F     500

Answer:

UPDATE EMPLOYEES SET Sex = CASE Sex WHEN 'M' THEN 'F' ELSE 'M' END

10. Table is as follows:

ID	C1	C2	C3
1	Red	Yellow	Blue
2	NULL	Red	Green
3	Yellow	NULL	Violet

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using 

Answer:

Select * From COLOURS WHERE 'Yellow' IN (C1, C2, C3);