SELECT specific columns in SQL - Step-by-Step Execution

Concept Flow - SELECT specific columns

Start Query

↓

Parse SELECT clause

↓

Identify columns to retrieve

↓

Scan table rows

↓

Extract specified columns from each row

↓

Return result set with selected columns

↓

End Query

The query starts by reading which columns to select, then scans the table rows, extracts only those columns, and returns them as the result.

Execution Sample

SQL

SELECT name, age FROM users;

This query retrieves only the 'name' and 'age' columns from every row in the 'users' table.

Execution Table

Step	Action	Current Row Data	Columns Extracted	Result Set So Far
1	Start query execution	N/A	N/A	[]
2	Read first row	{id:1, name:'Alice', age:30, city:'NY'}	name, age	[{name:'Alice', age:30}]
3	Read second row	{id:2, name:'Bob', age:25, city:'LA'}	name, age	[{name:'Alice', age:30}, {name:'Bob', age:25}]
4	Read third row	{id:3, name:'Carol', age:27, city:'Chicago'}	name, age	[{name:'Alice', age:30}, {name:'Bob', age:25}, {name:'Carol', age:27}]
5	No more rows	N/A	N/A	[{name:'Alice', age:30}, {name:'Bob', age:25}, {name:'Carol', age:27}]

💡 All rows processed, query returns result set with only 'name' and 'age' columns.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	Final
result_set	[]	[{name:'Alice', age:30}]	[{name:'Alice', age:30}, {name:'Bob', age:25}]	[{name:'Alice', age:30}, {name:'Bob', age:25}, {name:'Carol', age:27}]	[{name:'Alice', age:30}, {name:'Bob', age:25}, {name:'Carol', age:27}]

Key Moments - 2 Insights

Why does the result only include 'name' and 'age' and not other columns like 'id' or 'city'?

Because the SELECT clause specifies only 'name' and 'age' columns, the query extracts only these columns from each row as shown in execution_table rows 2-4.

What happens if the table has more rows than shown?

The query continues scanning each row, extracting only the specified columns, and appends them to the result set until no rows remain, as indicated in execution_table row 5.

Visual Quiz - 3 Questions

Look at the execution table, what columns are extracted from the second row?

A id, name

B name, age

C age, city

D id, city

💡 Hint

Check the 'Columns Extracted' column in execution_table row 3.

At which step does the query finish reading all rows?

A Step 5

B Step 4

C Step 3

D Step 2

💡 Hint

Look for the step where 'No more rows' is indicated in the 'Action' column.

If we add 'city' to the SELECT clause, how would the 'Columns Extracted' change at step 2?

A id, city

B name, age

C name, age, city

D id, name, age

💡 Hint

Adding 'city' means it will be included in the extracted columns for each row.

Concept Snapshot

SELECT specific columns syntax:
SELECT column1, column2 FROM table_name;

- Retrieves only listed columns from all rows.
- Result set contains fewer columns than the full table.
- Useful to get just needed data, improving clarity and performance.

Full Transcript

This visual execution trace shows how a SQL query with SELECT specific columns works. The query starts by reading which columns to select, then scans each row in the table. For each row, it extracts only the specified columns and adds them to the result set. This continues until all rows are processed. The final output contains only the chosen columns for every row. This helps to get just the data you want from a table without extra information.

Practice

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

easy

A. It retrieves all columns from the users table.

B. It updates the name and age columns in 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 clause

   The SELECT clause specifies which columns to retrieve from the table.

2. Step 2: Identify columns listed

   The columns listed are name and age, so only these will be returned.

3. Final Answer:

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

4. Quick Check:

   SELECT specific columns = selected columns only [OK]

Hint: SELECT lists columns to get only those columns [OK]

Common Mistakes:

• Thinking SELECT * returns specific columns
• Confusing SELECT with DELETE or UPDATE
• Not separating columns by commas

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

easy

A. SELECT id email FROM contacts;

B. SELECT id, email FROM contacts;

C. SELECT (id, email) FROM contacts;

D. SELECT id; email FROM contacts;

Solution

1. Step 1: Check column separation syntax

   Columns must be separated by commas without parentheses.

2. Step 2: Identify correct option

   SELECT id, email FROM contacts; uses commas correctly: SELECT id, email FROM contacts;

3. Final Answer:

   SELECT id, email FROM contacts; -> Option B

4. Quick Check:

   Columns separated by commas = correct syntax [OK]

Hint: Separate columns with commas, no parentheses [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Using semicolons between columns

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 < 50;

medium

A. All columns for products priced below 50

B. An error because price is not selected

C. Only the name and price columns for products priced below 50

D. Only the product_id column for products priced below 50

Solution

1. Step 1: Understand SELECT columns

   The query selects only name and price columns.

2. Step 2: Apply WHERE condition

   Only rows where price is less than 50 are included.

3. Final Answer:

   Only the name and price columns for products priced below 50 -> Option C

4. Quick Check:

   SELECT specific columns + WHERE filters rows [OK]

Hint: SELECT columns limits output columns, WHERE filters rows [OK]

Common Mistakes:

• Expecting all columns to be returned
• Thinking WHERE changes columns selected
• Assuming error if column not in SELECT

4. Identify the error in this SQL query:

SELECT name age FROM employees;

medium

A. Missing comma between columns name and age

B. Table name employees is incorrect

C. SELECT cannot be used without WHERE

D. No error, query is correct

Solution

1. Step 1: Check column separation

   Columns must be separated by commas; here name and age are not separated.

2. Step 2: Confirm table name and syntax

   Table name employees is valid and WHERE is optional.

3. Final Answer:

   Missing comma between columns name and age -> Option A

4. Quick Check:

   Columns need commas between them [OK]

Hint: Always separate columns with commas [OK]

Common Mistakes:

• Forgetting commas between columns
• Thinking WHERE is mandatory
• Assuming table name is wrong without evidence

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

hard

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

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

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

D. SELECT * FROM orders WHERE order_date LIKE '2023%';

Solution

1. Step 1: Select specific columns

   We want only order_id and total, so SELECT order_id, total is correct.

2. Step 2: Filter orders from 2023

   Using WHERE order_date LIKE '2023%' matches dates starting with 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 = '2023'; uses exact match which won't work for full dates; SELECT * FROM orders WHERE order_date LIKE '2023%'; selects all columns, not specific ones.

4. Final Answer:

   SELECT order_id, total FROM orders WHERE order_date LIKE '2023%'; -> Option A

5. Quick Check:

   Select columns + LIKE filter for year = correct query [OK]

Hint: Use LIKE '2023%' to filter year, separate columns with commas [OK]

Common Mistakes:

• Forgetting commas between columns
• Using = instead of LIKE for partial date match
• Selecting all columns instead of specific ones