SQL Café · Lesson 7 of 8

Please build a new box for reviews.

So far: SELECTFROM … JOIN … ONWHEREGROUP BYHAVINGORDER BYLIMITCOUNT SUM AVG MIN MAXINSERT UPDATE DELETE Lesson 1 · Lesson 2 · Lesson 3 · Lesson 4 · Lesson 5 · Lesson 6

Until now, the menu and orders tables were already there for you. Today you build a table of your own. You choose its name, its columns, what kind of data each column holds, and the rules it must follow.

You say to the café manager “Please make a new table for reviews. Each review needs its own number, the menu item it is about, how many stars (always filled in), and a comment. Thank you!”
↓ same thing in SQL
CREATE TABLE reviews (id INTEGER PRIMARY KEY, item_id INTEGER REFERENCES menu(id), stars INTEGER NOT NULL, comment TEXT);
The one thing to remember

Every column is: name, kind, rules

Making a table is like designing a paper form. Every box on the form gets a label, says what kind of answer goes in it (“numbers only”), and may have rules (“must be filled in”). In SQL each column is written in exactly that order.

starsNameWhat the column is called
INTEGERKindWhat sort of data fits in it
NOT NULLRulesOptional checks the database enforces
What CREATE TABLE gives you: an empty table, ready for rows
idINTEGER PRIMARY KEYitem_idINTEGER → menu.idstarsINTEGER NOT NULLcommentTEXT
No rows yet. Add them with INSERT (Lesson 6).
Kinds of data

Three kinds cover almost everything

The kind tells the database what can go in a column. It refuses anything else, so a typo like stars = 'five' can't sneak in.

INTEGER Whole numbers 1 42 310 -3

For ids, stars, calories, anything you count. 4.5 does not fit.

DECIMAL Numbers with a point 3.50 2.8 0.99

For prices and measurements. DECIMAL(5,2) means up to 5 digits, 2 of them after the point.

TEXT Words 'Lovely and hot'

For names and comments. Written in quotes when you add data.

Other names you will meet: different databases use slightly different words for the same ideas. INT means INTEGER. VARCHAR(100) is text with a maximum length of 100 characters (letters, digits, spaces and so on). NUMERIC and REAL are close cousins of DECIMAL. The playground understands all of these.
Rules

Rules the database checks for you

Rules come after the kind. You can give a column no rules, one rule, or several. Every time a row is added or changed, the database checks them and refuses anything that breaks one.

RuleSay it asExampleWhat it promises
PRIMARY KEY“each row's own key”id INTEGER PRIMARY KEYNever empty, never repeated. One per table, usually the id. You met this in Lesson 6.
NOT NULL“must be filled in”stars INTEGER NOT NULLA row without a value here is refused.
DEFAULT“if left out, use…”points INTEGER DEFAULT 0When INSERT leaves the column out, this value is used instead of empty.
UNIQUE“no two the same”email TEXT UNIQUETwo rows can't share a value, but the column may be empty.
REFERENCES“must match a row in…”item_id INTEGER REFERENCES menu(id)The foreign key from Lesson 6: the value must exist in the other table.
Why bother with rules? Without them, mistakes slip in quietly: a review with no stars, two customers with the same id, an order for an item that doesn't exist. Rules stop bad data at the door, so every SELECT you write later can trust the table.
The biggest eraser

DROP TABLE removes everything

DROP TABLE removes the whole table: every row, every column, and the table itself.

It is even bigger than DELETE without WHERE, which at least leaves the empty table behind. DROP TABLE reviews is fine for a practice table you made by mistake, but in a real database please double check the name first, because there is usually no Undo.

The database protects links here too: while orders REFERENCES menu, it refuses to drop menu. The playground also asks you to confirm every DROP.

The pattern to learn by heart

Name the table, then list the columns

CREATE TABLE reviews (“Please make a new table called reviews, with…”
  id INTEGER PRIMARY KEY,“an id of whole numbers, each row's own key,”
  stars INTEGER NOT NULL,“stars, whole numbers, always filled in,”
  comment TEXT“and a comment in words.”
);Close the bracket, add the full stop.
Name, kind, rules, comma. Name, kind, rules, comma. No comma after the last one.
Read the code one word at a time

Every piece has a meaning

Please press Next (or click any piece) and watch the sentence grow underneath.

Say it out loud
Today's words

Say each one out loud

WriteSay it asExampleWhy it's there
Building and removing tables
CREATE TABLE“please make a new table called…”CREATE TABLE reviews (…)Makes a new, empty table. The columns go inside the brackets.
DROP TABLE“please throw away the table…”DROP TABLE reviewsRemoves the table and all its rows.
Inside the brackets
INTEGER“whole numbers”stars INTEGERThe kind of data. Others: DECIMAL, TEXT.
,“and”id INTEGER, stars INTEGERSeparates one column from the next. None after the last column.
( )“with these columns”reviews (id INTEGER)Holds the whole list of columns.
Your turn

Playground: build your own tables

Please type a statement and press Run. You can use CREATE TABLE and DROP TABLE, plus everything from earlier lessons: SELECT, INSERT, UPDATE and DELETE. Reset tables puts the café back to how it started.

Mission

Ctrl + Enter also runs
Or try one of these
    The tables right now
    Lock it in

    Five quick questions

    Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.

    Take this with you

    Your pocket card

    Please take a screenshot of this card. Read it once tomorrow and once next week.

    CREATE TABLECREATE TABLE name (column KIND RULES, …)
    INTEGERWhole numbers.
    DECIMALNumbers with a point, like prices.
    TEXTWords.
    PRIMARY KEYEach row's own key: never empty, never repeated.
    NOT NULLMust be filled in.
    DEFAULTUsed when INSERT leaves the column out.
    REFERENCESMust match a row in another table.
    DROP TABLEThrows the whole table away. Look twice first.

    • Every column: name, kind, rules. Commas between columns, none after the last.
    • Rules stop bad data at the door.

    SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.