Please build a new box for reviews.
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.
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 calledINTEGERKindWhat sort of data fits in itNOT NULLRulesOptional checks the database enforces| idINTEGER PRIMARY KEY | item_idINTEGER → menu.id | starsINTEGER NOT NULL | commentTEXT |
|---|---|---|---|
| No rows yet. Add them with INSERT (Lesson 6). | |||
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.
1 42 310 -3
For ids, stars, calories, anything you count. 4.5 does not fit.
3.50 2.8 0.99
For prices and measurements. DECIMAL(5,2) means up to 5 digits, 2 of them after the point.
'Lovely and hot'
For names and comments. Written in quotes when you add data.
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 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.
| Rule | Say it as | Example | What it promises |
|---|---|---|---|
| PRIMARY KEY | “each row's own key” | id INTEGER PRIMARY KEY | Never empty, never repeated. One per table, usually the id. You met this in Lesson 6. |
| NOT NULL | “must be filled in” | stars INTEGER NOT NULL | A row without a value here is refused. |
| DEFAULT | “if left out, use…” | points INTEGER DEFAULT 0 | When INSERT leaves the column out, this value is used instead of empty. |
| UNIQUE | “no two the same” | email TEXT UNIQUE | Two 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. |
DROP TABLE removes everything
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.
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.Every piece has a meaning
Please press Next (or click any piece) and watch the sentence grow underneath.
Say each one out loud
| Write | Say it as | Example | Why 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 reviews | Removes the table and all its rows. |
| Inside the brackets | |||
| INTEGER | “whole numbers” | stars INTEGER | The kind of data. Others: DECIMAL, TEXT. |
| , | “and” | id INTEGER, stars INTEGER | Separates one column from the next. None after the last column. |
| ( ) | “with these columns” | reviews (id INTEGER) | Holds the whole list of columns. |
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.
Five quick questions
Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.
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.