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.

Practice

1. What is the main difference between MySQL CLI and MySQL Workbench?

easy

A. MySQL CLI is command-line based, while Workbench provides a graphical interface.

B. MySQL CLI can only read data, Workbench can only write data.

C. MySQL CLI is used for backups, Workbench is used for queries only.

D. MySQL CLI requires internet, Workbench works offline.

Solution

1. Step 1: Understand the interface types

   MySQL CLI uses text commands typed in a terminal, while Workbench shows windows and buttons.

2. Step 2: Compare their usage

   CLI is purely text-based; Workbench is graphical and easier for beginners.

3. Final Answer:

   MySQL CLI is command-line based, while Workbench provides a graphical interface. -> Option A

4. Quick Check:

   CLI = command-line, Workbench = GUI [OK]

Hint: CLI = text commands, Workbench = visual tool [OK]

Common Mistakes:

• Thinking CLI can only read data
• Believing Workbench requires internet
• Confusing backup and query roles

2. Which of the following is the correct way to start MySQL CLI from a terminal?

easy

A. mysql start

B. start mysql

C. mysql -u root -p

D. run mysql

Solution

1. Step 1: Recall MySQL CLI startup command

   The standard command to start MySQL CLI is 'mysql' with user and password options.

2. Step 2: Identify correct syntax

   'mysql -u root -p' means login as user root and prompt for password.

3. Final Answer:

   mysql -u root -p -> Option C

4. Quick Check:

   Start CLI with 'mysql -u user -p' [OK]

Hint: Use 'mysql -u username -p' to start CLI [OK]

Common Mistakes:

• Using 'mysql start' which is invalid
• Typing 'start mysql' which is not a command
• Forgetting the '-p' to prompt password

3. What will be the output when you run this command in MySQL CLI?
SHOW DATABASES;

medium

A. List of all tables in the current database

B. List of all databases on the MySQL server

C. Error: Command not found

D. Empty result set

Solution

1. Step 1: Understand the command purpose

   'SHOW DATABASES;' lists all databases available on the MySQL server.

2. Step 2: Differentiate from other commands

   'SHOW TABLES;' lists tables in current database, so not correct here.

3. Final Answer:

   List of all databases on the MySQL server -> Option B

4. Quick Check:

   'SHOW DATABASES;' = list databases [OK]

Hint: 'SHOW DATABASES;' lists all databases [OK]

Common Mistakes:

• Confusing databases with tables
• Expecting an error from a valid command
• Thinking it returns empty if no database

4. You try to run this command in MySQL CLI:
SELECT * FROM users
But you get an error. What is the most likely cause?

medium

A. The table 'users' does not exist in the current database

B. Missing semicolon at the end of the statement

C. You forgot to start MySQL Workbench

D. You typed 'SELECT' in lowercase

Solution

1. Step 1: Check syntax correctness

   Missing semicolon usually causes error, but MySQL CLI can accept multi-line input until semicolon is entered.

2. Step 2: Consider table existence

   If 'users' table is missing in current database, query fails with error.

3. Final Answer:

   The table 'users' does not exist in the current database -> Option A

4. Quick Check:

   Missing table = query error [OK]

Hint: Check if table exists before querying [OK]

Common Mistakes:

• Assuming lowercase SELECT causes error
• Thinking Workbench must be open for CLI
• Ignoring missing semicolon impact

5. You want to export a database using MySQL CLI and then open it in MySQL Workbench. Which command correctly exports the database named shopdb to a file backup.sql?

hard

A. mysqlexport -u root -p shopdb backup.sql

B. mysql -u root -p shopdb > backup.sql

C. mysqldump shopdb -u root -p backup.sql

D. mysqldump -u root -p shopdb > backup.sql

Solution

1. Step 1: Identify the export tool

   To export a database, 'mysqldump' is the correct command, not 'mysql'.

2. Step 2: Check command syntax

   'mysqldump -u root -p shopdb > backup.sql' exports 'shopdb' to 'backup.sql' file.

3. Final Answer:

   mysqldump -u root -p shopdb > backup.sql -> Option D

4. Quick Check:

   Use mysqldump with output redirection [OK]

Hint: Use mysqldump with > filename to export [OK]

Common Mistakes:

• Using 'mysql' instead of 'mysqldump' to export
• Wrong order of arguments in command
• Using non-existent 'mysqlexport' command