Selecting specific columns in MySQL - Mini Project: Build & Apply

Selecting Specific Columns in SQL

📖 Scenario: You are working with a small online bookstore database. The store keeps track of books with details like title, author, price, and year published.

🎯 Goal: Learn how to write a SQL query that selects only specific columns from the books table, such as the book title and author.

📋 What You'll Learn

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

Insert three specific book records into the books table

Write a SQL query to select only the title and author columns from the books table

Add a WHERE clause to select books published after the year 2000

💡 Why This Matters

🌍 Real World

Selecting specific columns helps you get only the data you need from a database, which makes your queries faster and your results clearer.

💼 Career

Database developers and analysts often write queries to extract specific information from large datasets to support business decisions.

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, price as decimal(5,2), and year_published as integer.

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books and define each column with its type. Remember to set id as the primary key.

2

Insert book records

Insert these three books into the books table with exact values: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 1925), (2, 'Harry Potter and the Goblet of Fire', 'J.K. Rowling', 12.50, 2000), and (3, 'The Da Vinci Code', 'Dan Brown', 15.00, 2003).

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT,
  price DECIMAL(5,2),
  year_published INT
);
-- Your code here to insert the three book records

Need a hint?

Use a single INSERT INTO books statement with multiple rows separated by commas.

3

Select specific columns

Write a SQL query to select only the title and author columns from the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT,
  price DECIMAL(5,2),
  year_published INT
);

INSERT INTO books (id, title, author, price, year_published) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 1925),
(2, 'Harry Potter and the Goblet of Fire', 'J.K. Rowling', 12.50, 2000),
(3, 'The Da Vinci Code', 'Dan Brown', 15.00, 2003);

-- Your code here to select title and author columns

Need a hint?

Use SELECT title, author FROM books; to get only those two columns.

4

Filter books by year published

Modify the previous SQL query to select only the title and author columns for books published after the year 2000.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT,
  price DECIMAL(5,2),
  year_published INT
);

INSERT INTO books (id, title, author, price, year_published) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 1925),
(2, 'Harry Potter and the Goblet of Fire', 'J.K. Rowling', 12.50, 2000),
(3, 'The Da Vinci Code', 'Dan Brown', 15.00, 2003);

SELECT title, author FROM books
-- Your code here to add a WHERE clause filtering year_published > 2000

Need a hint?

Add WHERE year_published > 2000 to filter the results.