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 BY ClauseOrder of StatementsChallenge: Total Ride Time for Each Subway LineAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVING ClauseSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesChallenge: Non-tech Department EmployeesUsing Inner Subqueries in the FROM SectionChallenge: Employees With More Than Average SalaryUNION ClauseChallenge: Combining Table Results
3. Joining Tables
JOIN 2 TablesChallenge: Get Category By Product PriceChallenge: Get Minimum Price By CategoryChallenge: Get Product Amount By CategoryLEFT, RIGHT and INNER JOINsChallenge: Table JoinChallenge: FULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

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

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

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

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:

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

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