Introduction to SQL, the language we use to talk to a relational database. Focusing on Postgres

Introduction to databases

In this section I want to introduce you to

  1. what is a database
  2. what is it useful for
  3. and the different kinds of databases you’ll find out there

A database is a collection of information carefully organized into a system. Think about a list of people and their age and email. Or a list of blog posts with their title and content.

A database does not necessarily have to be computer-based. It can be stored on a piece of paper, or index cards.

We call DBMS (= DataBase Management System) the software that lets us manage a database and access its data.

A DBMS manages the data of a database and provides us a way to store it, retrieve it, edit it, persist it to disk, and much more.

That said, the terms database and DBMS are often used interchangeably, and we’ll do the same in the Bootcamp.

When I’ll say database, I’ll mean DBMS.

Common database management software is Postgres, MySQL, SQLite and so on.

A DBMS must:

  • be efficient: a database needs to provide the best performance to store and retrieve data.
  • be persistent: data stored in the database is stored permanently. When the database software is terminated or the machine reboots, the data (excluding hardware failures) should still be there.
  • provide privacy and security: a database provides us the ability to privately and securely store data. It allows access to multiple users, and each user should be able to only access and edit the data it’s allowed to. Some users might only be able to access data and not edit or delete it.
  • provide shared access: multiple users need to be able, with the proper permissions, to access shared data. Multiple applications can access the same database, too.
  • manage lots of data: a database can handle huge amounts of data, and it can scale according to your needs, using various advanced techniques. That does not mean a database is just useful when you have a lot of data - it can be useful even with very few data entries, due to the properties listed above.

Introduction to SQL

We can interact with relational databases using the SQL language.

SQL lets us give instructions to create a database, define its tables schema, fill tables with data, and finally query the data when needed.

SQL stands for Structured Query Language and as the name suggests, it’s not really a programming language, but it was born as a language to query the database, and interact with it.

SQL is a huge subject and to complicate it, each database has its own dialect of SQL, which you’ll need to use. For example creating a table in Postgres is slightly different than creating the same table in SQLite.

In this section we’ll only cover the basics to get you up and running with the fundamentals.

Lessons in this unit:

0: ▶︎ Introduction
1: Using TablePlus
2: Create a Postgres database
3: Create a new table
4: Add data to a table
5: Query data with SELECT
6: Delete data from the database
7: Update data with UPDATE
8: Null constraints
9: Unique and primary keys
10: Updating a table structure
11: Joins
Want to learn more? Check out our courses