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.
A relational database is a system for storing and managing data as a collection of tables that are linked to each other through shared values. This model was introduced by Edgar F. Codd at IBM in the 1970s, and it replaced the earlier “navigation” approach to data management with a more intuitive, data-centric one ….
You know what a relational database is: a collection of tables linked by keys. But a database is not a file that sits on disk and manages itself. Something must enforce the keys, validate the data types, process the queries, handle concurrent access from multiple users, and recover from crashes. That something is the Relational…
You have seen the relational model and the software that implements it. This chapter is about the structure itself: the table, the row, the column, and the keys that connect them. These are the primitives that every SQL statement operates on. Every SELECT, every INSERT, every JOIN works because the table has columns, the columns…
Every SQL statement belongs to a category. The category determines what the statement does, when it takes effect, and whether it can be rolled back. A CREATE TABLE is not the same kind of operation as an INSERT. A GRANT is not the same as a SELECT. The categories are not a matter of style…
The previous chapters covered what a relational database is, how the RDBMS implements the model, and how tables and keys are structured. This chapter covers the practical step that comes before you write your first SELECT: installing a database server and the client tools that connect to it. There are three open-source RDBMSs that dominate…
Every table lives inside a database. Before you can create a table, insert data, or run a query, the database must exist. The CREATE DATABASE statement is the first command you run on a fresh RDBMS installation. The DROP DATABASE statement is the last command you run when the database is no longer needed. These…
Every column in a table has a data type. The data type defines what kind of values the column can hold, how much space those values consume, and what operations are valid on them. A column declared as INTEGER accepts whole numbers and rejects text. A column declared as VARCHAR(100) accepts strings up to 100…
The CREATE TABLE statement is the first DDL statement most developers write. It defines a new table in the database: the table name, the columns, their data types, and the constraints that govern what data can be stored. Every table in every relational database was created by a statement like this. The LFCA and SQL…
A table is created once and lives for years. The data inside it changes constantly, but the structure — the columns, the data types, the constraints — is not permanent. Requirements change. A new column is needed. A data type was chosen poorly. A constraint must be added to prevent invalid data. The ALTER TABLE…
A table that is no longer needed should be removed. A table that is still needed but whose data should be cleared should be emptied. These are two different operations with two different consequences. DROP TABLE removes the table and everything in it, including the table’s definition. TRUNCATE TABLE removes all rows but keeps the…