Selecting specific columns in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL SELECT statement do?

The SELECT statement retrieves data from a database table. You can choose to get all columns or specific columns.

beginner

How do you select only the 'name' and 'age' columns from a table called 'people'?

Use: SELECT name, age FROM people; This gets only the 'name' and 'age' columns from the 'people' table.

beginner

What happens if you use SELECT * in a query?

The * means 'all columns'. So, SELECT * returns every column in the table.

beginner

Can you select columns in any order you want?

Yes! You can list columns in any order in the SELECT statement. The result will show columns in that order.

beginner

Why might you want to select specific columns instead of all columns?

Selecting specific columns can make queries faster and results easier to read. It also reduces data sent over the network.

Which SQL query selects only the 'email' column from the 'users' table?

A SELECT email FROM users;

B SELECT * FROM users;

C SELECT users FROM email;

D SELECT email, users;

Only option D correctly selects the 'email' column from the 'users' table.

What does the asterisk (*) mean in a SELECT statement?

A Select no columns

B Select only one column

C Select all columns

D Select columns with * in their name

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

How would you select the columns 'first_name' and 'last_name' from a table 'employees'?

A SELECT first_name last_name FROM employees;

B SELECT employees FROM first_name, last_name;

C SELECT * FROM employees WHERE first_name, last_name;

D SELECT first_name, last_name FROM employees;

Option D correctly lists the columns separated by a comma after SELECT.

If you want to see columns in a different order, what should you do?

A Use ORDER BY clause

B List columns in the desired order in the SELECT statement

C Change the table structure

D You cannot change column order

You control the order of columns in the result by listing them in that order in the SELECT statement.

Why is it better to select only needed columns?

A To improve query speed and reduce data size

B To confuse users

C To delete other columns

D To rename columns automatically

Selecting only needed columns makes queries faster and results smaller.

Explain how to select specific columns from a table in SQL.

Think about the basic structure of a SELECT query.

Why might you choose to select specific columns instead of all columns?

Consider what happens when you get too much data.

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