DHIVA LABS · LEARN

Appearance

Databases ​

Learn how databases store and protect data, write the SQL you need every day, and change real data without losing any. Five short lessons and a hands-on lab with a practice shop database.

What you will be able to do ​

By the end of this track you can:

  • Explain what a table, a row, a column and a primary key are, and when a key-value or document database fits better.
  • Read and write SQL: find rows, sort them, count them, add, change and delete them.
  • Join tables on their keys, and use an index to make a lookup fast.
  • Wrap changes in a transaction, and roll them back when something goes wrong.
  • Work on data that matters without losing it: read-only first, a tested backup before every change, limited accounts, safe queries in code, and encryption.

Who it is for ​

Students, testers, new developers and curious home users. You need no SQL and no programming. If you can use a spreadsheet, you can follow it.

The lessons ​

Read them in order. Each one builds on the one before and ends with a short quiz.

Lesson 1What is a database?Tables, rows, columns and keys, and the same data as a table, key-value pairs and a document.Lesson 2SQL basicsSELECT, WHERE, ORDER BY and LIMIT, then INSERT, UPDATE, DELETE and counting with GROUP BY.Lesson 3Joins and keysForeign keys, one-to-many, INNER and LEFT JOIN, and indexes that make lookups fast.Lesson 4TransactionsAll or nothing: BEGIN, COMMIT and ROLLBACK, and constraints that refuse bad data.Lesson 5Safety and backupsProduction rules, tested backups, least privilege, SQL injection and encryption.LabExplore a shop databaseOpen a practice database in Database Manager, query it, roll back a mistake, add an index and back it up.

The practice database ​

Every example in the track uses the same small, made-up shop: 30 customers, 20 products, 120 orders and their items, and a store-credit table. You can download it as one SQLite file, shop.sqlite, and run every query yourself. The names are invented and every email address is at example.com.

Apps that use this knowledge ​

AppWhat you do with it
Database ManagerOpen SQLite, MySQL / MariaDB, PostgreSQL, MongoDB and LMDB databases, browse and edit rows, run queries, export CSV and take backups.
lmdb-lws-backupTake encrypted, verified backups of a running LMDB database.
ApiWorkbenchTest an API. Combined with Database Manager, check that a call really wrote the row it claims to have written.

How long it takes ​

About 20 minutes per lesson and 45 minutes for the lab: an afternoon in total, or one lesson a day for a week.

Next steps ​