Multiple choice

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow| and Table name-Dep ||| |---|---| |Deptno|deptname| |501|sales| |502|Hr| |503|CS| |504|EC|

Find the details of departments in which employees are not working.

  1. SELECT * FROM DEPT WHERE DEPTNO !=(SELECT DISTINCT DEPTNO FROM EMP)

  2. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP)

  3. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP)

  4. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO )

  5. None of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This is the correct syntax as it will show the those departments from the employee table that do not belongs to working employees. Here we used Distinct keyword to escape those results.

Keep practicing — related questions