SQL Queries Asked in Interviews

Queries:

1) There is a table which contains two columns Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above-average students Click Here 

SELECT Student
FROM <tablename>
WHERE Marks > (SELECT AVG(Marks) FROM <tablename>);

(or)

SELECT Student, Marks
FROM (  SELECT  Student, 
                                Marks, 
                                AVG(Marks) over () AS AvgMark
                FROM <tablename>) S

WHERE Marks > AvgMark;

2) What is High Water Mark in Oracle? Click Here

The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.
The issue with the high water mark is that full-table scans will always read up to the high water mark.
The remedy for a too-high high water mark (e.g. a fragmented table will lots of empty blocks) is to reorganize the table with 
Data Pump (expdp and impdp), 
the dbms_redefinition utility, or 

with the table shrink or coalesce.

3) PLSQL Interview Questions. Click Here

Query to find 2nd top salary in each department

Conventional Query:

select 

    department_id, 

    last_name, 

    salary

from employees e

where 1 = (select count(distinct salary) 

                    from employees e2 

                    where e2.salary > e.salary and e2.department_id = e.department_id)

order by department_id;

Analytical Query:

Select department_id, last_name, salary

from (select e.department_id, last_name, salary, 

            dense_rank() over (partition by department_id order by salary desc) rank

            from employees e)

where rank = 2    

order by department_id;