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;

Returning comma separated list

 To return a comma separated list of employees in each department we use the LISTAGG Analytical Function as below.

SELECT department_id, LISTAGG(First_Name,',') WITHIN GROUP (ORDER BY First_Name)
FROM   employees
GROUP BY department_id
ORDER BY department_id;

Department Id	Employees
10	Jennifer
20	Michael,Pat
30	Alexander,Den,Guy,Karen,Shelli,Sigal
40	Susan
50	Adam,Alana,Alexis,Anthony,Britney,Curtis,Donald,Douglas, Girard,Hazel,Irene,James,James,Jason,Jean,Jennifer,John, Joshua,Julia,Julia,Kelly,Kevin,Kevin,Ki,Laura,Martha, Matthew,Michael,Mozhe,Nandita,Payam,Peter,Randall,Randall, Renske,Samuel,Sarah,Shanta,Stephen,Steven,TJ, Timothy,Trenna,Vance,Winston
60	Alexander,Bruce,David,Diana,Valli
70	Hermann
80	Alberto,Allan,Alyssa,Amit,Charles,Christopher,Clara, Danielle,David,David,Eleni,Elizabeth,Ellen,Gerald,Harrison, Jack,Janette,John,Jonathon,Karen,Lindsey,Lisa,Louise,Mattea, Nanette,Oliver,Patrick,Peter,Peter,Sarath,Sundar,Sundita,Tayler, William
90	Lex,Neena,Steven
100	Daniel,Ismael,John,Jose Manuel,Luis,Nancy
110	Shelley,William
	Kimberely

In the results we see some duplicates, if we want to remove the duplicates then we use the distinct keyword as below.

SELECT department_id

              , LISTAGG( DISTINCT First_Name,',') WITHIN GROUP (ORDER BY First_Name)
FROM   employees
GROUP BY department_id
ORDER BY department_id;

Department Id	Employees
10	Jennifer
20	Michael,Pat
30	Alexander,Den,Guy,Karen,Shelli,Sigal
40	Susan
50	Adam,Alana,Alexis,Anthony,Britney,Curtis,Donald, Douglas,Girard,Hazel,Irene,James,Jason,Jean,Jennifer, John,Joshua,Julia,Kelly,Kevin,Ki,Laura, Martha,Matthew,Michael,Mozhe,Nandita, Payam,Peter,Randall,Renske, Samuel,Sarah,Shanta,Stephen,Steven,TJ,Timothy, Trenna,Vance,Winston
60	Alexander,Bruce,David,Diana,Valli
70	Hermann
80	Alberto,Allan,Alyssa,Amit,Charles,Christopher, Clara,Danielle,David,Eleni, Elizabeth,Ellen,Gerald,Harrison,Jack,Janette, John,Jonathon,Karen,Lindsey, Lisa,Louise,Mattea,Nanette,Oliver,Patrick,Peter, Sarath,Sundar,Sundita,Tayler, William
90	Lex,Neena,Steven
100	Daniel,Ismael,John,Jose Manuel,Luis,Nancy
110	Shelley,William
	Kimberely

  

There is another way to do this in Oracle 21c

SELECT department_name, 

            CURSOR(SELECT salary, commission_pct 

            FROM employees e

            WHERE e.department_id = d.department_id)

FROM departments d

ORDER BY department_name

How to find the 2nd lowest salary in each department

SELECT 

    department_id, first_name, salary, 

    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id 

ORDER BY salary 

RANGE BETWEEN UNBOUNDED PRECEDING 

AND UNBOUNDED FOLLOWING) "2nd_lowest_sal"

FROM employees

ORDER BY department_id, salary;

Cut Command in Shell Programming

 The cut command in UNIX is a command for cutting out the sections from each line of files and writing the result to standard output. It can be used to cut parts of a line by byte position, character and field. Basically the cut command slices a line and extracts the text. It is necessary to specify option with command otherwise it gives error. If more than one file name is provided then data from each file is not precedes by its file name.

$cut -d "delimiter" -f (field number) file.txt

[opc@instance-2 ~]$ cat myFirstScript.txt
pwd
This is my FirstScript
Once upon a time there lived a great king.
He was very kind and have never let any one down.

[opc@instance-2 ~]$ cut -d " " -f 1,5 myFirstScript.txt
pwd
This
Once there
He and

[opc@instance-2 ~]$ cut -d " " -f 1-5 myFirstScript.txt
pwd
This is my FirstScript
Once upon a time there
He was very kind and

https://www.geeksforgeeks.org/cut-command-linux-examples/

How to view a SQL query execution Plan in Oracle SQL*Plus

CLICK HERE

SQL> explain plan for
select d.department_name, e.first_name, e.salary, 

sum(e.salary) over (partition by e.department_id order by e.first_name) running_total

from employees e
left join departments d on e.department_id = d.department_id
order by d.department_name, e.first_name;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Write a Query to return Running Total of employees Salary within each department.

select d.department_name, e.first_name, e.salary, sum(e.salary) over (partition by e.department_id order by e.first_name) running_total 

from employees e

left join departments d on e.department_id = d.department_id

order by d.department_name, e.first_name