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.