SQL — Complete Beginner Tutorial

SELECT *
FROM students;

SELECT name, city, score
FROM students;

-- With aliases (rename columns in the result)
SELECT
  name        AS student_name,
  score       AS exam_score,
  grade       AS letter_grade
FROM students;

-- Without DISTINCT — shows every city, repeated
SELECT city FROM students;

-- With DISTINCT — each city only once
SELECT DISTINCT city
FROM students;

-- DISTINCT on multiple columns
SELECT DISTINCT city, grade
FROM students
ORDER BY city;

-- Students from Cape Town only
SELECT name, age, city
FROM students
WHERE city = 'Cape Town';

-- Students aged 18 or older
SELECT name, age
FROM students
WHERE age >= 18;

-- Students who did NOT get an A
SELECT name, grade
FROM students
WHERE grade != 'A';

-- AND: both conditions must be true
SELECT name, age, city
FROM students
WHERE city = 'Cape Town'
  AND age >= 20;

-- OR: either condition can be true
SELECT name, city
FROM students
WHERE city = 'Cape Town'
  OR city = 'Durban';

-- Combining AND + OR with parentheses
SELECT name, grade, score
FROM students
WHERE grade = 'A'
  AND (city = 'Cape Town' OR city = 'Durban');

-- Names that start with "A"
SELECT name FROM students
WHERE name LIKE 'A%';

-- Names that end with "e"
SELECT name FROM students
WHERE name LIKE '%e';

-- Names containing "ar" anywhere
SELECT name FROM students
WHERE name LIKE '%ar%';

-- IN: match any value from a list
SELECT name, city
FROM students
WHERE city IN ('Cape Town', 'Durban');

-- BETWEEN: range of values (inclusive)
SELECT name, score
FROM students
WHERE score BETWEEN 75 AND 90;

-- Sort by score, highest first
SELECT name, score
FROM students
ORDER BY score DESC;

-- Sort alphabetically by name
SELECT name, city
FROM students
ORDER BY name ASC;

-- Sort by city first, then by score within each city
SELECT name, city, score
FROM students
ORDER BY city ASC, score DESC;

-- Top 3 highest scoring students
SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 3;

-- Page 2: skip the first 3, get the next 3
SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

-- The single lowest score
SELECT name, score
FROM students
ORDER BY score ASC
LIMIT 1;

-- How many students are there?
SELECT COUNT(*) AS total_students
FROM students;

-- Average, min, and max score
SELECT
  AVG(score)  AS average_score,
  MIN(score)  AS lowest_score,
  MAX(score)  AS highest_score
FROM students;

-- Only count students aged 18+
SELECT COUNT(*) AS adult_students
FROM students
WHERE age >= 18;

-- How many students in each city?
SELECT city, COUNT(*) AS num_students
FROM students
GROUP BY city
ORDER BY num_students DESC;

-- Average score per grade
SELECT
  grade,
  COUNT(*)    AS students,
  AVG(score)  AS avg_score,
  MIN(score)  AS min_score,
  MAX(score)  AS max_score
FROM students
GROUP BY grade
ORDER BY grade;

-- Cities with MORE than 1 student
SELECT city, COUNT(*) AS num_students
FROM students
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY num_students DESC;

-- Grades where average score is above 75
SELECT
  grade,
  ROUND(AVG(score), 1) AS avg_score
FROM students
GROUP BY grade
HAVING AVG(score) > 75
ORDER BY avg_score DESC;

-- Which students are enrolled in which courses?
SELECT
  students.name     AS student,
  courses.title     AS course,
  courses.teacher
FROM students
INNER JOIN enrolments
  ON students.id = enrolments.student_id
INNER JOIN courses
  ON enrolments.course_id = courses.id
ORDER BY students.name;

-- All students, even those with no enrolments
-- (Diana and George have no courses → NULL)
SELECT
  students.name,
  courses.title AS enrolled_in
FROM students
LEFT JOIN enrolments
  ON students.id = enrolments.student_id
LEFT JOIN courses
  ON enrolments.course_id = courses.id
ORDER BY students.name;

-- Insert a single student
INSERT INTO students (name, age, city, grade, score)
VALUES ('Hannah', 19, 'Cape Town', 'B', 84);

-- Insert multiple students at once
INSERT INTO students (name, age, city, grade, score)
VALUES
  ('Ivan',  23, 'Johannesburg', 'A', 92),
  ('Julia', 17, 'Durban',       'C', 71);

-- After inserting, SELECT to confirm
SELECT name, age, city, grade, score
FROM students
WHERE name IN ('Hannah', 'Ivan', 'Julia');

-- Update Alice's score
UPDATE students
SET score = 97, grade = 'A'
WHERE name = 'Alice';

-- Raise everyone's score by 5
-- (in their city only)
UPDATE students
SET score = score + 5
WHERE city = 'Cape Town';

-- After updating, check the result
SELECT name, city, score, grade
FROM students
WHERE city = 'Cape Town'
ORDER BY score DESC;

-- Delete a specific student
DELETE FROM students
WHERE name = 'George';

-- Delete all students with a failing grade
DELETE FROM students
WHERE grade = 'F';

-- ─────────────────────────────────────────────
-- CREATE TABLE — build a new table from scratch
-- ─────────────────────────────────────────────
CREATE TABLE teachers (
  id        INTEGER      PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  subject   VARCHAR(100),
  email     VARCHAR(150) UNIQUE,
  hired_on  DATE
);

-- Insert into the new table
INSERT INTO teachers (id, name, subject, email)
VALUES
  (1, 'Mr Adams',  'Web Development', 'adams@school.edu'),
  (2, 'Ms Botha',  'Data Science',    'botha@school.edu'),
  (3, 'Mr Cloete', 'Python',          'cloete@school.edu');

SELECT * FROM teachers;