4 Exercise 3: Basics of SQL

Contents – In this exercise we write basic SQL queries.

Goal – The goal of the exercise is to familiarize oneself with SQL.

4.0.1 Preparation

Open pgAdmin in the browser and log in. Open the Query Tool (Right click trainingdatabase -> Query Tool).

4.1 Exercise 3.1: Simple SQL queries

Let’s inspect the spatial data sets imported in the last exercise:

SELECT
name_en
FROM
ne.countries_world;

Queries can be filtered with the WHERE keyword:

SELECT
name_en, wkb_geometry
FROM
ne.countries_world
WHERE
continent = 'Asia';

Functions can be used in SQL commands. This way you can for example calculate averages or standard deviations. Next let’s inspect the length of different countries’ names:

SELECT
avg(char_length(name_en)), stddev(char_length(name_en))
FROM
ne.countries_world
WHERE
continent = 'Europe';

With the following SQL statement we see which continents have the longest country names by average:

SELECT
continent, avg(char_length(name_en))
FROM
ne.countries_world
GROUP BY
continent
ORDER BY
avg(char_length(name_en)) DESC;

4.2 Exercise 3.2

Which continent has the longest country names by average?

SELECT
...
FROM
...
GROUP BY
...
ORDER BY
... desc
LIMIT ...;

-- Fill in the correct columns, schema and table. Check which functions calculates
-- the average. Use the char_length() function, which returns the length of a
-- string

SELECT
continent, average_function(char_length(country))
FROM
schema.table
GROUP BY
continent
ORDER BY
average_function(char_length(country)) desc
LIMIT 1;

SELECT
continent, avg(char_length(name_en))
FROM
ne.countries_world
GROUP BY
continent
ORDER BY
avg(char_length(name_en)) desc
LIMIT 1;

4.3 Exercise 3.3

Which continent has the shortest country names by average?

SELECT
...
FROM
...
GROUP BY
...
ORDER BY
... asc
LIMIT ...;

-- Fill in the correct columns, schema and table. Check which functions calculates
-- the average. Use the char_length() function, which returns the length of a
-- string

SELECT
continent, average_function(char_length(country))
FROM
schema.table
GROUP BY
continent
ORDER BY
average_function(char_length(country)) asc
LIMIT 1;

SELECT
continent, avg(char_length(name_en))
FROM
ne.countries_world
GROUP BY
continent
ORDER BY
avg(char_length(name_en)) asc
LIMIT 1;

4.4 Exercise 3.4

Which country has the longest name in the world?

SELECT
...
FROM
...
ORDER BY
...
LIMIT

-- Fill in the correct column, schema and table. Choose the function
-- which tells you the length of a string.
SELECT
country, continent, character_length(country)
FROM
schema.table
ORDER BY
character_length(country) desc
LIMIT 1;

SELECT
name_en as "Country", continent as "Continent", char_length(name_en) as "Length"
FROM
ne.countries_world
ORDER BY
char_length(name_en) desc
LIMIT 1;

4.5 Exercise 3.5

How many English country names are 5 letters long?

WITH ... AS
(
    SELECT ...
)

SELECT
...
FROM
...;

-- use a CTE (Common Table Expression)
WITH ltr5 AS
(

    SELECT
    ...
    FROM
    ...
    WHERE
    ... AND ...
)

SELECT
count() AS ... -- use the count() function (choose a suitable column from the CTE)
               -- You may also define an alias after the AS expression.
FROM
...; -- refer to the CTE

WITH ltr5 AS
(
    SELECT
    name_en as "Country", continent as "Continent", char_length(name_en) as "Length"
    FROM
    ne.countries_world
    WHERE
    char_length(name_en) = 5
)

SELECT
count("Country") AS "5 letter long country names"
FROM
ltr5;

4.6 Exercise 3.6

Which countries’ English and Spanish names are different, but of equal length? Order the results in a descending order based on the length of the english name.

SELECT
...
FROM
...
WHERE
...
ORDER BY
... DESC;

-- Fill in the columns
SELECT
country_name_en, country_name_es, char_length(country_name_en) AS "Length"
FROM
ne.countries_world
WHERE
country_name_en ... country_name_es AND char_length(country_name_en) ... char_length(country_name_es)
-- fill in the blanks '...' with the correct operators
-- if needed, check the documentation (link below)
ORDER BY
char_length(country_name_en) DESC;

Operators in the PostgreSQL documentation.

SELECT
name_en, name_es, char_length(name_en) AS "Length"
FROM
ne.countries_world
WHERE
name_en <> name_es AND char_length(name_en) = char_length(name_es)
ORDER BY
char_length(name_en) DESC;