Course Practicalities

SQL Quick Reference

This page is a small companion page for SQL practice. It does not replace the course materials, but it is useful when you want to quickly recall the shape of a statement.

Read the Shape First

Before focusing on syntax, ask three small questions:

  1. What table or tables does the query start from?
  2. What does one result row represent?
  3. Which clauses are shaping the result?

That habit becomes especially useful once joins and grouped queries appear.

Basic Read Query

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC
LIMIT 10;

Common notes:

  • WHERE filters rows.
  • ORDER BY sorts rows.
  • LIMIT keeps only the first part of the result.
  • Use SELECT * mainly for exploration, not as the default teaching or maintenance style.

A More Concrete Read Query

SELECT name, email
FROM users
WHERE name ILIKE '%Ada%'
ORDER BY name
LIMIT 5;

A useful way to read this is:

  • start from users,
  • keep only rows whose name matches the condition,
  • sort the remaining rows,
  • and then keep at most five rows.

Checking for NULL

SELECT id, exercise_id, user_id
FROM submissions
WHERE score IS NULL;

Use IS NULL or IS NOT NULL, not = NULL.

Insert

INSERT INTO courses (code, title)
VALUES ('DB101', 'Introduction to Databases');

You can also insert several rows at once:

INSERT INTO courses (code, title)
VALUES
  ('DB101', 'Introduction to Databases'),
  ('DB102', 'Database Design');

Update

UPDATE courses
SET title = 'Databases and Data Management'
WHERE code = 'DB101';

Delete

DELETE FROM courses
WHERE code = 'DB101';

In a real schema, a delete like this may be rejected if other tables still reference the row through foreign keys.

Inner Join

SELECT e.title, c.code AS course_code, ci.term, ci.year
FROM exercises AS e
JOIN course_instances AS ci ON ci.id = e.course_instance_id
JOIN courses AS c ON c.id = ci.course_id;

Read joins as relationship paths. Ask which table you start from, what one result row represents, and which foreign-key path the query follows.

Left Join

SELECT c.code, ci.term, ci.year, e.title
FROM course_instances AS ci
INNER JOIN courses AS c ON c.id = ci.course_id
LEFT JOIN exercises AS e ON e.course_instance_id = ci.id
ORDER BY c.code, ci.year, ci.term;

Use a LEFT JOIN when rows from the left side should stay visible even if no matching row exists on the right side.

NOT EXISTS

SELECT u.name
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM submissions AS s
  WHERE s.user_id = u.id
);

This pattern is useful when the question is about missing related rows, such as users with no submissions.

Grouping and Aggregation

SELECT exercise_id, COUNT(*) AS submission_count
FROM submissions
GROUP BY exercise_id
HAVING COUNT(*) >= 5;

Common notes:

  • WHERE filters rows before grouping.
  • HAVING filters grouped results after grouping.
  • Always ask what one grouped row represents.

Set Operations

SELECT user_id
FROM submissions
WHERE score >= 50

UNION

SELECT user_id
FROM enrollments;

With UNION, the result sets must have compatible columns. The result combines the rows and removes duplicates.

Create Table

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL
);

Add a Foreign Key

CREATE TABLE exercises (
    id SERIAL PRIMARY KEY,
    course_instance_id INTEGER NOT NULL REFERENCES course_instances(id),
    title TEXT NOT NULL
);

This is a common pattern in the course: one table stores a foreign key that points to another table.

Clause Order at a Glance

The common written order of a query is:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

This is worth remembering because many common mistakes come from mixing up:

  • row filtering with WHERE,
  • group filtering with HAVING,
  • and result ordering with ORDER BY.

Parameterized Query in Application Code

In the application parts of the course, values should be passed separately from the SQL structure. With Psycopg t-strings, a safe pattern looks like this:

conn.execute(t"""
    INSERT INTO tags (name)
    VALUES ({name})
""")

The important idea is that user input should not be pasted directly into the SQL text.

Safe SQL Habits

  • Start from the question, not from random syntax.
  • Read the table or schema before writing the query.
  • For UPDATE and DELETE, run a matching SELECT first.
  • After write queries, verify the new database state.
  • Prefer parameterized queries in application code.
  • Keep one eye on meaning: what does one result row represent?
← Glossary
Data and Databases →