SQL basics: DDL, constraints, SELECT and joins

SQL (Structured Query Language) is the standard language for talking to a relational database. In weeks four and five you wrote what you want mathematically with relational algebra. SQL writes the same idea in a readable language. The “write an SQL query that…” questions earn the most points in the exam. On this page you will learn two things: building tables (DDL) and getting data out of tables (SELECT and joins).

Golden rules:

1. The reading order of SELECT-FROM-WHERE is FROM → WHERE → SELECT. First build the table, then filter the rows, and select the columns last.
2. SQL does not remove duplicates. In relational algebra $\pi$ drops duplicates; in SQL you have to write DISTINCT for that.
3. Every join must have a condition. A bare FROM r, s is a Cartesian product.
4. NULL equals nothing, not even itself. If you are looking for NULL, write IS NULL.
5. A “those with no match” question = outer join + IS NULL (or NOT EXISTS from week 7).

The sample database

We use the same small university database throughout the course. Six tables:

Pay attention: Firat (S6) takes no course, Gizem’s department is NULL, Yildiz teaches nothing, nobody teaches or takes Biology, and Cem’s CS101 grade is still NULL. These gaps are the material for the outer join and NULL questions.

DDL: building the table

DDL (Data Definition Language) defines the schema. It creates structure, not rows.

CREATE TABLE course (
  course_id TEXT PRIMARY KEY,
  title     TEXT NOT NULL,
  dept_name TEXT REFERENCES department(dept_name),
  credits   INTEGER CHECK (credits BETWEEN 1 AND 6)
);

CREATE TABLE takes (
  stu_id    TEXT REFERENCES student(stu_id),
  course_id TEXT REFERENCES course(course_id),
  grade     TEXT,
  PRIMARY KEY (stu_id, course_id)
);

Commonly used data types: INT/INTEGER, NUMERIC(p,d) (p digits, d of them decimal), VARCHAR(n) (at most n characters), CHAR(n) (fixed n), DATE, REAL. SQLite is loose about types (TEXT, INTEGER, REAL); standard SQL and other systems are stricter.

Integrity constraints

A foreign key protects from both directions. You cannot add an enrollment for a non-existent student. You also cannot delete a student who has courses: DELETE FROM student WHERE stu_id='S1' gives an error, because takes still points to S1. If you want to allow deletion, you choose the behavior:

CREATE TABLE advisor (
  stu_id  TEXT PRIMARY KEY REFERENCES student(stu_id) ON DELETE CASCADE,
  inst_id TEXT NOT NULL REFERENCES instructor(inst_id)
);

With ON DELETE CASCADE, when the student is deleted the advisor row is deleted on its own. The other options are ON DELETE SET NULL and the default behavior, which is to reject.

PITFALL · FK checking is off by default in SQLite. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON; is run. Our verification script turns it on. In standard SQL and in PostgreSQL this check is always on.

ALTER and DROP

ALTER TABLE student ADD COLUMN email TEXT;   -- email = NULL in the existing rows
DROP TABLE club;                             -- the table and its schema are completely gone
DELETE FROM club;                            -- the rows go, an empty table remains

Basic query: SELECT-FROM-WHERE

SELECT name, salary FROM instructor WHERE dept_name = 'CS' AND salary > 85000;

In relational algebra this query means $\pi_{name,\,salary}(\sigma_{dept\_name='CS' \land salary>85000}(instructor))$. The mapping is:

DISTINCT and duplicates

SELECT dept_name FROM instructor returns 6 rows: CS, CS, EE, MATH, MATH, BIO. SQL returns a multiset (bag). When you add DISTINCT:

AS and arithmetic

SELECT name, salary / 12 AS monthly FROM instructor WHERE dept_name = 'CS';

PITFALL · integer division. In SQLite (and in PostgreSQL) the quotient of two INTEGERs is an integer, and the decimal part is dropped. If you write salary / 12.0, you get 7916.666666666667 and 6666.666666666667.

LIKE, BETWEEN, IN

LIKE matches patterns: % is a string of any length (it may be empty), _ is exactly one character.

