SQL Programming — Interactive Lesson | Dr. Yasas Sri Wickramasinghe

Q: What is the difference between CREATE DATABASE and CREATE TABLE?

CREATE DATABASE makes an empty container to hold tables. CREATE TABLE defines the actual structure — the columns, their data types, and the constraints — inside that container. You run CREATE DATABASE once, then CREATE TABLE for each table you need.

Q: What order do the parts of a SELECT statement go in?

SELECT columns, FROM table, WHERE row conditions, GROUP BY grouping, HAVING conditions on groups, then ORDER BY sorting. The database does not execute them in that order — FROM and WHERE run before SELECT — which is why you cannot use a column alias defined in SELECT inside a WHERE clause.

01

What you'll learn

CREATE
INSERT
SELECT
Live syntax

This is the full written version of an interactive, slide-by-slide lesson deck used in teaching. Everything covered in the deck is below — open the interactive version to work through it with live diagrams, worked examples and a practice quiz where included.

02

The Language

What is SQL?
SQL (Structured Query Language) is the standard language for talking to relational databases.
Make databases and tables
INSERT / SELECT
Add and read data
UPDATE / DELETE
Modify and remove data
MySQL = SQL + Database Server
The world's most popular open-source database
Used by Facebook, Twitter, YouTube and thousands of other applications worldwide.

SQL is not case-sensitive — SELECT = select = Select. But writing keywords in UPPERCASE is standard practice.

Database Management Systems

Building Blocks

MySQL Data Types

Every column in a table must have a data type — it tells MySQL what kind of value to expect.

Whole numbers
e.g. 1, 25, 1000
FLOAT / DECIMAL
Decimal numbers
e.g. 3.14, 99.99
Very large whole numbers
e.g. 9,223,372,036…
Text up to n characters
e.g. 'Alice', 'Hello'
Long text (no length limit)
e.g. blog post content
Fixed-length text
e.g. country codes 'MY'
Calendar date
e.g. '2024-09-01'
Date + time combined
e.g. '2024-09-01 09:30:00'
True or False (1 or 0)
e.g. is_active = TRUE
Database Management Systems

Command 1 of 5

Creating Databases & Tables
Step 1 — Create a Database
CREATE DATABASE school_db;
USE school_db;
Step 2 — Create a Table
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
email VARCHAR(150),
gpa DECIMAL(3,2)
🔑 Key Concepts
PRIMARY KEY— unique identifier for each row
VARCHAR(n)— text up to n characters
DECIMAL(3,2)— 3 digits, 2 after decimal (e.g. 3.75)
Result — empty table created
(no rows yet)
Database Management Systems

Command 2 of 5

Inserting Data
INSERT INTO table_name
(column1, column2, ...)
(value1, value2, ...);
Example — Insert 3 students
INSERT INTO students (id, name, age, email, gpa)
(1, 'Alice', 20, 'alice@uni.edu', 3.80),
(2, 'Bob', 22, 'bob@uni.edu', 3.50),
(3, 'Carol', 21, 'carol@uni.edu',3.90);
Anatomy of INSERT
INSERT INTO which table to add rows to
(columns) which columns you're filling
VALUES the actual data — match column order!
Result — students table now has rows

💡 You must supply a value for id — it is the PRIMARY KEY and must be unique for every row.

Database Management Systems

Command 3 of 5

Querying Data with SELECT
Select ALL columns
SELECT * FROM students;
Select SPECIFIC columns
SELECT name, gpa
FROM students;
Select with an alias
SELECT name AS 'Student Name',
gpa AS 'Grade Point'
FROM students;
SELECT * → all columns
SELECT name, gpa → 2 columns only
⭐ Use SELECT * for exploration; use specific columns in real apps for speed.
Database Management Systems

Filtering with WHERE

SELECT columns
FROM table
WHERE condition;
-- students older than 20
SELECT * FROM students
WHERE age > 20;
-- find a specific student
SELECT * FROM students
WHERE name = 'Alice';
-- multiple conditions
SELECT * FROM students
WHERE age > 20 AND gpa >= 3.70;
Comparison Operators
= equal to
!= not equal
> greater than
< less than
>= ≥
<= ≤
AND both true
OR either true
WHERE age > 20

