Skip to main content

Question 9: null in where clause

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 DEPARTMENT_ID = NULL;


What is true about this SQL statement?

A. The SQL statement displays the desired results.

B. The column in the WHERE clause should be changed to display the desired results.

C. The operator in the WHERE clause should be changed to display the desired results.

D. The WHERE clause should be changed to use an outer join to display the desired results.

Answer: C

Explanation:
The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL.
Oracle provides this functionality with a special function, called NVL().
You can use operation equal with the keywords IS NULL, or you can achieve desired results using NVL() function after the WHERE clause.


Incorrect Answers

A: The SQL statement will generate an error because you cannot use operation equal with NULL.

B: The column in the WHERE clause should not be changed to display the desired results.

D: Since there is only one table used in this query you don't need to use outer join to display the desired results.
Labels:

Comments

Post a Comment

Popular posts from this blog

Question 8: Aggregate Functions

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two) A. SUM(start_date) B. AVG(start_date) C. COUNT(start_date) D. AVG(start_date, end_date) E. MIN(start_date) F. MAXIMUM(start_date) Answer: C & E Explanation: It is possible to apply COUNT() and MIN() functions on the column with DATE data type. Incorrect Answers A: Function SUM() cannot be used with DATE data type column. B: Function AVG() cannot be used with DATE data type column. D: Function AVG() cannot be used with DATE data type column, and function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement. F: There is no MAXIMUM() function in Oracle, only MAX() function exists.
Post a Comment
Read more

19. Oracle Processes

Processes Oracle uses many small (focused) processes to manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology. Some of these processes include: PMON - Process Monitor SMON - System Monitor ARCn - Redo Log Archiver LGWR - Redo Log Writer DBWn - Database Writer CKPT - Checkpoint process RECO - Recoverer CJQn - Job Queue Coordinator QMNn - Queue-monitor processes Dnnn - Dispatcher Processes (multiplex server-processes on behalf of users) Snnn - Shared server processes (serve client-requests) MMAN - Internal process (used for internal database tasks) LSP0 - Logical standby coordinator process (controls Data Guard log-application) MRP - Media-recovery process (detached recovery-server process) MMON - Memory-monitor process MMNL - Memory monitor light (gathers and stores AWR statistics) PSP0 - Process-spawner (spawns Oracle processes) RFS - Remote file server process (archive to a remote site) DBRM ...
Post a Comment
Read more

20. TRIM Function

Which SELECT statement will the result 'elloworld' from the string 'HelloWorld'? A. SELECT SUBSTR( 'HelloWorld',1) FROM dual; B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual; C. SELECT LOWER(SUBSTR('HellowWorld', 1, 1) FROM dual; D. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual; E. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual; Answer: E Explanation: TRIM function accept a string describing the data you would like to trim from a column value. It can trim from both side of column value i.e. left and right. In the following statement this functionwill trim as SELECT LOWER(TRIM ('+' FROM 'HelloWorld')) FROM dual; From the above statement trim function will remove the character 'H' from 'HelloWorld' and LOWER function will convert the remaining character to lower case.
Post a Comment
Read more