SQL Queries

1. Find second highest salary of employee using self join?

select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where  e1.salary <= e2.salary);

1.2 Find third highest salary of employee using self join

select distinct salary from Empoyee e1 where 3 = (select count(distinct salary) from Employee e2 where  e1.salary <= e2.salary);

1.3 Find nth highest salary of employee using self join

select distinct salary from Empoyee e1 where n = (select count(distinct salary) from Employee e2 where  e1.salary <= e2.salary);

2. Find duplicate rows in a table?

select * from Employee a where rowid <> ( select max(rowid) from Employee b where a.Employee_num=b.Employee_num);

3. Get 3 Highest salaries records from Employee table ?

select distinct salary from employee a where 3 >= (select count(distinct salary) from employee b where a.salary <= b.salary) order by a.salary desc

4. Get the departments who has no employees assigned to it ?

Table Structure:

EMP
EMPNO   ENAME    DEPTNO

DEPT
DEPTNO   DNAME

Ans:

Select DName from DEPT where DeptNo Not In (Select Distinct DeptNO from EMP);

SELECT D.DNAME
FROM DEPT D
LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE E.DEPTNO IS NULL

SELECT D.DNAME
FROM DEPT D
WHERE
 NOT EXISTS (SELECT * FROM EMP E WHERE D.DEPTNO = E.DEPTNO)

5. Get information of Employee where Employee is not assigned to the department ?

Select * from Employee where Dept_no Not in(Select Department_no from Department);

6. Select employee which have the max salary in a given department

SELECT EmpNo

    FROM emp    WHERE Salary in        (        SELECT Max(Salary)        FROM emp        GROUP BY DeptNo        )

7.  What are the differences between Having and Where clause?

• The Where clause filters rows before grouping. Having clause filters rows after grouping.
• You cannot use aggregate functions in Where clause. In Having clause, you can use aggregate functions.

8. What are the differences between UNION and UNION ALL.

• Union and union all are used to merge rows from two or more tables.
• Union set operator removes duplicate records. Whereas union all does not.
• Union operator sorts the data in ascending order. union all does not.
• Union all is faster than union operator.

9. How Do you find all Employees with its managers?

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

10. Delete duplicate rows

Delete FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

11. What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

The difference between the results shown by these 2 functions occur in case there is a tie. When multiple values in the set have same ranking, then the RANK() function will assign non-consecutive ranks to the values resulting in gaps. When DENSE_RANK() is used consecutive ranks are assigned to values in the set.

For example in the set  {100,100, 200, 300, 300, 600}.

RANK() will return {1, 1, 3, 4, 4, 6} . Since values 2 and 5 are skipped there are gaps. 

DENSE_RANK() on the other hand will return {1, 1, 2, 3, 3, 4}.Here ranks are not skipped.

12. Explain Rank Function as Analytical function with Example.

Rank function is used as analytical function in SQL/PLSQL/SQL server which is used to give the rank to the specific record in the table.Rank function is giving you ranking in ordered partitions.Means Ties are assigned to the same values after using the order by clause.So Rank function is not useful where same data is repeated again and again.It is useful in Unique data where user can make partition and order  the data properly.

Syntax of Rank:

RANK () OVER (PARTITION BY expression ORDER BY expression)

Example:

SELECT Employee_Name,Department_No,Salary,RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;

If we consider above query the same rank will be given for same salaried Employees but it will jump to the new rank for next salaried employee.Kindly check following Output

Employee Name Department No Salary Rank
      Amit 10 15000 1
      Rahul 10 8700 2
      Aditya 10 8700 2  (Tie assigned same rank)
      Shrey 10 12500 4

13. What is Correlated Subquery? Give an example

Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.

Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.

Example for Correlated Subqueries:

Fetch the Employees who have not assigned a single department.

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

14. List departments that have less than 3 people in it ?

SELECT d.Name AS 'Department'

FROM departments d JOIN employees e 

ON e.department_id = d.Department_id

GROUP BY d.department_id

HAVING COUNT(e.employee_id) < 3

15. List ALL departments along WITH the NUMBER OF people there (tricky - people often do an "inner join" leaving OUT empty departments)

SELECT d.name AS 'Department', COUNT(e.employee_id) AS '# of Employees'

FROM departments d LEFT OUTER JOIN employees e 

ON e.department_id = d.department_id

GROUP BY d.Department_id

16. List employees who have the biggest salary IN their departments

SELECT d.name as department, e.name as empName, MAX(e.salary) as salary

FROM employees e

JOIN departments d ON e.department_id=d.id

GROUP BY e.department_id;

References:

https://www.folkstalk.com/2011/12/sql-queries-interview-questions-oracle.html

https://www.folkstalk.com/2011/12/sql-queries-interview-questions-oracle_30.html