DML (INSERT, UPDATE, DELETE) in DBMS Theory - Mini Project: Build & Apply

DML (INSERT, UPDATE, DELETE) Basics

📖 Scenario: You are managing a small library database. You need to add new books, update book details, and remove old books from the database.

🎯 Goal: Build a set of SQL commands to insert new records, update existing records, and delete records from a books table.

📋 What You'll Learn

Create an initial books table with columns id, title, and author

Insert three specific book records into the books table

Update the author of a specific book by id

Delete a book record by id

💡 Why This Matters

🌍 Real World

Managing data in databases is essential for many applications like libraries, stores, and websites. Knowing how to add, change, and remove data helps keep information accurate and up to date.

💼 Career

Database administrators, backend developers, and data analysts use DML commands daily to maintain and manipulate data stored in databases.

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 text, and author as text.

DBMS Theory

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

Need a hint?

Use CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT); to define the table.

2

Insert three books into books

Write three separate SQL INSERT INTO books statements to add these books exactly:
1. id=1, title='1984', author='George Orwell'
2. id=2, title='To Kill a Mockingbird', author='Harper Lee'
3. id=3, title='The Great Gatsby', author='F. Scott Fitzgerald'

DBMS Theory

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

Need a hint?

Use INSERT INTO books (id, title, author) VALUES (...); for each book.

3

Update the author of the book with id=3

Write a SQL UPDATE statement to change the author of the book with id=3 to 'Francis Scott Fitzgerald'.

DBMS Theory

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES (1, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (2, 'To Kill a Mockingbird', 'Harper Lee');
INSERT INTO books (id, title, author) VALUES (3, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Update the author of the book with id=3
-- Your code here

Need a hint?

Use UPDATE books SET author = 'Francis Scott Fitzgerald' WHERE id = 3; to update the author.

4

Delete the book with id=2

Write a SQL DELETE FROM books statement to remove the book record where id=2.

DBMS Theory

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES (1, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (2, 'To Kill a Mockingbird', 'Harper Lee');
INSERT INTO books (id, title, author) VALUES (3, 'The Great Gatsby', 'F. Scott Fitzgerald');

UPDATE books SET author = 'Francis Scott Fitzgerald' WHERE id = 3;

-- Delete the book with id=2
-- Your code here

Need a hint?

Use DELETE FROM books WHERE id = 2; to remove the book.