SQL ACADEMY ответы и решения заданий (часть 2, задания 23-44)
Ниже представлены решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Здесь представлены ответы на задания 23-44.
Ответы на задания 1-22 (часть 1) здесь.
Ответы на задания 45-66 (часть 3) будут тут.
Задание 23. Найдите самый дорогой деликатес (delicacies) и выведите его стоимость.
Задание 24. Определить кто и сколько потратил в июне 2005.
Задание 25. Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года
Задание 26. Определить группы товаров, которые не приобретались в 2005 году
Задание 27. Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму
Задание 28. Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow)?
Задание 29. Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134
Задание 30. Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности.
Задание 31. Вывести всех членов семьи с фамилией Quincey.
Задание 32. Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону.
Задание 33. Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).
Задание 34. Сколько всего 10-ых классов
Задание 35. Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях?
Задание 36. Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
Задание 38. Сколько Анн (Anna) учится в школе?
Задание 43. Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Отсортируйте преподавателей по фамилии.
Ответы на задания 45-66 здесь.
Задачи на продвинутые SQL запросы
Учебник PHP
Практика
Важное
Регулярки
Работа с htaccess
Файлы, папки
Сессии и куки
Работа с БД
Практика по работе с БД в PHP
Перед чтением см. новые уроки раздела «Важное», которые появились выше.
Практика
Движок PHP
Продвинутые БД
Аутентификация
Практика
ООП и MVC
Абстрактные классы и интерфейсы
Трейты
ООП Магия
Практика
Практика: классы как набор методов
Перед решением задач изучите теорию к данному уроку.
Примеры решения задач
Задача
Задача. Выберите из таблицы workers записи с id равным 3, 5, 6, 10.
Задача
Задача. Выберите из таблицы workers записи с id равным 3, 5, 6, 10 и логином, равным ‘eee’, ‘zzz’ или ‘ggg’.
Задача
Задача. Выберите из таблицы workers записи c зарплатой от 500 до 1500.
Задача
Задача
Задача. Найдите в таблице workers минимальный возраст.
Задача
Задача. Найдите в таблице workers суммарный возраст.
Задача
Задача. Вставьте в таблицу workers запись с полем date с текущим моментом времени в формате ‘год-месяц-день часы:минуты:секунды’.
Задача
Задача. Вставьте в таблицу workers запись с полем date с текущей датой в формате ‘год-месяц-день’.
Задача
Задача. При выборке из таблицы workers запишите день, месяц и год в отдельные поля.
Задача
Задача. Выберите из таблицы workers записи, в которых минуты больше секунд.
Задача
Задача. При выборке из таблицы workers прибавьте к дате 1 год.
Задача
Задача. При выборке из таблицы workers отнимите от даты 1 год.
Задача
Задача. При выборке из таблицы workers прибавьте к дате 3 года, 4 месяца.
Задача
Задача. При выборке из таблицы workers прибавьте к дате 4 дня, 3 часа, 2 минуты, 1 секунду.
Задача
Задача. При выборке из таблицы workers прибавьте к дате 3 дня и отнимите 2 часа.
Задачи для решения
На IN
Выберите из таблицы workers записи с id равным 1, 2, 3, 5, 14.
Выберите из таблицы workers записи с login равным ‘eee’, ‘bbb’, ‘zzz’.
Выберите из таблицы workers записи с id равным 1, 2, 3, 7, 9, и логином, равным ‘user’, ‘admin’, ‘ivan’ и зарплатой больше 300.
На BETWEEN
Выберите из таблицы workers записи c зарплатой от 100 до 1000.
Выберите из таблицы workers записи c id от 3 до 10 и зарплатой от 300 до 500.
На AS
На DISTINCT
Выберите из таблицы workers все записи так, чтобы туда попали только записи с разной зарплатой (без дублей).
Получите SQL запросом все возрасты без дублирования.
На MIN и MAX
Найдите в таблице workers минимальную зарплату.
Найдите в таблице workers максимальную зарплату.
На SUM
Найдите в таблице workers суммарную зарплату.
Найдите в таблице workers суммарную зарплату для людей в возрасте от 21 до 25.
Найдите в таблице workers суммарную зарплату для id, равного 1, 2, 3 и 5.
На AVG
Найдите в таблице workers среднюю зарплату.
Найдите в таблице workers средний возраст.
На NOW, CURRENT_DATE, CURRENT_TIME
Выберите из таблицы workers все записи, у которых дата больше текущей.
Вставьте в таблицу workers запись с полем date с текущим моментом времени в формате ‘год-месяц-день часы:минуты:секунды’.
Вставьте в таблицу workers запись с полем date с текущей датой в формате ‘год-месяц-день’.
Вставьте в таблицу workers запись с полем time с текущим моментом времени в формате ‘часы:минуты:секунды’.
На работу с частью даты
Выберите из таблицы workers все записи за 2016 год.
Выберите из таблицы workers все записи за март любого года.
Выберите из таблицы workers все записи за третий день месяца.
Выберите из таблицы workers все записи за пятый день апреля любого года.
Выберите из таблицы workers все записи за следующие дни любого месяца: 1, 7, 11, 12, 15, 19, 21, 29.
Выберите из таблицы workers все записи за вторник.
Выберите из таблицы workers все записи за первую декаду любого месяца 2016 года.
Выберите из таблицы workers все записи, в которых день меньше месяца.
При выборке из таблицы workers запишите день, месяц и год в отдельные поля.
При выборке из таблицы workers создайте новое поле today, в котором будет номер текущего дня недели.
На EXTRACT, DATE
При выборке из таблицы workers запишите год, месяц и день в отдельные поля с помощью EXTRACT.
При выборке из таблицы workers запишите день, месяц и год в отдельное поле с помощью DATE в формате ‘год-месяц-день’.
На DATE_FORMAT
При выборке из таблицы workers выведите дату в формате ‘31.12.2025’.
При выборке из таблицы workers выведите дату в формате ‘2025% 31.12’.
На INTERVAL, DATE_ADD, DATE_SUB
При выборке из таблицы workers прибавьте к дате 1 день.
При выборке из таблицы workers отнимите от даты 1 день.
При выборке из таблицы workers прибавьте к дате 1 день, 2 часа.
При выборке из таблицы workers прибавьте к дате 1 год, 2 месяца.
При выборке из таблицы workers прибавьте к дате 1 день, 2 часа, 3 минуты.
При выборке из таблицы workers прибавьте к дате 1 день, 2 часа, 3 минуты, 5 секунд.
При выборке из таблицы workers прибавьте к дате 2 часа, 3 минуты, 5 секунд.
При выборке из таблицы workers прибавьте к дате 1 день и отнимите 2 часа.
При выборке из таблицы workers прибавьте к дате 1 день и отнимите 2 часа, 3 минуты.
На математические операции
При выборке из таблицы workers создайте новое поле res, в котором будет число 3.
При выборке из таблицы workers создайте новое поле res, в котором будет строка ‘eee’.
При выборке из таблицы workers создайте новое поле 3, в котором будет число 3.
При выборке из таблицы workers создайте новое поле res, в котором будет лежать сумма зарплаты и возраста.
При выборке из таблицы workers создайте новое поле res, в котором будет лежать разность зарплаты и возраста.
При выборке из таблицы workers создайте новое поле res, в котором будет лежать произведение зарплаты и возраста.
При выборке из таблицы workers создайте новое поле res, в котором будет лежать среднее арифметическое зарплаты и возраста.
Выберите из таблицы workers все записи, в которых сумма дня и месяца меньше 10-ти.
На LEFT, RIGHT, SUBSTRING
При выборке из таблицы workers получите первые 5 символов поля description.
При выборке из таблицы workers получите последние 5 символов поля description.
При выборке из таблицы workers получите из поля description символы со второго по десятый.
На UNION
Даны две таблицы: таблица category и таблица sub_category с полями id и name. Достаньте одним запросом названия категорий и подкатегорий.
На CONCAT, CONCAT_WS
При выборке из таблицы workers создайте новое поле res, в котором будут лежать одновременно зарплата и возраст (слитно).
При выборке из таблицы workers создайте новое поле res, в котором будут лежать одновременно зарплата и возраст (слитно), а после возраста будут идти три знака ‘!’.
При выборке из таблицы workers создайте новое поле res, в котором будут лежать одновременно зарплата и возраст через дефис.
При выборке из таблицы workers получите первые 5 символов логина и добавьте троеточие.
На GROUP BY
Найдите самые маленькие зарплаты по группам возрастов (для каждого возраста свою минимальную зарплату).
Найдите самый большой возраст по группам зарплат (для каждой зарплаты свой максимальный возраст).
На GROUP_CONCAT
Выберите из таблицы workers уникальные возраста так, чтобы для каждого возраста было поле res, в котором будут лежать через дефис id записей с таким возрастом.
На подзапросы
Выберите из таблицы workers все записи, зарплата в которых больше средней зарплаты.
Выберите из таблицы workers все записи, возраст в которых меньше среднего возраста, деленного на 2 и умноженного на 3.
Выберите из таблицы workers записи с минимальной зарплатой.
Выберите из таблицы workers записи с максимальной зарплатой.
При выборке из таблицы workers создайте новое поле max, в котором будет лежать максимальное значение зарплаты для возраста 25 лет.
При выборке из таблицы workers создайте новое поле avg, в котором будет лежать деленная на 2 разница между максимальным значением возраста и минимальным значением возраста в во всей таблице.
При выборке из таблицы workers создайте новое поле avg, в котором будет лежать деленная на 2 разница между максимальным значением зарплаты и минимальным значением зарплаты для возраста 25 лет.
На JOIN
Даны две таблицы: таблица category с полями id и name и таблица page с полями id, name и category_id. Достаньте одним запросом все страницы вместе с их категориями.
Даны 3 таблицы: таблица category с полями id и name, таблица sub_category с полями id и name и таблица page с полями id, name и sub_category_id. Достаньте одним запросом все страницы вместе с их подкатегориями и категориями.
SQL запрос количество учащихся в школе
У меня есть таблица студентов и школ. Как мне выбрать общее количество учащихся в каждой школе?
Я уверен, что это очень простой запрос, однако я не уверен, как действовать дальше:
2 ответа
Sql запрос, чтобы узнать, нет ли мальчиков и девочек в школе правильно ли это select count(*) from schooldata group by sex;
В деревне есть школа. Он имеет N классов. В один прекрасный день кто-то пожертвовал B blue berry cheese cakes школам. Теперь вам нужно разделить эти пирожные таким образом, чтобы: Каждый класс получает не менее 1 торта. Каждый класс поделит торт(ы) между учениками. Ваша цель-свести к минимуму.
Сгруппируйте по школе и используйте count() для подсчета учащихся
Я также хочу добавить к принятому ответу. Работая в школьном округе и постоянно проверяя количество учеников, нужно иметь в виду несколько дополнительных вещей. Каких студентов вы ищете?
Я построил сценарий с идеей нормализованной базы данных школьного округа, где все разбито по учебному году и зачислению.
Часто базовый сценарий для меня выглядит примерно так.
Поскольку каждый учебный год-это новый год, у студентов и школ обычно есть таблица для основных данных, которая не меняется. Но также и таблицы, относящиеся к конкретному учебному году. В моем примере STUDENT_YEAR и SCHOOL_YEAR-это то, где мы подробно разбираемся в том, каких детей мы на самом деле получаем и где они в настоящее время находятся. Я использую таблицу YEAR, чтобы определить, на какой учебный год я хочу посмотреть.
В таблице STUDENT_YEAR мы храним флаг параллелизма студентов, вводим дату, и поэтому в этой таблице я могу использовать в предложении WHERE способ отфильтровать неактивных студентов, параллельных студентов и убедиться, что каждый студент учитывается только один раз.
Если бы эти значения года не были включены, по крайней мере, в мою базу данных, я бы получил всех студентов, когда-либо зачисленных за каждый год, который мы сохранили.
Похожие вопросы:
как я могу определить количество CoE учащихся в каждой школе в каждом городе? студенты се принадлежат к другому столу, чем школьный стол от городского стола. Мне действительно нужна помощь. спасибо
У меня есть этот запрос SQL: SELECT c.id as ID, c.class_name as CLASS, COUNT(e.student_id) AS STUDENT_COUNT FROM classes as c LEFT JOIN enrollments as e on e.class_id = c.id where c.teacher_id = 8.
У меня есть 2 стола SCHOOLS (ID, SCHOOL_NAME, CITY_ID) STUDENTS (ID, STUDENT_NAME, SCHOOL_ID). Я хочу перечислить школы в определенном городе вместе с количеством учащихся (название школы|.
Sql запрос, чтобы узнать, нет ли мальчиков и девочек в школе правильно ли это select count(*) from schooldata group by sex;
В деревне есть школа. Он имеет N классов. В один прекрасный день кто-то пожертвовал B blue berry cheese cakes школам. Теперь вам нужно разделить эти пирожные таким образом, чтобы: Каждый класс.
Таблица классов имеет столбцы class_id и class_name со значениями ff: class_id | class_Name 1 | Algebra 2 | History 3 | PE Таблица студентов имеет столбцы student_id и student_name : student_id |.
У меня есть 3 таблицы с данными учащихся из разных школ. Я должен получить количество учеников в каждой школе в одной таблице (готово), а затем, используя заданные вероятности, вычислить, сколько.
У меня есть словарь (studentPerf), в котором есть все ученики в школе, с кортежами в качестве ключей. Я хочу подсчитать количество учащихся мужского пола и количество учащихся женского пола в школе.
В этой таблице приведены записи учащихся, входящих и выходящих из школы. IN представляет учащегося, поступающего в школу, и OUT представляет учащегося, покидающего школу. Мне интересно, как.
Я попытался запустить этот запрос на MySQL workbench, если я удалил условие GROUP BY, то он выводит таблицу данных с общим количеством учащихся во всех классах. Мне нужно общее количество на класс.
на каком факультете обучается максимальное число студентов
помогите создать запросы)))). и подскажите где можно подробно почитать про такие запросы.
2. Определить на каком факультете обучается максимальное число студентов.
3. Определить среднее число студентов в группах факультета ФИТР.
4. Подсчитать число фамилий, которые начинаются на «Иван»
5. Найти фамилию старосты группы, в которой меньше всего студентов.
6. Определить сколько студентов с именем Денис обучается на АТФ,
7. Определить среднюю численность групп на каждом факультете.
БД «Списки студентов»
1. Факультеты(Шифр_фак,Название,Декан,Телефон_деканата).
2. Группы(Шифр_фак,Номер_группы,Староста).
3. Студенты(Фамилия,Имя,Отчество,Личный_номер,Номер_группы,Номе р_приказа_о_зачислении)).
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Определить на каком факультете обучается максимальное число студентов
БД «Списки студентов» 1.FAK(idf,Name,dekan). 2. Group(idG,idK,kurs,name,kilk,kurator).
Определить, на каком факультете обучается больше всего студентов
Дан список из n записей о студентах. Каждая запись содержит следующие поля: фамилия, имя, отчество.
Список записей: найти, на каком факультете обучается больше всего студентов
Дан список из n записей о студентах. Каждая запись содержит следующие поля: фамилия, имя, отчество.
Дан номер группы студента и текущий год. Напечатать, в каком году он поступил и на каком факультете учится
В университете принято, что старшая цифра трехзначного номера студенческой группы обозначает номер.
Тема 3. Язык SQL. Формирование запросов к базе данных
Изучить методы написания сложных SQL-запросов и способы их реализации на сервере MS SQL Server 2000.
Оглавление
Задания
1. Агрегатные функции в языке SQL
Назовем сложными такие запросы на языке SQL, которые требуют для своей реализации использования агрегатных функций или подзапросов. Агрегатные функции используются при дополнительной обработке информации, когда применяется группировка строк запроса по ряду специальных параметров. При выполнении операций группировки строки с одинаковыми значениями столбцов группировки объединяются в одну группу. После этого к созданным группам можно применить стандартные агрегатные функции обработки. Стандарт языка SQL предлагает пять базовых агрегатных функций (табл. 1).
Запросы с группировкой имеют следующий синтаксис:
Select
FROM
Where
GROUP BY
Having
При выполнении запросов с группировкой в результирующий набор данных может быть включен список столбцов группировки и набор агрегатных функций. Именование новых столбцов при этом формируется из имени агрегатных функций и имен столбцов и малоинформативно, поэтому желательно заменить их семантически значимыми наименованиями.
Например, пусть нам необходимо для каждого студента сосчитать количество сданных экзаменов. Сданным считаем экзамен, за который получена оценка, большая чем 2 балла (рис. 1).
Обратите внимание, что в список вывода можно включать либо полный список столбцов группировки и набор агрегатных функций, либо часть списка столбцов группировки и несколько агрегатных функций. При вычислении значений мы могли бы группировать данные по номеру зачетки, но так как нам необходимо выводить фамилию студента и мы должны включить в список вывода именно этот столбец, то мы включаем его и в список столбцов группировки.
Функция Avg() считает среднее арифметическое, но так как при вычислении среднего значения тип результата совпадает с типом столбца, для которого он вычисляется, то мы получим целочисленный результат, который округлен в меньшую сторону.
Например, при вычислении среднего балла за сессию мы получим следующий результат (рис. 2).
Если же мы выведем сумму баллов, полученных каждым студентом на экзамене, и количество полученных им оценок, результат будет таким (рис. 3).
Select count(*) from groops
Мы получили результат: всего 5 групп.
Функция count(distinct ) действует здесь аналогично.
В части HAVING мы можем задать условия отбора групп, то есть там может стоять корректное логическое выражение, позволяющее отобрать из всех сформированных групп те, которые удовлетворяют заданным условиям отбора.
Например, нам необходимо отобрать тех студентов, у которых при сдаче экзаменов было получено более одной пятерки. Данный запрос может быть сформулирован следующим образом (рис. 5).
Задания для самостоятельной работы
Выполнить следующие запросы к учебной базе данных «Сессия»:
2. Принципы создания составных запросов
До сих пор мы не сталкивались с понятием составных запросов. Составным запросом мы будем называть любой запрос, в котором используются результаты другого запроса.
Синтаксически основной и подчиненный запросы подчиняются общим правилам языка SQL, однако подчиненный запрос всегда заключается в круглые скобки.
Пример простого подчиненного запроса: вывести список студентов, которые не получили ни одной двойки на сессии. Этот запрос нельзя решить простым условием сравнения полученной оценки с двойкой. При подобном сравнении происходит построчное сравнение, и если хотя бы одна строка таблицы содержит оценку, отличную от двойки, студент попадает в результирующий список, хотя другая строка с данным студентом уже может содержать оценку 2.
Однако этот запрос можно решить, перефразируя условие следующим образом: найти студентов, номера зачетных книжек которых не входят в множество номеров зачетных книжек, имеющих хотя бы одну двойку при сдаче экзаменов в сессию. И в этом случае мы должны применить вложенный запрос:
select Last_Name
from students
where N_zach not in (select N_zach
from sessia
Where mark = 2)
Встроенные запросы могут быть зависимыми и независимыми.
Зависимым мы назовем такой подчиненный запрос, который имеет различное значение для каждого сравниваемого значения. Приведенный ранее запрос является независимым, потому что подчиненный запрос вычисляется один раз для всех сравниваемых строк. Пример подчиненного зависимого запроса следующий: вывести список студентов, которые сдали все требуемые экзамены в сессию. При построении данного запроса для каждого студента необходимо сравнивать количество экзаменов, которое должно быть сдано, и количество экзаменов, уже сданных каждым студентом.
Для построения запросов подобного типа необходимо задать условия взаимосвязи основного и подчиненного запроса. В основном запросе мы сосчитаем для каждого студента, сколько различных дисциплин у него вынесено на экзамен, а в подчиненном запросе мы сосчитаем, сколько этот студент уже сдал экзаменов на положительные оценки. Условием отбора в результирующий список будет равенство двух полученных чисел. Но для того, чтобы в подчиненном запросе нам считали количество сданных экзаменов именно для нашего текущего студента, мы должны в условие фильтрации подчиненного запроса добавить условие равенства номеров зачетных книжек этих студентов, а именно выражение:
В этом случае общий текс запроса выглядит следующим образом:
select Last_Name
from students, Groops, uch_plan
where students.N_groop = Groops.N_groop
and groops.kod_spec = uch_plan.kod_spec
group by Last_Name, N_zach
Having count(distinct Discipline) = (select count(distinct Discipline)
from sessia
where mark > 2
and sessia.N_zach = students.N_zach)
Задания для самостоятельной работы (продолжение)
3. Зачетный SQL-тест в системе ВУОКСа
В разделе статистики постоянно ведется учет набранных вами баллов. Следите за этим показателем.
В правом нижнем фрейме работает счетчик оставшегося времени. Учет времени ведется на сервере, а на вашем экране отображается состояние счетчика. Не забывайте, что отправить последние ответы надо не позднее чем за минуту до окончания теста, иначе ваши результаты могут быть не засчитаны.
Выполните все задания, предъявите выполненные запросы преподавателю и получите зачет по данной практической работе.