INSERT, UPDATE, DELETE
Changing the data in a database: adding rows with INSERT, changing them with UPDATE, removing them with DELETE, the trap of the forgotten WHERE and respect for foreign keys.
- 26 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
INSERT INTO … VALUES: what is it?
Show the answer
The SQL statement that adds to a table one or more rows whose values you write yourself, column by column.
It is better to name the columns:
INSERT INTO client (nom, ville) VALUES ('Léa', 'Lyon')stays correct if the table gains a column. Several rows are writtenVALUES (...), (...). Omitted columns take their default value, or NULL.A developer wants to lower the price of books by 10% and runs this statement. What happens?
```sql
UPDATE produit SET prix = prix * 0.9;`- The price of every product drops by 10%, whatever the category
- Nothing is changed: SQL refuses an UPDATE without a WHERE clause
- Only the first row of the table is changed
- Only the books are changed, since they are the ones the requirement names
Show the answer
The price of every product drops by 10%, whatever the category
Without
WHERE, the statement targets every row, and SQL does not ask for confirmation. It neededWHERE categorie = 'livre'.In an UPDATE, the … clause gives the new values and the … clause chooses the rows to change.
Show the answer
In an UPDATE, the SET clause gives the new values and the WHERE clause chooses the rows to change.
They have separate roles:
UPDATE produit SET stock = 0 WHERE categorie = 'jouet'sets the stock to 0 for toys only.INSERT … SELECT: what is it?
Show the answer
Adding to a table every row that a query returns, in a single statement.
The query replaces the list of values:
INSERT INTO archive (id, date_commande) SELECT id, date_commande FROM commande WHERE .... The source table is not changed, the rows are copied.