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