Common Database Operations

SQL provides a rich set of commands to perform various operations on a database. These commands can be grouped into several logical categories.

Database Preparation

Run this block to set up the Authors and Books tables used in the examples below.

DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    AuthorName VARCHAR(100),
    Nationality VARCHAR(50)
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INTEGER,
    Genre VARCHAR(50),
    PublishedYear INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

INSERT INTO Authors (AuthorID, AuthorName, Nationality)
VALUES (101, 'J.R.R. Tolkien', 'British'),
       (102, 'George Orwell', 'British'),
       (103, 'Frank Herbert', 'American');

INSERT INTO Books (BookID, Title, AuthorID, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 101, 'Fantasy', 1937),
       (2, '1984', 102, 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 101, 'Fantasy', 1954),
       (4, 'Dune', 103, 'Sci-Fi', 1965);

SELECT 'Database Ready!' AS Status;

1. Extracting Records (SELECT)

This is the most fundamental operation, used to retrieve data from a table.

Operation: Find the title and genre of all books.
Reference Table: Books
Query:

SELECT Title, Genre FROM Books;

Result:

2. Filtering Records (WHERE)

The WHERE clause is used to extract only those records that fulfill a specified condition.

Operation: Find the titles of all books in the 'Fantasy' genre.
Reference Table: Books
Query:

SELECT Title FROM Books WHERE Genre = 'Fantasy';

Result:

3. Sorting Records (ORDER BY)

The ORDER BY keyword is used to sort the result set.

Operation: Get the titles and publication years of all books, starting with the oldest.
Reference Table: Books
Query:

SELECT Title, PublishedYear FROM Books ORDER BY PublishedYear ASC;

Result:

4. Adding Records (INSERT INTO)

This command is used to add new rows of data into a table.

Operation: Add a new author to the Authors table.
Authors Table Before:

INSERT INTO Authors (AuthorID, AuthorName, Nationality)
VALUES (104, 'Isaac Asimov', 'American');

Authors Table After:

5. Updating Records (UPDATE)

The UPDATE command is used to modify existing records.

Operation: Correct Frank Herbert's nationality.
Authors Table Before:

UPDATE Authors
SET Nationality = 'American (USA)'
WHERE AuthorID = 103;

Authors Table After:

6. Deleting Records (DELETE)

This command is used to remove existing records.

Operation: Delete the book with BookID 2.
Books Table Before:

DELETE FROM Books WHERE BookID = 2;

Books Table After:

7. Table Manipulation (CREATE, DROP)

These commands are used to manage the tables themselves.

• CREATE TABLE: Creates a new table.
• DROP TABLE: Deletes an existing table and all its data.

Hands-on Exercise

Task: Retrieve the titles and publication years of all 'Fantasy' books, sorted from newest to oldest.

Your Query:

-- Write your query here

Expected Results: