Learn Order of Statements

In our statistical analysis, we need to count the number of stations on each metro line and sort them in ascending order based on the number of stations.

This means we should first determine the number of stations for each metro line and then arrange them from the line with the fewest stations to the one with the most.

This information will help the construction company decide which metro lines should be prioritized for adding more stations.

For this, it's crucial to understand the sequence of SQL clauses, especially where to place the GROUP BY clause.

So, the order looks like this:

SELECT statement;
FROM table;
WHERE clause;
GROUP BY clause;
ORDER BY clause;
LIMIT clause.

Let's consider an example of such statement order using our employee table. Suppose we need to retrieve the number of employees in each department whose salary is above 70000 and sort them from smallest to largest:


              12345
            
SELECT department, COUNT(employee_id) AS number_of_employees
FROM employees
WHERE salary > 70000
GROUP BY department
ORDER BY number_of_employees

Task

Swipe to start coding

Using the metro_travel_time table, find the number of stations (create a new column, named number_of_stations using station_name and COUNT() function) for each of the lines (line_name). Next, sort the result from smallest to largest.

Note

COUNT(column) is the function that counts the number of rows.

Brief Instructions

Retrieve line_name and the count of rows in the station_name column.
Add the alias number_of_stations to the second column.
Group the data by line_name.
Sort the result by number_of_stations.

Solution

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 2

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you show me how to write a similar SQL query for the metro lines and stations?

Can you explain why the `GROUP BY` clause comes before `ORDER BY` in SQL?

Can you provide more examples of using `GROUP BY` and `ORDER BY` together?

Awesome!

Completion rate improved to 4

Swipe to show menu