Database Interview Series 3
This is Database Interview Series 3 and most frequently asked question on any Java Developer and Sr. Java Developer position:
1. We have table employee with column: id,name and table addresses column: id,location and mapping table employee_address with column: emp_id,add_id which is reference of employee and person table. How you will join two table and get data can write SQL query ?
Answer: Select * from employee e inner join person p on e.id = p.id join employee_person ep on ep.per_id = p.id and ep.emp_id = e.id
2. Find first and last record from table ?
Answer: select max(id) as maxID_minID from employee union select min(id) from employee
3. How do you find the second highest record from the database ?
Answer: select max(id) from employee where id not in (select max(id) from employee)
4. How to find duplicate record in table ?
Answer: select count(id), id from employee group by id having count(*)>1
5. How do you find the nth highest record from the table ?
Answer: This query print highest record in descending order:
SELECT * FROM employee ORDER BY id desc LIMIT 10
6. What cursor type do you use to retrieve multiple record sets?
Answer: Explicit cursor
7. How will you copy the structure of a table without copying the data ?
Answer: select * into new_table_name from source_table_name where 1=2
8. If you have employee table where Table as below:
EMPNO ENAME JOB DEPTNO MGR
---------- ---------- --------- ---------- ----------
7839 KING PRESIDENT 10
7698 BLAKE MANAGER 30 7839
7782 CLARK MANAGER 10 7839
7566 JONES MANAGER 20 7839
7654 MARTIN SALESMAN 30 7698
7499 ALLEN SALESMAN 30 7698
7844 TURNER SALESMAN 30 7698
7900 JAMES CLERK 30 7698
7521 WARD SALESMAN 30 7698
7902 FORD ANALYST 20 7566
7369 SMITH CLERK 20 7902
How will you find employee with same manager. How will you find Manager without any direct report. How will you find employee with no manager.