Databases and tables

Find out what a database is, how tables are built from rows and columns, and how keys link tables together.

  • Beginner
  • 12 min
  • 10 questions
  • Lesson 1 of 28

In this lesson you will learn

  • Explain what a database and a DBMS are
  • Tell the rows, columns and cells of a table apart
  • Understand the role of primary and foreign keys
  • Run your first SQL query

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.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

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”.

SQL
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
Your first SQL query. Run it and look at the result.
  • 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.

SQL
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.
TableWhat it storesRows
studentsstudents: first and last name, age, grade, city, email12
coursescourses: title, subject, teacher, credits7
enrollmentswhich student takes which course, and the score20
productsproducts: name, category, price, stock10
customerscustomers: name, city, country, sign-up date7
ordersorders: who bought which product, how many and when12
The course's practice database. All examples work with these six tables.

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.

DBMSWhere it is usedNotable feature
SQLitemobile apps, browsers, small programsno server needed: the whole database is one file
MySQLwebsites and web applicationsopen source and very popular
PostgreSQLcomplex applications, analyticsopen source, very close to the SQL standard
SQL Servercompany systems built on Microsoft technologymade by Microsoft; its dialect is called T-SQL
1 / 6

Exercise

Show all columns of the customers table and sort the rows by id.

Exercise · SQL
-- show every column of the customers table, sorted by id
Expected 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.

Exercise · SQL
SELECT *
FROM enrollments
-- keep only course 3 and sort by id
Expected 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.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

10 questions. Every correct answer earns XP.

Question 1 / 10

Question 1 / 10. Where is all the information about one item (for example, one student) stored in a table?