Using the NVL() function Create a SQL query that shows the customer address1, cu

Question

Using the NVL() function

Create a SQL query that shows the customer address1, customer address 2, customer city, customer state and customer postal code from the CUSTOMERS table. Use the NVL() function to turn all NULL values for customer street address 2 to say ‘First Floor’.

Using the NVL2() function

Modify the previous SQL query and add the customer email address. Use the NVL2() to return the string ‘on file’ if someone has an email address already. If someone doesn’t have an email address use the same NVL2() function to return an email address in the format first name’.’last name’@email.com’. Use to concatenation operator ‘||’ to form the email address.

Using the NULLIF() function

Create a SQL query that shows the product ID, product name and tags from the PRODUCTS_INFORMATION table. Use the NULLIF() function to turn the value returned to NULL if the tag ‘Top seller’ appears in the tags column.

Nesting NULL value functions

Modify the previous SQL query to nest the NULLIF() function inside of a regular NVL() function to show ‘in stock’ for the tags column if the value returned is NULL, which it should be since we used the NULLIF() function to turn all values for the column tags NULL.

Column Data Type Length Precision Scale Nullable CUSTOMER ID CUST FIRST NAME VARCHAR2 CUST LAST NAME VARCHAR2 STREET ADDRESS POSTAL_CODE CITY STATE PROVINCE COUNTRY ID PHONE_NUMBER NUMBER 0 No 20 20 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 CHAR VARCHAR2 VARCHAR2 VARCHAR2 NUMBER VARCHAR2 NUMBER es 30 25 es NLS LANGUAGE es NLS TERRITORY CREDIT LIMIT CUST EMAIL ACCOUNT_ MGR ID 30 30 es

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, NVL(STREET_ADDRESS, "First Floor"), CITY, STATE_PROVINCE, POSTAL_CODE

FROM Customer;

-- 2)

SELECT CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, NVL(STREET_ADDRESS, "First Floor"), CITY, STATE_PROVINCE, POSTAL_CODE, NVL2(CUST_EMAIL, "on file", CUSTOMER_FIRST_NAME|| '.' || CUSTOMER_LAST_NAME || '@email.com')

FROM Customer;

-- 3)

SELECT PRODUCT_ID, PRODUCT_NAME, NULLIF(tag, "Top seller")

FROM PRODUCTS_INFORMATION;

-- 4)

SELECT PRODUCT_ID, PRODUCT_NAME, NVL(NULLIF(tag, "Top seller"), "in stock")

FROM PRODUCTS_INFORMATION;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.