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