SQL and Relational Databases

Ask a table a question

SQL is the language you use to work with data that is kept in tables. You write a short statement that says what you want, and the database sends the rows back.

This is the artists table of the small music store used all through the course:

Ask it for the name of artist number two:

SELECT name
FROM artists
WHERE artist_id = 2;

The answer arrives as a small table of its own:

The database looks in the artists table, keeps only the row whose artist_id is the number you asked for, and returns the name of that row. You said what you wanted, not how to find it. That is the whole idea of SQL.

Keep each fact in one place

A database is a structured collection of data. To add, read and change that data you need a database system, and SQL is how you ask it for things.

A relational database keeps data in separate tables instead of one big store. An album row does not repeat the artist name: it keeps the artist number, so a filter on albums uses artist_id.

SELECT title
FROM albums
WHERE artist_id = 2;

Two album rows carry the number two, so two titles come back. With WHERE artist_id = 1 instead, the first and fourth album rows match, so the query returns For Those About To Rock and Let There Be Rock. Because the name is stored once in artists, an artist that changes its name is edited in one row only. The database also enforces the rules you set between tables, such as a foreign key from albums to artists. A foreign key does not stop duplicate rows; that needs a unique constraint. For example, with ON DELETE RESTRICT, it stops you from deleting artist two while albums still point to it. With ON DELETE CASCADE, it deletes those albums too. It also refuses a new album whose artist_id matches no artist.

Tell the language from the product

SQL is the language you write; the database system is the product that keeps the data and carries out your statement. People use the word database for both, and that is where the confusion starts.

The statement you read a moment ago is only text. On its own it stores nothing and finds nothing: some product has to receive it, look in the tables and answer with rows.

Meet four products this course refers to

This course refers to four products by name, so it is worth putting a face to each of them. Each one is described here with the words of its own documentation:

SQL Server is a relational database management system, and the other three are database systems too: none of the four is the SQL language itself.

Most SQL databases run as a server that your program connects to, and applications and tools reach SQL Server that way. SQLite is different: it has no separate server process, and a whole database with its tables lives in a single ordinary file.

This course does not pick one of them for you, and none of them is needed to follow the lessons. What you learn here is the language they share.

Run the same query anywhere

The four products have a great deal in common. All of them keep data in tables of rows and columns, and all of them read SQL.

SQL is a standardized language, and each of the four products implements it, so a plain statement can be sent to any of them. PostgreSQL, for one, states that it conforms to at least 170 of the 177 mandatory features of the core standard.

SELECT name, unit_price
FROM tracks
WHERE unit_price > 0.99
ORDER BY name;

Two tracks cost more than the others, so two rows come back. The rows come back sorted by name, so All I Really Want is printed before Walk On Water. Every word of that statement is plain SQL, which is why the core of the language is worth learning once.

Expect some differences

The products are not identical, and their own pages say so. Each product documents extra data types and features of its own, so a script that uses them needs checking before it moves to another product. Applications talk to SQL Server in a dialect of its own called Transact-SQL, and SQLite, unlike most other SQL databases, works without a separate server process.

PostgreSQL also states that no relational database meets full conformance with the SQL standard, which is a fair warning for all of them.

Learn the shared core, which is what this course teaches, and when a statement looks unusual, or when you move it to another product, check the documentation of the product you are using.

Key takeaways

SQL is the language you write; the database system is the product that keeps the data and carries out your statement. You say what you want, and the rows come back.

A well-designed relational database keeps each fact in one place, in separate tables, and enforces the rules you define between them.

PostgreSQL, MySQL, SQL Server and SQLite are four database systems, not four languages. They store data in tables and they read SQL, so the core you learn here travels. The parts that a product documents as its own are the parts to check before you move a statement.