Course Content

Intermediate SQL

## Intermediate SQL

# Order of Statements

For statistical analysis, we were tasked with **counting the number of stations** on each line and **arranging them in increasing order** of station count for each metro line.

To do this, we need to **find the number of stations** on each of the metro lines and then sort them from the least number of stations to the most.

This way, the construction company will understand **which metro lines they need to prioritize** for adding stations.

It's important for us to understand the **order of writing clauses**, specifically where the `GROUP BY`

clause should be placed.

So, the order looks like this:

`SELECT`

statement;`FROM table`

;`WHERE`

clause;`GROUP BY`

clause;`ORDER BY`

clause;`LIMIT`

clause.

From this order, it's clear that the `GROUP BY`

statement **must be written AFTER** the `WHERE`

statement (or after the `FROM`

table if there is no filtering in your query using `SELECT`

) and also **BEFORE** the `ORDER BY`

statement.

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:

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

Note:

It's worth noting that the

`LIMIT`

clause isalways written last. This way, you can easily remember its placement in the query.

Now, let's move on to the task!

Here is the preview of a `metro_travel_time`

table we are working with:

Task

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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.

Task

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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.

Everything was clear?

# Order of Statements

For statistical analysis, we were tasked with **counting the number of stations** on each line and **arranging them in increasing order** of station count for each metro line.

To do this, we need to **find the number of stations** on each of the metro lines and then sort them from the least number of stations to the most.

This way, the construction company will understand **which metro lines they need to prioritize** for adding stations.

It's important for us to understand the **order of writing clauses**, specifically where the `GROUP BY`

clause should be placed.

So, the order looks like this:

`SELECT`

statement;`FROM table`

;`WHERE`

clause;`GROUP BY`

clause;`ORDER BY`

clause;`LIMIT`

clause.

From this order, it's clear that the `GROUP BY`

statement **must be written AFTER** the `WHERE`

statement (or after the `FROM`

table if there is no filtering in your query using `SELECT`

) and also **BEFORE** the `ORDER BY`

statement.

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:

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

Note:

It's worth noting that the

`LIMIT`

clause isalways written last. This way, you can easily remember its placement in the query.

Now, let's move on to the task!

Here is the preview of a `metro_travel_time`

table we are working with:

Task

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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.

Task

`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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.

Everything was clear?

# Order of Statements

For statistical analysis, we were tasked with **counting the number of stations** on each line and **arranging them in increasing order** of station count for each metro line.

To do this, we need to **find the number of stations** on each of the metro lines and then sort them from the least number of stations to the most.

This way, the construction company will understand **which metro lines they need to prioritize** for adding stations.

It's important for us to understand the **order of writing clauses**, specifically where the `GROUP BY`

clause should be placed.

So, the order looks like this:

`SELECT`

statement;`FROM table`

;`WHERE`

clause;`GROUP BY`

clause;`ORDER BY`

clause;`LIMIT`

clause.

From this order, it's clear that the `GROUP BY`

statement **must be written AFTER** the `WHERE`

statement (or after the `FROM`

table if there is no filtering in your query using `SELECT`

) and also **BEFORE** the `ORDER BY`

statement.

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:

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

Note:

It's worth noting that the

`LIMIT`

clause isalways written last. This way, you can easily remember its placement in the query.

Now, let's move on to the task!

Here is the preview of a `metro_travel_time`

table we are working with:

Task

`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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.

Task

`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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.

Everything was clear?

**counting the number of stations** on each line and **arranging them in increasing order** of station count for each metro line.

**find the number of stations** on each of the metro lines and then sort them from the least number of stations to the most.

**which metro lines they need to prioritize** for adding stations.

**order of writing clauses**, specifically where the `GROUP BY`

clause should be placed.

So, the order looks like this:

`SELECT`

statement;`FROM table`

;`WHERE`

clause;`GROUP BY`

clause;`ORDER BY`

clause;`LIMIT`

clause.

`GROUP BY`

statement **must be written AFTER** the `WHERE`

statement (or after the `FROM`

table if there is no filtering in your query using `SELECT`

) and also **BEFORE** the `ORDER BY`

statement.

**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:

Note:

`LIMIT`

clause isalways written last. This way, you can easily remember its placement in the query.

Now, let's move on to the task!

Here is the preview of a `metro_travel_time`

table we are working with:

Task

`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, you need to sort the result from smallest to largest.

Note

`COUNT(column)`

is the function that counts the number of rows.