INSERT Queries and Adding Data

# CHAPTER 6

INSERT Queries and Adding Data #

1. Introduction #

2. Learning Objectives #

• Understand the syntax of the INSERT INTO statement.

• Add a single row of data into a table.

• Insert data into specific, targeted columns.

• Perform bulk inserts to add multiple rows in one command.

• Understand how strings and numbers are formatted in SQL queries.

3. The INSERT INTO Syntax #

1. 1. The columns you want to fill.

1. 2. The VALUES you want to put into those columns.

*Important Syntax Rule:*

• Numbers (INT, DECIMAL) do not need quotes. e.g., 50

• Strings/Text (VARCHAR, DATE) MUST be wrapped in single quotes! e.g., 'Alice' or '2023-01-01'.

4. Inserting a Single Row #

-- Insert a brand new employee into the table
INSERT INTO employees (id, name, salary) 
VALUES (1, 'John Doe', 55000);

*(Notice how the order of the VALUES exactly matches the order of the columns defined in the first set of parentheses!)*

5. Inserting into Specific Columns #

-- Only inserting the ID and the Name. The salary is skipped!
INSERT INTO employees (id, name) 
VALUES (2, 'Jane Smith');

6. Bulk Inserts (Inserting Multiple Rows) #

-- Bulk Insert 3 employees at once!
INSERT INTO employees (id, name, salary) 
VALUES 
    (3, 'Alice Johnson', 60000),
    (4, 'Bob Builder', 45000),
    (5, 'Charlie Brown', 50000);

*(This is how web applications upload massive CSV files into a database in milliseconds!)*

7. Mini Project: Product Inventory System #

-- Add our initial inventory
INSERT INTO products (product_id, product_name, price, in_stock)
VALUES 
    (101, 'Mechanical Keyboard', 129.99, TRUE),
    (102, 'Wireless Mouse', 49.50, TRUE),
    (103, 'Gaming Monitor', 349.00, FALSE);

8. Common Mistakes #

• Mismatched Column/Value Count: If you list 3 columns in the first parentheses, but only provide 2 values in the second parentheses, SQL will instantly crash with a "Column count doesn't match value count" error.

• Forgetting Single Quotes for Text: INSERT INTO users (name) VALUES (John) will fail. SQL thinks John is a system command because it lacks quotes. It MUST be 'John'.

• Double Quotes vs Single Quotes: Standard SQL requires strings to be wrapped in Single Quotes ''. Double quotes "" are technically used for system identifiers in strict SQL engines like PostgreSQL. Always use single quotes for data!

9. Best Practices #

• Always Explicitly Name Columns: Technically, if you insert data into *every* column, SQL lets you skip writing the column names: INSERT INTO employees VALUES (1, 'John', 50000). Never do this. If another developer adds a "department" column to the table tomorrow, your query will instantly break because the implicit order changed!

10. Exercises #

1. 1. What SQL keyword precedes the data you are trying to insert into the table?

1. 2. Which of the following values MUST be wrapped in single quotes during an INSERT: 100, 'New York', or 45.99?

11. SQL Challenges #

INSERT INTO books (book_id, title, published_year)
VALUES 
    (1, '1984', 1949),
    (2, 'Dune', 1965);

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain why relying on "Implicit Inserts" (omitting the column names in the INSERT INTO statement) is considered a dangerous architectural anti-pattern in production codebases.

• Q: A backend Node.js script needs to insert 1,000 new users into the database. Should the script execute 1,000 separate INSERT commands, or a single bulk INSERT command? Explain the performance implications.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #