Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону
Round – нельзя использовать, т.к. округление возрастов не работает с ROUND.
SELECT ROUND(
AVG(
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), ‘%Y’) + 0
),
0
) as age
FROM FamilyMembers;
Задание 32
Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).
SELECT AVG(unit_price) as cost
FROM Payments a
JOIN Goods b ON a.good = b.good_id
WHERE good_name LIKE ‘% caviar’
Быстрее сработает этот запрос, потому что из подзапроса меньше выбор. В первом варианте он будет брать каждую строку и сравнивать.
SELECT AVG(UNIT_PRICE) AS COST
FROM PAYMENTS
WHERE GOOD IN (
SELECT GOOD_id
FROM GOODS
WHERE GOOD_NAME LIKE ‘%_CAVIAR’
);
Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ?
SELECT COUNT(DISTINCT(classroom)) AS count
FROM Schedule
WHERE date = ‘2019-09-02T00:00:00.000Z’;
SELECT DISTINCT COUNT(CLASSROOM) AS COUNT
FROM Schedule
WHERE DATE = ‘2019-09-02’;
Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
SELECT *
FROM student
WHERE address LIKE ‘ul. Pushkina%’;
Сколько лет самому молодому обучающемуся ?
SELECT MIN(
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), ‘%Y’) + 0
) AS year
FROM Student;
SELECT TIMESTAMPDIFF(YEAR, BIRTHDAY, CURRENT_dATE) AS YEAR
FROM student
ORDER BY YEAR
LIMIT 1;
Задание 38
Сколько Анн (Anna) учится в школе ?
SELECT COUNT(first_name) as count
FROM Student
WHERE first_name = ‘Anna’;
Задание 40
Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?
SELECT name as subjects
FROM Subject a
JOIN Schedule b ON a.id = b.subject
JOIN Teacher c ON b.teacher = c.id
WHERE last_name = ‘Romashkin’
AND first_name LIKE ‘P%’
AND middle_name LIKE ‘P%’;
Задание 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). Отcортируйте преподавателей по фамилии.
SELECT last_name
FROM Teacher a
JOIN Schedule b ON a.id = b.teacher
JOIN Subject c ON b.subject = c.id
WHERE c.name = ‘Physical Culture’
ORDER BY last_name;
Задание 44
Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
Используйте конструкцию «as max_year» для указания максимального возраста в годах. Это необходимо для корректной проверки.
SELECT MAX(TIMESTAMPDIFF (YEAR, birthday, CURRENT_DATE)) as max_year
FROM Student
JOIN Student_in_class ON Student.id = Student_in_class.student
JOIN Class ON Student_in_class.class = Class.id
WHERE Class.name LIKE «10%»;
Какой(ие) кабинет(ы) пользуются самым большим спросом?
SELECT classroom
FROM Schedule
GROUP BY classroom
HAVING COUNT(classroom) = (
SELECT COUNT(classroom)
FROM Schedule
GROUP BY classroom
ORDER BY COUNT(classroom) DESC
LIMIT 1
)
Задание 47
Сколько занятий провел Krauze 30 августа 2019 г.?
SELECT COUNT(teacher) as count
FROM Schedule
WHERE date = «2019-08-30» and teacher = (
SELECT id
FROM Teacher
WHERE last_name = ‘Krauze’);
Задание 57
Перенести расписание всех занятий на 30 мин. вперед.
UPDATE Timepair
SET DATEADD(MINUTE, 30, MINUTE(start_pair))
Задание 51
Добавьте товар с именем «Cheese» и типом «food» в список товаров (Goods).
В качестве первичного ключа (good_id) укажите количество записей в таблице + 1.
INSERT INTO Goods
SET good_id = (SELECT COUNT(*)+1 FROM Goods AS a),
Good_name = «Cheese»,
Type = (SELECT Good_type_id FROM GoodTypes WHERE good_type_name = «food»);
SELECT MAX(good_id) + 1 INTO @id_add_1
SELECT good_type_id INTO @good_type_id_food
FROM GoodTypes
WHERE good_type_name = «food»;
#SELECT @good_type_id_food
INSERT INTO Goods(good_id, good_name, type)
VALUES (
@id_add_1,
— good_id
‘Cheese’,
— good_name
@good_type_id_food — type
);
SELECT *
FROM Goods;
Add to the list of product types (GoodTypes) a new type of «auto».
INSERT INTO GoodTypes(good_type_id, good_type_name)
SELECT MAX(good_type_id) + 1,
‘auto’
FROM GoodTypes;
Remove all family members whose last name is «Quincey».
DELETE FROM FamilyMembers
WHERE member_name LIKE ‘%Quincey’
Задание 55
Delete the companies that made the least number of flights.
DELETE FROM company
WHERE name IN (SELECT name
FROM (SELECT name
FROM trip
INNER JOIN company
ON trip.company = company.id
GROUP BY name
HAVING Count(trip.id) = (SELECT Min(kolv) AS mini
FROM (SELECT
Count(trip.id) AS kolv
FROM trip
INNER JOIN company
ON trip.company = company.id
GROUP BY name
ORDER BY kolv)k)) o)
— find minimum count
SELECT MIN(count) INTO @min
FROM (
SELECT COUNT(*) count
FROM Trip
GROUP BY company
) a;
— show records with minimum count
SELECT DISTINCT(a.company) AS c
FROM Trip a
JOIN (
SELECT company
FROM Trip
GROUP BY company
HAVING COUNT(*) = @min
) b ON a.company = b.company;
— delete all companies from companies
DELETE FROM Company
WHERE id in (2, 3, 4)
SQL ACADEMY ответы и решения заданий (часть 2, задания 23-44)
SQL Academy (ответы и решения заданий 23-44)
ВКонтакте WhatsApp Pinterest Facebook Email
Задание 23. Найдите самый дорогой деликатес (delicacies) и выведите его стоимость.
SELECT g.good_name, p.unit_price FROM Goods AS g JOIN Payments AS p ON (g.good_id=p.good) JOIN GoodTypes AS gt ON (g.type=gt.good_type_id) WHERE p.unit_price = (SELECT MAX(p.unit_price) FROM Payments AS p JOIN Goods AS g ON (g.good_id=p.good) JOIN GoodTypes AS gt ON (g.type=gt.good_type_id) WHERE gt.good_type_name='delicacies');Задание 24. Определить кто и сколько потратил в июне 2005.
SELECT member_name, SUM(amount*unit_price) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id=p.family_member WHERE MONTH(date) = 06 AND YEAR(date) = 2005 GROUP BY member_name;Задание 25. Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года
SELECT good_name FROM Goods WHERE good_id NOT IN (SELECT good FROM Payments WHERE YEAR(date) = 2005);Задание 26. Определить группы товаров, которые не приобретались в 2005 году
SELECT good_type_name FROM GoodTypes WHERE good_type_id NOT IN ( SELECT good_type_id FROM GoodTypes JOIN Goods ON good_type_id=type JOIN Payments ON good_id=good WHERE YEAR(date)=2005);Задание 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_id=good WHERE YEAR(date)=2005 GROUP BY good_type_name;Задание 28. Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow)?
SELECT COUNT(*) 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 Pass_in_trip.passenger=Passenger.id JOIN Trip ON Trip.id=Pass_in_trip.trip WHERE town_to='Moscow' AND plane='TU-134';Задание 30. Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности.
SELECT trip, COUNT(Passenger) as count FROM Pass_in_trip GROUP BY trip ORDER BY count DESC;Задание 31. Вывести всех членов семьи с фамилией Quincey.
SELECT * FROM FamilyMembers WHERE member_name LIKE '%Quincey';Задание 32. Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону.
SELECT FLOOR(AVG(YEAR(CURRENT_DATE) - YEAR(birthday))) AS age FROM FamilyMembers;Задание 33. Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).
SELECT AVG(unit_price) AS cost FROM Payments WHERE good IN (SELECT good_id FROM Goods WHERE good_name LIKE '%caviar');Задание 34. Сколько всего 10-ых классов
SELECT COUNT(name) AS count FROM Class WHERE name LIKE '10%';Задание 35. Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях?
SELECT COUNT(classroom) AS count FROM Schedule WHERE date='2019-09-02';Задание 36. Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
SELECT * FROM Student WHERE address LIKE 'ul. Pushkina%';Задание 37. Сколько лет самому молодому обучающемуся ?
SELECT MIN(TIMESTAMPDIFF(YEAR,birthday,CURRENT_DATE)) AS year FROM Student;Задание 38. Сколько Анн (Anna) учится в школе?
SELECT COUNT(first_name) AS count FROM Student WHERE first_name='Anna';Задание 39. Сколько обучающихся в 10 B классе ?
SELECT COUNT(student) AS count FROM Student_in_class JOIN Class ON Student_in_class.class=Class.id WHERE Class.name = '10 B';Задание 40. Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?
SELECT name AS subjects FROM Subject JOIN Schedule ON Subject.id=Schedule.subject JOIN Teacher ON Schedule.teacher=Teacher.id WHERE Teacher.last_name='Romashkin' AND Teacher.first_name LIKE 'P%' AND Teacher.middle_name LIKE 'P%';Задание 41. Во сколько начинается 4-ый учебный предмет по расписанию ?
SELECT start_pair FROM Timepair WHERE >Задание 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 Schedule.subject=Subject.id WHERE Subject.name = 'Physical Culture' ORDER BY Teacher.last_nameЗадание 44. Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
SELECT MAX(TIMESTAMPDIFF(YEAR,birthday,CURRENT_DATE)) as max_year FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student JOIN Class ON Student_in_class.class=Class.id WHERE Class.name LIKE '10%';Нормы учебной нагрузки в школе
Как составляется расписание в школе? Какое максимальное количество уроков допустимо, и есть ли ограничения по дополнительной нагрузке? Кто устанавливает продолжительность занятий и перемен? Знакомимся с требованиями СанПиН и выясняем, как должен выглядеть день современного школьника в соответствии с действующим законодательством Российской Федерации.
Учебная нагрузка в школе – больная тема для многих семей. Дети жалуются, что устают и ничего не успевают. Родители возмущаются, что ребята портят зрение и осанку, сидя допоздна над домашними заданиями. Освоить весь объем программы в самой обычной школе зачастую не под силу даже способному ученику. Почему это происходит? Ведь режим учебного дня школьника регулируется нормами СанПиН («Санитарно-эпидемиологические требования к условиям и организации обучения в общеобразовательных учреждениях»), и справляться с нагрузкой должен каждый среднестатистический ребенок? Разбираемся вместе! С вами Mr. Great, и мы говорим о нормах режима учебного дня в школе.
Начало и окончание занятий
Согласно требованиям СанПиН, учебный день ни при каких обстоятельствах не должен начинаться раньше 8 часов утра. Однако, на практике известны случаи, когда учителя ставят так называемые «нулевые уроки» до начала основных занятий и буквально принуждают детей приходить в школу раньше. Безусловно, цели преследуются благие, и, вероятно, польза от дополнительных занятий есть, однако, с точки зрения санитарно-эпидемиологических норм это не допускается. Поспать любят все, а дети и подростки особенно. Учеба во вторую смену – мечта для тех, кому трудно рано вставать. Но возможность выспаться, пожалуй, единственный плюс подобного расписания. Заставить себя делать домашние задания с утра могут далеко не все, а родители, которые проконтролировали бы процесс, на работе. 
Учиться после обеда могут только ребята из общеобразовательных школ, ученики учебных заведений с углубленным изучением тех или иных предметов лишены такой возможности. Ученики 1-х, 5-х, 9-х и 11-х классов также должны учиться строго в первую смену.
Расписание уроков
- В начальной школе сложные и простые предметы должны чередоваться. К сложным относятся русский язык, математика, иностранный язык, информатика, природоведение. Простыми считаются изобразительное искусство, музыка, физкультура и труд;
- В средних и старших классах попеременно ставятся предметы гуманитарного и естественно-математического цикла;
- С учетом мозговой активности детей планируется место наиболее трудных предметов в расписании: первоклашкам их ставят на 2-ой урок, учащимся вторых-четвертых классов на 2-ой и 3-ий уроки, а вот школьники среднего и старшего звена грызут гранит самых сложных дисциплин со 2-ого по 4-ый уроки;
- В течение дня недопустимо больше одной контрольной работы независимо от возраста учащихся;
- Сдвоенные уроки разрешаются только в средней и старшей школе. Для учащихся начальных классов пара может быть только по физкультуре, например, если дети занимаются лыжным спортом или плаванием;
- Расписание обязательных и факультативных уроков составляется отдельно. Между окончанием последнего обязательного занятия и началом первого дополнительного обязателен перерыв не менее 45 минут;
- Продолжительность урока в школе не должна превышать 45 минут. Исключение составляют занятия первоклассников – в их случае урок длится не более 40 минут.
Составить сбалансированное расписание и при этом соблюсти все требования удается не всегда. Одного взгляда на расписание бывает достаточно, чтобы понять – какие-то дни недели перегружены сложными предметами, а другие можно смело пропускать без ущерба образованию.
Особый интерес вызывает градация предметов по уровню сложности. Согласно СанПиН, для пятиклассника математика (10 баллов из 10 возможных) ровно в два раза сложнее истории (5 баллов), а литература, труд и ритмика находятся на одном уровне (4 балла). Классификация, безусловно, спорная!
Учебная нагрузка
Первоклассники должны проводить в школе не более 21 академического часа в неделю. Старшеклассники – не более 34 часов при пятидневке.
Учебная нагрузка по СанПиН не выглядит чрезмерной:
- для первоклассников 4 урока ежедневно + 5 уроков в один из дней недели за счет физкультуры;
- для учеников вторых-четвертых классов 5 уроков ежедневно + 6 уроков раз в недели за счет физкультуры;
- с пятого по седьмой класс – не более 7 уроков ежедневно;
- с восьмого по одиннадцатый классы – не более 8 уроков ежедневно.
В четверг или пятницу нагрузка меньше, чем в другие дни. Своеобразный «облегченный» день рекомендуется ставить в расписание, чтобы позволить ребятам немного расслабиться, избежать переутомления и сохранять оптимальную работоспособность в другие дни.
Но, к сожалению, освоить программу на основных занятиях не получается, приходится брать дополнительные часы. Педагоги государственных школ разводят руками – учебный план не укладывается в установленные рамки недельной нагрузки.
Отдельное внимание СанПиН уделяет первоклассникам. Считается, что первый год в школе – большой стресс для любого ребенка, поэтому режим должен быть насколько это возможно комфортным. В первом классе дети должны учиться в первую смену, допустимо использовать только 5-дневку. В первые два месяца учебы в расписании всего 3 урока в день. С ноября по май – уже по 4 урока. В разгар учебного дня детям устраивают динамическую паузу – возможность поиграть, побегать на свежем воздухе, физическая активность помогает отвлечься и вернуться к урокам с новыми силами. Первоклассникам не задают домашние задания и не ставят оценки.
Перемены
Перемены важны для обучающихся не меньше, чем уроки! Поэтому их длительность и количество также регламентируется. Перемена не может длиться менее 10 минут. После второго и третьего урока положены большие перемены – по 20-30 минут. Самый длительный дневной перерыв на отдых – 45-минут. Он включает двигательные активности, которые в зависимости от погодных условий проводятся на улице или в спортивном зале школы. Младшие школьники бегают и играют на школьном дворе, старшеклассники могут просто пообщаться и подышать свежим воздухом.
Если школа работает в две смены, пересменка должна длиться не менее 30 минут, обязательна влажная уборка.
Резюме
Соблюсти все требования СанПиН – обязанность каждой образовательной организации.
Частным школам легче справиться с этой задачей – в малых группах дети быстрее усваивают материал и меньше устают, а материально-техническая база позволяет сделать отдых во время перемен более продуктивным.
Не стоит забывать, что современное образование немыслимо без внеурочной деятельности. Занятия спортом, музыкой, творчеством, программированием – внеклассная часть программы для подавляющего большинства детей. А ведь чтобы добраться до кружков и секций тоже нужно время и силы! Ученики частных школ в выигрыше и здесь. Например, на кампусе Heritage School ребята могут заниматься йогой, керамикой, театральным искусством, музыкой, футболом и шахматами. Никуда ехать после основных занятий не нужно.
Обучение в школе – полноценная работа для ребенка. Но, как и любая работа, она может быть любимой и приносить радость или наоборот, высасывать все силы и вызывать раздражение. Чтобы учебная нагрузка была сбалансированной и не стала ребенку в тягость, нужно не только выполнять требования СанПиН, но и учитывать характер, темперамент, склонности учащегося к определенным предметам и видам деятельности. К сожалению, это возможно далеко не в каждой школе. Соблюдая нормативы, важно не забывать про индивидуальный подход.
Всегда ваш,
Mr. Great
Saved searches
Use saved searches to filter your results more quickly
Cancel Create saved search
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.
IvanPadoltsev/SQL-Academy
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
Switch branches/tags
Branches Tags
Could not load branches
Nothing to show
Could not load tags
Nothing to show
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Cancel Create
- Local
- Codespaces
HTTPS GitHub CLI
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
d86953d Jun 7, 2021
Git stats
Files
Failed to load latest commit information.
Latest commit message
Commit time
June 7, 2021 17:29
June 7, 2021 17:32
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%’;
Задание 45: Какой(ие) кабинет(ы) пользуются самым большим спросом?
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%’;
Задание 60: Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов.
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;
Задание 64: Выведите имена всех пар пассажиров, летевших вместе на одном рейсе два или более раз, и количество таких совместных рейсов. В passengerName1 разместите имя пассажира с наименьшим идентификатором.
Задание 65: Необходимо вывести рейтинг для комнат, которые хоть раз арендовали, как среднее значение рейтинга отзывов округленное до целого вниз. SELECT room_id, FLOOR(AVG(rating)) AS rating FROM Reservations JOIN Reviews ON Reviews.reservation_id=Reservations.id GROUP BY room_id;
Задание 66: Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат. SELECT home_type, address, COALESCE(SUM(DATEDIFF(end_date, start_date)), 0) as days, COALESCE(SUM(Reservations.total), 0) AS total_fee FROM Reservations RIGHT JOIN Rooms ON Rooms.id=Reservations.room_id WHERE has_tv !=0 AND has_internet !=0 AND has_kitchen !=0 AND has_air_con !=0 GROUP BY address, home_type;