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

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

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

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

Hint

Add WHERE year_published > 2000 to filter the results.

Practice

1. What does the SQL statement SELECT name, age FROM users; do?

easy

A. It updates the name and age columns in the users table.

B. It retrieves all columns from the users table.

C. It deletes the name and age columns from the users table.

D. It retrieves only the name and age columns from the users table.

Solution

1. Step 1: Understand the SELECT statement

   The SELECT keyword is used to choose specific columns from a table.

2. Step 2: Identify the columns listed

   The columns listed after SELECT are name and age, so only these will be retrieved.

3. Final Answer:

   It retrieves only the name and age columns from the users table. -> Option D

4. Quick Check:

   Select specific columns = name, age [OK]

Hint: SELECT columns by listing names separated by commas [OK]

Common Mistakes:

• Thinking SELECT * returns specific columns
• Confusing SELECT with DELETE or UPDATE
• Forgetting commas between column names

2. Which of the following is the correct syntax to select the columns id and email from a table named customers?

easy

A. SELECT id email FROM customers;

B. SELECT id, email FROM customers;

C. SELECT (id, email) FROM customers;

D. SELECT * FROM customers WHERE id, email;

Solution

1. Step 1: Recall correct SELECT syntax

   Columns must be separated by commas without parentheses.

2. Step 2: Check each option

   SELECT id, email FROM customers; uses commas correctly: SELECT id, email FROM customers;.

3. Final Answer:

   SELECT id, email FROM customers; -> Option B

4. Quick Check:

   Columns separated by commas = SELECT id, email FROM customers; [OK]

Hint: Use commas between columns, no parentheses [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Misusing WHERE clause for column selection

3. Given the table products with columns product_id, name, and price, what will be the output of this query?

SELECT name, price FROM products WHERE price > 100;

medium

A. Only the name and price of products with price greater than 100.

B. All columns for products with price greater than 100.

C. Only the product_id and price of products with price greater than 100.

D. Syntax error due to missing commas.

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only name and price columns.

2. Step 2: Analyze the WHERE clause

   It filters rows where price > 100.

3. Final Answer:

   Only the name and price of products with price greater than 100. -> Option A

4. Quick Check:

   Select specific columns with condition = Only the name and price of products with price greater than 100. [OK]

Hint: SELECT columns listed, WHERE filters rows [OK]

Common Mistakes:

• Assuming all columns are returned
• Confusing WHERE with column selection
• Thinking missing commas cause error here

4. Identify the error in this query:

SELECT id name FROM employees;

medium

A. SELECT keyword is misspelled.

B. Table name is incorrect.

C. Missing comma between column names.

D. No error, query is correct.

Solution

1. Step 1: Check column list syntax

   Columns must be separated by commas. Here, id and name are missing a comma.

2. Step 2: Verify other parts

   Table name employees and SELECT keyword are correct.

3. Final Answer:

   Missing comma between column names. -> Option C

4. Quick Check:

   Comma missing between columns = Missing comma between column names. [OK]

Hint: Separate columns with commas to avoid syntax errors [OK]

Common Mistakes:

• Forgetting commas between columns
• Assuming space works as separator
• Misreading error as table or keyword issue

5. You have a table orders with columns order_id, customer_id, order_date, and total. You want to get only the order_id and total for orders placed in 2023. Which query is correct?

hard

A. SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023;

B. SELECT order_id total FROM orders WHERE order_date = 2023;

C. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%';

D. SELECT * FROM orders WHERE order_date = 2023;

Solution

1. Step 1: Select specific columns

   The query must select order_id and total only, so SELECT order_id, total is correct.

2. Step 2: Filter orders from 2023

   Using WHERE YEAR(order_date) = 2023 correctly filters orders placed in 2023.

3. Step 3: Check other options

   SELECT order_id total FROM orders WHERE order_date = 2023; misses comma and uses wrong date filter. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%'; uses LIKE which may work but is less precise. SELECT * FROM orders WHERE order_date = 2023; selects all columns, not specific ones.

4. Final Answer:

   SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; -> Option A

5. Quick Check:

   Select columns + correct year filter = SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; [OK]

Hint: Use YEAR() to filter dates, commas to select columns [OK]

Common Mistakes:

• Omitting commas between columns
• Using wrong date filter syntax
• Selecting all columns instead of specific ones