Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Total Ride Time for Each Subway Line Challenge | 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

bookTotal Ride Time for Each Subway Line Challenge

The construction company has decided to increase the number of stations on the Yellow metro line.

Our next task is to find the turnaround time for each line. It's important for the company to ensure that they can close the Yellow line for maintenance and expansion by adding new metro stations, so it's crucial for them not to inconvenience passengers too much.

Therefore, we need to find the total turnaround time for the train by summing the time to each station (using the SUM() function).

Note:

If we simply calculate the sum of the time to each station, it will be the train's time from one end station to the other. However, it's also important for us to know the total turnaround time for the train along the metro line. To achieve this, we need to multiply the sum by 2.

To understand how to do this task, let's consider an example with an employees table.

Suppose we need to find the department with the highest average monthly salary.

For this, we can use the following query:


              
1234

            
SELECT department, AVG(salary) / 12 AS average_monthly_salary
FROM employees
GROUP BY department
ORDER BY average_monthly_salary DESC
copy

Thus, we obtain the necessary data as a result.

Let's return to the task because the workers await our verdict on the expansion of the metro station.

Here is the preview of a metro_travel_time table we are working with:

Task

Calculate the turnaround time for each of the metro lines. To do this, use SUM(), group the data, and order in ascending order. The company already trusts you, as you have helped them a lot before.

Note:

Name the new column turnaround_time so that your solution can be successfully tested. Also, don't forget to multiply the sum of time by 2.

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 3
toggle bottom row

bookTotal Ride Time for Each Subway Line Challenge

The construction company has decided to increase the number of stations on the Yellow metro line.

Our next task is to find the turnaround time for each line. It's important for the company to ensure that they can close the Yellow line for maintenance and expansion by adding new metro stations, so it's crucial for them not to inconvenience passengers too much.

Therefore, we need to find the total turnaround time for the train by summing the time to each station (using the SUM() function).

Note:

If we simply calculate the sum of the time to each station, it will be the train's time from one end station to the other. However, it's also important for us to know the total turnaround time for the train along the metro line. To achieve this, we need to multiply the sum by 2.

To understand how to do this task, let's consider an example with an employees table.

Suppose we need to find the department with the highest average monthly salary.

For this, we can use the following query:


              
1234

            
SELECT department, AVG(salary) / 12 AS average_monthly_salary
FROM employees
GROUP BY department
ORDER BY average_monthly_salary DESC
copy

Thus, we obtain the necessary data as a result.

Let's return to the task because the workers await our verdict on the expansion of the metro station.

Here is the preview of a metro_travel_time table we are working with:

Task

Calculate the turnaround time for each of the metro lines. To do this, use SUM(), group the data, and order in ascending order. The company already trusts you, as you have helped them a lot before.

Note:

Name the new column turnaround_time so that your solution can be successfully tested. Also, don't forget to multiply the sum of time by 2.

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 3
toggle bottom row

bookTotal Ride Time for Each Subway Line Challenge

The construction company has decided to increase the number of stations on the Yellow metro line.

Our next task is to find the turnaround time for each line. It's important for the company to ensure that they can close the Yellow line for maintenance and expansion by adding new metro stations, so it's crucial for them not to inconvenience passengers too much.

Therefore, we need to find the total turnaround time for the train by summing the time to each station (using the SUM() function).

Note:

If we simply calculate the sum of the time to each station, it will be the train's time from one end station to the other. However, it's also important for us to know the total turnaround time for the train along the metro line. To achieve this, we need to multiply the sum by 2.

To understand how to do this task, let's consider an example with an employees table.

Suppose we need to find the department with the highest average monthly salary.

For this, we can use the following query:


              
1234

            
SELECT department, AVG(salary) / 12 AS average_monthly_salary
FROM employees
GROUP BY department
ORDER BY average_monthly_salary DESC
copy

Thus, we obtain the necessary data as a result.

Let's return to the task because the workers await our verdict on the expansion of the metro station.

Here is the preview of a metro_travel_time table we are working with:

Task

Calculate the turnaround time for each of the metro lines. To do this, use SUM(), group the data, and order in ascending order. The company already trusts you, as you have helped them a lot before.

Note:

Name the new column turnaround_time so that your solution can be successfully tested. Also, don't forget to multiply the sum of time by 2.

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!

The construction company has decided to increase the number of stations on the Yellow metro line.

Our next task is to find the turnaround time for each line. It's important for the company to ensure that they can close the Yellow line for maintenance and expansion by adding new metro stations, so it's crucial for them not to inconvenience passengers too much.

Therefore, we need to find the total turnaround time for the train by summing the time to each station (using the SUM() function).

Note:

If we simply calculate the sum of the time to each station, it will be the train's time from one end station to the other. However, it's also important for us to know the total turnaround time for the train along the metro line. To achieve this, we need to multiply the sum by 2.

To understand how to do this task, let's consider an example with an employees table.

Suppose we need to find the department with the highest average monthly salary.

For this, we can use the following query:


              
1234

            
SELECT department, AVG(salary) / 12 AS average_monthly_salary
FROM employees
GROUP BY department
ORDER BY average_monthly_salary DESC
copy

Thus, we obtain the necessary data as a result.

Let's return to the task because the workers await our verdict on the expansion of the metro station.

Here is the preview of a metro_travel_time table we are working with:

Task

Calculate the turnaround time for each of the metro lines. To do this, use SUM(), group the data, and order in ascending order. The company already trusts you, as you have helped them a lot before.

Note:

Name the new column turnaround_time so that your solution can be successfully tested. Also, don't forget to multiply the sum of time by 2.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Section 1. Chapter 3
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
some-alt