SQL basics

01

Relational databases and SELECT basics

The vocabulary of relational databases

A relational database keeps data in tables (grids of rows and columns) and links tables to each other through key values. Theory uses different names for the same things, so it's worth learning the pairs.

Relations have a few properties. No two tuples can be exactly the same, there's no order among tuples or among attributes, and every value is atomic — it can't be broken down any further.

Keys come in several kinds, too.

The rules that keep data correct at all times are called integrity constraints. The classic ones are entity integrity (a primary key can't be NULL or duplicated), referential integrity (a foreign key value must exist in the table it references, or be NULL) and domain integrity (a column value must stay inside its defined domain).

The theoretical operations behind relational databases are called relational algebra. Here's how each part of SQL lines up with them.

The four kinds of SQL statements

SELECT and WHERE

Here's the skeleton of a SELECT.

SELECT [DISTINCT] column [AS alias], ...
FROM   table
WHERE  condition
ORDER BY column [ASC | DESC];

• DISTINCT removes duplicate rows from the result (a row counts as a duplicate only if the whole SELECT list matches)
• An alias is attached with AS; if it contains spaces or special characters, wrap it in double quotes
• To join strings together, Oracle uses || and SQL Server uses + (both have the CONCAT function)

These are the operators you'll use in WHERE.

When you chain several conditions, precedence matters. According to the Oracle docs, the order is parentheses → comparison operators → NOT → AND → OR. AND comes before OR, so A OR B AND C is evaluated as A OR (B AND C). If you're ever unsure, add parentheses — it's the safe bet.

ORDER BY: sorting, and where NULLs end up

ORDER BY sorts in ascending order (ASC) by default, and you can sort by a column name, an alias or a position number in the SELECT list (1, 2 …). The tricky part is NULL. Oracle treats NULL as the largest value, so in ascending order it goes last; SQL Server treats it as the smallest, so it goes first. In Oracle you can place NULLs yourself with NULLS FIRST or NULLS LAST.

The order a SELECT is processed in

A SELECT statement isn't processed in the order it's written. Once you know the real order, questions like “Why can't I use an alias in WHERE?” and “Why can't I put SUM in WHERE?” answer themselves. Step through the figure below.

SELECT   DEPT_CODE, SUM(SALARY) AS TOTAL
▶FROM     EMP
WHERE    SALARY >= 260
GROUP BY DEPT_CODE
HAVING   SUM(SALARY) >= 280
ORDER BY TOTAL DESC;

To sum up: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. An alias is created in SELECT, so only the ORDER BY that comes after it can use it.

02

Functions, GROUP BY and HAVING

You've pulled columns out with SELECT. Now it's time to reshape values (functions) and to bundle rows up and count them (GROUP BY).

Single-row functions: one value back per row

Functions come in two kinds. Single-row functions return one result for each row, and multi-row functions (aggregate functions) bundle many rows into a single result. You can use single-row functions anywhere — SELECT, WHERE, ORDER BY — and you can nest one function inside another.

In Oracle, adding a number to a date counts in days: HIRE_DATE + 1 is the next day, and HIRE_DATE + 1/24 is an hour later. One more thing: Oracle treats an empty string (‘’) as NULL, while SQL Server keeps empty strings and NULL apart.

Type conversion and CASE

There are two ways to change a data type: explicit conversion, where you call a function yourself (Oracle: TO_CHAR, TO_NUMBER, TO_DATE; SQL Server: CAST, CONVERT), and implicit conversion, where the database does it for you. Implicit conversion is handy, but it can convert things in ways you didn't expect or stop an index from being used, so it's a good habit to convert explicitly.

To return different values depending on a condition, use CASE. There's the simple CASE, which compares against a single value, and the searched CASE, which takes conditions. Oracle also has the DECODE function, which does the same job.

SELECT NAME,
       CASE WHEN SALARY >= 350 THEN 'High'
            WHEN SALARY >= 280 THEN 'Mid'
            ELSE 'Low' END AS GRADE,      -- without ELSE you get NULL
       DECODE(DEPT_CODE, 'D10', 'Sales', 'D20', 'Dev', 'Other') AS DEPT_NAME
FROM   EMP;

Functions for handling NULL

Aggregate functions and NULL

Aggregate functions like COUNT, SUM, AVG, MAX and MIN bundle many rows into a single value. There's one rule to remember: every one of them except COUNT(*) ignores NULLs. Try switching functions in the figure below.

Put DISTINCT inside an aggregate function and each duplicate value is counted only once (COUNT(DISTINCT DEPT_CODE)). If no rows match at all, COUNT returns 0, while SUM, AVG, MAX and MIN return NULL.

GROUP BY and HAVING

GROUP BY bundles rows that share the same value and aggregates each group. Once rows are grouped, each group becomes a single line, and that brings some rules with it.

• Any column in the SELECT list that isn't inside an aggregate function must also be in GROUP BY
• Don't use SELECT aliases in GROUP BY — given the processing order, the alias doesn't exist yet (the latest Oracle 23ai allows it, but exams go by the traditional rule)
• NULLs form a group of their own
• GROUP BY doesn't guarantee any order — if you need one, use ORDER BY

To put a condition on groups, use HAVING. WHERE filters rows before they're grouped; HAVING filters groups after they're formed. That's why conditions on aggregate functions can only go in HAVING. If a condition doesn't need an aggregate function, filtering it early in WHERE is the better choice, since it leaves fewer rows to process.

SELECT   DEPT_CODE, COUNT(*) AS HEADCOUNT, AVG(SALARY) AS AVG_SAL
FROM     EMP
WHERE    SALARY >= 250          -- filter rows first
GROUP BY DEPT_CODE
HAVING   COUNT(*) >= 2;         -- then filter the groups