SQL Query Interview Questions

Table of Contents

SQL Join

71. Select FIRST_NAME, incentive amount from employee and incentives table for those employees who have incentives?

Here we cannot use EXISTS clause as the SELECT list needs to have incentive amount from INCENTIVES too. Therefore what we have to do is to join EMPLOYEE table with INCENTIVES table so that we can select only those employees who are in INCENTIVES table. This join will also allow us to select incentive amount too.

SELECT FIRST_NAME, INCENTIVE_AMOUNT 
FROM EMPLOYEE A INNER JOIN INCENTIVES B 
	ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID;

72. Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000?

SELECT FIRST_NAME,INCENTIVE_AMOUNT 
FROM EMPLOYEE A INNER JOIN INCENTIVES B 
	ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID 
AND INCENTIVE_AMOUNT >3000;

73. Select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives?

SELECT FIRST_NAME, INCENTIVE_AMOUNT
FROM EMPLOYEE A LEFT JOIN INCENTIVES B 
     ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID;

 

74. Select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives and set incentive amount as 0 for those employees who didn’t get incentives?

SELECT FIRST_NAME,NVL(INCENTIVE_AMOUNT,0) 
FROM EMPLOYEE A LEFT JOIN INCENTIVES B 
	ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID;

75. Select first_name, incentive amount from employee and incentives table for all employees who got incentives using left join?

SELECT FIRST_NAME,NVL(INCENTIVE_AMOUNT,0)
FROM EMPLOYEE A RIGHT JOIN INCENTIVES B
	ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID;

76. Select max incentive with respect to employee from employee and incentives table using sub query?

SELECT DEPARTMENT,
       (SELECT NVL(MAX(INCENTIVE_AMOUNT),0) 
	       FROM INCENTIVES WHERE EMPLOYEE_REF_ID=EMPLOYEE_ID) MAX_INCENTIVE 	
FROM EMPLOYEE;

Top N Salary

77. Select TOP 2 salary from employee table?

SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM <3;

78. Select TOP N salary from employee table?

SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM

79. Select 2nd Highest salary from employee table?

SELECT MIN(SALARY) FROM
       (SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM <3);

80. Select Nth Highest salary from employee table?

SELECT MIN(SALARY)
       FROM (SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM

Pages: 1 2 3 4 5 6 7 8 9 10

Translate »