About the
Lab.

Welcome to the SQL Queries Lab. In this session, we'll master the fundamentals of structured databases entirely through interactive operations — learning columns, keys, indexes, filters, modifications, and table deletes.

Recommended Learning

Master SQL & Relational Databases

Explore the DBMS ecosystem — from queries and relational joins to indexing, schema normalization, and ACID transaction rules.

Start Learning
sqlite3 — production.db
sqlite> .tables
employees orders products
sqlite> SELECT name, role FROM employees;
Alice Smith|Engineering
Bob Jones|Marketing
Charlie Brown|Engineering
sqlite> ▌
What we are building

We will construct, query, filter, sort, update, and drop a database table. By the end of this lab, you'll know how database engines parse query schemas and structure information.

Lab Objectives

  • 1
    Define database schemas with strict types and primary keys.
  • 2
    Write queries to filter, sort, and slice information.
  • 3
    Update row values and drop database tables cleanly.

The Paradigm.

Relational databases organize records into predefined Tables. Unlike general programming languages where you write how to loop and find data, SQL is a declarative language—you specify what data you want, and the database engine decides the optimal execution path.

Declarative vs Imperative

In JavaScript or Python, to find a user, you might write a for loop checking every object. In SQL, you simply write SELECT * FROM users WHERE id = 3;. The database query optimizer automatically parses, plans, and fetches the result.

Prerequisites

  • 1
    Basic understanding of tabular data structures.
  • 2
    Familiarity with standard data types (numbers, text).
  • 3
    Curiosity to see how data is stored under the hood.

Getting Started

SQL (Structured Query Language) is the global standard for communicating with relational databases. Before we write queries, let's install SQLite—a lightweight, serverless engine—on your machine.

Terminal 
# On Windows (via winget or scoop)
winget install SQLite.SQLite
# On macOS (via Homebrew)
brew install sqlite
# On Linux (apt)
sudo apt install sqlite3
Why SQLite?

SQLite requires zero configuration and stores your entire database inside a single file on your disk. This makes it perfect for local development, embedded software, and learning SQL query syntax rapidly.

Database Schema.

Databases organize information in tabular structures. We use the CREATE TABLE statement to define column names and specify what type of data each field must store.

Terminal 
# Create a new table with schema rules
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    role TEXT,
    salary INTEGER,
    join_date TEXT
);
Constraints & Keys

PRIMARY KEY tells the database that the ID column uniquely identifies each row record. NOT NULL is a constraint ensuring that no employee can be created without a name field value.

Populating Data

Now that our database schema is set up, let's load initial row records using the INSERT INTO command.

Terminal 
# Insert multiple records into the table
INSERT INTO employees (name, role, salary, join_date) 
VALUES 
    ('Alice Smith', 'Engineering', 85000, '2025-01-15'),
    ('Bob Jones', 'Marketing', 62000, '2025-02-10'),
    ('Charlie Brown', 'Engineering', 90000, '2025-03-01'),
    ('Diana Prince', 'Design', 75000, '2025-01-20');
Auto-incrementing IDs

Notice we didn't specify value inputs for the id column. Since id is defined as an INTEGER PRIMARY KEY, SQLite automatically generates unique, sequential integers for us.

Read Operations.

The SELECT statement is the core retrieval command in SQL. It fetches specific columns or all columns from a target table.

Terminal 
# Fetch all columns and rows from the table
SELECT * FROM employees;

# Retrieve only specific columns
SELECT name, salary FROM employees;
Production Best Practices

While SELECT * is convenient, always list specific columns in actual applications to reduce data payload size, accelerate query time, and avoid columns breaking on schema changes.

Filtering Rows

Use the WHERE clause to filter query results and retrieve only the specific rows that match your logical criteria.

Terminal 
# Filter employees by salary
SELECT * FROM employees WHERE salary > 70000;

# Filter by exact role match
SELECT * FROM employees WHERE role = 'Engineering';
Boolean Evaluation

The SQL engine sweeps the table, evaluating the WHERE expression for each row. Only rows that evaluate to TRUE are forwarded to the final output list.

Ordering Data

By default, database rows are returned in insertion order. Use the ORDER BY clause to sort them in ascending (ASC) or descending (DESC) sequence.

Terminal 
# Sort employees by salary in descending order
SELECT * FROM employees ORDER BY salary DESC;

# Sort alphabetically by name (ascending)
SELECT * FROM employees ORDER BY name ASC;
Default Ordering

If you omit specify ASC or DESC, SQL sorts values in ascending sequence by default. You can also sort by multiple columns sequentially (e.g. ORDER BY role, salary DESC).

Limiting Results

To prevent performance strain, restrict the maximum number of rows returned by your query using the LIMIT clause.

Terminal 
# Get the top 2 highest paid employees
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 2;
Paging Logic

Combining LIMIT with OFFSET allows applications to paginate table results (e.g. LIMIT 10 OFFSET 20 fetches the 3rd page of records containing 10 rows per page).

Modifying Data

To edit field values inside existing rows, execute the UPDATE statement. Always include a filtering clause to isolate the target row!

Terminal 
# Update Alice's salary using a WHERE filter
UPDATE employees 
SET salary = 95000 
WHERE name = 'Alice Smith';
Warning: No-Filter Updates

If you run UPDATE employees SET salary = 95000; without a WHERE clause, the database will overwrite the salary field for every single employee row in the entire table!

Removing Data

The DELETE FROM statement clears row records from a table. Like update queries, specify filters so you only delete the intended targets.

Terminal 
# Delete a specific employee row record
DELETE FROM employees WHERE name = 'Bob Jones';
Delete vs Schema Preservation

Deleting rows clears database values and frees space, but the structural schema design (table rules, keys, and indexes) is preserved for future insert operations.

Schema Removal

To wipe the entire table structure along with all its data, use the DROP TABLE statement.

Terminal 
# Drop the employees table permanently
DROP TABLE employees;

SQL Operations Checklist

  • 1
    Did you specify a filtering WHERE clause on updates and deletes?
  • 2
    Are columns matching data type constraints defined in the schema?
Desktop Recommended

Interactive labs are designed for desktop. You can still keep this open on your mobile to follow along while you build on your workstation.