Database Interview Series 6

Database Interview Series 6

This is common question which interview ask in interview for Java Developer or Sr. Java Developer position:

1. Can you write a query which return 4th max record from the table

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC
) a
ORDER BY salary

N minimum salary:

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC)

2. Can you write a query which will return only even value from the table using Oracle rownum

SELECT * FROM EMP WHERE EMPNO IN
(
SELECT CASE MOD(ROWNUM,2)
WHEN 0 THEN EMPNO
END
FROM EMP
);
This will fetch all the odd records

SELECT * FROM EMP WHERE EMPNO IN
(
SELECT CASE MOD(ROWNUM,2)
WHEN 1 THEN EMPNO
END
FROM EMP
);

3. What is unique index

4. How find maximum record from table

select MAX(salary) from table group by salary

5. How you find 2nd maximum record from table

SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

SELECT MAX(Salary) From Employee WHERE Salary < ( SELECT Max(Salary) FROM Employee);

6. We have table Employee with column: EmpID,name and table Department column: EmpID,location and mapping table EmpDept with column: EmpID,add_id which is reference of employee and person table. How you will join two table and get data can write SQL query

SELECT E.*, D.*
FROM Employee AS E
LEFT JOIN EmpDept AS ED ON E.EmpID = ED.EmpID
LEFT JOIN Department AS D ON ED.DeptID = D.DeptID
WHERE E.EmpId = @EmpId

To get Deparment:

SELECT E.*, D.*
FROM Department AS D
LEFT JOIN EmpDept AS ED ON ED.DeptID = D.DeptID
LEFT JOIN Employee AS E ON ED.EmpID = E.EmpID
WHERE D.DeptId = @DeptId

7. Write query on two table to find sum of salary

select region,sum(number) total
from
(
select region,number
from cash_table
union all
select region,number
from cheque_table
) t
group by region

OR:


SELECT (SELECT SUM(London) FROM CASH) + (SELECT SUM(London) FROM CHEQUE) as result