Spotting null values using CASE clause

[Admin]

Here is the problem:

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Profiling the Customers table, answer the following question.

Are there any columns with null values? Indicate any below. Select all that apply.

Company

Postal Code

Fax

Address

Phone

FirstName

Here is my tentative solution:

SELECT *
FROM customers
WHERE Company IS NULL

Source: https://www.coursera.org/learn/sql-for-data-science

With that query, it is possible to get null values for Company. But not able to figure out how to include other fields (postal code, fax…) with it.

---

Reply

---

For starters, you’re returning rows, when the question asked about columns

see the difference?

and supposing you were to do something like this —

SELECT *
  FROM customers
 WHERE Company IS NULL
    OR PostalCode IS NULL
    OR Fax IS NULL
etc.

Still, you will be returning null values.

Doesn’t address the question asked — Are there any columns with null values?

the answer to this question should be a flat YES or NO.

but more instructive would be to identify the columns.

SELECT CASE WHEN COUNT(*) > COUNT(Company)
            THEN 'Company has NULLs'
            ELSE NULL END
     , CASE WHEN COUNT(*) > COUNT(PostalCode)
            THEN 'PostalCode has NULLs'
            ELSE NULL END
   etc.
  FROM Customers

[Author]

Posts