Why result control matters in PostgreSQL - See It in Action

Why Result Control Matters in PostgreSQL Queries

📖 Scenario: You are managing a small bookstore database. You want to retrieve book information but only for books that are currently in stock. Controlling the query result is important to avoid showing irrelevant or incorrect data.

🎯 Goal: Build a simple PostgreSQL query that selects books only if their stock count is greater than zero, demonstrating why controlling query results matters.

📋 What You'll Learn

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

Insert three books with specific stock values

Write a query to select only books with stock greater than zero

Add an ORDER BY clause to sort the results by title

💡 Why This Matters

🌍 Real World

Filtering and ordering data is essential in real-world databases to show only relevant information to users, such as available products in an online store.

💼 Career

Database developers and analysts must write queries that control results precisely to support business decisions and user interfaces.

1

Create the books table

Write a SQL statement to create a table called books with columns id as integer primary key, title as text, and stock as integer.

PostgreSQL

-- Create the books table with id, title, and stock columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert sample book data

Insert three rows into the books table with these exact values: (1, 'The Great Gatsby', 3), (2, '1984', 0), and (3, 'To Kill a Mockingbird', 5).

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    stock INTEGER
);
-- Insert three books with given id, title, and stock
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Select books with stock greater than zero

Write a SQL query to select id, title, and stock from books where stock is greater than zero.

PostgreSQL

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

INSERT INTO books (id, title, stock) VALUES
(1, 'The Great Gatsby', 3),
(2, '1984', 0),
(3, 'To Kill a Mockingbird', 5);

-- Select books where stock is greater than zero
-- Your code here

Need a hint?

Use WHERE stock > 0 to filter results.

4

Order the results by title

Modify the previous query to add an ORDER BY clause that sorts the results by title in ascending order.

PostgreSQL

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

INSERT INTO books (id, title, stock) VALUES
(1, 'The Great Gatsby', 3),
(2, '1984', 0),
(3, 'To Kill a Mockingbird', 5);

SELECT id, title, stock FROM books WHERE stock > 0
-- Add ORDER BY title here
-- Your code here

Need a hint?

Use ORDER BY title ASC to sort alphabetically.