Data Science/Business Analytics for Small Business Applications › Forums › SQL › Spotting null values using CASE clause
Tagged: CASE clause in SQL
- This topic is empty.
April 23, 2022 at 12:46 pm #144AdminKeymaster
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.
Here is my tentative solution:
SELECT * FROM customers WHERE Company IS NULL
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.
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
- You must be logged in to reply to this topic.