SELECT all columns in SQL - Mini Project: Build & Apply

Selecting All Columns from a Table

📖 Scenario: You are working with a small bookstore database. The database has a table called books that stores information about each book available in the store.

🎯 Goal: Learn how to write a SQL query that selects all columns from the books table to see every detail about each book.

📋 What You'll Learn

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

Insert three specific book records into the books table.

Write a SQL query that selects all columns from the books table using SELECT *.

Complete the query with a semicolon ; to end the statement.

💡 Why This Matters

🌍 Real World

Selecting all columns is a common task when you want to see every detail stored in a database table, such as viewing all information about books in a bookstore.

💼 Career

Database administrators and developers often need to retrieve full records from tables to analyze data or build reports.

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

-- Write the CREATE TABLE statement for books
-- Your code here

Hint

Use CREATE TABLE books (column definitions); with the exact column names and types.

2

Insert three books into the books table

Write three SQL INSERT INTO books statements to add these books exactly:
1. id=1, title='The Hobbit', author='J.R.R. Tolkien', year_published=1937
2. id=2, title='1984', author='George Orwell', year_published=1949
3. id=3, title='To Kill a Mockingbird', author='Harper Lee', year_published=1960

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year_published INTEGER
);
-- Insert the three books below
-- Your code here

Hint

Use three separate INSERT INTO books (columns) VALUES (values); statements with the exact data.

3

Write a query to select all columns from books

Write a SQL query that selects all columns from the books table using SELECT *.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year_published INTEGER
);
INSERT INTO books (id, title, author, year_published) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien', 1937);
INSERT INTO books (id, title, author, year_published) VALUES (2, '1984', 'George Orwell', 1949);
INSERT INTO books (id, title, author, year_published) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee', 1960);
-- Write the SELECT query below
-- Your code here

Hint

Use SELECT * FROM books to get all columns.

4

Complete the query with a semicolon

Add a semicolon ; at the end of the SELECT * FROM books query to complete the SQL statement.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year_published INTEGER
);
INSERT INTO books (id, title, author, year_published) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien', 1937);
INSERT INTO books (id, title, author, year_published) VALUES (2, '1984', 'George Orwell', 1949);
INSERT INTO books (id, title, author, year_published) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee', 1960);
SELECT * FROM books
-- Add a semicolon to end the query
-- Your code here

Hint

Remember to end SQL statements with a semicolon ;.

Practice

1. What does the SQL query SELECT * FROM employees; do?

easy

A. It deletes all data from the employees table.

B. It creates a new employees table.

C. It updates all columns in the employees table.

D. It retrieves all columns and all rows from the employees table.

Solution

1. Step 1: Understand the SELECT * syntax

   The asterisk (*) means select all columns from the specified table.

2. Step 2: Apply to the employees table

   The query fetches every column and every row from the employees table.

3. Final Answer:

   It retrieves all columns and all rows from the employees table. -> Option D

4. Quick Check:

   SELECT * = all columns and rows [OK]

Hint: SELECT * means get everything from the table [OK]

Common Mistakes:

• Thinking SELECT * deletes or updates data
• Confusing SELECT * with creating tables
• Assuming it selects only one column

2. Which of the following is the correct syntax to select all columns from a table named products?

easy

A. SELECT * FROM products;

B. SELECT products.*;

C. SELECT columns FROM products;

D. SELECT ALL FROM products;

Solution

1. Step 1: Recall the correct SELECT syntax

   The correct way to select all columns is using SELECT * FROM table_name.

2. Step 2: Check each option

   SELECT * FROM products; matches the correct syntax exactly.

3. Final Answer:

   SELECT * FROM products; -> Option A

4. Quick Check:

   SELECT * FROM table = correct syntax [OK]

Hint: Use SELECT * FROM table_name to get all columns [OK]

Common Mistakes:

• Using SELECT ALL instead of SELECT *
• Forgetting FROM keyword
• Trying to select 'columns' literally

3. Given the table students with columns id, name, and age, what will the query SELECT * FROM students WHERE age > 20; return?

medium

A. All students regardless of age.

B. Only the age column for students older than 20.

C. All columns for students whose age is greater than 20.

D. An error because * cannot be used with WHERE.

Solution

1. Step 1: Understand SELECT * with WHERE clause

   SELECT * fetches all columns, and WHERE filters rows based on condition.

2. Step 2: Apply condition age > 20

   Only rows where age is greater than 20 are returned, with all columns shown.

3. Final Answer:

   All columns for students whose age is greater than 20. -> Option C

4. Quick Check:

   SELECT * + WHERE filters rows [OK]

Hint: SELECT * returns all columns; WHERE filters rows [OK]

Common Mistakes:

• Thinking WHERE limits columns instead of rows
• Assuming * selects only one column
• Believing * cannot be used with WHERE

4. Identify the error in this SQL query: SELECT * employees;

medium

A. Table name should be in quotes.

B. Missing FROM keyword before table name.

C. Using * is not allowed in SELECT.

D. SELECT cannot be used without WHERE.

Solution

1. Step 1: Check SQL SELECT syntax

   The correct syntax requires FROM before the table name.

2. Step 2: Identify missing keyword

   The query misses FROM between SELECT * and employees.

3. Final Answer:

   Missing FROM keyword before table name. -> Option B

4. Quick Check:

   SELECT * FROM table is correct syntax [OK]

Hint: Always use FROM before table name in SELECT [OK]

Common Mistakes:

• Omitting FROM keyword
• Thinking * is invalid
• Adding unnecessary quotes around table name

5. You want to quickly check all data in a large table named orders. Which query is best to avoid slowing down your database?

hard

A. SELECT * FROM orders LIMIT 10;

B. SELECT * FROM orders;

C. SELECT order_id FROM orders;

D. DELETE FROM orders WHERE order_id < 10;

Solution

1. Step 1: Understand SELECT * impact on large tables

   SELECT * fetches all rows and columns, which can be slow on big tables.

2. Step 2: Use LIMIT to reduce rows fetched

   Adding LIMIT 10 fetches only 10 rows, making the query faster and safer.

3. Final Answer:

   SELECT * FROM orders LIMIT 10; -> Option A

4. Quick Check:

   LIMIT reduces rows, speeding up SELECT * [OK]

Hint: Use LIMIT with SELECT * on big tables to speed queries [OK]

Common Mistakes:

• Running SELECT * without LIMIT on large tables
• Confusing DELETE with SELECT
• Selecting only one column when all data is needed