Read Data (SELECT) and Aliases

The Read operation in CRUD is performed using the SELECT statement. It is the most frequently used command in SQL, used to query the database and retrieve data that matches criteria that you specify.

Database Preparation

Before you begin, run this code block to create and populate the Books table.

DROP TABLE IF EXISTS Books;

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

INSERT INTO Books (BookID, Title, AuthorID, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 101, 'Fantasy', 1937),
       (2, '1984', 102, 'Dystopian', 1949);

-- Check that the table was created successfully
SELECT * FROM Books;

Basic SELECT Statement

Operation: Retrieve the names and genres of all books.

SELECT Title, Genre FROM Books;

Result:

Selecting All Columns (SELECT *)

If you want to see every column for every record in a table, you can use the asterisk (*) wildcard. This is a shorthand for "all columns."

Operation: Retrieve the entire Books table.

SELECT * FROM Books;

Result:

Using Aliases to Rename Columns and Tables

2. Table Aliases

Table aliases are used to shorten table names in a query. While they can be used in simple queries, they become essential for more complex operations like JOINs and subqueries, which are covered in later sections. We will introduce them properly in those contexts where their value is most clear.

Hands-on Exercise

Task:

1. Retrieve only the Title and PublishedYear for all books in the table.
2. Retrieve all columns from the Books table, but use an alias to display the Title column as Book_Title.

Your Query:

-- Write your query here

Expected Results: