MavigBot

Since I started developing software professionally, SQL has proven itself to be one of the most valuable tools in my toolbox. The versatility of the language makes learning it one of the most effective uses of time I can think of. Without a doubt, it’s something that I feel every programmer should be familiar with.

Structured Query Language is used by developers and applications to interact with relational databases, accomplishing tasks like retrieving and altering the data within. SQL is the standard language for database creation and manipulation, meaning it’s a tool that can be used with all of the most widespread databases of today.

In my SQL articles I’ll be focusing primarily on PostgreSQL, which is one of the more popular and mature relational database engines in use today. While I’ve chosen PostgreSQL because of its reliability and extensive features, the concepts approached will be largely applicable to most other database engines.

The best way to learn SQL is to start working with it – and to do that, we’ll need a database to interact with.

Installing PostgreSQL

Before we can make a new PostgreSQL database to play with, we’ll first need to get our environment ready. I’m running Ubuntu version 22.04.2, but the steps would be similar for other versions of Ubuntu, or even other distributions of Linux.

To get started, we’ll first want to make sure our package index is up to date, install the PostgreSQL package, and ensure the service is running by executing the following commands in our terminal:

sudo apt update
sudo apt install postgresql
sudo systemctl start postgresql.service

Now that we have the database engine running, we can move on to creating our first database.

Creating a Database

The process we went through above created a user called postgres, which we’ll need to switch to in order to start working with postgres. To do this, run the following command:

sudo -u postgres -i

Now that we’re using the postgres user account, we can access the postgres prompt. This will allow us to interactively issue commands and queries to PostgreSQL.

psql

From this prompt, we can finally get started with some SQL. The first thing we will want to look at is the postgres-specific command to list all databases on our machine:

\l

For a new postgres installation, this should output the following three default databases:

• postgres
• template0
• template1

For practice, it’s a good idea to create a brand new database, which we can call “test_database”. From the psql prompt, we can run the following SQL statement:

CREATE DATABASE test_database;

Once our new database is created, we need to connect to it to interact, using another postgres-specific command:

\c test_database

From this point, we have the ability to query, add, delete, and alter anything within the database – but for now, we’ll simply disconnect from the database using this postgres-specific command:

\q

After disconnecting from the database, we can easily return to our original shell like so:

exit

SQL is an amazing tool, and learning it is sure to promote better development habits. Check out this article to learn how to start defining the structure of the database we just created.