Lesson 03 – SQL Foundations: A Practical Introduction

Prerequisites: Complete Lesson 02 – Python Basics to be comfortable with variables and control flow before exploring database queries.

Structured Query Language, or SQL, is the language used to communicate with relational databases. This lesson covers the essential SQL commands used to manage data—specifically how to retrieve, insert, update, and delete records. We'll use a simple employees table to demonstrate these concepts in action.

---

The employees Table

To follow along, we’ll use a single table called employees. It holds basic information: an ID, a name, a job role, and a salary. Here’s how the table is defined:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    role TEXT NOT NULL,
    salary INTEGER
);

The id is the primary key, which means it uniquely identifies each record in the table. name and role must be filled in, thanks to the NOT NULL constraint. salary is optional, though we’ll treat it as required in practice.

Employees Schema Diagram

erDiagram
    employees {
        INTEGER id PK
        TEXT name
        TEXT role
        INTEGER salary
    }

You can also view this diagram separately in media/02_sql_schema.mmd.

---

Inserting Sample Data

Let’s add some initial data. This gives us something meaningful to query and modify:

INSERT INTO employees (name, role, salary) VALUES
('Alice', 'Developer', 65000),
('Bob', 'Manager', 72000),
('Carol', 'Designer', 50000),
('Dave', 'QA Engineer', 48000),
('Eve', 'Developer', 70000);

---

Reading Data with SELECT

The SELECT command is used to retrieve rows from the table. You can retrieve everything, or just specific columns, depending on what you need.

To see all the records:

SELECT * FROM employees;

To see just the names of employees earning more than 50,000:

SELECT name FROM employees WHERE salary > 50000;

You can also sort results:

SELECT * FROM employees ORDER BY salary DESC;

Or limit how many rows come back:

SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

To search for employees with a specific pattern in their role:

SELECT name FROM employees WHERE role LIKE '%Engineer%';

These tools—WHERE, ORDER BY, LIMIT, and LIKE—are often used together to shape query results.

---

Adding Data with INSERT

To create a new record, use INSERT. Specify the columns and the values to insert.

Here’s how to add a new employee:

INSERT INTO employees (name, role, salary)
VALUES ('Frank', 'Intern', 30000);

id is automatically assigned since it’s a primary key and most databases auto-increment it by default.

---

Updating Data with UPDATE

To change data that already exists, use UPDATE. Be specific about which rows to change—otherwise, you’ll update everything.

Let’s increase all salaries by 10%:

UPDATE employees
SET salary = salary * 1.10;

To change Carol’s job title:

UPDATE employees
SET role = 'Lead Designer'
WHERE name = 'Carol';

Be careful with UPDATE—without a WHERE clause, every row will be modified.

---

Removing Data with DELETE

To delete rows from a table, use the DELETE command. Always include a WHERE clause unless you mean to clear everything.

Here’s how to delete a specific employee:

DELETE FROM employees WHERE id = 3;

And here’s how to remove all interns:

DELETE FROM employees WHERE role = 'Intern';

---

Exploring Data with Aggregate Functions

SQL provides aggregate functions to compute values across rows—like totals or averages.

To get the average salary:

SELECT AVG(salary) FROM employees;

To count how many people hold each role:

SELECT role, COUNT(*) FROM employees GROUP BY role;

To find the total payroll cost:

SELECT SUM(salary) FROM employees;

These functions give you insight into data, rather than just raw rows.

---

Filtering with Other Clauses

SQL also offers flexible filtering with clauses like BETWEEN, IN, and IS NULL.

To find salaries between two numbers:

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 70000;

To select only specific names:

SELECT * FROM employees WHERE name IN ('Alice', 'Bob');

To find records where salary hasn’t been set (if allowed):

SELECT * FROM employees WHERE salary IS NULL;

These operators help refine queries when conditions get more specific.

---

How SQL Enforces Rules with Constraints

Constraints enforce rules on data. Even in our simple table, we’ve used several already:

• PRIMARY KEY ensures each row is uniquely identified.
• NOT NULL requires a value.

• Other constraints you might encounter:

• UNIQUE ensures all values in a column are distinct.

• CHECK limits valid values (salary > 0, for example).
• DEFAULT sets a fallback value when none is provided.

These rules protect data integrity.

---

Grouping Changes with Transactions (Optional)

When making multiple related changes, you can use transactions to ensure they all succeed—or none do.

Here’s a transaction that gives managers a raise and removes underpaid employees:

BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.05
WHERE role = 'Manager';

DELETE FROM employees
WHERE salary < 25000;

COMMIT;

If something goes wrong, you can cancel with ROLLBACK before committing.

---

Recap of Key Commands

Here’s a summary of the core SQL commands:

Command	Purpose	Example
SELECT	Read data	SELECT * FROM employees
INSERT	Add new rows	INSERT INTO employees (...) VALUES (...)
UPDATE	Modify existing rows	UPDATE employees SET ... WHERE ...
DELETE	Remove rows	DELETE FROM employees WHERE ...
GROUP BY	Summarize by category	SELECT role, COUNT(*) FROM employees ...
ORDER BY	Sort results	SELECT * FROM employees ORDER BY salary DESC

---

Try It Yourself

To practice, try writing SQL for the following:

• Get all Developers who earn over 60,000, sorted by salary.
• Insert a new employee named “Grace” who earns 82,000 as a Product Manager.
• Update Dave’s name to “David”.
• Delete all employees earning less than 45,000.
• Show the average salary per job role.

---

Would you like this turned into a printable format (like PDF or Markdown)? Or would you like an interactive SQL sandbox setup?

Next Up

Advance to Lesson 04 – OOP and Algorithm Basics to structure programs using classes and classic problem-solving techniques.