Conteúdo do Curso

SQL Intermediário

GROUP BY

Welcome to the Intermediate SQL course!

In the first section, we're diving into how we can group and aggregate data within our tables.

Let's understand what "grouping data" means using a simple example of an employees table:

We have a task to find out the number of employees in each department. To do this, we will group the data by the department column and use aggregation with the COUNT(*) function.

Here's what the implementation will look like:


              123
            
SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department

So, as you can see, the syntax for grouping data looks like this:

Então, como você pode ver, a sintaxe para agrupar dados é assim:

Nota

AGG_FUNC significa funções de agregação como MAX, MIN, COUNT, etc.


              123
            
SELECT department, AVG(salary) as average_salary
FROM employees
GROUP BY department

In this section, we will work with the Montreal Metro system database, which contains the metro_travel_time table.

This table will contain information about the station line(line_name), its name(station_name), and the amount of time it takes for a train to travel from one station to the next one(time_to_next_station).

Here is what this table looks like and the data preview in it:

Neste curso, trabalharemos com o banco de dados do sistema de metrô de Montreal, que contém a tabela metro_travel_time.

Essa tabela conterá informações sobre a linha da estação(line_name), seu nome(station_name), e a quantidade de tempo que um trem leva para viajar de uma estação para a próxima(time_to_next_station).

Aqui está a aparência desta tabela e a prévia dos dados que ela contém:

In the assignments, you’ll often use a concept called an alias. An alias is essentially a "nickname" for a column you retrieve with a SELECT statement. It’s specified using the following syntax:

An alias only affects how the column appears in the response.

For example, instead of MAX(time), the column could be called max_time if you assign that alias. This makes the output more readable and clear.

Tarefa

Swipe to begin your solution

Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX() function and alias it as max_time, grouping the data by the line_name column.

Brief Instructions

Retrieve the line_name column and the maximum value of the time_to_next_station column.
Add the alias max_time for the maximum value.
Group the data by the line_name column.

_{Once you've completed this task, click the button below the code to check your solution.}

Solução

Mude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 1. Capítulo 1

GROUP BY

Welcome to the Intermediate SQL course!

In the first section, we're diving into how we can group and aggregate data within our tables.

Let's understand what "grouping data" means using a simple example of an employees table:

We have a task to find out the number of employees in each department. To do this, we will group the data by the department column and use aggregation with the COUNT(*) function.

Here's what the implementation will look like:


              123
            
SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department

So, as you can see, the syntax for grouping data looks like this:

Então, como você pode ver, a sintaxe para agrupar dados é assim:

Nota

AGG_FUNC significa funções de agregação como MAX, MIN, COUNT, etc.


              123
            
SELECT department, AVG(salary) as average_salary
FROM employees
GROUP BY department

In this section, we will work with the Montreal Metro system database, which contains the metro_travel_time table.

Here is what this table looks like and the data preview in it:

Neste curso, trabalharemos com o banco de dados do sistema de metrô de Montreal, que contém a tabela metro_travel_time.

Aqui está a aparência desta tabela e a prévia dos dados que ela contém:

An alias only affects how the column appears in the response.

For example, instead of MAX(time), the column could be called max_time if you assign that alias. This makes the output more readable and clear.

Tarefa

Swipe to begin your solution

Brief Instructions

Retrieve the line_name column and the maximum value of the time_to_next_station column.
Add the alias max_time for the maximum value.
Group the data by the line_name column.

_{Once you've completed this task, click the button below the code to check your solution.}

Solução

Mude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 1. Capítulo 1

Mude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo