kazminaviktoriya1
Пригласите друга
В профиль
Выход
![](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="42" height="42"><rect fill-opacity="0"/></svg>)
Добавить аккаунт
Чат поддержки
Практикум
Привет! Это поддержка Практикума 👋 Если что-то не получается — постараемся помочь. Пишите в чат или выберите другой способ связипт, 2 декабряв 17:21
Мы уже начали заниматься вашим вопросом, ответим в ближайшее время
Практикум
Здравствуйте!
Поддержка не сможет помочь с вопросами, связанными с теорией или задачам из курса «Системный аналитик» — в этой ситуации рекомендуем вам обратиться к вашему преподавателю в чате в Slack.
Если же у вас возникнут иные вопросы — обязательно пишите, будем рады помочь :)
17:24
Выбрать способ связи
Обзор тренажера
Операторы LEFT OUTER JOIN и RIGHT OUTER JOIN
С операторами
LEFT OUTER JOIN и RIGHT OUTER JOIN вы уже знакомы. Напомним: оператор LEFT OUTER JOIN предполагает, что в результат слияния обязательно войдут все записи из левой таблицы. Записи из правой таблицы сохранятся только в том случае, если значения в поле, по которому происходит объединение, совпадают со значениями в левой таблице. Оператор RIGHT OUTER JOIN устроен аналогичным образом, но бóльшим приоритетом обладает правая таблица. Сразу перейдём к практике. Что нужно сделать:
Объединить данные таблиц
artist и album и вывести всех исполнителей, добавив информацию о музыкальных альбомах, которые они выпустили.Таблица
artist хранит информацию об исполнителях и содержит поле artist_id с идентификатором и поле name с именем исполнителя. Таблица album содержит данные о музыкальных альбомах и включает в себя поля album_id и title с идентификатором альбома и его названием соответственно, а также поле artist_id с идентификатором исполнителя.Выбираем тип объединения
Таблицы
artist и album связаны по ключу artist_id, и связь между ними — «один ко многим», ведь одному исполнителю может соответствовать несколько музыкальных альбомов.Раз нужно вывести данные обо всех исполнителях, оператор
INNER JOIN не подойдёт: в таком случае исполнители без альбома в итоговую таблицу не попадут. Если использовать оператор LEFT OUTER JOIN, все исполнители попадут в выдачу. Соединяем таблицы
Чтобы объединить таблицы, нужно указать подходящий оператор:
LEFT OUTER JOIN или сокращённую версию LEFT JOIN. SQL
SELECT art.name,
alb.title
FROM artist AS art
LEFT OUTER JOIN album AS alb ON art.artist_id = alb.artist_id
LIMIT 10; Таблицы
artist и album объединены по ключу artist_id, и благодаря оператору LEFT OUTER JOIN все значения из поля name таблицы artist попадут в итоговую таблицу. Если для какого-то исполнителя не найдётся альбома, на этом месте в поле title появится значение NULL.| name | title |
|---|---|
| AC/DC | For Those About To Rock We Salute You |
| AC/DC | Let There Be Rock |
| Accept | Balls to the Wall |
| Accept | Restless and Wild |
| Aerosmith | Big Ones |
| Alanis Morisette | Jagged Little Pill |
| Alice in Chains | Facelift |
| Antônio Carlos Jobim | Warner 25 Anos |
| Antônio Carlos Jobim | Chill: Brazil (Disc 2) |
| Apocalyptica | Plays Metallica By Four Cellos |
Получить такую же таблицу можно и другим запросом.
SQL
SELECT art.name,
alb.title
FROM album AS alb
RIGHT OUTER JOIN artist AS art ON art.artist_id = alb.artist_id
LIMIT 10; | name | title |
|---|---|
| AC/DC | For Those About To Rock We Salute You |
| AC/DC | Let There Be Rock |
| Accept | Balls to the Wall |
| Accept | Restless and Wild |
| Aerosmith | Big Ones |
| Alanis Morisette | Jagged Little Pill |
| Alice in Chains | Facelift |
| Antônio Carlos Jobim | Warner 25 Anos |
| Antônio Carlos Jobim | Chill: Brazil (Disc 2) |
| Apocalyptica | Plays Metallica By Four Cellos |
Результат не поменялся. Это связано с тем, что
LEFT OUTER JOIN и RIGHT OUTER JOIN в большинстве случаев взаимозаменяемы. Достаточно просто поменять местами таблицы в запросе. В некоторых СУБД оператор RIGHT OUTER JOIN совсем не используют, потому что получить нужный результат можно, меняя таблицы местами.Агрегируем и сортируем
Получившуюся таблицу можно обновить, посчитав, сколько приходится альбомов на одного исполнителя. Поле с количеством альбомов лучше переименовать.
SQL
SELECT art.name,
COUNT(alb.title) AS total_album
FROM artist AS art
LEFT OUTER JOIN album AS alb ON art.artist_id = alb.artist_id
GROUP BY art.name
ORDER BY total_album DESC
LIMIT 10; | name | total_album |
|---|---|
| Iron Maiden | 21 |
| Led Zeppelin | 14 |
| Deep Purple | 11 |
| Metallica | 10 |
| U2 | 10 |
| Ozzy Osbourne | 6 |
| Pearl Jam | 5 |
| Faith No More | 4 |
| Foo Fighters | 4 |
| Various Artists | 4 |
Так выглядит топ-10 исполнителей по количеству альбомов. Неудивительно, что Iron Maiden на первом месте, — они играют ещё с начала восьмидесятых!
Схема базы данных
1.
Выведите названия всех треков, добавив информацию о датах, в которые эти треки покупали. Ни один трек не должен потеряться, даже если его не покупали вообще. Чтобы получить нужный результат, надо соединить три таблицы сразу, ведь таблица
invoice, которая хранит данные о дате заказа, не содержит информации о купленных треках.Сначала соедините таблицы
track и invoice_line по ключу track_id, а затем присоедините таблицу invoice по ключу invoice_id. В итоговую таблицу поместите два поля: name из таблицы track и invoice_date из таблицы invoice. Приведите дату к нужному формату.Подсказка
Помните, что в итоговую таблицу должны войти все треки, даже если они не продавались. Выберите один из операторов:
LEFT OUTER JOIN или RIGHT OUTER JOIN.2.
Посчитайте для каждого года число уникальных названий купленных треков.
Подсказка
Подумайте, как присоединить таблицы на этот раз, чтобы отобрать только купленные треки. Сгруппируйте данные по году и найдите для каждого года количество треков. Не забудьте использовать ключевое слово
DISTINCT, чтобы отобрать уникальные значения.3.
Выгрузите таблицу из двух полей: первое поле с фамилией сотрудника, второе — с количеством пользователей, чьи запросы этот сотрудник обработал. Назовите поля
employee_last_name и all_customers соответственно. Сгруппируйте записи по идентификатору сотрудника. Отсортируйте количество пользователей по убыванию, а фамилии сотрудников — в лексикографическом порядке.Подсказка
К таблице
staff присоедините таблицу client с помощью оператора LEFT OUTER JOIN.4.
У некоторых сотрудников есть менеджеры — их идентификаторы указаны в поле
reports_to. Посмотрите внимательно на схему базы: таблица staff отсылает сама к себе. Это нормально, можно не создавать новую таблицу с менеджерами. Теперь можно разобраться в иерархии команды. Отобразите таблицу с двумя полями: в первое поле войдут фамилии всех сотрудников, а во второе — фамилии их менеджеров. Назовите поля
employee_last_name и manager_last_name.Подсказка
Таблицу можно присоединять саму к себе. Таблицу
staff присоедините по такому условию: значение reports_to должно быть равно employee_id.5.
Отобразите названия фильмов, в которых снимались актёры, не указанные в базе.
Подсказка
Присоединяя к
movie другие таблицы, используйте LEFT OUTER JOIN — так ни один фильм не пропадёт из итоговой таблицы.6.
Отобразите на экране имена исполнителей, для которых в базе данных не нашлось ни одного музыкального альбома.
Подсказка
Присоедините таблицу
album к таблице artist оператором LEFT OUTER JOIN. Артисты без альбомов останутся в итоговой таблице. Сгруппировав данные по полю из таблицы artist, можно найти количество альбомов, выпущенных исполнителем. Вам понадобятся те записи, в которых это число равно нулю.Код
xxxxxxxxxx1
SELECT a.name2
FROM artist AS a3
LEFT OUTER JOIN album AS al ON a.artist_id = al.artist_id4
WHERE al.album_id IS NULL;Результат
| name |
|---|
| Peter Tosh |
| Milton Nascimento & Bebeto |
| Azymuth |
| Youssou N'Dour |
| Nação Zumbi |
| Fernanda Porto |
| Regina Spektor |
| Santana Feat. Eagle-Eye Cherry |
| Christina Aguilera featuring BigElf |
| Luiz Melodia |
| Academy of St. Martin in the Fields, Sir Neville Marriner & William Bennett |
| Baby Consuelo |
| Nando Reis |
| Seu Jorge |
| Big & Rich |
| Santana Feat. The Project G&B |
| Corinne Bailey Rae |
| Vinícius De Moraes & Baden Powell |
| Pedro Luís E A Parede |
| Jack's Mannequin & Mick Fleetwood |
| Hermeto Pascoal |
| Vinícius E Qurteto Em Cy |
| Nega Gizza |
| Os Cariocas |
| R.E.M. Feat. KRS-One |
| Otto |
| Jackson Browne |
| Xis |
| Motörhead & Girlschool |
| Santana Feat. Maná |
| Ney Matogrosso |
| Dhani Harrison & Jakob Dylan |
| Snow Patrol |
| Rodox |
| Jack Johnson |
| Banda Black Rio |
| Stereo Maracana |
| Simply Red |
| Jaguares |
| Barão Vermelho |
| João Gilberto |
| Los Lonely Boys |
| Jorge Vercilo |
| Santana Feat. Rob Thomas |
| Charlie Brown Jr. |
| Los Hermanos |
| Sabotage E Instituto |
| Santana Feat. Eric Clapton |
| DJ Dolores & Orchestra Santa Massa |
| Vinícius E Odette Lara |
| Bebel Gilberto |
| Ben Harper |
| Instituto |
| The Postal Service |
| Kid Abelha |
| Edson, DJ Marky & DJ Patife Featuring Fernanda Porto |
| Sandra De Sá |
| Santana Feat. Dave Matthews |
| Vinicius, Toquinho & Quarteto Em Cy |
| A Cor Do Som |
| Avril Lavigne |
| Santana Feat. Everlast |
| The Flaming Lips |
| Black Eyed Peas |
| Aerosmith & Sierra Leone's Refugee Allstars |
| Whitesnake |
| Pedro Luís & A Parede |
| Matisyahu |
| Mundo Livre S/A |
| Santana Feat. Lauryn Hill & Cee-Lo |
| Gustavo & Andres Veiga & Salazar |