Null values

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A student has not handed in a task yet. Which value best shows 'no mark recorded' in the mark column?

A 0

B NULL

2. Which statement about NULL is correct?

A NULL is the same as an empty string ''

B NULL is not the same as an empty string ''

3. Which query correctly finds students with no house recorded?

A SELECT * FROM students WHERE house IS NULL;

B SELECT * FROM students WHERE house = NULL;

4. Which query returns only rows where the mark has a value?

A WHERE mark IS NOT NULL

B WHERE mark != NULL

5. What does the NOT NULL constraint do on a column?

A It stops a row being saved with that field empty

B It fills the field with 0 when no value is given

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1email VARCHAR(50) NOT NULL		AThe email field must always have a value, so a record cannot be saved with it left empty (NULL)
2SELECT * FROM students WHERE house IS NULL;		BWHERE mark IS NULL
3To test for a missing value in a WHERE clause, you must use IS NULL, not an equals sign		CFinds the students who have no value stored in the house field
4A primary key can never be NULL, because every record needs a value that identifies it		DUses = to test for NULL. NULL is not a value, so the comparison never matches. It should be WHERE mark IS NULL.
5SELECT * FROM results WHERE mark = NULL;		ETries to insert NULL into the primary key studentId. A primary key can never be NULL, so it needs a unique value such as 11.
6INSERT INTO students (studentId, firstName) VALUES (NULL, 'Ava');		FstudentId INT PRIMARY KEY

Definitions

NULL

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT firstName FROM students WHERE house _______;

name VARCHAR(30) ________,

SELECT COUNT(*) FROM students WHERE house IS ___ NULL;

studentId INT ___________,

UPDATE students SET house = ____ WHERE studentId = 4;

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT firstName

B FROM students

C WHERE house = NULL;

Question 2

A UPDATE staff SET phone=''

B WHERE staffId = 3;

C SELECT * FROM staff;

Question 3

A CREATE TABLE books (

B bookId INT PRIMARY KEY NULL,

C title TEXT NOT NULL

D );

Question 4

A CREATE TABLE pets (

B petId INT PRIMARY KEY,

C name TEXT IS NOT NULL

D );

Question 5

A SELECT name FROM staff

B WHERE phone = ''

C ORDER BY name;

Answer Key

Null values

Section A: Quick Questions

B — NULL

B — NULL is not the same as an empty string ''

A — SELECT * FROM students WHERE house IS NULL;

A — WHERE mark IS NOT NULL

A — It stops a row being saved with that field empty

Section B: Match the Pairs

email VARCHAR(50) NOT NULL → The email field must always have a value, so a record cannot be saved with it left empty (NULL)

SELECT * FROM students WHERE house IS NULL; → Finds the students who have no value stored in the house field

To test for a missing value in a WHERE clause, you must use IS NULL, not an equals sign → WHERE mark IS NULL

A primary key can never be NULL, because every record needs a value that identifies it → studentId INT PRIMARY KEY

SELECT * FROM results WHERE mark = NULL; → Uses = to test for NULL. NULL is not a value, so the comparison never matches. It should be WHERE mark IS NULL.

INSERT INTO students (studentId, firstName) VALUES (NULL, 'Ava'); → Tries to insert NULL into the primary key studentId. A primary key can never be NULL, so it needs a unique value such as 11.

Section C: Definitions

NULL — A field with no value at all. It is not zero and not an empty string. Test for it with IS NULL, never = NULL. A primary key can never be NULL.

Section D: Fill in the Blanks

SELECT firstName FROM students WHERE house 
IS NULL;

name VARCHAR(30) NOT NULL,

SELECT COUNT(*) FROM students WHERE house IS 
NOT NULL;

studentId INT PRIMARY KEY,

UPDATE students SET house = NULL
 WHERE studentId = 4;

Section E: Spot the Error

C — Nothing is ever equal to NULL, so = NULL never matches any row. Test for a missing value with IS NULL: WHERE house IS NULL;

A — An empty string '' is a value (text with no characters), not a missing value. To record that there is no phone number, set the field to NULL: SET phone=NULL

B — A primary key can never be NULL because it must identify every record. Remove NULL: bookId INT PRIMARY KEY,

C — IS NOT NULL is used to test values in a WHERE clause. When defining a column, the constraint is written NOT NULL: name TEXT NOT NULL

B — An empty string is not NULL, so this would not find staff with no phone value recorded. Use IS NULL: WHERE phone IS NULL