SQL Cheatsheet for Beginners (PostgreSQL)

SQL (Structured Query Language) is the language used to add to, access, and modify data in relational databases. It is the most common database query language and an in-demand skill for many tech roles. SQL has different flavors, all having some slight variations in syntax but generally very alike. Some examples of these flavors include PostgreSQL, MySQL, Oracle, SQL Server, etc.

This article is a beginner-friendly cheat sheet to get you started on the basics of SQL. Also, if you have ever felt overwhelmed with remembering the commands and syntax for getting started in writing queries, or you just need a refresher to refer to often for the basic commands, then this article is for you. 

For this article, I will be using PostgreSQL, so some of the syntaxes might differ slightly if you use a different flavor. In this article, I will work through the following:

• Where to execute SQL queries
• How to select fields from a table
• Creating new fields from existing fields
• Best PostgreSQL query style practices

Where to execute SQL queries

To begin, where can you execute these SQL queries? PostgreSQL is a database server that you can connect to using different clients such as pgAdmin 4, DBeaver, VSCode with an SQL extension installed, BI tools like Metabase, online SQL editors, JetBrains IDEs, and even your local terminal shell, etc. Once you connect the client to your database, you can write and execute queries via the client. For most clients, you need to install PostgreSQL on your local system to connect to your database. 

How to select fields from a table

Assuming that you have PostgreSQL installed and can connect to your database, let’s go ahead to write basic queries. I will be using ExtendsClass online editor as my Postgres client for this article. 

First, I create the table I intend to query: a table of students with the fields: id, first_name, last_name, country, and age. 

CREATE table students (
    id SERIAL  PRIMARY KEY, 
    first_name varchar(100), 
    last_name varchar(100), 
    country varchar(100), 
    age integer
);

INSERT INTO students (first_name, last_name, country, age) 
VALUES ('Ann', 'James', 'USA', 18);

INSERT INTO students (first_name, last_name, country, age) 
VALUES ('John', 'Obi', 'Nigeria', 25);

INSERT INTO students (first_name, last_name, country, age) 
VALUES ('Musa', 'Aliyu', 'Nigeria', 40);

INSERT INTO students (first_name, last_name, country, age) 
VALUES ('Ben', 'Moses', 'Ghana', 33);

INSERT INTO students (first_name, last_name, country, age) 
VALUES ('Charles', 'Robert', 'USA', 27);

SELECT id,
    first_name, 
    last_name, 
    country,
    age 
FROM 
    students;

After creating my table as you can see above, we can go ahead to query the data. 

Here is a cheat sheet of some of the basic PostgreSQL queries.

(S/N)	Keyword	Description	Sample query1	Sample query2
1.	SELECT	To specify columns to be returned from the query. * means all fields in the table should be selected.	SELECT id, first_name, last_name	SELECT *
2.	FROM	To specify the table from the database that you intend to query.	SELECT id, first_name,  last_name,  country, age FROM students;	SELECT first_name, last_name FROM students;
3.	GROUP BY	GROUP BY returns aggregates grouped by the field(s) passed with the GROUP BY keyword.	SELECT country, COUNT(id) AS number_of_students FROM students GROUP BY country;	SELECT country, age, COUNT(id) AS number_of_students FROM students GROUP BY country, age;
4.	ORDER BY	This specified the sorting order of the results. It could either be in ascending (ASC) or descending (DESC) order. Ascending order goes from the lowest or smallest value to the highest.	SELECT id, first_name,  last_name,  country, age FROM students ORDER BY id ASC;	SELECT id, first_name,  last_name,  country, age FROM students ORDER BY age DESC;
5.	LIMIT	LIMIT is an integer value that determines how many rows are returned from the query.	SELECT id, first_name,  last_name,  country, age FROM students LIMIT 3;	SELECT id, first_name,  last_name,  country, age FROM students ORDER BY age ASC LIMIT 3;
6.	OFFSET	This tells the query what row number to begin returning the results from	SELECT id, first_name,  last_name,  country, age FROM students ORDER BY age ASC OFFSET 2;	SELECT id, first_name,  last_name,  country, age FROM students ORDER BY id ASC OFFSET 1;

Creating new fields from existing fields

Sometimes, you might need to create new fields derived from already existing fields in the dataset.

• You can do this using the CASE statement, which works just like an IF…ELSE IF or Switch case statement in other programming languages.
  In the CASE statement, WHEN specifies the condition to be fulfilled for creating the new field. THEN sets the values for the field when that condition is fulfilled. END signifies the end of the CASE statement and is usually followed by the alias for the derived field.

SELECT id,
    first_name, 
    last_name, 
    country,
    age,
    CASE
        WHEN age < 20 THEN '<20'
        WHEN age >= 20 
            and age < 30 THEN '<30'
        WHEN age >= 30 
            and age < 40 THEN '<40'
        WHEN age >= 40 
            and age < 50 THEN '<50'
    END age_range
FROM 
    students;

• You can also create a new field by concatenating two fields. The resulting field is of string datatype.

SELECT id,
    first_name, 
    last_name, 
    country,
    age,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM 
    students;

Best PostgreSQL query style practices

To wrap up the article, I will be sharing six best practices and tips I have picked up over the years for writing PostgreSQL queries.

1. Write keywords in caps: It is advisable to write keywords in upper case. Typically field names are in lower case, so this helps in improving readability and avoiding confusion.
2. Use whitespaces, new lines, and tabs: Each new keyword should be on a new line and multiple attributes for each keyword should be on a new line and an additional indent.
3. Avoid using SELECT *: Although this is used to select all fields, it is better to list out all fields in the SELECT for clarity purposes. Listing out all columns could also improve query performance due to caching, especially if you will re-run the query multiple times within a short timeframe.
4. Use aliases: For long field names, derived fields, or fields after joins, it is advisable to use aliases that are short, concise, and self-explanatory.
5. Singe quotes(‘ ’): In PostgreSQL, strings and dates have to be in single quotes, not double quotes.
6. End of query: Because you can write multiple queries or subqueries together, it is best practice to add a semicolon(;) at the end of a query. 

Conclusion

In this article, I covered the basic commands in PostgreSQL, how to derive new fields, and some best style practices for writing PostgreSQL queries. This article will be the first of a series of articles on SQL and Postgres. In subsequent articles, I will cover some more query keywords, joining tables, conditional operations, handling dates in PostgreSQL, subqueries, etc. 

I would also appreciate any questions or comments you might have as well, as this could help shape subsequent articles so that it is of immense value to you. Please drop a comment below or shoot me an email at: contactaniekan at gmail dot com. 

Thank you for reading.

Aniekan.