Databases and SQL — how structured data is saved and retrieved

A relational database stores data structured in tables like a spreadsheet but with rules for how the tables connect. The database ensures that data remains consistent even when many users read and write simultaneously and that you can find and retrieve large amounts of data quickly through indexes.

§Tables, rows, and columns

A table represents one kind of data — for example customers, orders or users. Each column has a name and a data type (text, number, date), and each row is one specific entry. A primary key is a column or combination of columns that uniquely identifies each row — typically a serial number that is never reused even when the row is deleted.

§Connections between tables

Data is deliberately spread across multiple tables to avoid storing the same information in more than one place — this is called normalization. A foreign key in one table points to the primary key in another, for example a line in an order points to the customer number the order belongs to. Normalization makes it easier to correct information in one place and reduces the risk of data in two tables contradicting each other.

§SQL – the language that queries the database

• 01SELECT retrieves data from one or more tables possibly with conditions (WHERE) and sorting
• 02INSERT adds a new row to a table
• 03UPDATE changes values in existing rows.
• 04DELETE removes rows
• 05JOIN combines data from multiple tables based on their key relationship, e.g. customers together with their orders

“A well-designed database makes the right questions easy to ask and the wrong ones impossible to cheat on.”

— Professional basic rule in system development.