SQL Interview Questions And Answers - Part 1

Frequently Asked Questions On SQL | SQL FAQs

1. Write a query to copy table structure without the data.

CREATE TABLE emp_new AS
SELECT * FROM emp WHERE 1 = 2;

2. Write a query to rename a column phone to phone_number.

CREATE TABLE emp_new (emp_id, emp_name, address, phone_number, salary) AS
SELECT emp_id, emp_name, address, phone, salary FROM emp;

DROP TABLE emp;
RENAME emp_new TO emp;

3. Write a query to display 1 row selected while there is no data in the table.

SELECT COUNT(*) FROM emp;

4. Write a query which display emp_id & emp_name along with an extra field * & All Employees like this.

Employee Code	Employee Name
*	All Employees
E001	Susheel
E002	Jain

SELECT emp_id "Employee Code", emp_name "Employee Name" FROM emp
UNION
SELECT '*' "Employee Code", 'All Employees' "Employee Name" FROM emp
ORDER BY 1;

5. Write a query to display alternate odd or even rows.

SELECT * FROM emp WHERE (ROWID, 0) NOT IN
(SELECT ROWID, MOD(ROWNUM, 2) FROM emp);

6. Display the name of  employees and their managers from emp table by using self join

SELECT e.emp_name, m.emp_name FROM emp e, emp m WHERE e.manager_id = m.emp_id

ORDER BY e.emp_id;

7. Write a query to display the departments and no. of employees working in those departments

SELECT dept_id, COUNT(emp_id) FROM emp GROUP BY dept_id;

8. Write a query which displays those entries which are not duplicate.

SELECT * FROM emp WHERE ROWID NOT IN

(SELECT MIN(ROWID) FROM emp GROUP BY emp_id);

9. Write a query which display emp_id and min & max salary in same column like this.

Employee	Salary
E001	1,000  min(salary)
E005	10,000 max(salary)

SELECT emp_id "Employee", salary "Salary" FROM emp
WHERE salary = (SELECT MIN(salary) FROM emp)
UNION
SELECT emp_id "Employee", salary "Salary" FROM emp
WHERE salary = (SELECT MAX(salary) FROM emp);

SELECT emp_id, salary FROM emp
WHERE salary <= all (SELECT salary FROM emp)
UNION
SELECT emp_id, salary FROM emp
WHERE salary >= all (SELECT salary FROM emp);

10. Write a query to display nearest min and nearest max  from average salary.

SELECT emp_id, salary FROM emp WHERE salary <= (SELECT MAX(salary) FROM emp WHERE salary = (SELECT AVG(salary) FROM emp))
UNION
SELECT emp_id, salary FROM emp WHERE salary >= (SELECT MIN(salary) FROM emp WHERE salary = (SELECT AVG(salary) FROM emp));

11. Display the list of top n employees getting highest salary

SELECT empno, sal FROM (SELECT empno, sal FROM emp ORDER BY sal DESC)
WHERE rownum < = &n;

12. Find Maximum salary Department and Employee wise

SELECT a.empno, a.deptno, b.sal
  FROM emp a, (SELECT * FROM (SELECT empno, deptno, sal, dense_rank()

over(partition by deptno order by sal desc) rn FROM emp)) b
 WHERE a.deptno = b.deptno(+) AND b.empno(+) = a.empno AND b.rn(+) = 1;

13. Display the list of employee getting n^th highest salary.

SELECT empno, sal FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= &n
MINUS
SELECT empno, sal FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= &n-1;

OR,

SELECT rn, empno, sal FROM (SELECT rownum rn, empno, sal FROM
(SELECT empno, sal FROM emp ORDER BY sal DESC)) WHERE  rn = &n;

OR,

SELECT DISTINCT a.empno, a.sal FROM emp a WHERE &n =
(SELECT COUNT(DISTINCT b.sal) FROM emp b WHERE a.sal <= b.sal);



OR,

SELECT * FROM (SELECT empno,sal,rank() over (ORDER BY sal DESC) rnk FROM emp)

WHERE rnk <= &n;
 
OR,

SELECT level, max(salary) FROM emp WHERE level = &n

CONNECT BY PRIOR salary > salary GROUP BY level;

14. Delete duplicate entries from a table.

DELETE FROM emp WHERE ROWID NOT IN

(SELECT MIN(ROWID) FROM emp GROUP BY emp_id);

15. Delete the entries from emp table which are not matching from emp_history table by using correlated subquery.

DELETE FROM emp e WHERE (e.emp_id, e.manager_id, e.salary) NOT IN

(SELECT h.emp_id, h.manager_id, h.salary FROM emp_history h

WHERE e.emp_id = h.emp_id AND e.manager_id = h.manager_id AND e.salary = h.salary);

Part-2