Selecting specific columns in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Selecting specific columns from a table

Given a table Employees with columns id, name, department, and salary, what is the output of this query?

SELECT name, salary FROM Employees WHERE department = 'Sales';

MySQL

SELECT name, salary FROM Employees WHERE department = 'Sales';

A [{"name": "Alice"}, {"name": "Bob"}]

B [{"id": 1, "name": "Alice", "salary": 50000}, {"id": 2, "name": "Bob", "salary": 55000}]

C [{"name": "Alice", "department": "Sales"}, {"name": "Bob", "department": "Sales"}]

D [{"name": "Alice", "salary": 50000}, {"name": "Bob", "salary": 55000}]

💡 Hint

The query selects only the name and salary columns for employees in the Sales department.

The query filters employees by department 'Sales' and selects only the name and salary columns, so the output includes only those fields for matching rows.

📝 Syntax

intermediate

Identify the syntax error in column selection

Which option contains a syntax error when selecting specific columns from the Products table with columns product_id, product_name, and price?

A SELECT product_id, price FROM Products;

B SELECT product_name FROM Products;

C SELECT product_id product_name FROM Products;

D SELECT product_name, price FROM Products;

💡 Hint

Check if commas separate the column names.

Option C misses a comma between product_id and product_name, causing a syntax error.

❓ query_result

advanced

Selecting columns with aliasing

What is the output of this query on the Orders table with columns order_id, customer, and total?

SELECT order_id AS id, total AS amount FROM Orders WHERE total > 100;

MySQL

SELECT order_id AS id, total AS amount FROM Orders WHERE total > 100;

A [{"id": 101, "amount": 150}, {"id": 102, "amount": 200}]

B [{"order_id": 101, "total": 150}, {"order_id": 102, "total": 200}]

C [{"id": 101, "total": 150}, {"id": 102, "total": 200}]

D [{"order_id": 101, "amount": 150}, {"order_id": 102, "amount": 200}]

💡 Hint

The query renames columns using AS and filters totals greater than 100.

The query renames order_id to id and total to amount, returning only those columns for orders with total > 100.

❓ optimization

advanced

Optimizing column selection for performance

You want to retrieve only the username and email columns from a large Users table. Which query is the most efficient?

A SELECT username, email FROM Users;

B SELECT username, email, password FROM Users;

C SELECT * FROM Users;

D SELECT email FROM Users;

💡 Hint

Selecting only needed columns reduces data transfer and processing.

Option A selects only the required columns username and email, making it more efficient than selecting all columns or extra columns.

🔧 Debug

expert

Debugging incorrect column selection in join query

Given tables Customers(id, name) and Orders(id, customer_id, amount), which query correctly selects customer names and their order amounts?

A SELECT name, amount FROM Customers, Orders WHERE Customers.id = Orders.id;

B SELECT Customers.name, Orders.amount FROM Customers JOIN Orders ON Customers.id = Orders.customer_id;

C SELECT name, amount FROM Customers JOIN Orders ON Customers.id = Orders.id;

D SELECT Customers.name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.id;

💡 Hint

Check the join condition matches customer id with order's customer_id.

Option B correctly joins Customers and Orders on Customers.id = Orders.customer_id and selects the desired columns.

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