Difficulty: Beginner
Estimated Time: 90 minutes

Goal

In this session we are going to dive into more in depth in SQL. To do that, we will install a popular DBMS (PostgreSQL) and we will start from simple insert queries and go until queries with nested subqueries that also use the WITH clause.

After this lecture you should be able to undestand:

  • How to create tables in SQL.
  • How to insert values into these tables and some simple restrictions about inserting values.
  • How to read parts of your tables using the SQL SELECT clause.
  • How to perform joins on tables and also some join variants.
  • How to execute common SQL aggregations.
  • How to write nested SQL queries and use the WITH clause.

Interactive session with SQL

Step 1 of 7

Step 1

Installing Postgres

We will follow again the steps from last time to install a Postgres instance.

Run the following commands in your katakoda interactive environment to install Postgres.

apt-get install wget ca-certificates

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

apt-get update

apt-get install -y postgresql postgresql-contrib

Now that Postgres is install create a running instance and connect to the database.

pg_ctlcluster 12 main start

su - postgres

psql

Now that we are connected to the database let's create and connect to a test database to run our queries.

CREATE DATABASE testdatabase;

\c testdatabase

Everything is set up, so let's get started!