SQL and databases cheat sheet (PostgreSQL)

1. 1Relational model and your setupWhat a relational database is, the keys that connect tables, and the server, client and sample data you need before writing real queries.7 concepts

   1. 1Relational modelData stored in tables: each table describes one kind of thing, each row is one instance of it and each column is one attribute with a fixed type.
   2. 2Primary keyA column, or set of columns, whose value is unique and never NULL for every row, so it identifies exactly one row.
   3. 3Foreign keyA column that stores the primary key of a row in another table, declared with a constraint so the database rejects values that do not exist there.
   4. 4DDL, DML and DCLThe families of SQL statements: DDL defines structure (CREATE, ALTER, DROP), DML reads and changes rows (SELECT, INSERT, UPDATE, DELETE), DCL grants access…
   5. 5Command-line clientA terminal program that connects to the database server and runs SQL you type or pipe in: psql for PostgreSQL, mysql for MySQL, sqlcmd for SQL Server.
   6. 6pgAdminThe open source graphical tool for PostgreSQL: an object browser, a query tool with history and a graphical EXPLAIN, and backup and restore screens.
   7. 7Sample databaseA ready-made database with realistic tables and thousands of rows that you load locally to practise on, such as pagila for PostgreSQL, sakila for MySQL or…

   7 in this chapterOpen this chapter
2. 2Querying one tableSELECT from a single table: choosing columns, filtering rows, handling NULL, sorting, limiting and shaping results with expressions.9 concepts

   1. 1SELECT and FROMSELECT lists the columns or expressions you want back and FROM names the table they come from.
   2. 2WHEREWHERE keeps only the rows for which a condition is true, using comparisons, AND, OR, NOT, IN for a list, BETWEEN for an inclusive range and LIKE for simple…
   3. 3NULL and three-valued logicNULL means a value is missing or unknown.
   4. 4ORDER BYORDER BY sorts the result by one or more columns or expressions, ascending by default or DESC for descending.
   5. 5Limiting rowsReturning only the first N rows of a sorted result.
   6. 6DISTINCTDISTINCT removes duplicate rows from the result, comparing all selected columns together.
   7. 7CASE expressionCASE returns a value based on conditions checked in order, like if and else inside a query.
   8. 8String and date functionsBuilt-in functions for text (joining, trimming, changing case, extracting parts) and for dates (current time, adding intervals, truncating to a month).
   9. 9Logical order of a queryThe order a query is evaluated in, which differs from the order you write it: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and…

   9 in this chapterOpen this chapter
3. Joins and set operationsCombining rows from several tables with joins, and combining whole result sets with UNION, INTERSECT and EXCEPT.8 conceptsOpens with credits
4. Aggregation, subqueries and windowsSummarising rows with GROUP BY, nesting queries with subqueries and CTEs, and calculating rankings and running totals with window functions.11 conceptsOpens with credits
5. Schema designTurning requirements into tables: entities and relationships, keys, normalisation, data types, constraints and views.9 conceptsOpens with credits
6. Changing data safelyINSERT, UPDATE, DELETE and upserts, and the transactions, isolation levels and locks that keep concurrent changes correct.9 conceptsOpens with credits
7. Indexes and query plansHow indexes make reads fast, how to read what the database actually did with EXPLAIN or an execution plan, and the habits that keep queries quick.10 conceptsOpens with credits
8. SQL in applications and operationsUsing SQL safely from code, keeping schema changes under control, and the operational basics: pools, permissions, backups and where other kinds of database fit.9 conceptsOpens with credits
9. NoSQL and MongoDBHow a document database compares with the relational model, using MongoDB: documents, queries mapped from SQL, embedding versus referencing, indexes and transactions, and JSON inside your own database.5 conceptsOpens with credits
10. 10Glossary, A to ZEvery concept in the sheet and the smaller terms it uses, in one list.197 terms

Queries, design, indexes, transactions.