MySQL CLI and Workbench - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to connect to a MySQL server using the CLI.

MySQL

mysql -u [1] -p

Drag options to blanks, or click blank then click option'

Aroot

Bpassword

Clocalhost

Dmysql

Attempts:

3 left

💡 Hint

Common Mistakes

Using the password directly after -u instead of the username.

Confusing hostname with username.

Use the -u option to specify the username, commonly root for admin access.

2fill in blank

medium

Complete the command to show all databases in MySQL CLI.

MySQL

[1];

Drag options to blanks, or click blank then click option'

ASHOW TABLES

BSHOW SCHEMAS

CSHOW DATABASES

DLIST DATABASES

Attempts:

3 left

💡 Hint

Common Mistakes

Using SHOW TABLES; which only shows tables in the current database.

Using LIST DATABASES; which is not valid MySQL syntax.

The command SHOW DATABASES; lists all databases on the MySQL server.

3fill in blank

hard

Fix the error in the command to select a database named 'shop'.

MySQL

USE [1];

Drag options to blanks, or click blank then click option'

Ashops

Bshop

CShop

DSHOP

Attempts:

3 left

💡 Hint

Common Mistakes

Using a plural or capitalized version of the database name.

Forgetting the semicolon at the end.

The database name is case-sensitive in some systems. Use the exact name shop.

4fill in blank

hard

Fill both blanks to create a new user 'alice' with password 'mypassword' in MySQL CLI.

MySQL

CREATE USER '[1]'@'localhost' IDENTIFIED BY '[2]';

Drag options to blanks, or click blank then click option'

Aalice

Bmypassword

Croot

Dpassword123

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping username and password positions.

Omitting quotes around username or password.

Use CREATE USER 'alice'@'localhost' IDENTIFIED BY 'mypassword'; to create the user with the password.

5fill in blank

hard

Fill all three blanks to grant all privileges on database 'shop' to user 'alice' in MySQL CLI.

MySQL

GRANT [1] ON [2].* TO '[3]'@'localhost';

Drag options to blanks, or click blank then click option'

AALL PRIVILEGES

Bshop

Calice

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SELECT instead of ALL PRIVILEGES for full access.

Forgetting the database name or using wrong username.

Use GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'localhost'; to give full access to the user on the database.

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