Database basics

tables, keys, types, NULL

What is a relational database and what is SQL?

A relational database stores data in tables: each row is one record (a user, an account, a transaction), each column is its property. Tables are linked to each other with keys: a transaction knows which account it belongs to, and an account knows which user.

SQL (Structured Query Language) is the language used to work with this data: read, add, change, delete and describe the structure. The main feature is that SQL is declarative: you describe what you want to get, and how exactly to find it is decided by the DBMS itself.

Popular DBMSs: PostgreSQL, MySQL, Microsoft SQL Server, Oracle. Basic SQL is the same in them, and the details differ: LIMIT versus TOP, date functions, some types. The examples in this guide are for PostgreSQL.

In an interview: A relational database stores data in tables linked to each other, and SQL is a declarative language for working with them: I describe what data I want, and the database decides how to find it. I have worked mostly with PostgreSQL, but the basic syntax is the same across different DBMSs.

What are a primary key and a foreign key?

A primary key (PRIMARY KEY) is a column that uniquely identifies a row. It is unique and can't be NULL. Usually it is id.
A foreign key (FOREIGN KEY) is a column that references the primary key of another table. It creates a link and doesn't let you write a reference to a nonexistent record.

In our schema accounts.user_id references users.id. An attempt to create an account for user 999, who doesn't exist, will fail with a foreign key violation error.

CREATE TABLE accounts (
  id       SERIAL PRIMARY KEY,
  user_id  INTEGER NOT NULL REFERENCES users(id),
  currency CHAR(3) NOT NULL,
  balance  NUMERIC(14,2) NOT NULL DEFAULT 0
);

In an interview: A primary key uniquely identifies a row: it is unique and never empty. A foreign key references the primary key of another table and guarantees integrity: for example, you can't create an account for a user who doesn't exist. For me as QA it is also a hint of what to test: whether the system handles such errors correctly.

Which data types should QA know?

Type	What for	What to watch for
`INTEGER`, `BIGINT`	Integers, IDs	Range limits: INTEGER up to ~2.1 billion
`NUMERIC(p,s)` / `DECIMAL`	Exact fractions, money	`NUMERIC(14,2)`: up to 14 digits, 2 after the point
`REAL`, `DOUBLE PRECISION`	Approximate fractions	They have an error: not for money
`VARCHAR(n)`, `TEXT`	Strings	Length: what happens with a string longer than n
`BOOLEAN`	Yes / no	May also be NULL
`DATE`, `TIMESTAMP`, `TIMESTAMPTZ`	Dates and time	With or without a time zone
`UUID`, `JSONB`	Unique IDs, flexible data	Format, nested fields

A column's type directly suggests boundary values for tests: the maximum string length, the number of decimal places, the numeric range.

In an interview: Most often I deal with integers for IDs, NUMERIC for money, strings, booleans and dates with or without a time zone. A column's type is for me a source of boundary values: the maximum string length, the number of decimal places, the numeric range. If the UI allows more than the database, that is a potential bug.

What is NULL and how does it differ from 0 and an empty string?

NULL means "the value is unknown or absent". It is neither zero nor an empty string:

0 is a known value: the balance is zero.
'' is a known value: a string with nothing in it.
NULL is an unknown value: the balance hasn't been calculated yet, the phone isn't given.

The main property: any comparison with NULL gives neither TRUE nor FALSE, but UNKNOWN. Even NULL = NULL is not TRUE. And arithmetic with NULL gives NULL: 100 + NULL = NULL.

Because of this NULL is a source of many bugs: rows "disappear" from filters, sums become empty, counters don't add up.

In an interview: NULL means that a value is unknown or absent: it is neither zero nor an empty string. Any comparison with NULL gives not "yes" and not "no", but "unknown", so rows with NULL often drop out of filters, and arithmetic with NULL gives NULL. So I always check separately how the system behaves with empty values.

Which groups are SQL commands divided into?

Group	Commands	What they do
DQL: queries	`SELECT`	Read data
DML: manipulation	`INSERT`, `UPDATE`, `DELETE`	Change data
DDL: definition	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	Change structure
DCL: access	`GRANT`, `REVOKE`	Manage permissions
TCL: transactions	`BEGIN`, `COMMIT`, `ROLLBACK`	Manage transactions

Sometimes SELECT is put under DML: both variants occur.

In an interview: SQL commands are divided into groups: SELECT for reading, INSERT, UPDATE and DELETE for changing data, CREATE, ALTER and DROP for structure, GRANT and REVOKE for permissions, and BEGIN, COMMIT and ROLLBACK for transactions. As QA I write SELECT most often, and INSERT and UPDATE less often, for preparing test data, and always in a transaction.

Why does QA need to know SQL?

Verifying the result: the UI shows "Transfer successful", but does the database have the right amount, status, fee, a record in the history?
Test data: find a user in the needed state or create one without going through ten steps in the UI.
Diagnostics: is the bug in the front end, in the API or in the data itself? A database query often answers in a minute.
Integrity: find duplicates, "orphaned" records, impossible states.
Automated tests: data preparation and cleanup, checks after actions.

In an interview: SQL for me is a way to check the truth outside the interface. The UI may show "success", while the database has the wrong amount or status. I also use SQL to quickly find or create test data, to localise a bug (front end, backend or data) and to look for integrity violations such as duplicates or records without a parent.