Level 1 · Set up a supplier import table

Most of SQL is reading tables that someone else built. This chapter is about building your own. The first two statements you need are CREATE TABLE, which defines a table’s columns, and INSERT, which puts rows into it.

CREATE TABLE

A table definition lists each column with a data type:

CREATE TABLE supplier_import (
  import_id INTEGER PRIMARY KEY,
  product_name VARCHAR,
  category VARCHAR,
  unit_price DECIMAL(10, 2),
  received_on DATE
);

The type decides what the column accepts and how it behaves. INTEGER holds whole numbers, VARCHAR holds text, DATE holds a calendar day, and DECIMAL(10, 2) holds exact numbers with up to 10 digits, 2 of them after the decimal point. Money always goes in DECIMAL, never in a floating-point type, because floats store binary approximations: 0.1 + 0.2 is not exactly 0.3 in a DOUBLE.

PRIMARY KEY means every import_id must be present and unique. Insert a second row with id 3 and the database refuses it with a constraint error. That is the point: keys are how tables join, so the database protects them.

INSERT INTO … VALUES

INSERT INTO supplier_import VALUES
  (1, 'Harlow Brass Pendant', 'Lighting', 189.00, '2025-11-03'),
  (5, 'Wren Ceramic Table Lamp', 'Lighting', NULL, '2025-11-04');

Each parenthesized list is one row, with values in the same order as the columns. Text and dates go in single quotes, numbers do not, and a missing value is written NULL without quotes. 'NULL' in quotes would be a four-letter string, not a missing value.

When you only fill some columns, name them, and the rest become NULL (or their default):

INSERT INTO supplier_import (import_id, product_name) VALUES (6, 'Sample Lamp');

Naming the columns is also safer in general: the insert keeps working if someone adds a column later.

Read it back

After any change, query the table to confirm it holds what you expect. Here the final SELECT adds a review flag. The CASE checks for the missing price first, because a NULL price is neither above nor below 500: NULL > 500 is unknown, and an unknown condition never matches a WHEN.

In this game every run is rolled back afterwards, which is why the solution creates the table each time. On a real database the table would stay until someone drops it.

Common mistakes

• Quoting numbers or forgetting quotes around text and dates. '189.00' usually still converts, but Lighting without quotes is read as a column name and fails.
• Putting values in the wrong column order. VALUES follows the table’s column order unless you list the columns.
• Writing 'NULL' or an empty string '' for a missing value. Both are real values, so IS NULL will not find them.
• Using FLOAT or DOUBLE for prices.

In SQL Server

The same table in T-SQL usually uses NVARCHAR(100) for text (Unicode) and often an IDENTITY column that numbers rows for you:

CREATE TABLE dbo.SupplierImport (
  ImportID INT IDENTITY(1,1) PRIMARY KEY,
  ProductName NVARCHAR(100),
  UnitPrice DECIMAL(10,2)
);

DuckDB’s VARCHAR needs no length, and INSERT INTO ... VALUES with several rows works the same in both.