SELECT course_id, title FROM course WHERE title LIKE '%a%s';

The pattern says “there is an a somewhere inside and it ends with s”. Circuits ends with s but has no a inside. course_id LIKE 'CS_01' finds CS101 and CS201.

PITFALL · LIKE and uppercase. In standard SQL, LIKE is case-sensitive. SQLite is case-insensitive for ASCII letters: title LIKE 'data%' finds Data Structures and Databases in SQLite, and finds nothing in PostgreSQL.

BETWEEN includes both ends:

SELECT name, salary FROM instructor WHERE salary BETWEEN 70000 AND 85000;

IN asks for membership in a list: WHERE dept_name IN ('EE', 'MATH') → Cem, Defne, Firat. This is the same as dept_name = 'EE' OR dept_name = 'MATH'.

ORDER BY

SELECT name, dept_name, salary FROM instructor ORDER BY dept_name ASC, salary DESC;

First ascending by department, and within the same department descending by salary. Without ORDER BY, the order of rows is not guaranteed. For the first n rows, SQLite and MySQL use LIMIT n, and standard SQL uses FETCH FIRST n ROWS ONLY (SQLite does not recognize the latter).

Join: combining tables

Inner join and three ways to write it

The courses taken by EE students:

SELECT s.name, t.course_id, t.grade
FROM student s JOIN takes t ON s.stu_id = t.stu_id
WHERE s.dept_name = 'EE';

Four ways to write it that give the same result (all were run in the script):

FROM student s JOIN takes t ON s.stu_id = t.stu_id      -- ON: you write the condition
FROM student JOIN takes USING (stu_id)                  -- USING: equate on these columns
FROM student NATURAL JOIN takes                         -- ALL columns with the same name
FROM student, takes WHERE student.stu_id = takes.stu_id -- old style

Firat is not in this result. An inner join keeps only the rows that have a partner.

The NATURAL JOIN trap

student JOIN takes USING (stu_id) JOIN course USING (course_id) gives 14 rows, each record once. But:

SELECT name, course_id, dept_name FROM student NATURAL JOIN takes NATURAL JOIN course;

returns only 9 rows. The reason: student and course both have dept_name. NATURAL JOIN equates those too, and the query silently turns into the question “courses the student took from their own department”. Ada’s MA101, Cem’s MA101 and CS101, Defne’s CS305 and Gizem’s MA202 disappear.

Outer join

An inner join drops rows with no partner. An outer join keeps them and fills the missing side with NULL.

• LEFT JOIN: all rows on the left.
• RIGHT JOIN: all rows on the right.
• FULL OUTER JOIN: all rows on both sides.

Students who have taken no course:

SELECT s.stu_id, s.name, t.course_id
FROM student s LEFT JOIN takes t ON s.stu_id = t.stu_id
WHERE t.course_id IS NULL;

Students and their departments, including the orphans on both sides:

SELECT s.name, d.dept_name, d.building
FROM student s FULL OUTER JOIN department d ON s.dept_name = d.dept_name;

Because Gizem’s department is NULL, she matches no department. BIO has no students.

PITFALL · FULL OUTER JOIN is not everywhere. Before 3.39 (2022), SQLite did not support RIGHT and FULL joins; the queries on this page were run on 3.53. MySQL still does not support FULL OUTER JOIN. There you build the same result by taking the LEFT JOIN result and doing a UNION with the unmatched rows on the right.

Self join

You use the same table under two different aliases. Instructors who earn more than another instructor in the same department:

SELECT a.name AS higher, b.name AS lower
FROM instructor a JOIN instructor b
  ON a.dept_name = b.dept_name AND a.salary > b.salary;

The alias is mandatory: instructor.salary > instructor.salary cannot say which is which.

Common mistakes

SELECT name, building
FROM student, department
WHERE budget > 300000;

SELECT name, course_id
FROM student NATURAL JOIN takes NATURAL JOIN course;

SELECT s.name, d.dept_name
FROM student s FULL OUTER JOIN department d
  ON s.dept_name = d.dept_name;