SQL for Data Analytics · Types and nullability

Types and nullability

Data Types and Nullability5 min

Data Types and Nullability5 min

A note on this video

In the video, the instructor table in the College Courses diagram has an annual_salary column. The college database on this site leaves it out.

The College Courses diagram

Here's the College Courses diagram from the video. The query boxes on this page run against the college database it shows.

The classes and instructors in the college database are real as of October 2026. They're Green River's software development classes from 2026 and early 2027, and the faculty who teach them. Everything else is made up: the students, their enrollments, and their GPAs.

Loading…

The diagram has the same parts as the HR diagram: a box for each table and a line for each relationship. It marks primary keys with a small key instead of an asterisk. It also shows two more things about each column. The column's data type is on the right. A ? after the type means the column can be NULL.

A key made of two columns

The enrollment box in the College Courses diagram has two keys, next to student_id and next to class_id. That means the two columns together are the table's primary key. A primary key picks out exactly one row, so its value can't show up on two rows. In enrollment, neither column does that on its own.

Enrollment rows

Loading the college database…

A student who takes three classes has three rows with the same student_id. A class with 20 students in it has 20 rows with the same class_id. What's different on every row is the pair: one student_id together with one class_id. Each row is one student in one class.

The pair is also what stops a student from being enrolled in the same class twice. A second row with the same pair would break the primary key, so MySQL refuses it with a "Duplicate entry" error, like the one in the widget.

A student fails Data Structures and takes it again the next quarter. With this primary key, can the enrollment table hold both enrollments?

How I'd answer it

Yes. Each row in the class table is one offering of a class, with its own start and end date, so Data Structures in the spring has a different id from Data Structures in the winter. The second enrollment has the same student_id but a different class_id, so its pair is new. Enrolling the same student in the same offering twice is what the key stops.

Data types

A column's data type says what kind of value the column holds. When a value doesn't match the type, MySQL either changes the value so it fits or refuses it with an error. Each box below is a column from the college database. Type in it to see what MySQL does with your value. Green means it stores the value as you typed it, yellow means it changes the value first, and red means it refuses it.

int holds whole numbers, like 1, 2, and 3. Every id column is an int.

varchar(255) holds text, up to the number of characters in the parentheses. first_name can be up to 255 characters long, and phone_number is a varchar(11), so it holds at most 11.

decimal(2,1) holds a number with a decimal point. The 2 is how many digits a value can have in all. The 1 is how many of those come after the decimal point, so a gpa can be anything from -9.9 to 9.9.

date holds a date, written year-month-day, like a student's birthday.

datetime holds a date and a time of day, like enrollment_time. The time comes after the date, on a 24-hour clock.

There are many more types. W3Schools has the full list for MySQL.

Nullable columns

A ? after a column's type in the College Courses diagram means the column can be NULL. A column with no ? has to have a value in every row.

Loading…

In the video, I made gpa nullable. Run this to see how many enrollments have a GPA, for the classes that end on each date.

Enrollments and GPAs, by the date the class ends.
⌘/Ctrl + Enter

The classes that end in December 2026 and March 2027 have no GPAs at all. Why not? What would go wrong if gpa couldn't be NULL?

How I'd answer it

In the college database, a GPA goes in when a class ends. The December and March classes hadn't ended when the data was put together, so their students are enrolled with no GPA. If gpa couldn't be NULL, a student couldn't be enrolled until there was a GPA to put in. In the video I gave the same reason: a student who has just enrolled hasn't turned anything in yet.

In the video, I also made instructor_id nullable, because a class can be on the schedule before anyone is assigned to teach it. The college database has six classes like that.

Write a query that shows the title and start date of every class that doesn't have an instructor yet.
⌘/Ctrl + Enter

A class with no instructor has NULL in instructor_id. = NULL is never true. Use IS NULL.

Which of these new rows can't go into the student table?