Why DML operations modify data in MySQL - See It in Action

Understanding Why DML Operations Modify Data

📖 Scenario: You are managing a small library database. You need to understand how to change the data about books using SQL commands.

🎯 Goal: Learn how to use Data Manipulation Language (DML) commands to modify data in a database table.

📋 What You'll Learn

Create a table called books with columns id, title, and author

Insert initial data into the books table

Update the author of a specific book

Delete a book record from the table

💡 Why This Matters

🌍 Real World

Managing and updating records in databases is common in libraries, stores, and many businesses.

💼 Career

Database administrators and developers use DML commands daily to keep data accurate and up to date.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as a text field, and author as a text field.

MySQL

-- Write the CREATE TABLE statement for books
-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns with their types and primary key.

2

Insert initial data into books

Write SQL INSERT statements to add these three books into the books table: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), and (3, 'Pride and Prejudice', 'Jane Austen').

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);
-- Insert the three books below
-- Your code here

Need a hint?

Use one INSERT INTO books statement with multiple rows.

3

Update the author of a book

Write a SQL UPDATE statement to change the author of the book with id = 2 to 'Eric Arthur Blair'.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen');
-- Update the author of book with id 2 below
-- Your code here

Need a hint?

Use UPDATE books SET author = 'Eric Arthur Blair' WHERE id = 2; to change the author.

4

Delete a book record

Write a SQL DELETE statement to remove the book with id = 3 from the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen');

UPDATE books SET author = 'Eric Arthur Blair' WHERE id = 2;
-- Delete the book with id 3 below
-- Your code here

Need a hint?

Use DELETE FROM books WHERE id = 3; to remove the record.