Intermediate SQL - Grouping, Nested Subqueries,
Joining Tables, DDL and DML in SQL
group and aggregate data aggregate
We have a task to find out the number of
Grouping Data
employees in each department. To do this, we will
group the data by the department column and use
aggregation with the COUNT(*) function.
, means aggregate functions like MAX, MIN, COUNT, etc.
This syntax exists to find certain values using
aggregate functions in specific columns.
Let's consider another example: we've been tasked
with finding the department with the highest
average salary.
To retrieve such data, we need to group the data
by the department column and then use the AVG()
function to calculate the average salary:
SELECT department, AVG(salary) as
average_salary FROM employees
GROUP BY department
the syntax for grouping data
SELECT column1,
AGG_FUNC(column2)
FROM table
GROUP BY column1
AGG_FUNC
Note
AGG_FUNC means aggregate functions like MAX, MIN,
COUNT, etc.
This syntax exists to find certain values using
aggregate functions in specific columns.
, Let's consider another example: we've been tasked
with finding the department with the highest
average salary.
To retrieve such data, we need to group the data
by the department column and then use the AVG()
function to calculate the average salary:
SELECT department, AVG(salary) as
average_salary FROM employees
GROUP BY department
This table will contain
information about the station
Montreal Metro system line(line_name), its
database, which contains the name(station_name), and the
metro_travel_time table. amount of time it takes for a
train to travel from one
station to the next
one(time_to_next_station).
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:
Alias SELECT column AS alias
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.
Joining Tables, DDL and DML in SQL
group and aggregate data aggregate
We have a task to find out the number of
Grouping Data
employees in each department. To do this, we will
group the data by the department column and use
aggregation with the COUNT(*) function.
, means aggregate functions like MAX, MIN, COUNT, etc.
This syntax exists to find certain values using
aggregate functions in specific columns.
Let's consider another example: we've been tasked
with finding the department with the highest
average salary.
To retrieve such data, we need to group the data
by the department column and then use the AVG()
function to calculate the average salary:
SELECT department, AVG(salary) as
average_salary FROM employees
GROUP BY department
the syntax for grouping data
SELECT column1,
AGG_FUNC(column2)
FROM table
GROUP BY column1
AGG_FUNC
Note
AGG_FUNC means aggregate functions like MAX, MIN,
COUNT, etc.
This syntax exists to find certain values using
aggregate functions in specific columns.
, Let's consider another example: we've been tasked
with finding the department with the highest
average salary.
To retrieve such data, we need to group the data
by the department column and then use the AVG()
function to calculate the average salary:
SELECT department, AVG(salary) as
average_salary FROM employees
GROUP BY department
This table will contain
information about the station
Montreal Metro system line(line_name), its
database, which contains the name(station_name), and the
metro_travel_time table. amount of time it takes for a
train to travel from one
station to the next
one(time_to_next_station).
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:
Alias SELECT column AS alias
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.
