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 database schema is not designed by typing CREATE TABLE. It is designed by understanding what the application needs to store, how the pieces relate, and what constraints the relationships impose. The Entity-Relationship model is the notation that captures this understanding before a single line of SQL is written. An entity is a thing the…
A stored procedure is a program the database runs on demand. A user-defined function is a value the query calls, either in a column expression or in a FROM clause. The two look similar, but they are used differently, optimized differently, and limited differently. A scalar function returns a single value. A table-valued function returns…
A stored procedure is a named program that lives in the database catalog. It is invoked by name with the CALL statement, it runs as a unit, and it can return values through output parameters, a result set, or a return code. Unlike a user-defined function, a procedure is allowed to modify the database state:…
A stored procedure without parameters is a fixed program. It does the same thing every time it runs, on the same data, and it can only be reused if the operation happens to be identical. Parameters change that. An input parameter passes a value into the procedure, so the same program serves a range of…
SQL is a declarative language. A query says what it wants, not how to compute it. Procedural SQL adds the “how” back: variables, conditionals, loops, and exception handling. The procedural extensions differ by database. PostgreSQL has PL/pgSQL. SQL Server has T-SQL. MySQL has its own stored program language. Oracle has PL/SQL. The syntax differs, but…
A database operation can fail. A unique constraint is violated, a foreign key has no matching row, a division by zero occurs, a deadlock is detected, a connection drops. The error is not the exception in a well-designed system; it is part of the contract. The question is what the database does when the error…
A trigger is a piece of code the database runs automatically when a table changes. The application does not call it; the INSERT, the UPDATE, or the DELETE calls it. The trigger is the mechanism for the rules that must hold regardless of which application or which developer performs the change. The audit log, the…
SQL is a set-based language. A SELECT returns the whole result at once, and the UPDATE affects every matching row in a single statement. Some tasks do not fit that model. A batch process that calls an external API per row, a data migration that must process the rows in a specific order with the…
Every row needs an identifier. The identifier must be unique, it must be generated without a round trip to the application, and it must work when two transactions insert at the same time. The database provides the mechanism: a counter that produces the next value on demand. The SQL standard calls it a sequence, the…
A database is a shared resource. The application connects with one account, the reporting tool with another, the migration script with a third. Each connection can read some tables, write others, and is denied the rest. The mechanism that defines this is the access control: the GRANT gives the privilege, the REVOKE takes it away,…