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 is always 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.
Thanks for your feedback!
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 is always 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.
Thanks for your feedback!
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 is always 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.
Thanks for your feedback!
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 is always 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.