Practise · Add a row that fits

Opening your saved work…

The reading

Add a row that fits

INSERT INTO menu_items (name, category, size, price) VALUES ('Chai Latte', 'Tea', 'Large', 5.50) adds one row. Three habits live in that one statement. Name the columns: SQL lets you skip the column list and supply every value in table order, and every script that does so breaks the day someone adds a column. Leave id out: INTEGER PRIMARY KEY hands out the next number, and the row gets 46 because 45 was the last. Read changes() afterwards, or look at the row: one row, the one you meant.

INSERT INTO menu_items (name, category, size, price)
VALUES ('Chai Latte', 'Tea', 'Large', 5.50);

One new row, id 46, with NULL in every column the statement did not name. Open in the playground

A column you do not name gets its DEFAULT if the table has one, and NULL if it does not. calories, is_seasonal and description are not in the list above, so the new row has NULL in all three. That is fine when NULL means 'not recorded yet' and wrong when a report will treat it as zero.

Every constraint the table was created with is a rule about which inserts fail. A menu item with no name is refused: NOT NULL constraint failed: menu_items.name. An employee whose shop_id names a shop that does not exist is refused, once foreign keys are on: FOREIGN KEY constraint failed. The message names the rule that fired, which is why you paste error messages into reports as text: the wording is the diagnosis. A refused insert is not a failure of the exercise. It is the table doing its job, and you will write inserts that must fail on purpose in Lab 10.

Rule of thumb. Name the columns, leave the id out, and expect the table to refuse a row that breaks its rules.

Going deeper (optional, not on the exam)

The positional form, INSERT INTO menu_items VALUES (NULL, 'Chai Latte', 'Tea', 'Large', 5.50, NULL, NULL, NULL), runs. It also puts a value in the wrong column the day the table's column order changes, and nothing tells you.

Add a row that fits · INSERT with named columns, defaults, a parent that exists · level 5 of 6

The tea called Tea

Level 5: Repair it · Exercise 1 of 1

A new item appears on the menu with the name Tea and the category Earl Grey.

This insert runs and makes a row, with its name and category swapped. Fix it so the row is an Earl Grey in the Tea category, Small, at 3.25, and say why the engine accepted the wrong version.

Data: Northshore Coffee. Every run starts from a fresh copy, so nothing you try here is permanent.

I would like help with this exercise

Each run starts from a fresh copy of the data, then shows the rows the question is about.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run to see the rows the question is about.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.