SQL Tutorial for Beginners 3: INSERT - Adding Records to Your Database
Based on Corey Schafer's video on YouTube. If you like this content, support the original creators by watching, liking and subscribing to their content.
Use `INSERT INTO people VALUES (...)` to add rows, but it depends on the table’s column order.
Briefing
Populating a database table starts with the INSERT statement, and the key practical detail is how SQL matches the values you provide to the table’s columns. After creating a `people` table with an integer `ID` and a variable-length string `name` (up to 255 characters), the next step is adding rows—first with a simple INSERT that relies on the table’s column order, then with a safer INSERT that explicitly names the columns.
The tutorial begins with the straightforward form: `INSERT INTO people VALUES (1, 'Corey')`. Running the query and refreshing the data shows a new record with `ID` = 1 and `name` = Corey. The same pattern adds additional rows, such as `VALUES (2, 'Travis')`, which results in a second record appearing in the table.
Next comes a more controlled approach: specifying the exact fields to populate. Instead of relying on the implicit order of columns, SQL can target columns directly: `INSERT INTO people (ID, name) VALUES (3, 'Dave')`. Here, the first value maps to `ID` and the second maps to `name`. Because the column list is explicit, the values can be provided in any order that matches the listed columns. The tutorial demonstrates this flexibility by switching the column order to `INSERT INTO people (name, ID) VALUES ('Bronx', 4)`, and the database still stores the correct `ID` and `name` in their respective fields.
That explicit mapping also prevents a common mistake: mixing up the order of values when the column list isn’t used (or when it’s used incorrectly). An example shows an attempted insert where `name` and `ID` are effectively swapped—trying to insert `Corey` into the integer `ID` field and `5` into the string `name` field. SQL rejects the operation with a syntax/type error (“invalid input syntax for integer”), and no bad row is added. The table remains clean: there is no `ID` of 5 and no `name` of 5 because the insert fails entirely.
By the end, the table contains multiple valid records, setting up the next lesson on retrieving data with `SELECT` and filtering with `WHERE`. The takeaway is simple but important: INSERT works reliably when values are correctly mapped to columns, and SQL will block inserts that violate data types—especially when integers and strings get mixed up.
Cornell Notes
The INSERT command adds new rows to the `people` table by providing values for `ID` (integer) and `name` (varchar up to 255 characters). A basic form like `INSERT INTO people VALUES (1, 'Corey')` depends on column order, while a safer form names the columns explicitly, such as `INSERT INTO people (ID, name) VALUES (3, 'Dave')`. Explicit column lists let values be supplied in a different order as long as they match the listed columns, e.g., `INSERT INTO people (name, ID) VALUES ('Bronx', 4)`. If values are mismatched—like trying to insert `'Corey'` into the integer `ID` field—SQL throws an error and the invalid row is not added. This prepares the table for later retrieval using `SELECT` and `WHERE`.
What is the simplest way to insert a new record into the `people` table, and what does it rely on?
How does specifying column names in an INSERT statement change how values are matched?
Why is `INSERT INTO people (name, ID) VALUES ('Bronx', 4)` still correct even though the order looks reversed?
What happens when values are accidentally mismatched by type, such as inserting a string into an integer column?
What practical lesson should beginners take from the error example?
Review Questions
- When would you choose `INSERT INTO people VALUES (...)` over `INSERT INTO people (ID, name) VALUES (...)`?
- How does SQL determine which value goes into `ID` versus `name` when the column list is provided?
- What is the expected outcome of an INSERT that tries to put `'Corey'` into the integer `ID` field?
Key Points
- 1
Use `INSERT INTO people VALUES (...)` to add rows, but it depends on the table’s column order.
- 2
For clarity and safety, specify columns explicitly: `INSERT INTO people (ID, name) VALUES (...)`.
- 3
Explicit column lists allow values to be provided in any order as long as they match the listed columns.
- 4
Swapping value order incorrectly can cause type errors, such as inserting a string into an integer field.
- 5
When an INSERT fails due to invalid input, the database does not add the incorrect record.
- 6
After inserting multiple valid rows, the table becomes ready for querying with `SELECT` and filtering with `WHERE`.
- 7
Always verify that each provided value matches the target column’s data type (integer vs varchar).