Relational Database Fundamentals & SQL
Overview
Relational databases are the backbone of most web applications. In this tutorial, you will learn the core principles of the relational model, how to define schemas using Data Definition Language (DDL), and how to manipulate data using Data Manipulation Language (DML). You will also master the art of querying multiple tables using joins and aggregations.
1. Relational Database Management System (RDBMS)
An RDBMS stores data in tables (relations) consisting of rows (tuples) and columns (attributes). Key concepts:
- Primary Key (PK): Uniquely identifies each row.
- Foreign Key (FK): Links to a primary key in another table, establishing relationships.
- Schema: The structure of the database (tables, columns, constraints).
- Index: A data structure that speeds up data retrieval (more in Tutorial 2).
Popular RDBMSs: PostgreSQL, MySQL, Oracle, SQL Server.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE
);
2. SQL Basics (DDL & DML)
2.1 Data Definition Language (DDL)
DDL statements define the database structure:
CREATE TABLE– create a new table.ALTER TABLE– modify an existing table (add/drop columns).DROP TABLE– delete a table.CREATE INDEX– create an index.
2.2 Data Manipulation Language (DML)
DML statements manipulate the data inside tables:
INSERT INTO– add new rows.UPDATE– modify existing rows.DELETE FROM– remove rows.SELECT– retrieve data (the most frequently used).
INSERT INTO customers (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
-- Update a customer
UPDATE customers SET email = 'alice.new@example.com' WHERE id = 1;
-- Delete a customer
DELETE FROM customers WHERE id = 1;
3. SELECT Queries & Joins
The SELECT statement is the workhorse of SQL. Its basic structure:
FROM table1
JOIN table2 ON table1.column = table2.column
WHERE condition
ORDER BY column;
3.1 INNER JOIN
Returns only rows that have matching values in both tables.
3.2 LEFT (OUTER) JOIN
Returns all rows from the left table, and matched rows from the right table. NULL if no match.
3.3 RIGHT JOIN & FULL OUTER JOIN
Similar, but preserving rows from the right or both sides respectively.
4. Aggregations & Grouping
Aggregate functions compute a single value from a set of rows:
COUNT(*)– number of rows.SUM(column)– sum of values.AVG(column)– average.MIN(column)/MAX(column)– min/max.
Use GROUP BY to group rows that share a property, and HAVING
to filter groups (similar to WHERE but for groups).
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
Quiz
Question 1
What is the primary key used for in a relational table?
- To store large binary data
- To uniquely identify each row
- To define a foreign relationship
- To improve query performance
Show answer
Question 2
Which SQL clause is used to filter rows before grouping?
- HAVING
- WHERE
- FILTER
- GROUP BY
Show answer
Question 3
Which join returns all rows from the left table and matching rows from the right table, filling with NULL when there is no match?
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN
- CROSS JOIN
Show answer
Exercises
Exercise 1
Given a table employees with columns: id, name, salary, department_id. Write a SQL query to find the average salary per department.
Sample answer
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
Exercise 2
Using an INNER JOIN, write a query that returns all orders (orders table) along with the customer name (customers table).
Sample answer
SELECT orders.id, customers.name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
Homework
Homework 1
Design a database schema for an e‑commerce platform. Include at least 4 tables (e.g., products, customers, orders, order_items). Define the primary and foreign keys. Then write a SQL query to list all products that have never been ordered.
Sample schema & query
Schema:
customers(id PK, name, email)products(id PK, name, price, stock)orders(id PK, customer_id FK, order_date)order_items(id PK, order_id FK, product_id FK, quantity)
Query:
SELECT p.* FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;
Mini‑Project
Library Management System
Design a schema for a library management system:
books(id, title, author, isbn, published_year)members(id, name, email, membership_date)loans(id, book_id FK, member_id FK, loan_date, return_date)
Write SQL statements to:
- Insert 3 sample books.
- Insert 2 members.
- Insert a loan for a member borrowing a book.
- Query to find all currently borrowed books (where return_date IS NULL).
Sample implementation
DDL:
CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(255), author VARCHAR(100), isbn VARCHAR(20), published_year INT);
CREATE TABLE members (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255), membership_date DATE);
CREATE TABLE loans (id INT PRIMARY KEY, book_id INT, member_id INT, loan_date DATE, return_date DATE, FOREIGN KEY (book_id) REFERENCES books(id), FOREIGN KEY (member_id) REFERENCES members(id));
DML:
INSERT INTO books VALUES (1, '1984', 'George Orwell', '123456', 1949);
INSERT INTO books VALUES (2, 'Dune', 'Frank Herbert', '789012', 1965);
INSERT INTO members VALUES (1, 'John Doe', 'john@lib.com', '2025-01-15');
INSERT INTO loans VALUES (1, 1, 1, '2026-09-01', NULL);
SELECT b.title, m.name FROM loans l JOIN books b ON l.book_id = b.id JOIN members m ON l.member_id = m.id WHERE l.return_date IS NULL;
Tutorial Summary
You learned the fundamentals of relational databases and SQL. We covered
the core RDBMS concepts, DDL and DML statements, how to write powerful
SELECT queries with joins, and how to use aggregations with
GROUP BY. The exercises and mini‑project gave you hands‑on
practice in designing schemas and writing queries.
Key takeaway: SQL is a declarative language – you describe what data you want, not how to get it. Mastering joins and aggregations is essential for any data‑driven application.