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 3 of 6

Which id does the new shop get?

Level 3: Predict the result · Exercise 1 of 1

There are eight shops, ids 1 to 8. INSERT INTO shops (name, city) VALUES ('Northshore Harbor', 'Duluth') leaves the id out. Before you check, what id will the new shop receive?

SELECT MAX(id) + 1 AS next_id
FROM shops;

The query. Predict before you run it.

Data: Northshore Coffee.

I would like help with this exercise

Opens once your prediction is right, or after the worked answer.

Query results

The real result appears here after you run it.

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