Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Order of Statements | Grouping
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results
3. Joining Tables
JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

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

  1. SELECT statement;
  2. FROM table;
  3. WHERE clause;
  4. GROUP BY clause;
  5. ORDER BY clause;
  6. 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:


              
12345

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

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
test

Swipe to show code editor

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.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 1. Chapter 2
toggle bottom row

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

  1. SELECT statement;
  2. FROM table;
  3. WHERE clause;
  4. GROUP BY clause;
  5. ORDER BY clause;
  6. 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:


              
12345

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

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
test

Swipe to show code editor

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.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 1. Chapter 2
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt