Databases & SQL

Where most of the world's important data lives, and the language used to ask it questions. How databases organize, protect and recover data — and how to write SQL that gives the right answer.

18 pages 114 glossary terms DP-900 DataSys+

How to use this topic

The first pages explain what a database is and how the relational model works. The next pages teach SQL step by step — reading data, filtering, grouping, joining and changing it. Then come design (tables, constraints, normalization), performance and transactions, and finally security, backups, NoSQL, analytics and day-to-day administration. Turning query results into answers — data quality, spreadsheets, analytical SQL, charts, BI tools and data pipelines — is the subject of Data & Analytics.

The sample database

Almost every example uses the same small web shop, so you can follow one set of data from page to page. It has five tables: customers, products, orders, order_items (one row per product in an order) and employees. All names and addresses are made up, and prices are whole euros to keep the arithmetic exact.

Every SQL example was run exactly as shown, on SQLite with this database loaded, and printed the result below it. You can do the same: install SQLite (or use any SQLite tool), download shop.sql and run sqlite3 shop.db < shop.sql. The SQL is standard wherever possible; where PostgreSQL, MySQL or SQL Server differ, the page says so.

Pages are tagged with the certifications they help with: DP-900 (Microsoft Azure Data Fundamentals) and CompTIA DataSys+.

Learning path

  1. 01What Is a Database?What a database is and why organizations use one instead of files or spreadsheets: data and information, database management systems, and the main kinds of databases.BeginnerDP-900DataSys+
  2. 02The Relational ModelHow relational databases organize data: tables, rows and columns, primary and foreign keys, and the relationships between tables.BeginnerDP-900DataSys+
  3. 03Querying with SELECTReading data with SQL: choosing columns, filtering rows with WHERE, sorting with ORDER BY, and limiting and de-duplicating results.BeginnerDP-900DataSys+
  4. 04Filtering, NULL and FunctionsMore precise filtering with LIKE, IN and BETWEEN, the traps of NULL, and functions for text, numbers and dates.BeginnerDP-900DataSys+
  5. 05Aggregation and GroupingSummarizing data: COUNT, SUM, AVG, MIN and MAX, grouping rows with GROUP BY, and filtering groups with HAVING.BeginnerDP-900DataSys+
  6. 06JoinsCombining tables in one query: inner, left, right and full outer joins, joining more than two tables, self-joins, and the mistakes that duplicate rows.IntermediateDP-900DataSys+
  7. 07Subqueries, CTEs and ViewsBuilding queries from queries: subqueries in WHERE and FROM, correlated subqueries, common table expressions with WITH, and views.IntermediateDP-900DataSys+
  8. 08Changing DataAdding, changing and removing rows with INSERT, UPDATE and DELETE — and why the WHERE clause decides whether you change one row or all of them.BeginnerDP-900DataSys+
  9. 09Designing TablesCreating tables with CREATE TABLE: data types, constraints such as NOT NULL, UNIQUE, CHECK and foreign keys, and changing a table later.IntermediateDP-900DataSys+
  10. 10NormalizationOrganizing tables so each fact is stored once: anomalies, first, second and third normal form, and when to denormalize on purpose.IntermediateDP-900DataSys+
  11. 11Indexes and Query PerformanceMaking queries fast: how an index works, when it helps and when it hurts, reading a query plan, and finding slow queries.IntermediateDataSys+
  12. 12Transactions and ACIDKeeping data correct when things go wrong or happen at once: transactions, COMMIT and ROLLBACK, the ACID properties, isolation levels and locking.IntermediateDP-900DataSys+
  13. 13Database SecurityProtecting data in a database: accounts and roles, GRANT and REVOKE, least privilege, SQL injection, encryption and auditing.IntermediateDataSys+
  14. 14Backup, Recovery and ReplicationNot losing data: logical and physical backups, transaction logs and point-in-time recovery, and replication for availability.IntermediateDataSys+
  15. 15NoSQL DatabasesDatabases beyond tables: key-value, document, wide-column and graph stores, what they trade away, and the CAP theorem.IntermediateDP-900DataSys+
  16. 16Data Warehousing and AnalyticsDatabases for analysis instead of transactions: OLTP versus OLAP, data warehouses and lakes, ETL, and star schemas with facts and dimensions.IntermediateDP-900
  17. 17Working with Databases from CodeUsing a database from a program: connections and drivers, parameterized queries, transactions in code, and object-relational mappers.IntermediateDataSys+
  18. 18Database Administration in PracticeKeeping databases healthy: monitoring, maintenance, schema migrations, capacity, and the problems a database administrator meets most.AdvancedDataSys+

Databases & SQL glossary

Short definitions of the terms used in this topic. The full glossary covers every topic.