Database Interview Series 3

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.