Tasks
All the tasks of the course in one place, by module. Each one leads to the lesson that explains what you need.
Tasks from the lessons
Section titled "Tasks from the lessons"Welcome to databases
Reading data
- SELECT: choosing columns
- WHERE: filtering rows
- IN, BETWEEN and LIKE
- NULL: no value
- ORDER BY, LIMIT and DISTINCT
Expressions and functions
Summarising
- Aggregate functions: COUNT, SUM, AVG
- GROUP BY: a report by groups
- HAVING: a condition for the groups
- The order of query execution
Joining tables
- Primary key and foreign key
- JOIN: two tables in one query
- LEFT JOIN: rows without a match too
- Many tables, and a table joined to itself
- UNION, INTERSECT, EXCEPT and CROSS JOIN
Subqueries and windows
- Subqueries
- Correlated subqueries
- A report in steps: WITH
- Trees and sequences: WITH RECURSIVE
- Window functions
Changing data
Tables and rules
- The database: from creating it to DROP
- CREATE TABLE: a new table
- Constraints: rules for the data
- Foreign keys: links between tables
- ALTER TABLE: changing a table
- Views
Designing a database
- Relationship types: 1:1, 1:N, M:N
- ER diagram: from a description to a schema
- Normalization
- Natural keys and denormalization
Transactions
Indexes and speed
The database in a program
- Connecting to the database from code
- SQL injection and parameters
- ORM and the N+1 problem
- Schema migrations
- Connection pools, backups and permissions
Kinds of databases
- OLTP and OLAP
- Key-value and the cache
- Document databases
- Event streams
- Graph databases
- Columnar and time series databases
- Searching text
- Searching by meaning
- Replication, sharding and CAP
- How to choose a database