A school has 1,000 students, dozens of subjects and thousands of marks. Keeping all of that in a notebook — or even in one Excel file — soon becomes painful: data gets duplicated, mistakes creep in and finding what you need takes ages. Banks, online shops, social networks and electronic school diaries solve this problem with a database. And there is a special language for talking to it — SQL.
What is a database?
A database is an organised collection of data that is easy to search, change and protect. We don't work with the database files directly — a special program does it for us. It stores the data on disk, runs our queries, lets hundreds of users work at the same time and keeps out anyone without permission.
DBMS (database management system)Definition
The software that creates and stores databases and runs the queries sent to them. Examples: SQLite, MySQL, PostgreSQL, Microsoft SQL Server.
In this course we study relational databases. They keep all data in tables and link the tables to each other. The idea was proposed in 1970 by IBM researcher Edgar F. Codd, and it is still the most widely used approach today.
Tables: rows and columns
A table stores information about one kind of thing: students, courses, products. All examples in this course use the same practice database. Let's look at the courses table. Run the query below: SELECT * means “take all columns”, FROM courses means “from the courses table”, and ORDER BY id means “sort by id”.
SELECT *
FROM courses
ORDER BY id;Expected output
id | title | subject | teacher | credits 1 | Algebra | Math | Ramin Səfərov | 4 2 | Geometry | Math | Ramin Səfərov | 3 3 | Mechanics | Physics | Ülviyyə Axundova | 4 4 | Organic Chemistry | Chemistry | Samir Hacıyev | 3 5 | World History | History | Nərmin Vəliyeva | 2 6 | Python Basics | Informatics | Elnur Qasımov | 3 7 | English B1 | English | Sara Mitchell | 2
- A column (field) is one property:
title,teacher,credits. Every column has a name and a data type: text, whole number, decimal number, date. - A row (record) holds all the information about one item. For example, row 3 describes the Mechanics course.
- A cell is a single value where a row and a column meet, e.g. the credits of Mechanics: 4.
- Rows in a table have no built-in order. If we want the result in a particular order, we write
ORDER BY.
Keys: how tables are linked
Primary keyDefinition
A column that identifies each row uniquely. Its values can never repeat or be empty. In our practice database it is the id column of every table.
Foreign keyDefinition
A column that refers to the primary key of another table. For example, enrollments.student_id refers to students.id and tells us which student an enrollment belongs to.
The enrollments table does not contain student names — only their numbers. The query below shows the courses of student number 1, Aysel: course_id 1 is Algebra and 6 is Python Basics. This way a student's name and city are stored in just one place — the students table — and are never duplicated.
SELECT *
FROM enrollments
WHERE student_id = 1
ORDER BY id;Expected output
id | student_id | course_id | score | enrolled_on 1 | 1 | 1 | 92 | 2025-09-15 2 | 1 | 6 | 88 | 2025-09-15
WHERE keeps only the rows that match a condition — you will learn it in detail soon.| Table | What it stores | Rows |
|---|---|---|
| students | students: first and last name, age, grade, city, email | 12 |
| courses | courses: title, subject, teacher, credits | 7 |
| enrollments | which student takes which course, and the score | 20 |
| products | products: name, category, price, stock | 10 |
| customers | customers: name, city, country, sign-up date | 7 |
| orders | orders: who bought which product, how many and when | 12 |
SQL and its dialects
SQL (Structured Query Language) is the standard language for talking to databases. It is declarative: you describe what you want, not how to find it, and the DBMS chooses the best way itself. SQL keywords are not case-sensitive, but writing them in capitals is the common convention. Each statement usually ends with a semicolon (;).
SQL is a standard, but every DBMS has added its own extras. These variants are called dialects. The core commands (SELECT, WHERE, JOIN and so on) are the same everywhere; the differences are in small details. In this course the examples run in SQLite, and we point out the important differences along the way.
| DBMS | Where it is used | Notable feature |
|---|---|---|
| SQLite | mobile apps, browsers, small programs | no server needed: the whole database is one file |
| MySQL | websites and web applications | open source and very popular |
| PostgreSQL | complex applications, analytics | open source, very close to the SQL standard |
| SQL Server | company systems built on Microsoft technology | made by Microsoft; its dialect is called T-SQL |
Exercise
Show all columns of the customers table and sort the rows by id.
-- show every column of the customers table, sorted by idExpected output
id | name | city | country | joined_on 1 | Anar Mustafayev | Bakı | Azerbaijan | 2024-01-10 2 | Lalə Əhmədova | Gəncə | Azerbaijan | 2024-03-22 3 | Emre Yılmaz | İstanbul | Türkiye | 2024-05-05 4 | Olga Ivanova | Moscow | Russia | 2024-06-18 5 | John Carter | London | United Kingdom | 2024-08-30 6 | Zəhra Hüseynli | Bakı | Azerbaijan | 2025-02-14 7 | Mehmet Kaya | Ankara | Türkiye | 2025-04-01
Exercise
From enrollments, show every enrollment in course number 3 (Mechanics) and sort them by id.
SELECT *
FROM enrollments
-- keep only course 3 and sort by idExpected output
id | student_id | course_id | score | enrolled_on 4 | 2 | 3 | 81 | 2025-09-16 7 | 4 | 3 | 68 | 2025-09-18 12 | 6 | 3 | 77 | 2025-09-20 19 | 11 | 3 | 94 | 2025-09-23
Remember
- A database is an organised collection of data managed by a DBMS.
- In a relational database data lives in tables: a row is one item, a column is one property.
- A primary key identifies each row uniquely; a foreign key points to a row in another table.
- SQL is declarative: you say what you want and the DBMS works out how to get it.
- SQLite, MySQL, PostgreSQL and SQL Server share the same core SQL and differ only in details.