Решение модуля 3.3 из курса «Интерактивный тренажер по SQL»

Разобрали ответы и решения урока 3.3(База данных «Абитуриент», запросы на выборку) под курс «Интерактивный тренажер по SQL».

Предыдущий разбор модуля 3.2 следующий разбор модуль 3.4

Вывести абитуриентов, которые хотят поступать на образовательную программу «Мехатроника и робототехника» в отсортированном по фамилиям виде.

SELECT name_enrollee
FROM enrollee INNER JOIN program_enrollee USING(enrollee_id)
INNER JOIN program USING(program_id)
WHERE name_program = 'Мехатроника и робототехника'
ORDER BY name_enrollee 

Вывести образовательные программы, на которые для поступления необходим предмет «Информатика». Программы отсортировать в обратном алфавитном порядке.

SELECT name_program FROM program
INNER JOIN program_subject USING(program_id)
INNER JOIN subject USING(subject_id)
WHERE name_subject = 'Информатика'
ORDER BY name_program DESC;

Выведите количество абитуриентов, сдавших ЕГЭ по каждому предмету, максимальное, минимальное и среднее значение баллов по предмету ЕГЭ. Вычисляемые столбцы назвать КоличествоМаксимумМинимумСреднее. Информацию отсортировать по названию предмета в алфавитном порядке, среднее значение округлить до одного знака после запятой.

SELECT name_subject, 
       COUNT(enrollee_id) AS Количество,
       MAX(result) AS Максимум,
       MIN(result) AS Минимум,
       ROUND(AVG(result), 1) AS Среднее
FROM subject
       INNER JOIN enrollee_subject USING(subject_id)
GROUP BY 1
ORDER BY 1;

Вывести образовательные программы, для которых минимальный балл ЕГЭ по каждому предмету больше или равен 40 баллам. Программы вывести в отсортированном по алфавиту виде.

SELECT name_program
FROM (
    SELECT name_program, MIN(min_result) r
    FROM program
        NATURAL JOIN program_subject
    GROUP BY 1
    HAVING r >= 40) table1
ORDER BY 1

Вывести образовательные программы, которые имеют самый большой план набора,  вместе с этой величиной.

SELECT name_program, plan
FROM program
WHERE plan = (SELECT MAX(plan) FROM program) 

Посчитать, сколько дополнительных баллов получит каждый абитуриент. Столбец с дополнительными баллами назвать Бонус. Информацию вывести в отсортированном по фамилиям виде.

SELECT name_enrollee,
       IFNULL(SUM(achievement.bonus),0) AS Бонус
FROM enrollee
     LEFT JOIN enrollee_achievement USING(enrollee_id)
     LEFT JOIN achievement USING(achievement_id)
GROUP BY 1
ORDER BY 1;

Выведите сколько человек подало заявление на каждую образовательную программу и конкурс на нее (число поданных заявлений деленное на количество мест по плану), округленный до 2-х знаков после запятой. В запросе вывести название факультета, к которому относится образовательная программа, название образовательной программы, план набора абитуриентов на образовательную программу (plan), количество поданных заявлений (Количество) и Конкурс. Информацию отсортировать в порядке убывания конкурса.

SELECT name_department, name_program, plan,
	COUNT(*) AS Количество,
	ROUND(COUNT(*)/plan ,2) AS Конкурс
FROM program_enrollee
	JOIN program USING (program_id)
	JOIN department USING (department_id)
GROUP BY name_department, name_program, plan
ORDER BY plan, name_program DESC

Вывести образовательные программы, на которые для поступления необходимы предмет «Информатика» и «Математика» в отсортированном по названию программ виде.

SELECT name_program
FROM program
    JOIN program_subject ps USING(program_id)
    JOIN subject s ON ps.subject_id=s.subject_id AND name_subject IN ('Информатика','Математика')
GROUP BY 1
HAVING COUNT(name_subject)=2
ORDER BY 1

Посчитать количество баллов каждого абитуриента на каждую образовательную программу, на которую он подал заявление, по результатам ЕГЭ. В результат включить название образовательной программы, фамилию и имя абитуриента, а также столбец с суммой баллов, который назвать itog. Информацию вывести в отсортированном сначала по образовательной программе, а потом по убыванию суммы баллов виде.

SELECT p.name_program, e.name_enrollee, SUM(es.result) AS itog
FROM program_subject ps
    INNER JOIN program p USING(program_id)
    INNER JOIN program_enrollee pe USING(program_id)
    INNER JOIN enrollee e USING(enrollee_id)
    INNER JOIN enrollee_subject es ON es.subject_id = ps.subject_id AND
                                  es.enrollee_id = pe.enrollee_id
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

Вывести название образовательной программы и фамилию тех абитуриентов, которые подавали документы на эту образовательную программу, но не могут быть зачислены на нее. Эти абитуриенты имеют результат по одному или нескольким предметам ЕГЭ, необходимым для поступления на эту образовательную программу, меньше минимального балла. Информацию вывести в отсортированном сначала по программам, а потом по фамилиям абитуриентов виде.

