SQL ACADEMY ответы и решения заданий (часть 1, задания 1-22)
SQL Academy (ответы и решения заданий 1-22)
Ниже представлены наши варианты решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Ответы на 66 заданий представленные в онлайн тренажере разбиты на 3 части. Здесь, в первой части, представлены ответы на первые 22 задания.
Ответы на задания 23-44 (часть 2) здесь.
Ответы на задания 45-66 (часть 3) будут тут.
Ответы на задания 67-76 (часть 4) тут.
Задание 1. Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний.
Задание 2. Вывести названия всеx авиакомпаний.
Задание 3. Вывести все рейсы, совершенные из Москвы
Задание 4. Вывести имена людей, которые заканчиваются на “man”
Задание 5. Вывести количество рейсов, совершенных на TU-134
Задание 6. Какие компании совершали перелеты на Boeing
Задание 7. Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)
Задание 8. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
Задание 9. Какие компании организуют перелеты с Владивостока (Vladivostok)?
Задание 10. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.
Задание 11. Вывести пассажиров с самым длинным именем
Задание 12. Вывести id и количество пассажиров для всех прошедших полётов
Задание 13. Вывести имена людей, у которых есть полный тёзка среди пассажиров
Задание 14. В какие города летал Bruce Willis?
Задание 15. Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London)?
Задание 16. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.
Задание 17. Определить, сколько потратил в 2005 году каждый из членов семьи
Задание 18. Узнать, кто старше всех в семьe
Задание 19. Определить, кто из членов семьи покупал картошку (potato)
Задание 20. Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму
Задание 21. Определить товары, которые покупали более 1 раза
Вывести имена людей которые заканчиваются на man
Structured Query Language (SQL) — язык структурированных запросов, с помощью него пишутся специальные запросы (SQL инструкции) к базе данных с целью получения этих данных из базы и для манипулирования этими данными.
С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.
DDL – Data Definition Language
Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.
В эту группу входят следующие операторы:
- CREATE – используется для создания объектов базы данных;
- ALTER – используется для изменения объектов базы данных;
- DROP – используется для удаления объектов базы данных.
DML – Data Manipulation Language
Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.
В эту группу входят самые распространённые операторы языка SQL:
- SELECT – осуществляет выборку данных;
- INSERT – добавляет новые данные;
- UPDATE – изменяет существующие данные;
- DELETE – удаляет данные.
DCL – Data Control Language
Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.
TCL – Transaction Control Language
Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.
Базовый синтаксис SQL команды SELECT
Одна из основных функций SQL — получение данных из СУБД. Для построения всевозможных запросов к базе данных используется оператор SELECT. Он позволяет выполнять сложные проверки и обработку данных.
Общая структура запроса
В описанной структуре запроса необязательные параметры указаны в квадратных скобках.
- DISTINCT используется для исключения повторяющихся строк из результата
- ALL (по умолчанию) используется для получения всех данных, в том числе и повторений
- FROM перечисляет используемые в запросе таблицы из базы данных
- WHERE — это условный оператор, который используется для ограничения строк по какому-либо условию
- GROUP BY используется для группировки строк
- HAVING применяется после группировки строк для фильтрации по значениям агрегатных функций
- ORDER BY используется для сортировки. У него есть два параметра:
- ASC (по умолчанию) используется для сортировки по возрастанию
- DESC — по убыванию
- LIMIT используется для ограничения количества строк для вывода
Псевдонимы используются для представления столбцов или таблиц с именем отличным от оригинального. Это может быть полезно для улучшения читабельности имён и создания более короткого наименования столбца или таблицы.
Например, если в вашей таблице есть столбец good_type_id, вы можете переименовать его просто в id, для того, чтобы сделать его более коротким и удобным в использовании в будущем.
Для создания псевдонимов используется оператор AS:
Вы можете выводить любые строки и числа вместо столбцов:
Для того, чтобы вывести все данные из таблицы Company, вы можете использовать символ «*», который буквально означает «все столбцы»:
Вы можете вывести любой столбец, определённый в таблице, например, town_to из таблицы Trip:
Также вы можете вывести несколько столбцов. Для этого их нужно перечислить через запятую:
Иногда возникают ситуации, в которых нужно получить только уникальные записи. Для этого вы можете использовать DISTINCT. Например, выведем список городов без повторений, в которые летали самолеты:
Эта конструкция используется для формирования словарей, примеры рассмотрим в главе про команду INSERT
Условный оператор WHERE
Ситуация, когда требуется сделать выборку по определенному условию, встречается очень часто. Для этого в операторе SELECT существует параметр WHERE, после которого следует условие для ограничения строк. Если запись удовлетворяет этому условию, то попадает в результат, иначе отбрасывается.
Общая структура запроса с оператором WHERE
В описанной структуре запроса необязательные параметры указаны в квадратных скобках.
В условном операторе применяются операторы сравнения, специальные и логические операторы.
Операторы сравнения служат для сравнения 2 выражений, их результатом может являться ИСТИНА (1), ЛОЖЬ (0) и NULL.
Результат сравнения с NULL является NULL. Исключением является оператор эквивалентности.
| Оператор | Описание |
|---|---|
| = | Оператор равенство |
| <=> | Оператор эквивалентность Аналогичный оператору равенства, с одним лишь исключением: в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL <=> NULL |
| <> или != |
Оператор неравенство |
| < | Оператор меньше |
| <= | Оператор меньше или равно |
| > | Оператор больше |
| >= | Оператор больше или равно |
IS [NOT] NULL — позволяет узнать равно ли проверяемое значение NULL.
Для примера выведем всех членов семьи, у которых статус в семье не равен NULL:
[NOT] BETWEEN min AND max — позволяет узнать расположено ли проверяемое значение столбца в интервале между min и max.
Выведем все данные о покупках с ценой от 100 до 500 рублей из таблицы Payments:
[NOT] IN — позволяет узнать входит ли проверяемое значение столбца в список определённых значений.
Выведем имена членов семьи, чей статус равен «father» или «mother»:
[NOT] LIKE шаблон [ESCAPE символ] — позволяет узнать соответствует ли строка определённому шаблону.
Например, выведем всех людей с фамилией «Quincey»:
В шаблоне разрешается использовать два трафаретных символа:
- символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении
- символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении.
ESCAPE-символ используется для экранирования трафаретных символов. В случае если вам нужно найти строки, содержащие проценты (а процент — это зарезервированный символ), вы можете использовать ESCAPE-символ.
Например, вы хотите получить идентификаторы задач, прогресс которых равен 3%:
Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3.
Логические операторы необходимы для связывания нескольких условий ограничения строк.
- Оператор NOT — меняет значение специального оператора на противоположный
- Оператор OR — общее значение выражения истинно, если хотя бы одно из них истинно
- Оператор AND — общее значение выражения истинно, если они оба истинны
- Оператор XOR — общее значение выражения истинно, если один и только один аргумент является истинным
Выведем все полёты, которые были совершены на самолёте «Boeing», но, при этом, вылет был не из Лондона:
Выборка сводных данных (из двух и более таблиц)
При формировании сводной выборки данные беруться из нескольких таблиц. В операторе FROM исходные таблицы перечисляются через запятую. Также им могут быть присвоены алиасы. Синтаксис запроса выглядит следующийм образом:
При выборке сводных таблиц нужно учитывать, что исходные таблицы перемножаются. Т.е. если на входе у нас были таблицы:
| id | Name |
|---|---|
| 1 | Иванов |
| 2 | Петров |
| id | Name | Phone |
|---|---|---|
| 1 | Иванов | 322223 |
| 2 | Петров | 111111 |
То при простом запросе без условий
Получим примерно следующее:
| id | Name | id2 | Name2 | Phone |
|---|---|---|---|---|
| 1 | Иванов | 1 | Иванов | 322223 |
| 1 | Иванов | 2 | Петров | 111111 |
| 2 | Петров | 1 | Иванов | 322223 |
| 2 | Петров | 2 | Петров | 111111 |
Чтобы выбрать уникальные значения, нам нужно использовать оператор WHERE для связи этих таблиц
| id | Name | Phone |
|---|---|---|
| 1 | Иванов | 322223 |
| 2 | Петров | 111111 |
Сводные выборки нужны при импорте данных в базу. Сначала вы выделяете из таблиц импорта словари. А потом из таблиц импорта и словарей формируете запрос INSERT . SELECT для записи данных в основную таблицу.
Вложенные SQL запросы\
Вложенный запрос — это запрос на выборку, который используется внутри инструкции SELECT, INSERT, UPDATE или DELETE или внутри другого вложенного запроса. Подзапрос может быть использован везде, где разрешены выражения.
Пример структуры вложенного запроса
Здесь, SELECT поля_таблиц FROM список_таблиц WHERE конкретное_поле IN (. ) — внешний запрос, а SELECT поле_таблицы FROM таблица — вложенный (внутренний) запрос.
Каждый вложенный запрос, в свою очередь, может содержать один или несколько вложенных запросов. Количество вложенных запросов в инструкции не ограничено.
Подзапрос может содержать все стандартные инструкции, разрешённые для использования в обычном SQL-запросе: DISTINCT, GROUP BY, LIMIT, ORDER BY, объединения таблиц, запросов и т.д.
Подзапрос может возвращать скаляр (одно значение), одну строку, один столбец или таблицу (одну или несколько строк из одного или нескольких столбцов). Они называются скалярными, столбцовыми, строковыми и табличными подзапросами.
Подзапрос как скалярный операнд
Скалярный подзапрос — запрос, возвращающий единственное скалярное значение (строку, число и т.д.).
Следующий простейший запрос демонстрирует вывод единственного значения (названия компании). В таком виде он не имеет большого смысла, однако ваши запросы могут быть намного сложнее.
Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE, используя операторы сравнения.
С помощью данного запроса возможно получить самого младшего члена семьи. Здесь используется подзапрос для получения максимальной даты рождения, которая затем используется для фильтрации строк.
Подзапросы с ANY, IN, ALL
ANY — ключевое слово, которое должно следовать за операцией сравнения (>, <, <>, = и т.д.), возвращающее TRUE, если хотя бы одно из значений столбца подзапроса удовлетворяет обозначенному условию.
ALL — ключевое слово, которое должно следовать за операцией сравнения, возвращающее TRUE, если все значения столбца подзапроса удовлетворяет обозначенному условию.
IN — ключевое слово, являющееся псевдонимом ключевому слову ANY с оператором сравнения = (эквивалентность), либо <> ALL для NOT IN. Например, следующие запросы равнозначны:
Строковый подзапрос — это подзапрос, возвращающий единственную строку с более чем одной колонкой. Например, следующий запрос получает в подзапросе единственную строку, после чего по порядку попарно сравнивает полученные значения со значениями во внешнем запросе.
Данную конструкцию удобно использовать для замены логических операторов. Так, следующие два запроса полностью эквивалентны:
Связанным подзапросом является подзапрос, который содержит ссылку на таблицу, которая была объявлена во внешнем запросе. Здесь вложенный запрос ссылается на внешюю таблицу «таблица_1»:
Подзапросы как производные таблицы
Производная таблица — выражение, которое генерирует временную таблицу в предложении FROM, которая работает так же, как и обычные таблицы, которые вы указываете через запятую. Так выглядит общий синтаксис запроса с использованием производных таблиц:
Обратите внимание на то, что для производной таблицы обязательно должен указываться её псевдоним, для того, чтобы имелась возможность обратиться к ней в других частях запроса.
Обработка вложенных запросов
Вложенные подзапросы обрабатываются «снизу вверх». То есть сначала обрабатывается вложенный запрос самого нижнего уровня. Далее значения, полученные по результату его выполнения, передаются и используются при реализации подзапроса более высокого уровня и т.д.
Добавление данных, оператор INSERT
Для добавления новых записей в таблицу предназначен оператор INSERT.
Общая структура запроса с оператором INSERT
В описанной структуре запроса необязательные параметры указаны в квадратных скобках. Вертикальной чертой обозначен альтернативный синтаксис.
Значения можно вставлять перечислением с помощью слова values, перечислив их в круглых скобках через запятую или c помощью оператора select. Таким образом, добавить новые записей можно следующими способами:
Первичный ключ при добавлении новой записи
Следует помнить, что первичный ключ таблицы является уникальным значением и добавление уже существующего значения приведет к ошибке.
При добавлении новой записи с уникальными индексами выбор такого уникального значения может оказаться непростой задачей. Решением может быть дополнительный запрос, направленный на выявление максимального значения первичного ключа для генерации нового уникального значения.
В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ good_id атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения good_id достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца good_id, плюс единица.
Теперь, зная синткасис команд INSERT и SELECT, можем разобраться как создать из исходного набора данных словари и загрузить данные в БД с учетом внешних ключей
Допустим есть список агентов (данные полученные от заказчика в виде CSV-файла), у которых есть поля название, тип и т.д. (далее по тексту я её называю таблица импорта)
В структуре БД поле «тип агента» создано как внешний ключ на таблицу типов
Для добавления «типов агентов» в таблицу AgentType мы будем использовать альтернативный синтаксис INSERT . SELECT
Пишем инструкцию SELECT, которая выбирает уникальные записи из таблицы импорта:
- Ключевое слово DISTINCT относится только к топу полю, перед которым написано. В нашем случае выбирает уникальные названия типов агентов.
- Откуда брать поле Image в предметной области не написано и в исходных данных его нет. Но т.к. в целевой таблице это поле не обязательное, то можно его пропустить
Этот запрос можно выполнить отдельно, чтобы проверить что получится
После отладки запроса SELECT перед ним допишем запрос INSERT:
- Поле ID можно пропустить, оно автоинкрементное и создастся само (по крайней мере в MsSQL)
- Количество вставляемых полей (Title) должно быть равным количеству выбираемых полей (Тип_агента)
Если в таблице есть обязательные поля, а нем неоткуда взять для них данные, то мы можем в SELECT вставить фиксированные значения (в примере пустая строка):
Заполнение основной таблицы
Тоже сначала пишем SELECT запрос, чтобы проверить те ли данные получаются
напоминаю, что порядок и количество выбираемых и вставляемых полей должны быть одинаковыми
в поле AgentTypeID мы должны вставить ID соответсвующей записи из таблицы AgentType, поэтому выборка у нас из двух таблиц и чтобы не писать перед каждым полем полные названия таблиц мы присваиваем им алиасы
Т.е. мы выбираем перечисленные поля из таблицы agents_import и добавляем к ним ID агента у которого совпадает название.
При выборке из нескольких таблиц исходные данные перемножаются. Т.е. если мы не заполним перед этой выборкой словарь, то 100 * 0 = пустая выборка .
Если же мы не укажем условие WHERE, то выберутся, к примеру, 100 * 10 = 1000 записей (каждый агент будет в каждой категории). Поэтому важно, чтобы условие WHERE выбирало уникальные значения.
Естественно, количество внешних ключей в таблице может быть больше одного, в таком случае в секции FROM перечисляем все используемые словари и в секции WHERE перечисляем условия для всех таблиц объединив их логическим выражением AND
где алиасы b, c, d — словарные таблицы, а алиас «а» — таблица импорта
Написав и проверив работу выборки (она должна возвращать чтолько же записей, сколько в таблице импорта) дописываем команду вставки данных:
В каталоге data этого репозитория находится структура БД ( ms.sql ) и файлы для импорта: products_k_import.csv , materials_short_k_import.txt , productmaterial_k_import.xlsx .
Необходимо во-первых, восстановить структуру БД из скрипта, во-вторых, импортировать исходные данные в Excel, в-третьих исправить данные (смотрите на структуру таблиц в БД, где-то надо явно указать тип данных, где-то вырезать лишние данные. ) и в-четвёртых загрузить исправленные данные в БД (сначала просто импорт во временные таблицы, потом разнести SQL-запросами по нужным таблицам)
SQL ACADEMY ответы и решения заданий (часть 1, задания 1-22)
SQL Academy (ответы и решения заданий 1-22)
Ниже представлены наши варианты решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Ответы на 66 заданий представленные в онлайн тренажере разбиты на 3 части. Здесь, в первой части, представлены ответы на первые 22 задания.
Ответы на задания 45-66 будут тут.
Задание 1. Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний.
Задание 2. Вывести названия всеx авиакомпаний.
Задание 3. Вывести все рейсы, совершенные из Москвы
Задание 4. Вывести имена людей, которые заканчиваются на “man”
Задание 5. Вывести количество рейсов, совершенных на TU-134
Задание 6. Какие компании совершали перелеты на Boeing
Задание 7. Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)
Задание 8. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
Задание 9. Какие компании организуют перелеты с Владивостока (Vladivostok)?
Задание 10. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.
Задание 11. Вывести пассажиров с самым длинным именем
Задание 12. Вывести id и количество пассажиров для всех прошедших полётов
Задание 13. Вывести имена людей, у которых есть полный тёзка среди пассажиров
Задание 14. В какие города летал Bruce Willis?
Задание 15. Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London)?
Задание 16. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.
Задание 17. Определить, сколько потратил в 2005 году каждый из членов семьи
Задание 18. Узнать, кто старше всех в семьe
Задание 19. Определить, кто из членов семьи покупал картошку (potato)
Задание 20. Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму
Задание 21. Определить товары, которые покупали более 1 раза
SQL. Занимательные задачки
Вот уже более 3-х лет я преподаю SQL в разных тренинг центрах, и одним из моих наблюдений является то, что студенты осваивают и понимают SQL лучше, если ставить перед ними задачу, а не просто рассказывать о возможностях и теоретических основах.
В этой статье я поделюсь с вами своим списком задач, которые я даю студентам в качестве домашнего задания и над которыми мы проводим разного рода брейнстормы, что приводит к глубокому и четкому пониманию SQL.
SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…
Почитать об SQL можно из разных источников.
Данная статья не преследует цели обучить вас SQL с нуля.
Будем использовть всем известную схему HR в Oracle с ее таблицами (Подробнее):
Отмечу что мы будем рассматривать только задачи на SELECT. Тут нет задач на DML и DDL.
Задачи
Restricting and Sorting Data
Таблица Employees. Получить список с информацией обо всех сотрудниках
Using Single-Row Functions to Customize Output
Таблица Employees. Получить список всех сотрудников у которых длина имени больше 10 букв
Using Conversion Functions and Conditional Expressions
Таблица Employees. Получить список всех сотрудников которые пришли на работу в первый день месяца (любого)
Reporting Aggregated Data Using the Group Functions
Таблица Employees. Получить репорт по department_id с минимальной и максимальной зарплатой, с ранней и поздней датой прихода на работу и с количествов сотрудников. Сорировать по количеству сотрудников (по убыванию)
Displaying Data from Multiple Tables Using Joins
Таблица Employees, Departaments, Locations, Countries, Regions. Получить список регионов и количество сотрудников в каждом регионе
Using Subqueries to Solve Queries
Таблица Employees. Получить список сотрудников с самым длинным именем.
На этом пока всё.
Надеюсь, задачи были интересными и увлекательными.
Буду по возможности дополнять этот список задач.
Также буду рад любым замечаниям и предложениям.
Saved searches
Use saved searches to filter your results more quickly
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.
fang313 / SQL Public
fang313/SQL
Name already in use
- Local
- Codespaces
Use Git or checkout with SVN using the web URL.
Work fast with our official CLI. Learn more about the CLI.
Sign In Required
Please sign in to use Codespaces.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching Xcode
If nothing happens, download Xcode and try again.
Launching Visual Studio Code
Your codespace will open once ready.
There was a problem preparing your codespace, please try again.
Latest commit
Git stats
Files
Failed to load latest commit information.
README.md
Решение задач по SQL
Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний
Решение:
SELECT name FROM Passenger
Вывести названия всеx авиакомпаний
Решение:
SELECT name FROM Company
Вывести все рейсы, совершенные из Москвы
Решение:
SELECT * FROM Trip
WHERE town_from LIKE ‘Moscow’
Вывести имена людей, которые заканчиваются на «man»
Решение:
SELECT name FROM Passenger
WHERE name LIKE ‘%man’
Вывести количество рейсов, совершенных на TU-134
Решение:
SELECT COUNT(plane) AS count
FROM Trip
WHERE plane LIKE ‘TU-134’
Какие компании совершали перелеты на Boeing
Решение:
SELECT DISTINCT name FROM Company
JOIN Trip
ON Company.id=trip.company
WHERE plane LIKE ‘Boeing’
Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)
Решение:
SELECT DISTINCT plane
FROM Trip
WHERE town_to LIKE ‘Moscow’
В какие города можно улететь из Парижа (Paris) и сколько времени это займёт
Решение:
SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time
FROM Trip
WHERE town_from = ‘Paris’
Какие компании организуют перелеты с Владивостока (Vladivostok)
Решение:
SELECT DISTINCT name FROM Company
JOIN Trip
ON Company.id=trip.company
WHERE town_from LIKE ‘Vladivostok’
Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.
Решение:
SELECT * FROM Trip
WHERE time_out
BETWEEN ‘1900-01-01 10:00:00’ AND ‘1900-01-01 14:00:00’
Вывести пассажиров с самым длинным именем
Решение:
SELECT name FROM Passenger
WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM Passenger)
Вывести id и количество пассажиров для всех прошедших полётов
Решение:
SELECT trip, COUNT(passenger) as count
FROM Pass_in_trip
GROUP BY trip
Вывести имена людей, у которых есть полный тёзка среди пассажиров
Решение:
SELECT name FROM Passenger
GROUP BY name
HAVING COUNT(name) > 1
Вывести имена людей которые заканчиваются на man
If nothing happens, download GitHub Desktop and try again.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching Xcode
If nothing happens, download Xcode and try again.
Launching Visual Studio Code
Your codespace will open once ready.
There was a problem preparing your codespace, please try again.
Latest commit
Git stats
Files
Failed to load latest commit information.
README.md
Задание 1: Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний SELECT name from Passenger
Задание 2: Вывести названия всеx авиакомпаний SELECT name FROM Company;
Задание 3: Вывести все рейсы, совершенные из Москвы SELECT * FROM Trip WHERE town_from = ‘Moscow’;
Задание 4: Вывести имена людей, которые заканчиваются на «man» SELECT name FROM Passenger WHERE name LIKE ‘%man’;
Задание 5: Вывести количество рейсов, совершенных на TU-134 SELECT DISTINCT COUNT(‘plane’) AS count FROM Trip WHERE plane LIKE ‘TU-134’;
Задание 6: Какие компании совершали перелеты на Boeing SELECT Company.name FROM Trip LEFT JOIN Company ON Company.id = Trip.company WHERE plane = ‘Boeing’ GROUP BY company;
Задание 7: Вывести все названия самолётов, на которых можно улететь в Москву (Moscow) SELECT plane FROM Trip WHERE town_to = ‘Moscow’ GROUP BY plane;
Задание 8: В какие города можно улететь из Парижа (Paris) и сколько времени это займёт? SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM Trip WHERE town_from = ‘Paris’;
Задание 9: SELECT name FROM Company AS c LEFT JOIN Trip AS t ON c.id = t.company WHERE t.town_from = ‘Vladivostok’;
Задание 10: Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г. SELECT * FROM Trip WHERE time_out BETWEEN ‘1900-01-01T10:00:00.000Z’ AND ‘1900-01-01T14:00:00.000Z’;
Задание 11: Вывести пассажиров с самым длинным именем SELECT name FROM Passenger ORDER BY LENGTH(name) DESC LIMIT 1;
Задание 12: Вывести id и количество пассажиров для всех прошедших полётов SELECT trip, COUNT(passenger) AS count FROM Pass_in_trip GROUP BY trip;
Задание 13: Вывести имена людей, у которых есть полный тёзка среди пассажиров SELECT name FROM Passenger GROUP BY name HAVING COUNT(*) > 1;
Задание 14: В какие города летал Bruce Willis SELECT t.town_to FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger WHERE name = ‘Bruce Willis’;
Задание 15: Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London) SELECT t.time_in FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger WHERE name = ‘Steve Martin’ AND town_to = ‘London’;
Задание 16: Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет. SELECT p.name, COUNT(passenger) AS count FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger GROUP BY p.name HAVING count >= 1 ORDER BY count DESC, p.name ASC;
Задание 17: Определить, сколько потратил в 2005 году каждый из членов семьи SELECT member_name, status, SUM(unit_price * amount) as costs FROM Payments AS p JOIN FamilyMembers AS fm ON p.family_member = fm.member_id WHERE date LIKE ‘2005%’ GROUP BY family_member;
Задание 18: Узнать, кто старше всех в семьe SELECT member_name FROM FamilyMembers WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);
Задание 19: Определить, кто из членов семьи покупал картошку (potato) SELECT status FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods AS g ON p.good = g.good_id WHERE good_name LIKE ‘potato’ GROUP BY status;
Задание 20: Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму SELECT status, member_name, SUM(unit_price*amount) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes as gp ON g.type = gp.good_type_id WHERE good_type_name = ‘entertainment’ GROUP BY family_member;
Задание 21: Определить товары, которые покупали более 1 раза SELECT good_name FROM Payments AS p JOIN Goods as g ON p.good = g.good_id GROUP BY good HAVING COUNT(good_name) > 1;
Задание 22: Найти имена всех матерей (mother) SELECT member_name FROM FamilyMembers WHERE status = ‘mother’;
Задание 23: SELECT good_name, unit_price FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes as gp ON g.type = gp.good_type_id WHERE good_type_name = ‘delicacies’ LIMIT 1;
Задание 24: Определить кто и сколько потратил в июне 2005 SELECT member_name, SUM(unit_price*amount) as costs FROM Payments as p JOIN FamilyMembers as fm ON p.family_member = fm.member_id WHERE date LIKE ‘2005-06%’ GROUP BY member_name;
Задание 25: Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года SELECT good_name FROM Goods
LEFT JOIN Payments ON Goods.good_id = Payments.good AND YEAR(Payments.date) = 2005 WHERE Payments.good IS NULL GROUP BY good_id;
SELECT good_name, good_id, good, date FROM Goods as g LEFT OUTER JOIN Payments as p ON g.good_id = p.good WHERE date IS NULL OR date NOT LIKE ‘2005%’ ORDER BY good;
Задание 26: Определить группы товаров, которые не приобретались в 2005 году ГРУППЫ, ТОВАРЫ, КОГДА ПРИОБРЕТАЛИСЬ: SELECT good_type_name, good_name, good_id, good, payment_id, date FROM Goods JOIN Payments ON Goods.good_id = Payments.good JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type;
РЕШЕНИЕ: SELECT good_type_name FROM GoodTypes WHERE good_type_id NOT IN (SELECT good_type_id FROM Goods JOIN Payments ON Goods.good_id = Payments.good AND YEAR(date) = 2005 JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type);
Задание 27: Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму SELECT good_type_name, SUM(amount*unit_price) AS costs FROM GoodTypes JOIN Goods ON good_type_id = type JOIN Payments ON good = good_id AND YEAR(date) = 2005 GROUP BY good_type_name;
Задание 28: Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow) ? SELECT COUNT(id) AS count FROM Trip WHERE town_from = ‘Rostov’ AND town_to = ‘Moscow’;
Задание 29: Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134 SELECT DISTINCT name FROM Passenger JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger JOIN Trip ON Pass_in_trip.trip = Trip.id WHERE plane = ‘TU-134’ AND town_to = ‘Moscow’;
Задание 30: Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности. SELECT trip, COUNT(passenger) AS count FROM Passenger JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger JOIN Trip ON Pass_in_trip.trip = Trip.id GROUP BY trip ORDER BY count DESC;
Задание 31: Вывести всех членов семьи с фамилией Quincey. SELECT * FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;
Задание 32: Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону. SELECT FLOOR(AVG(FLOOR(DATEDIFF(NOW(), birthday)/365))) AS age FROM FamilyMembers;
Задание 33: Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar). SELECT AVG(unit_price) AS cost FROM Payments JOIN Goods ON good=good_id WHERE good_name = ‘red caviar’ OR good_name = ‘black caviar’;
Задание 34: Сколько всего 10-ых классов? SELECT COUNT(name) AS count FROM Class WHERE name LIKE ‘10%’;
Задание 35: Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ? SELECT DISTINCT COUNT(classroom) AS count FROM Schedule WHERE date LIKE ‘2019-09-02%’;
Задание 36: Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)? SELECT * FROM Student WHERE address LIKE ‘%Pushkina%’;
Задание 37: Сколько лет самому молодому обучающемуся ? SELECT ROUND(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student; SELECT FLOOR(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student;
Задание 38: SELECT COUNT(1) As count FROM Student WHERE first_name LIKE ‘Anna’;
SELECT COUNT(class) AS count FROM Student_in_class JOIN Class ON Class.id=class WHERE name LIKE ’10 B’;
SELECT COUNT(class) AS count FROM Student_in_class JOIN Class ON Class.id=class AND name = ’10 B’;
Задание 40: Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ? SELECT DISTINCT(Subject.name) AS subjects FROM Subject JOIN Schedule ON Subject.id=Schedule.subject JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name=’Romashkin’;
Задание 41: Во сколько начинается 4-ый учебный предмет по расписанию ? SELECT start_pair FROM Timepair WHERE start_pair FROM Timepair LIMIT 3, 1; SELECT start_pair FROM Timepair LIMIT 1 OFFSET 3;
Задание 42: Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет ? SELECT DISTINCT TIMEDIFF((SELECT end_pair FROM Timepair WHERE (SELECT start_pair FROM Timepair WHERE as time FROM Timepair;
Задание 43: Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Остортируйте преподавателей по фамилии. SELECT last_name FROM Teacher JOIN Schedule ON Teacher.id=Schedule.teacher JOIN Subject ON Subject.id=Schedule.subject WHERE Subject.name=’Physical Culture’ ORDER BY last_name ASC;
Задание 44: Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ? SELECT FLOOR(MAX((DATEDIFF(NOW(), birthday)/365))) AS max_year FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student JOIN Class ON Class.id=Student_in_class.class WHERE Class.name LIKE ‘10%’;
SELECT classroom, COUNT(classroom) as count FROM Schedule GROUP BY classroom HAVING COUNT() > 4 ORDER BY COUNT() DESC; — какие кабинеты в топе?
Задание 46: В каких классах введет занятия преподаватель «Krauze» ? SELECT DISTINCT name FROM Class JOIN Schedule ON Class.id=Schedule.class JOIN Teacher ON Teacher.id=Schedule.teacher WHERE last_name = ‘Krauze’;
Задание 47: Сколько занятий провел Krauze 30 августа 2019 г.? SELECT COUNT(teacher) AS count FROM Schedule JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name = ‘Krauze’ WHERE date LIKE ‘2019-08-30%’;
Задание 48: Выведите заполненность классов в порядке убывания SELECT name, COUNT(class) as count FROM Class JOIN Student_in_class ON Class.id=Student_in_class.class GROUP BY name ORDER BY COUNT(*) DESC;
Задание 49: Какой процент обучающихся учится в 10 A классе ? SELECT (COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student)) AS percent FROM Student_in_class JOIN Class ON Class.id=Student_in_class.class AND name = ’10 A’;
Задание 50: Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону. SELECT FLOOR((COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student))) AS percent FROM Student WHERE YEAR(birthday) = 2000;
Задание 51: Добавьте товар с именем «Cheese» и типом «food» в список товаров (Goods). INSERT INTO Goods(good_id, good_name, type) VALUES (17, ‘Cheese’, 2);
Задание 52: Добавьте в список типов товаров (GoodTypes) новый тип «auto». INSERT INTO GoodTypes(good_type_id, good_type_name) VALUES (9, ‘auto’);
Задание 53: Измените имя «Andie Quincey» на новое «Andie Anthony». UPDATE FamilyMembers SET member_name=’Andie Anthony’ WHERE member_id=3;
Задание 54: Удалить всех членов семьи с фамилией «Quincey». DELETE FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;
Задание 55: Удалить компании, совершившие наименьшее количество рейсов. SELECT name, COUNT(company) as company FROM Trip JOIN Company ON Company.id=Trip.company GROUP BY name; DELETE FROM Company WHERE FROM Company WHERE FROM Company WHERE >
Задание 56: Удалить все перелеты, совершенные из Москвы (Moscow). DELETE FROM Trip WHERE town_from LIKE ‘%Moscow’;
Задание 57: Перенести расписание всех занятий на 30 мин. вперед. UPDATE Timepair SET start_pair = DATE_ADD(start_pair, INTERVAL 30 MINUTE); UPDATE Timepair SET end_pair = DATE_ADD(end_pair, INTERVAL 30 MINUTE);
Задание 58: Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу «11218, Friel Place, New York», от имени «George Clooney» SELECT Users.name, Reservations.* FROM Reservations JOIN Rooms ON Rooms.id=Reservations.room_id JOIN Users ON Users.id=Reservations.user_id WHERE address = ‘11218, Friel Place, New York’
INSERT INTO Reviews (id, reservation_id, rating) VALUES (23, 2, 5);
Задание 59: Вывести пользователей,указавших Белорусский номер телефона ? Телефонный код Белоруссии +375. SELECT * FROM Users WHERE phone_number LIKE ‘+375%’;
SELECT teacher FROM Schedule JOIN Teacher ON Teacher.id=Schedule.teacher JOIN Subject ON Subject.id=Schedule.subject JOIN Class ON Class.id=Schedule.class WHERE Class.name IN (’11 A’, ’11 B’) GROUP BY teacher HAVING COUNT(teacher)>=1 ORDER BY teacher;
Задание 61: Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года. SELECT Rooms.* FROM Rooms JOIN Reservations ON Rooms.id=Reservations.room_id AND YEAR(start_date)=2020 AND YEAR(end_date)=2020 WHERE WEEK(start_date, 1)=12 OR WEEK(end_date, 1)=12;
Задание 62: Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты. Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён. SELECT SUBSTRING_INDEX(email, ‘@’, -1) as domain, count(*) AS count FROM Users GROUP BY domain ORDER BY count DESC, domain ASC;
Задание 63: Выведите отсортированный список (по возрастанию) имен студентов в виде Фамилия.И.О. SELECT CONCAT(last_name, ‘.’, LEFT(first_name, 1), ‘.’, LEFT(middle_name, 1), ‘.’) AS name FROM Student ORDER BY first_name ASC;
Найти данные, оканчивающиеся на ___ в SQL?
Попытка получить эту информацию из базы данных: количество почтовых индексов, где название города оканчивается на «бор» или «город» и имеет среднюю стоимость дома в 250 000 долларов США или ниже.
Это то, что я положил:
И продолжайте получать это сообщение: ОШИБКА 1064 (42000): у вас есть ошибка в вашем синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, чтобы узнать правильный синтаксис для использования рядом с «% boro» ИЛИ городом, например, «% town») и average_house_value 0
2 ответа
where city like ’%boro’ and city like ‘%town’ and average_house_value <= 250000
Проблемы с вашим запросом:
Вы должны использовать стандартные одинарные кавычки вместо этих прикольных символов ’ ; скорее всего, это причина, по которой вы получаете синтаксическую ошибку
Вы хотите, чтобы города заканчивались на ‘boro’ или ‘town’ , но вы сформулировали запрос, который ищет города, заканчивающиеся на оба: это гарантированно не вернет ни строки, ни значения может заканчиваться обеими строками одновременно
Как вывести имена всех людей, которые покупали морские продукты? Пример на SQL тренажере
Пытаюсь разобраться в sql с помощью задачек на этом онлайн-тренажере https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
Исходя из базы на этом сайте есть задание:
Вывести только имена всех людей, которые покупали морские продукты
Насколько я поняла, для этого надо связать друг с другом 4 таблицы.
Структура
Customers: CustomerID, CustomerName
Orders: OrderID, CustomerID
OrderDetails: OrderID, ProductID
Products: ProductID, CategoryID
Слова, заканчивающиеся на буквы "man"
Русский:Привет
English:Hello
France:Bonjour
Spanish:Hola
Dautch:Guten Aben