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

Need a 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

Need a 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

Need a 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

Need a hint?

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