SELECT name_program, name_enrollee
FROM enrollee
     JOIN program_enrollee USING(enrollee_id)
     JOIN program USING(program_id)
     JOIN program_subject USING(program_id)
     JOIN subject USING(subject_id)
     JOIN enrollee_subject USING(subject_id)
WHERE enrollee_subject.enrollee_id = enrollee.enrollee_id and result < min_result
ORDER BY 1, 2

Придумайте один или несколько запросов на выборку для предметной области «Абитуриент» (в таблицы занесены данные, как на первом шаге урока). Проверьте, правильно ли они работают.

SELECT * FROM department;

Если у вас не отображается решение последних задач, значит у вас включен блокировщик рекламы который вырезает эти ответы

Понравилась статья? Поделиться с друзьями:
Подписаться
Уведомить о
guest

3 комментариев
Новые
Старые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии
King
King
5 месяцев назад

Последнее
— 1. Вывести список всех абитуриентов и программ, на которые они поступили:
SELECT e.name_enrollee, p.name_program
FROM enrollee e
JOIN program_enrollee pe ON e.enrollee_id = pe.enrollee_id
JOIN program p ON pe.program_id = p.program_id;

— 2. Получить список программ и количество абитуриентов, поступивших на каждую программу:
SELECT p.name_program, COUNT(pe.enrollee_id) AS enrollee_count
FROM program p
LEFT JOIN program_enrollee pe ON p.program_id = pe.program_id
GROUP BY p.name_program;

— 3. Вывести имена всех абитуриентов, которые получили какие-либо достижения:
SELECT e.name_enrollee, a.name_achievement
FROM enrollee e
JOIN enrollee_achievement ea ON e.enrollee_id = ea.enrollee_id
JOIN achievement a ON ea.achievement_id = a.achievement_id;

— 4. Получить список абитуриентов, чей результат по физике выше среднего:
SELECT e.name_enrollee, es.result
FROM enrollee e
JOIN enrollee_subject es ON e.enrollee_id = es.enrollee_id
JOIN subject s ON es.subject_id = s.subject_id
WHERE s.name_subject = ‘Физика’ AND es.result > (SELECT AVG(result) FROM enrollee_subject WHERE subject_id = 3);

— 5. Вывести имена абитуриентов и сумму баллов их достижений:
SELECT e.name_enrollee, SUM(a.bonus) AS total_bonus
FROM enrollee e
JOIN enrollee_achievement ea ON e.enrollee_id = ea.enrollee_id
JOIN achievement a ON ea.achievement_id = a.achievement_id
GROUP BY e.name_enrollee;

Artem
Artem
7 месяцев назад

Вывести название образовательной программы и фамилию тех абитуриентов, которые подавали документы на эту образовательную программу, но не могут быть зачислены на нее. Эти абитуриенты имеют результат по одному или нескольким предметам ЕГЭ, необходимым для поступления на эту образовательную программу, меньше минимального балла. Информацию вывести в отсортированном сначала по программам, а потом по фамилиям абитуриентов виде.
select name_program, name_enrollee
from enrollee
INNER JOIN program_enrollee ON enrollee.enrollee_id = program_enrollee.enrollee_id
INNER JOIN program ON program.program_id = program_enrollee.program_id
INNER JOIN program_subject ON program.program_id = program_subject.program_id
INNER JOIN subject ON subject.subject_id = program_subject.subject_id
INNER JOIN enrollee_subject ON subject.subject_id = enrollee_subject.subject_id 
and enrollee_subject.enrollee_id = enrollee.enrollee_id
where result < min_result
ORDER BY name_program asc;

еее бади, лайт вейт

olga
olga
9 месяцев назад

Посчитать количество баллов каждого абитуриента на каждую образовательную программу, на которую он подал заявление, по результатам ЕГЭ. В результат включить название образовательной программы, фамилию и имя абитуриента, а также столбец с суммой баллов, который назвать itog. Информацию вывести в отсортированном сначала по образовательной программе, а потом по убыванию суммы баллов виде.

select name_program, name_enrollee, sum(result) AS itog
from enrollee
INNER JOIN program_enrollee ON enrollee.enrollee_id = program_enrollee.enrollee_id
INNER JOIN program ON program.program_id = program_enrollee.program_id
INNER JOIN program_subject ON program.program_id = program_subject.program_id
INNER JOIN subject ON subject.subject_id = program_subject.subject_id
INNER JOIN enrollee_subject ON subject.subject_id = enrollee_subject.subject_id 
and enrollee_subject.enrollee_id = enrollee.enrollee_id
group by 1,2 
order by 1,3 desc

3
0
Оставьте комментарий! Напишите, что думаете по поводу статьи.x