Question { IBM, 36988 }

how to check the 3rd max salary from an employee table?

Answer

SELECT *
FROM(
SELECT SALARY, DEPARTMENT_ID,EMPLOYEE_ID, DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RN
FROM EMPLOYEES
)
WHERE RN = 2
;

Question { IBM, 28797 }

How to improve the performance of a pl/sq stored procedures
or functions or triggers and packages ?

Answer

SELECT *
FROM employees
ORDER BY EMPLOYEE_ID
OFFSET 99 ROWS
FETCH NEXT 21 ROWS ONLY;

Question { IBM, 20740 }

What does “select count(1) from tab” result?

Answer

select* from tab; gives you the o/p as a table containing rows and columns. The rows contain the name of table ,select count(1) from tab returns the no or rows in tab .
Indirectly the statement returns no of tables in tab;

Question { IBM, 19528 }

i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100

i want to get sal which is less than the avg sal of thri dept.

eno dno sal
2 10 150
3 10 100
4 20 75

Answer

select salary from emp
wher dno in (select dno
from dept
where salary < any
(select avg(salary)
from dept
where dept = 3)