CODEWITHSUNDEEP
sqloracle-databaseselect
Jacek Trociński
Jacek Trociński

Reputation: 920

SQL fundamentals practise select statement

I am preparing for the Oracle SQL Fundamentals I exam and am having trouble with this question:

  1. Examine the structure of the EMP table:

    Name       Null?    Type
EMPNO      NOT NULL NUMBER(3)
ENAME               VARCHAR2(25) 
SALARY              NUMBER(10,2)
COMM_PCT            NUMBER(4,2)

I want to generate a report that fulfills the following requirements: 1. Displays employees' names and commission amounts 2. Excludes employees who do not have a commission 3. Displays a zero for employees whose SALARY does not have a value

You issue the following SQL statement:

SQL>SELECT ename, NVL(salary * comm_pct, 0)
FROM emp
WHERE comm_pct <> NULL;

What is the outcome?

ANSWER is It executes successfully but displays no result.

Why does nothing display though?

Also if you guys have any resources for good SQL fundamentals practise questions please let me know.

Upvotes: 1

Views: 372

Answers (1)

tbone
tbone

Reputation: 15473

Be careful with nulls and comparisons. If you want rows where there is some data, you would have "WHERE comm_pct IS NOT NULL". Using the comparison operator <> with NULL will return nothing. 

select * from dual where dummy <> NULL

is not the same as

select * from dual where dummy is not null;

The other stuff in the question is just a distraction from this point. You can't compare a NULL to something else (null really means "I don't know what the heck the value is").

Same thing with = comparison. You don't say "WHERE comm_pct = NULL", you say "WHERE comm_pct IS NULL".

As a reference, here's the online SQL Reference Guide. Here is the link that refers to NULLs and comparisons.

Upvotes: 3

Related Questions