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.
Every table in a relational database needs a way to identify its rows. Every relationship between two tables needs a way to enforce that relationship. The primary key and the foreign key are the two constraints that make both possible. They are the most important constraints in the relational model, and they are the foundation…
The previous chapter covered the two constraints that define the structure of a relational schema: the primary key and the foreign key. This chapter covers the three constraints that define the rules for individual columns: NOT NULL, UNIQUE, and DEFAULT. They are simpler than the primary and foreign keys, but they are just as important….
The previous chapter covered the three constraints that define simple rules for individual columns: NOT NULL, UNIQUE, and DEFAULT. This chapter covers the constraint that defines complex rules: CHECK. The CHECK constraint specifies a condition that the value in a column must satisfy. The condition can be as simple as price >= 0 or as…
Every SQL query that reads data starts with SELECT. It is the statement that retrieves rows from one or more tables and returns them as a result set. The SELECT statement is part of DQL — Data Query Language — and it is the most frequently used statement in SQL. Every application that displays data,…
The SELECT statement retrieves rows. Without a WHERE clause, it retrieves every row in the table. With a WHERE clause, it retrieves only the rows that match a condition. The WHERE clause is the filter. It is the difference between “give me all the orders” and “give me the orders placed in the last thirty…
Every WHERE clause is built on comparison operators. The = operator tests equality. The > operator tests ordering. The <> operator tests inequality. Without comparison operators, the WHERE clause has nothing to evaluate. The previous chapter covered the WHERE clause and introduced the comparison operators briefly. This chapter goes deeper into each operator, the rules…
Comparison operators test a single condition. Logical operators combine multiple conditions. The AND operator requires both conditions to be true. The OR operator requires at least one condition to be true. The NOT operator negates a condition. Together, they form the boolean algebra that every WHERE clause, every CASE expression, and every CHECK constraint is…
A comparison operator tests one boundary. total > 100 tests whether a value exceeds 100. total < 200 tests whether a value is below 200. To test whether a value falls within a range, you need two comparisons joined by AND. The BETWEEN operator is a shorthand for that pair of comparisons. It tests whether…
A comparison operator tests one value. The BETWEEN operator tests a range. The IN operator tests a set. It answers the question “Is this value one of these values?” The NOT IN operator answers the opposite question: “Is this value not one of these values?” The two operators are the tool for filtering by membership…
The comparison operators test exact values. The IN operator tests set membership. The LIKE operator tests a pattern. It answers the question “Does this string match this pattern?” The pattern uses wildcards — special characters that match any sequence or any single character. The LIKE operator is the tool for text search in SQL: finding…