Зміст курсу

Розширений Рівень SQL

1. Групування

GROUP BY Послідовність інструкцій Загальний Час Поїздки на Кожній Лінії Метро: Завдання Челендж: Середній Бал Студента Пошук Кращих Математичних Учнів - Челендж HAVING Декілька агрегативних функцій у запиті Складніші Фільтри

2. Вкладені Запити

Вкладені запити Знайти Співробітників Нетехнічних Відділів Челендж Використання Внутрішніх Підзапитів у Розділі FROM Знаходження Співробітників з Зарплатою Вище Середньої Челендж Клауза UNION Об'єднання Результатів Таблиць

3. Об'єднання Таблиць

Приєднання 2 Таблиць Отримання Категорії за Ціною Продукту Отримати Мінімальну Ціну За Категорією Отримати Кількість Продуктів за Категорією LEFT, RIGHT and INNER JOINs Об'єднання Таблиц Челендж Використання FULL JOIN

4. DDL та DML в SQL

CREATE та обмеження Операції ALTER та INSERT DROP and TRUNCATE UPDATE and DELETE

Використання Внутрішніх Підзапитів у Розділі FROM

An inner subquery creates a table with which we then perform operations in the outer query.

From this, we can conclude that we can also use an inner query within the FROM section to select something from this table.

Let's consider an example where we need to work with employees whose salary is above a certain value. But instead of a WHERE clause, we will use an inner query:


              123456
            
SELECT *
FROM (
  SELECT * 
  From employees
  WHERE salary >= 70000
) AS high_salary_employees

We've created a new table using an inner subquery with which we can work and gather information.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Завдання

Swipe to start coding

Your task is to retrieve the average budget (budget column) for all managerial departments from the department table. Use an inner subquery in the FROM section to filter only the managerial type of department. Also, use the alias manager_departments for this table so the task is checked correctly.

The response should have only one column, average_budget. This is an alias for the aggregate function AVG().

Note

This task could also be done using a WHERE clause, but we use a nested query here to practice this syntax, which will be useful for solving more complex problems in the future.

Brief Instructions

Retrieve the average value of the budget column.
Assign the alias average_budget to that column.
In the FROM clause, you need to write a nested query.
In the nested query, select all columns from the department table.
In the nested query, use the WHERE clause with the condition type = 'manager'.
Assign the alias manager_departments to the nested query.

Рішення

Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 2. Розділ 3

Використання Внутрішніх Підзапитів у Розділі FROM

An inner subquery creates a table with which we then perform operations in the outer query.

From this, we can conclude that we can also use an inner query within the FROM section to select something from this table.

Let's consider an example where we need to work with employees whose salary is above a certain value. But instead of a WHERE clause, we will use an inner query:


              123456
            
SELECT *
FROM (
  SELECT * 
  From employees
  WHERE salary >= 70000
) AS high_salary_employees

We've created a new table using an inner subquery with which we can work and gather information.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Завдання

Swipe to start coding

The response should have only one column, average_budget. This is an alias for the aggregate function AVG().

Note

This task could also be done using a WHERE clause, but we use a nested query here to practice this syntax, which will be useful for solving more complex problems in the future.

Brief Instructions

Retrieve the average value of the budget column.
Assign the alias average_budget to that column.
In the FROM clause, you need to write a nested query.
In the nested query, select all columns from the department table.
In the nested query, use the WHERE clause with the condition type = 'manager'.
Assign the alias manager_departments to the nested query.

Рішення

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 2. Розділ 3