Data quality: completeness

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A students table has a row where lastName is empty (NULL) but lastName is a required field. Which data quality problem is this?

A Incomplete data: a required value is missing

B Inaccurate data: the value is wrong

2. Which statement describes complete data?

A Every record has a value in each required field and no needed records are missing

B Every value is spelled correctly and uses the right data type

3. A school has 10 students but the students table only holds 8 rows. What is wrong?

A Missing values in a field, so the data is inaccurate

B Missing records, so the data is incomplete

4. What does NULL mean in an SQL table?

A No value has been stored in that field

B The number zero has been stored in that field

5. Which query finds rows with a missing mark in the results table?

A SELECT * FROM results WHERE mark = NULL;

B SELECT * FROM results WHERE mark IS NULL;

Match the Pairs

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

Left column		Right column
1WHERE email IS NULL		ANULL cannot be tested with =, so this returns no rows and the missing values are not found. It should be WHERE house IS NULL.
2SELECT COUNT(*) - COUNT(phone) FROM students;		BlastName VARCHAR(30) NOT NULL
3A required field must not be left empty, so the table stops a record being saved without it		CSELECT s.firstName FROM students s LEFT JOIN results r ON s.studentId = r.studentId WHERE r.studentId IS NULL;
4Missing records are found by looking for a parent record with no matching rows in the related table		DFinds records where the email value is missing, so the data is incomplete for that field
5SELECT * FROM students WHERE house = NULL;		ECounts how many records have no phone value, because COUNT(phone) skips NULLs
6INSERT INTO students (studentId, firstName) VALUES (11, 'Kai');		FThe record is incomplete because required fields like lastName and yearLevel are not given a value, so they are NULL. Every required field needs a value.

Definitions

completeness

Fill in the Blanks

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

SELECT * FROM students WHERE house IS ____;

SELECT * FROM products WHERE stock __ NULL;

SELECT firstName FROM students WHERE points IS ___ NULL;

SELECT firstName FROM students WHERE studentId ______ (SELECT studentId FROM results);

CREATE TABLE club ( name VARCHAR(30) ________ );

Spot the Error

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

Question 1

A SELECT * FROM students

B WHERE house = NULL

C ORDER BY lastName;

Question 2

A SELECT COUNT(house) AS missing

B FROM students

C WHERE house IS NULL;

Question 3

A CREATE TABLE contacts (

B id INTEGER PRIMARY KEY,

C name TEXT,

D email TEXT NULL

E );

Question 4

A UPDATE students

B SET house = 'Red'

C WHERE house IS NOT NULL

D AND studentId = 4;

Question 5

A if name != '':

B print('Name missing')

C else:

D print('Saved')

Answer Key

Data quality: completeness

Section A: Quick Questions

A — Incomplete data: a required value is missing

A — Every record has a value in each required field and no needed records are missing

B — Missing records, so the data is incomplete

A — No value has been stored in that field

B — SELECT * FROM results WHERE mark IS NULL;

Section B: Match the Pairs

WHERE email IS NULL → Finds records where the email value is missing, so the data is incomplete for that field

SELECT COUNT(*) - COUNT(phone) FROM students; → Counts how many records have no phone value, because COUNT(phone) skips NULLs

A required field must not be left empty, so the table stops a record being saved without it → lastName VARCHAR(30) NOT NULL

Missing records are found by looking for a parent record with no matching rows in the related table → SELECT s.firstName FROM students s LEFT JOIN results r ON s.studentId = r.studentId WHERE r.studentId IS NULL;

SELECT * FROM students WHERE house = NULL; → NULL cannot be tested with =, so this returns no rows and the missing values are not found. It should be WHERE house IS NULL.

INSERT INTO students (studentId, firstName) VALUES (11, 'Kai'); → The record is incomplete because required fields like lastName and yearLevel are not given a value, so they are NULL. Every required field needs a value.

Section C: Definitions

completeness — Data quality where every record has a value in each required field and no needed records are missing. In SQL, a missing value shows as NULL.

Section D: Fill in the Blanks

SELECT * FROM students
WHERE house IS NULL;

SELECT * FROM products
WHERE stock IS NULL;

SELECT firstName FROM students
WHERE points IS NOT NULL;

SELECT firstName FROM students
WHERE studentId NOT IN
(SELECT studentId FROM results);

CREATE TABLE club (
  name VARCHAR(30) NOT NULL
);

Section E: Spot the Error

B — NULL means a value is missing, and = can never match it. Use IS NULL to find students with no house: WHERE house IS NULL.

A — COUNT(house) skips NULL values, so it would count 0 missing houses. COUNT(*) counts every row that the WHERE clause finds.

D — NULL allows the email to be left empty, which makes the data incomplete. A required field should be declared NOT NULL.

C — Filling in a missing value must target rows where the house is NULL. IS NOT NULL would overwrite houses that already exist.

A — The completeness check must catch an empty name. != '' is true when a name was typed, so the check is back to front. It should be name == ''.