MySQL CLI and Workbench - Mini Project: Build & Apply

Using MySQL CLI and Workbench to Manage a Simple Database

📖 Scenario: You are helping a small bookstore keep track of their books and authors using MySQL. You will use both the MySQL Command Line Interface (CLI) and MySQL Workbench to create and manage the database.

🎯 Goal: Create a database called bookstore with two tables: authors and books. Add some sample data using MySQL CLI commands and then connect to the database using MySQL Workbench to view the tables.

📋 What You'll Learn

Create a database named bookstore using MySQL CLI

Create an authors table with columns author_id (primary key) and name

Create a books table with columns book_id (primary key), title, and author_id (foreign key)

Insert exactly two authors into the authors table

Insert exactly three books into the books table with correct author_id references

Use MySQL Workbench to connect to the bookstore database and verify the tables and data

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use databases to keep track of products, customers, and transactions.

💼 Career

Knowing how to create and manage databases with MySQL CLI and Workbench is a key skill for database administrators and backend developers.

1

Create the bookstore database

Use the MySQL CLI to create a database called bookstore by typing the command CREATE DATABASE bookstore;

MySQL

# Type the command to create the database
# Your code here

Hint

Remember to end your SQL command with a semicolon ;.

2

Create the authors and books tables

Write the SQL commands to create the authors table with columns author_id as an integer primary key and name as a VARCHAR(100), and the books table with columns book_id as an integer primary key, title as VARCHAR(100), and author_id as an integer foreign key referencing authors.author_id. Use the commands CREATE TABLE authors (...); and CREATE TABLE books (...);

MySQL

CREATE DATABASE bookstore;
# Write SQL to create authors and books tables
# Your code here

Hint

Use USE bookstore; to select the database before creating tables.

3

Insert sample data into authors and books

Insert two authors into the authors table with author_id 1 and 2 and names 'Jane Austen' and 'Mark Twain'. Then insert three books into the books table with book_id 1, 2, 3, titles 'Pride and Prejudice', 'Emma', and 'Adventures of Huckleberry Finn', and correct author_id references (1 for Jane Austen's books, 2 for Mark Twain's book). Use INSERT INTO authors (...) VALUES (...); and INSERT INTO books (...) VALUES (...); commands.

MySQL

CREATE DATABASE bookstore;
USE bookstore;
CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(100)
);
CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
# Insert two authors and three books
# Your code here

Hint

Each INSERT command must end with a semicolon ;.

4

Connect and verify data in MySQL Workbench

Open MySQL Workbench and connect to your MySQL server. Select the bookstore database from the left panel. Use the GUI to view the authors and books tables and verify that the data you inserted is visible.

MySQL

CREATE DATABASE bookstore;
USE bookstore;
CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(100)
);
CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
INSERT INTO authors (author_id, name) VALUES (1, 'Jane Austen');
INSERT INTO authors (author_id, name) VALUES (2, 'Mark Twain');
INSERT INTO books (book_id, title, author_id) VALUES (1, 'Pride and Prejudice', 1);
INSERT INTO books (book_id, title, author_id) VALUES (2, 'Emma', 1);
INSERT INTO books (book_id, title, author_id) VALUES (3, 'Adventures of Huckleberry Finn', 2);
# Connect to MySQL Workbench and verify the data
# Your code here

Hint

Use MySQL Workbench's left sidebar to find the bookstore database and click on tables to see the data.

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