239 cards
SQL, or Structured Query Language, is the standardized language used to manage relational databases—databases that organize data into tables connected through defined relationships. A relational database is managed by software called an RDBMS, with popular implementations including PostgreSQL, MySQL, SQLite, Oracle, an...
Designing tables thoughtfully is essential to avoid anomalies when data is inserted, updated, or deleted. Normalization is the systematic process of organizing tables to reduce redundancy and eliminate unwanted dependencies. It is expressed as a series of progressively stricter rules called normal forms. The first norm...
The SELECT statement is the heart of SQL data retrieval. A query begins by specifying source tables with FROM, then filters rows using WHERE, groups rows with similar values using GROUP BY, and further filters those groups with HAVING. Finally, SELECT chooses which columns and expressions to output, ORDER BY sorts the...
Real-world queries almost always span more than one table, which is where joins come in. A JOIN combines rows from two or more tables based on a related column, typically a primary key matched against a foreign key. An INNER JOIN returns only rows where the join condition matches in both tables, while a LEFT JOIN keeps...
Aggregate functions summarize sets of rows. COUNT(*) tallies every row including NULLs, COUNT(column) tallies non-NULL values, and COUNT(DISTINCT column) counts unique non-NULL values. SUM totals a numeric column, AVG computes its mean, and MIN and MAX return the smallest and largest values. When used without GROUP BY,...
Beyond reading data, SQL defines ways to create, modify, and remove both rows and the structures that hold them. The CRUD operations—Create, Read, Update, Delete—correspond to INSERT, SELECT, UPDATE, and DELETE. INSERT INTO ... SELECT copies results from a query into a target table. UPDATE modifies existing rows matche...
Beyond plain queries, SQL offers ways to encapsulate logic and reshape results. A Common Table Expression (CTE), introduced with WITH, names a subquery so that the main query can reference it like a table; recursive CTEs reference themselves and are ideal for traversing hierarchical data such as org charts or comment t...
Indexes are the most important tool for query performance. A B-tree index—the most common kind—supports equality and range lookups, a hash index supports only equality, a unique index enforces distinctness, and a composite index spans multiple columns. A clustered index determines the physical order of rows in a table...