INSERT with specific columns in SQL - Mini Project: Build & Apply

Insert Data into Specific Columns in SQL

📖 Scenario: You are managing a small bookstore database. You need to add new books to the books table. Sometimes you only have partial information, so you want to insert data into specific columns without filling all columns.

🎯 Goal: Learn how to insert new rows into a SQL table by specifying only certain columns, leaving others with default or NULL values.

📋 What You'll Learn

Create a table called books with columns id, title, author, and year_published

Insert a new book specifying only the title and author columns

Insert another book specifying title, author, and year_published

Insert a book specifying all columns including id

Use the INSERT INTO statement with specific columns listed

💡 Why This Matters

🌍 Real World

In real databases, you often insert data when you only have some information available. Knowing how to insert into specific columns helps keep data accurate and flexible.

💼 Career

Database administrators and developers frequently insert data into tables. Understanding how to specify columns in INSERT statements is a fundamental skill for managing data.

1

Create the books table

Write a SQL statement to create a table called books with these columns: id as an integer primary key, title as text, author as text, and year_published as an integer.

SQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books and define each column with its type. Make id the primary key.

2

Insert a book with only title and author

Write a SQL INSERT INTO statement to add a new book with title 'The Great Gatsby' and author 'F. Scott Fitzgerald'. Specify only the title and author columns in the insert statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);
-- Your code here to insert the first book with title and author only

Need a hint?

Use INSERT INTO books (title, author) and provide values for those columns only.

3

Insert a book with title, author, and year_published

Write a SQL INSERT INTO statement to add a new book with title '1984', author 'George Orwell', and year_published 1949. Specify these three columns in the insert statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');
-- Your code here to insert the second book with title, author, and year_published

Need a hint?

Specify all three columns in the INSERT INTO statement and provide matching values.

4

Insert a book specifying all columns including id

Write a SQL INSERT INTO statement to add a new book with id 3, title 'To Kill a Mockingbird', author 'Harper Lee', and year_published 1960. Specify all columns in the insert statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');

INSERT INTO books (title, author, year_published) VALUES ('1984', 'George Orwell', 1949);
-- Your code here to insert the third book with all columns including id

Need a hint?

List all columns in the INSERT INTO statement and provide values for each.