w3resource

SQL Exercises: Filter rows against NULL

SQL Wildcard & Special Operator: Exercise-20 with Solution

From the following table, write a SQL query to find all those customers who does not have any grade. Return customer_id, cust_name, city, grade, salesman_id.

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

Sample Solution:

-- This query selects all columns from the 'customer' table.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'customer').
FROM customer
-- Filters the rows to only include those where the 'grade' column is NULL.
WHERE grade IS NULL;

Output of the Query:

customer_id	cust_name	city	grade	salesman_id
3001		Brad Guzan	London		5005

Code Explanation:

The said SQL query to retrieve all columns (denoted by "SELECT *") from the 'customer' table where the "grade" column is NULL. The "WHERE grade IS NULL" condition filters the results to only retrieve rows where the "grade" column is missing a value or has a NULL value.

Relational Algebra Expression:

Relational Algebra Expression: Filter rows against NULL.

Relational Algebra Tree:

Relational Algebra Tree: Filter rows against NULL.

Explanation :

Syntax of filter rows against NULL

Visual presentation :

Exercise: Filter rows against NULL

Practice Online


Query Visualization:

Duration:

Query visualization of Filter rows against NULL - Duration

Rows:

Query visualization of Filter rows against NULL - Rows

Cost:

Query visualization of Filter rows against NULL - Cost

 

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filter rows using not like and % character.
Next SQL Exercise: Using where clause with not operator and NULL.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.