Structured Query Language, the standard language for interacting with relational databases. It covers querying data with SELECT, modifying it with INSERT, UPDATE, and DELETE, defining schemas with CREATE and ALTER, and controlling access with GRANT and REVOKE. SQL is the universal interface to databases like PostgreSQL, MySQL, SQLite, and SQL Server.
Dates and times are not numbers, but they behave like them in some ways and unlike them in others. Adding a day to a date is not the same as adding 1 to an integer, because months have different lengths and leap years exist. Subtracting two dates produces an interval, not a number. Extracting the…
Numbers are the simplest data type in SQL, and the math functions are the simplest functions. Addition, subtraction, multiplication, and division are the operators. The functions—absolute value, rounding, ceiling, floor, power, square root, and the trigonometric and logarithmic functions—extend them. This chapter covers the numeric and math functions, the differences between integer and floating-point arithmetic,…
A value has a type. The type determines what the value can do: a string can be concatenated, a number can be added, a date can be compared to another date. When a value needs to be used in a context where its type is not accepted, it must be converted. SQL provides two functions…
A window function computes a value across a set of rows without collapsing them into a single row. An aggregate function with GROUP BY produces one row per group; a window function produces one row per input row, with the computed value attached. This is the difference between “the average salary of the department” and…
The PARTITION BY clause is the part of a window function that divides the rows into groups. Without it, the window is the entire result set. With it, the window is each group, and the function is computed independently within each group. This is what makes “the average salary of the department, shown next to…
A ranking function assigns a number to each row based on its position in an ordered set. The three functions—ROW_NUMBER, RANK, and DENSE_RANK—look similar and are often confused, but they handle ties differently. The difference matters in practice: a leaderboard where ties should share a rank needs RANK, a paginated list where every row needs…
The NTILE function divides an ordered set of rows into a specified number of approximately equal groups, called tiles or buckets. It is the ranking function that answers “which quartile is this row in?” or “which decile?” or “which third?” The function assigns a bucket number to each row, starting at 1, and the buckets…
The window functions so far—the ranking functions, the aggregates with OVER—compute values from the rows in the window. The LAG and LEAD are different: they access a specific row relative to the current row. The LAG looks back, the LEAD looks forward, and the offset and the default control the distance and the fallback. They…
The window functions so far access the adjacent rows and the ranked positions. The boundary functions—FIRST_VALUE, LAST_VALUE, and NTH_VALUE—access the specific rows at the edges of the window or at the arbitrary position. They answer the questions “what was the first value?” and “what was the last value?” and “what was the third value?” The…
The aggregate functions—SUM, AVG, MIN, MAX, COUNT—have two lives in SQL. The first is the GROUP BY form: the rows are collapsed, and one row per group is returned. The second is the window form: the OVER clause keeps all the rows, and the aggregate is attached to each one. This chapter is about the…