SQL Exercise: Let’s practice Working with Nothing

Spread the love

In this exercise, I will be using AdventureWorks 2008 Database and will write my SQL queries using SSMS 18. All the exercises are from the book: Beginning T-SQL 2008

Q1: Write a query displaying the ProductID, Name, and Color columns from rows in the Production.Product table. Display only those rows where no color has been assigned.

SELECT ProductID, Name, Color
FROM Production.Product
WHERE Color IS NULL;

Q2: Write a query displaying the ProductID, Name, and Color columns from rows in the Production.Product table. Display only those rows in which the color is not blue.

If we only use Color <> ‘Blue’ in our WHERE clause then it will ignore all the NULL values. So we need to take that into consideration when writing our WHERE clause. The query can be written in multiple ways.

The first one simply adds two conditions using OR.
The second one utilizes the ISNULL function. ISNULL returns the specified value IF the expression is NULL otherwise, returns the expression.

SELECT ProductID, Name, Color
FROM Production.Product
WHERE Color IS NULL OR Color <> 'Blue';

SELECT ProductID, Name, Color
FROM Production.Product
WHERE ISNULL(Color, '') <> 'Blue';

Q3: Write a query displaying ProductID, Name, Style, Size, and Color from the Production.Product table. Include only those rows where at least one of the Style, Size, or Color columns contains a value.

The query can be written in multiple ways.

Use COALESCE function. It returns the first non-null value in a list, using that we can check for not Null condition.
We could add multiple conditions and use OR to combine the conditions.

SELECT ProductID, Name, Style, Size, Color
FROM Production.Product
WHERE COALESCE(Style, Size, Color) IS NOT NULL;

SELECT ProductID, Name, Style, Size, Color
FROM Production.Product
WHERE 
	Style IS NOT NULL 
	OR Size IS NOT NULL 
	OR Color IS NOT NULL;

Feel free to reach out with any suggestions. Thank you.

Spread the love