💡 Use LIKE '%term%' to search text — e.g. WHERE name LIKE 'A%' finds all names starting with A.

Database Management Systems

Sorting with ORDER BY

SELECT columns
FROM table
ORDER BY column ASC|DESC;
-- highest GPA first
SELECT * FROM students
ORDER BY gpa DESC;
-- alphabetical by name
SELECT * FROM students
ORDER BY name ASC;
-- combined with WHERE + LIMIT
SELECT * FROM students
WHERE age > 20
ORDER BY gpa DESC LIMIT 10;
Ascending (default)
smallest → largest
A → B → C
largest → smallest
C → B → A
ORDER BY gpa DESC

💡 Add LIMIT 10 at the end to get only the top N results — great for leaderboards!

Database Management Systems

Command 4 of 5

Updating Records
UPDATE table
SET column1 = new_value
WHERE condition;
-- Bob got a better grade!
UPDATE students
SET gpa = 3.75
WHERE id = 2;
-- Update multiple columns
UPDATE students
SET age = 23, email = 'bob.new@uni.edu'
WHERE id = 2;
Before UPDATE
After UPDATE
⚠️ Always use WHERE! Without it, every row gets updated — a common mistake!
Database Management Systems

Command 5 of 5

Deleting Records
DELETE FROM table
WHERE condition;
-- remove one student
DELETE FROM students
WHERE id = 2;
-- remove low-GPA records
DELETE FROM students
WHERE gpa < 3.60;
Before DELETE WHERE id = 2
After DELETE
⚠️ DELETE vs TRUNCATE
DELETE FROM t WHERE … — removes specific rows
DELETE FROM t — removes all rows (slow)
TRUNCATE TABLE t — wipes all rows instantly, resets IDs
Database Management Systems

Going Further

Aggregate Functions
Perform calculations across many rows and return a single result.
-- count all students
SELECT COUNT(*) FROM students;
-- average GPA
SELECT AVG(gpa) FROM students;
-- highest and lowest GPA
SELECT MAX(gpa), MIN(gpa)
FROM students;
-- group by + count
SELECT age, COUNT(*) AS total
FROM students
GROUP BY age;
Counts number of rows
Average of a column
Highest value
Lowest value
GROUP BY age result
💡 GROUP BY groups rows so functions run per group.
Database Management Systems

Joining Tables

INNER JOIN query
FROM students AS s
INNER JOIN grades AS g
ON s.id = g.student_id;
Combined result
INNER JOIN returns only matching rows from both tables.
Database Management Systems

Quick Reference

Summary & Quick Reference
Make a new database or table
CREATE TABLE t (…);
Add rows to a table
INSERT INTO t (…) VALUES (…);
Read / query data
SELECT * FROM t WHERE …;
Modify existing rows
UPDATE t SET col=v WHERE …;
Remove rows
DELETE FROM t WHERE …;
ORDER BY
Sort results
ORDER BY col ASC|DESC;
Calculate across rows
COUNT · AVG · MAX · MIN · SUM
Combine multiple tables
INNER JOIN t ON a.id=b.id;
Database Management Systems

Common questions

What is the difference between CREATE DATABASE and CREATE TABLE?

CREATE DATABASE makes an empty container to hold tables. CREATE TABLE defines the actual structure — the columns, their data types, and the constraints — inside that container. You run CREATE DATABASE once, then CREATE TABLE for each table you need.

What order do the parts of a SELECT statement go in?

SELECT columns, FROM table, WHERE row conditions, GROUP BY grouping, HAVING conditions on groups, then ORDER BY sorting. The database does not execute them in that order — FROM and WHERE run before SELECT — which is why you cannot use a column alias defined in SELECT inside a WHERE clause.

Related lessons

Advanced Database Concepts — Table design, backup & restore, and a plain-English look at SQL injection.
Free SQL Certifications — Nine genuinely free credentials, from vendor badges to project-based certs.

Work through it interactively

The interactive deck adds live diagrams, step-by-step reveals and practice activities that this written version can't carry.

Open the Interactive Deck → See how I teach → Get new lessons by email →