MySQL CLI and Workbench - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a SELECT query with WHERE clause in MySQL CLI

Consider a table employees with columns id, name, and department. What is the output of the following query executed in MySQL CLI?

SELECT name FROM employees WHERE department = 'Sales';

MySQL

SELECT name FROM employees WHERE department = 'Sales';

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

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

C [{"id": 1, "name": "Alice"}, {"id": 3, "name": "Bob"}]

D SyntaxError

💡 Hint

Focus on the SELECT columns and the WHERE condition filtering by department.

The query selects only the 'name' column for employees whose department is 'Sales'. Only Alice and Bob belong to Sales, so their names are returned.

📝 Syntax

intermediate

Identify the syntax error in a MySQL Workbench query

Which option contains a syntax error when run in MySQL Workbench?

SELECT * FROM orders WHERE order_date = '2023-01-01'

MySQL

SELECT * FROM orders WHERE order_date = '2023-01-01'

A SELECT * orders WHERE order_date = '2023-01-01';

B SELECT * FROM orders WHERE order_date = '2023-01-01'

C SELECT * FROM orders WHERE order_date = '2023-01-01';

D ;'10-10-3202' = etad_redro EREHW sredro MORF * TCELES

💡 Hint

Check the FROM clause syntax carefully.

Option A is missing the keyword FROM before the table name, causing a syntax error.

❓ optimization

advanced

Optimizing a slow query in MySQL CLI

You have a large table sales with millions of rows. The query below is slow:

SELECT * FROM sales WHERE customer_id = 12345;

Which option will most improve query speed when run in MySQL CLI?

MySQL

SELECT * FROM sales WHERE customer_id = 12345;

A Add an index on the customer_id column.

B Rewrite the query as SELECT * FROM sales;

C Use ORDER BY customer_id DESC in the query.

D Increase the LIMIT to 10000 rows.

💡 Hint

Indexes help MySQL find rows faster when filtering.

Adding an index on customer_id allows MySQL to quickly locate matching rows instead of scanning the entire table.

🔧 Debug

advanced

Debugging connection error in MySQL Workbench

You try to connect to a MySQL server in Workbench but get the error:
"Can't connect to MySQL server on 'localhost' (10061)"
Which option is the most likely cause?

A The table name is misspelled in the query.

B The SQL query syntax is incorrect.

C MySQL server is not running on localhost.

D The SELECT statement is missing a semicolon.

💡 Hint

Connection errors usually relate to server availability or network.

Error 10061 means the client cannot reach the server, often because the server is not running or listening on the expected port.

🧠 Conceptual

expert

Understanding transaction behavior in MySQL CLI

In MySQL CLI, you start a transaction with START TRANSACTION; and run:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

But you forget to run COMMIT; and close the CLI. What happens to the changes?

A All changes are saved automatically despite missing COMMIT.

B The database throws an error and keeps partial changes.

C Only the first UPDATE is saved, the second is lost.

D All changes are rolled back and not saved to the database.

💡 Hint

Think about what happens to uncommitted transactions when the session ends.

Without COMMIT, MySQL rolls back the transaction when the connection closes, so no changes are saved.