Intermediate guide to SQL

Let’s go back to the same SQL table we worked on within the beginner's guide to SQL. The table can be downloaded here, and we will call it soccer_team . You can copy-paste the table, save it as a .csv file, and then import it to a database in the SQL application. Let’s see what the table looks like. From the beginner’s guide to SQL, you know how to show all available columns from the table, but only show the first 10 rows.

SELECT *
FROM soccer_team
LIMIT 10

Description of soccer_team table:

• team_id — ID for each team
• team_group — the name of the group in which the team belongs
• match_played — how many matches a team played in group stage
• won — how many matches a team won
• draw — how many matches a team draws
• lost — how many matches a team lost
• goal_for — how many goals a team conceded
• goal_agnst — how many goals a team scored
• goal_diff — the difference of goals scored and goals conceded
• points — how many points a team achieved from their group stage matches
• group_position — in which position a team finished their group stage matches

In this article, let’s try to add more operations to the SQL queries.

First, let’s talk about the order of operations. Here’s the list you need to refer to when writing the SQL query:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

This order needs to be followed precisely. Otherwise, the query will not be executed. You are already familiar with some of the operations.

• SELECT asks to show the table’s columns.
• FROM specifies which table you want to see.
• WHERE is similar to using Filer in Excel. It can be used to look for certain values/text, specifying the boundaries of a range, etc.
• LIMIT asks to only show the first X amount of rows.

1. Let’s start with the query that asks to show only certain columns (team_id, team_group, won, draw, lost) for teams that won 1 or more matches or draw 2 or more matches.

SELECT team_id, team_group, won, draw, lost
FROM soccer_team
WHERE won > 0 OR draw > 1

2. What if we didn’t want to see this information by individual teams and only wanted to see it by team groups? We can use a GROUP BY operator.

• GROUP BY summarizes information by requested column(s). You can find additional information about it here.

SELECT team_group, won, draw, lost
FROM soccer_team
WHERE won > 0 OR draw > 1
GROUP BY team_group

However, if you look closer, the last table doesn’t quite look right. The values in columns won, draw, lost should be higher. Also, if you compare the first row in tables #1 and #2, it is identical.

3. Here is the way to fix this issue, use SUM() operator.

SELECT team_group, SUM(won), SUM(draw), SUM(lost)
FROM soccer_team
WHERE won > 0 OR draw > 1
GROUP BY team_group

GROUP BY is often used with operators like SUM(), COUNT(), etc.

4. Now let’s say that we only want to see team groups with 4 or more winners, meaning SUM(won) is greater than 3. If we add this condition to WHERE statement to get WHERE won > 0 OR draw > 1 AND SUM(won) > 3, we will get the following error:

This happens, because WHEREcannot be applied to aggregate operators. Aggregate operators have to be used with HAVING.

SELECT team_group, SUM(won), SUM(draw), SUM(lost)
FROM soccer_team
WHERE won > 0 OR draw > 1
GROUP BY team_group
HAVING SUM(won) > 3

5. If we want to sort the results by how many matches were won in a group, we should apply ORDER BY. To sort from largest to smallest, use DESC. To sort from smallest to largest, use ASC.

SELECT team_group, SUM(won), SUM(draw), SUM(lost)
FROM soccer_team
WHERE won > 0 OR draw > 1
GROUP BY team_group
HAVING SUM(won) > 3
ORDER BY SUM(won) DESC

6. Let’s add the remaining operator LIMIT.

SELECT team_group, SUM(won), SUM(draw), SUM(lost)
FROM soccer_team
WHERE won > 0 OR draw > 1
GROUP BY team_group
HAVING SUM(won) > 3
ORDER BY SUM(won) DESC
LIMIT 3

These are the main operators used in SQL and will be enough to write most of the queries.