SQL Programming

In this lesson

• CREATE
• INSERT
• SELECT
• Live syntax

Introduction to SQL with MySQL

From databases to your first queries — a beginner-friendly guide to structured data.

mysql> SELECT * FROM knowledge;█

Database Management Systems

SQL (Structured Query Language) is the standard language for talking to relational databases.

Make databases and tables

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.

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

Very large whole numbers

Long text (no length limit)

e.g. '2024-09-01 09:30:00'

Creating Databases & Tables

Step 1 — Create a Database

CREATE DATABASE school_db;
USE school_db;

CREATE TABLE students (
  id      INT PRIMARY KEY,
  name    VARCHAR(100),
  age     INT,
  email   VARCHAR(150),
  gpa     DECIMAL(3,2)
);

• 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

INSERT INTO table_name
  (column1, column2, ...)
VALUES
  (value1, value2, ...);

Example — Insert 3 students

INSERT INTO students (id, name, age, email, gpa)
VALUES
  (1, 'Alice', 20, 'alice@uni.edu', 3.80),
  (2, 'Bob',   22, 'bob@uni.edu',  3.50),
  (3, 'Carol', 21, 'carol@uni.edu',3.90);

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.

Querying Data with SELECT

SELECT name, gpa
FROM students;

SELECT name AS 'Student Name',
       gpa  AS 'Grade Point'
FROM students;

SELECT name, gpa → 2 columns only

⭐ Use SELECT * for exploration; use specific columns in real apps for speed.

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;

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

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

1  →  2  →  3
A  →  B  →  C

Descending largest → smallest

3  →  2  →  1
C  →  B  →  A

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

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;

⚠️ Always use WHERE! Without it, every row gets updated — a common mistake!

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

DELETE FROM t WHERE … — removes specific rows DELETE FROM t — removes all rows (slow) TRUNCATE TABLE t — wipes all rows instantly, resets IDs

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;

💡 GROUP BY groups rows so functions run per group.

SELECT
  s.name,
  g.score
FROM students AS s
INNER JOIN grades AS g
  ON s.id = g.student_id;

INNER JOIN returns only matching rows from both tables.

Summary & Quick Reference

Make a new database or table

INSERT INTO t (…) VALUES (…);

SELECT * FROM t WHERE …;

UPDATE t SET col=v WHERE …;

COUNT · AVG · MAX · MIN · SUM

INNER JOIN t ON a.id=b.id;