Let’s create a table named “Books” with columns for book ID, title, author, and publication year.
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(50),
PublicationYear INT
);
Let’s insert a few records into the “Books” table.
INSERT INTO Books (BookID, Title, Author, PublicationYear)
VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925),
(2, 'To Kill a Mockingbird', 'Harper Lee', 1960),
(3, '1984', 'George Orwell', 1949),
(4, 'Pride and Prejudice', 'Jane Austen', 1813);
Using DML Commands:
Now, let’s perform some Data Manipulation Language (DML) operations.
-- Retrieve all books SELECT * FROM Books; -- Retrieve books published after 1950 SELECT * FROM Books WHERE PublicationYear > 1950;
-- Update the author of a book UPDATE Books SET Author = 'F. Scott Fitzgerald (edited)' WHERE Title = 'The Great Gatsby';
-- Delete a book by its ID DELETE FROM Books WHERE BookID = 3;
-- Retrieve books by a specific author SELECT * FROM Books WHERE Author = 'Jane Austen';
-- Retrieve books published between 1900 and 2000 SELECT * FROM Books WHERE PublicationYear BETWEEN 1900 AND 2000;
— Retrieve books with specific IDs SELECT * FROM Books WHERE BookID IN (1, 4, 6);
-- Insert a new book
INSERT INTO Books (BookID, Title, Author, PublicationYear)
VALUES
(5, 'The Catcher in the Rye', 'J.D. Salinger', 1951);
-- Insert multiple books
INSERT INTO Books (BookID, Title, Author, PublicationYear)
VALUES
(6, 'Lord of the Rings', 'J.R.R. Tolkien', 1954),
(7, 'To Kill a Mockingbird', 'Harper Lee', 1960); -- Note: Duplicate title, different book ID
-- Update the publication year of a book UPDATE Books SET PublicationYear = 1962 WHERE Title = 'To Kill a Mockingbird';
-- Update multiple records using a condition UPDATE Books SET Author = 'George Orwell' WHERE Author = 'George Orwell (edited)';
-- Delete books published before a certain year DELETE FROM Books WHERE PublicationYear < 1900;
-- Delete all records (use with caution!) -- DELETE FROM Books;