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:
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.