Why databases over files in SQL - See It in Action

Why Databases Over Files

📖 Scenario: Imagine you run a small bookstore. You want to keep track of your books and sales. You could write all this information in simple text files, but it can get messy and slow as your store grows.This project will help you understand why using a database is better than just using files.

🎯 Goal: You will create a simple table to store book information, add a configuration for a minimum stock level, write a query to find books that need restocking, and finalize the setup with a primary key.

📋 What You'll Learn

Create a table called Books with columns BookID, Title, and Stock

Add a variable min_stock to set the minimum stock level

Write a query to select books where Stock is less than min_stock

Add a primary key constraint on BookID

💡 Why This Matters

🌍 Real World

Stores, libraries, and many businesses use databases to keep their data organized and easy to access instead of messy files.

💼 Career

Knowing how to create tables, set variables, and write queries is essential for database jobs and managing data efficiently.

1

Create the Books table

Write a SQL statement to create a table called Books with columns BookID as integer, Title as text, and Stock as integer.

SQL

-- Create the Books table with BookID, Title, and Stock columns
-- Your code here

Need a hint?

Use CREATE TABLE Books (BookID INTEGER, Title TEXT, Stock INTEGER);

2

Set minimum stock level

Add a SQL variable called min_stock and set it to 5 to represent the minimum stock level.

SQL

CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Stock INTEGER
);
-- Declare min_stock variable and set it to 5
-- Your code here

Need a hint?

Use DECLARE min_stock INTEGER DEFAULT 5; to set the variable.

3

Query books needing restock

Write a SQL query to select all columns from Books where the Stock is less than the variable min_stock.

SQL

CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Stock INTEGER
);

-- Set minimum stock level
DECLARE min_stock INTEGER DEFAULT 5;

-- Select books with stock less than min_stock
-- Your code here

Need a hint?

Use SELECT * FROM Books WHERE Stock < min_stock; to find books to restock.

4

Add primary key to Books table

Modify the Books table creation statement to add a primary key constraint on the BookID column.

SQL

-- Modify the Books table to add primary key on BookID
CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Stock INTEGER
);

-- Set minimum stock level
DECLARE min_stock INTEGER DEFAULT 5;

-- Query books needing restock
SELECT * FROM Books WHERE Stock < min_stock;
-- Your code here

Need a hint?

Add PRIMARY KEY after BookID INTEGER in the table definition.