INSERT

SQL · Unit 11: Changing data

Full plan
SQL
11 exercises

You've spent ten units asking the store questions, and none of them left a mark. Unit 1 said that "SELECT only reads," that the real table "never finds out," and that changing what's stored "takes other words, and those come much later." There are three of them: INSERT, UPDATE, and DELETE. Starting today, the table does find out.

A new customer signs up: Zoe, from Denver, age 29. INSERT INTO names the table and, in parentheses, the columns. VALUES gives a value for each one, in that same sequence. An INSERT doesn't ask anything, so it's not a query anymore. It's a statement, like everything up to the ;. It doesn't return rows either, which is why a SELECT goes under it to show how the table ended up.

INSERT INTO customers
  (name, city, age)
VALUES ('Zoe', 'Denver', 29);
SELECT id, name, city, age
FROM customers
WHERE id >= 7;

Returns

id	name	city	age
7	Hannah	Austin	NULL
8	Brian	Denver	38
9	Zoe	Denver	29

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
Nick signs up, from Denver, age 23. Ann, Valerie, and Brian already lived in Denver. What does the SELECT at the end return?
2. Multiple choice
In a real database, someone runs Zoe's INSERT, isn't sure it worked, and runs it again. What's in customers now?
3. Complete the code
Ruby signs up over the phone. She gives her name and her age, 45, and doesn't say where she lives. Fill in the blank with the list of columns.
4. Multiple choice
An INSERT also runs without the list of columns, as long as VALUES gives a value for every column in the table, left to right: INSERT INTO customers VALUES (9, 'Zoe', 'Denver', 29);. One day someone adds a phone column to customers. What happens to that statement here?
5. Put the lines in order
The same truck brings two devices: the Printer, at 150 with 4 units, and the Camera, at 280 with 6. Put in order the INSERT that adds both and the SELECT that shows what's new. Two lines don't belong.
6. Find the case that fails
Toby signs up, from Boston. Whoever is entering him looks at the table, sees that the last id is 8, and types in 9 by hand. Which of these changes, made a moment earlier, makes the database reject the statement?
7. Find the bug
The Radio arrives, for the Audio section, with 10 units. Whoever entered it didn't have the price handy, which is 35, and left it for later. The database rejects the row. Which line has the problem?
8. Find the bug
Valerie, customer 5, orders another 10 Cables today, product 4. Here SQL answers FOREIGN KEY constraint failed, and the message doesn't say which column it's talking about. Which line has the problem?
9. Put the lines in order
Dylan, customer 6, orders 2 Mice today, product 2. Put in order the INSERT and the SELECT that shows Dylan's orders. Two lines don't belong.
10. Write the code
Molly signs up. She lives in Boston and doesn't give her age. Write only the INSERT. The app then looks at how the whole table ended up. Don't give the id, which the table fills in, and leave the age empty.
11. Write the code
Today, January 15, Ann, customer 1, orders a Laptop, product 5, and 2 pairs of Headphones, product 7. In this store each order is for one product, so that's two rows. Write them in a single INSERT, without giving the id.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →