Why a database, not a spreadsheet

Part of the free LogicWiz SQL & Databases course, Module 1: What a database is. The queries and practice run in the app on real Postgres.

The college marks sheet lives on WhatsApp as marks_final_v3_REAL.xlsx. Two teachers edited two copies, and now Riya has 72 in one file and 27 in the other. Which one is true?

You'll learn to

• Say why colleges, banks and apps keep data in a database, not in spreadsheets
• Read a table as rows and columns (and their exam names: tuple and attribute)
• Run your first query, `SELECT * FROM …`

Learn

Where spreadsheets break

A spreadsheet is fine for one person and a few hundred rows. A college is not that.

• Copies everywhere. Every email or WhatsApp forward makes a new copy, and the copies drift apart.
• No rules. Anyone can type Dehli, put 11 in a CGPA cell, or leave the roll number empty.
• One editor at a time. Two people saving together means one person's work is lost.
• No safety net. A crash in the middle of a save can leave half a file.

A database keeps one copy on a server. Every app asks it questions, and it checks every change against rules before saving.

Tables, rows and columns

A database keeps data in tables. Each table is about one kind of thing: students, departments, courses.

• A row is one thing: one student.
• A column is one fact about every row: the student's city.
• Every value in a column has the same type: cgpa holds numbers, joined_on holds dates.

That last rule is why a database can say no to cgpa = 'very good'. A spreadsheet cell will accept anything.

Three rows of the students table

Each row is one student. Each column is one fact. Aditya has no CGPA yet (he is in his first term), so that cell is NULL: "not known". You will meet NULL properly in Module 2.

Your first query

SELECT *
FROM students;

SELECT * means "every column" and FROM students says which table. Read it as "show me everything in students". Press Run: 20 students, 9 columns. Nothing you run here can break anything; every run starts from a fresh database.

Another table

SELECT *
FROM departments;

Six departments. Look at Mathematics (dept_id 6): it exists even though no student belongs to it yet. Real data is full of such rows, and this course uses them on purpose.

Asking a sharper question

SELECT name, city
FROM students
WHERE city = 'Delhi';

A preview of Module 2: list the columns you want after SELECT, and keep only some rows with WHERE. Text goes in single quotes. Try changing Delhi to Pune.

A table has no built-in row order. There is no "row 5" the way a spreadsheet has. If you need an order, you ask for it with ORDER BY (Module 2). Never trust the order rows happen to come back in.

Exams use the formal names: a table is a relation, a row is a tuple, a column is an attribute. The number of columns is the degree and the number of rows is the cardinality. So departments has degree 3 and cardinality 6. The set of values a column may hold is its domain.

In one line

A database keeps one checked copy of the data in tables (relations) of rows (tuples) and columns (attributes); SELECT * FROM t shows a table.