Contents of the CUSTOMERS table

-Based on the contents of the CUSTOMERS table,which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?
A) SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE customer# = 1003);
B) SELECT customer# FROM customers WHERE referred EXISTS (SELECT referred FROM customers WHERE customer# = 1003);
C) SELECT customer# FROM customers WHERE NVL(referred,0)= (SELECT NVL(referred,0)FROM customers WHERE customer# = 1003);
D) SELECT customer# FROM customers WHERE NVL(referred,0)= (SELECT referred FROM customers WHERE customer# = 1003);

Question

Contents of the CUSTOMERS table​
 
-​Based on the contents of the CUSTOMERS table,which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?
A) ​SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE customer# = 1003); 
B) ​SELECT customer# FROM customers WHERE referred EXISTS (SELECT referred FROM customers WHERE customer# = 1003); 
C) ​SELECT customer# FROM customers WHERE NVL(referred,0)= (SELECT NVL(referred,0)FROM customers WHERE customer# = 1003); 
D) ​SELECT customer# FROM customers WHERE NVL(referred,0)= (SELECT referred FROM customers WHERE customer# = 1003);

Quizplus · Accepted Answer

The NVL function in option C ensures that even if the referred field is null for customer# 1003, the query will still return results by treating it as a zero. This allows for an accurate comparison of the referred field with that of other customers who were referred by the same individual as customer# 1003. Option A uses a subquery to select the referred field of customer# 1003 and then compare it to the referred field of all other customers, while option D does the same but without the bulletproofing of null values. Option B has incorrect syntax as it does not use any comparison operator after "EXISTS".

Contents of the CUSTOMERS Table​

Contents of the CUSTOMERS Table