SQL Exercises: Using where clause with not operator and NULL

SQL Wildcard & Special Operator: Exercise-21 with Solution

From the following table, write a SQL query to locate all customers with a grade value. 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 not NULL.
WHERE grade IS NOT NULL;

or equivalent:

-- 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 not NULL.
WHERE NOT grade IS NULL;

Output of the Query:

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

Code Explanation:

The said SQL query to retrieve all columns (denoted by "SELECT *") from the 'customer' table where the "grade" column is NOT NULL. The "WHERE grade IS NOT NULL" condition filters the results to only fetches rows where the "grade" column has a non-NULL value, effectively excluding rows with missing or NULL values in the "grade" column.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation :

Visual presentation :

Practice Online

Query Visualization for Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for alternate Sample Solution:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filter rows against NULL.
Next SQL Exercise: Employees whose last name begins with the letter D.