The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARMENT_ID = NULL; What is true about this SQL statement?
The SQL statement displays the desired results.
The column in the WHERE clause should be changed to display the desired results.
The operator in the WHERE clause should be changed to display the desired results.
The WHERE clause should be changed to use an outer join to display the desired results.
In SQL, NULL represents an unknown value and cannot be compared using the = operator. Any comparison with NULL returns NULL (unknown), never true or false. To check for NULL values, you must use the IS NULL or IS NOT NULL operators. The statement WHERE DEPARTMENT_ID IS NULL would correctly find employees without a department assignment.
To answer this question, let's go through each option to understand why it is correct or incorrect:
Option A) The SQL statement displays the desired results. This option is incorrect because the SQL statement will not display the desired results.
Option B) The column in the WHERE clause should be changed to display the desired results. This option is incorrect because the column in the WHERE clause is correct. The column 'DEPARTMENT_ID' is being checked for NULL values, which is the correct approach.
Option C) The operator in the WHERE clause should be changed to display the desired results. This option is correct because the operator used in the WHERE clause is incorrect. To check for NULL values, you should use the 'IS NULL' operator instead of the '=' operator. So the correct SQL statement should be: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID IS NULL;
Option D) The WHERE clause should be changed to use an outer join to display the desired results. This option is incorrect because using an outer join is not necessary to display the desired results. The problem lies in the operator used in the WHERE clause, not the join type.
The correct answer is Option C. The operator in the WHERE clause should be changed to use 'IS NULL' instead of '=' to display the desired results.