Ниже представлены решения заданий № 45-66 из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org).
Ответы на задания 1-22 (часть 1) здесь.
Ответы на задания 23-44 (часть 2) тут.
Ответы на задания 67-76 (часть 4) тут.
Задание 45. Какой(ие) кабинет(ы) пользуются самым большим спросом?
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)
Задание 46. В каких классах введет занятия преподаватель “Krauze” ?
SELECT DISTINCT name
FROM Class
JOIN Schedule
ON Class.id=Schedule.class
JOIN Teacher
ON Schedule.teacher=Teacher.id
WHERE Teacher.last_name='Krauze';
Задание 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');
Задание 48. Выведите заполненность классов в порядке убывания
SELECT c.name, COUNT(sc.student) AS count
FROM Class AS c
JOIN Student_in_class AS sc
ON c.id=sc.class
GROUP BY c.id
ORDER BY count DESC;
Задание 49. Какой процент обучающихся учится в 10 A классе ?
SELECT COUNT(student) * 100 / (SELECT COUNT(student) FROM Student_in_class) AS percent
FROM Student_in_class
JOIN Class
ON Student_in_class.class=Class.id
WHERE name='10 A';
Задание 50. Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону.
SELECT FLOOR(COUNT(id)*100/(SELECT COUNT(id) FROM Student)) AS percent
FROM Student
WHERE YEAR(birthday)=2000;
Задание 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');
Задание 52. Добавьте в список типов товаров (GoodTypes) новый тип “auto”. В качестве первичного ключа (good_type_id) укажите количество записей в таблице + 1
INSERT INTO GoodTypes
SET good_type_id=(SELECT COUNT(*)+1 FROM GoodTypes AS a),
good_type_name='auto';
Задание 53. Измените имя “Andie Quincey” на новое “Andie Anthony”.
UPDATE FamilyMembers
SET member_name='Andie Anthony'
WHERE member_name='Andie Quincey';
Задание 54. Удалить всех членов семьи с фамилией “Quincey”.
DELETE FROM FamilyMembers
WHERE member_name LIKE '%Quincey';
Задание 55. Удалить компании, совершившие наименьшее количество рейсов.
DELETE FROM Company
WHERE Company.id IN (
SELECT company FROM Trip
GROUP BY company
HAVING COUNT(id) = (SELECT MIN(count) FROM (SELECT COUNT(id) AS count FROM Trip GROUP BY company) AS min_count)
);
Задание 56. Удалить все перелеты, совершенные из Москвы (Moscow).
DELETE FROM Trip
WHERE town_from='Moscow';
Задание 57. Перенести расписание всех занятий на 30 мин. вперед.
UPDATE Timepair
SET start_pair=start_pair + INTERVAL 30 MINUTE,
end_pair=end_pair + INTERVAL 30 MINUTE;
Задание 58. Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу “11218, Friel Place, New York”, от имени “George Clooney”.В качестве первичного ключа (id) укажите количество записей в таблице + 1.
INSERT INTO Reviews
SET id=(SELECT COUNT(*)+1 FROM Reviews AS a),
rating=5,
reservation_id= (SELECT r.id FROM Reservations AS r
JOIN Rooms ON r.room_id=Rooms.id
JOIN Users ON r.user_id=Users.id
WHERE address='11218, Friel Place, New York'
AND name='George Clooney');
Задание 59. Вывести пользователей, указавших Белорусский номер телефона ? Телефонный код Белоруссии +375.
SELECT *
FROM Users
WHERE phone_number LIKE '+375%';
Задание 60. Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов.
SELECT teacher
FROM Schedule
JOIN Class
ON Schedule.class=Class.id
WHERE name LIKE '11%'
GROUP BY teacher
HAVING COUNT(DISTINCT name) = 2;
Задание 61. Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года.
SELECT DISTINCT Rooms.*
FROM Rooms
JOIN Reservations
ON Rooms.id=Reservations.room_id
WHERE WEEK(start_date, 1) = 12 AND YEAR(start_date)=2020;
Задание 62. Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты.
Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён.
SELECT SUBSTRING_INDEX(email,'@',-1) AS domain,
COUNT(SUBSTRING_INDEX(email,'@',-1)) AS count
FROM Users
GROUP BY domain
ORDER BY count DESC, domain;
Задание 63. Выведите отсортированный список (по возрастанию) имен студентов в виде Фамилия.И.О.
SELECT CONCAT(last_name, '.', LEFT(first_name, 1), '.', LEFT(middle_name, 1), '.') AS name
FROM Student
ORDER BY last_name, first_name;
Задание 64. Выведите имена всех пар пассажиров, летевших вместе на одном рейсе два или более раз, и количество таких совместных рейсов.
В passengerName1 разместите имя пассажира с наименьшим идентификатором.
SELECT passengerName1, passengerName2, COUNT(tr1) AS COUNT
FROM (SELECT Passenger.id AS p1, name AS passengerName1, trip AS tr1
FROM Passenger
INNER JOIN Pass_in_trip
ON Passenger.id = Pass_in_trip.passenger
GROUP BY Passenger.id, name, trip) AS UP1
INNER JOIN (SELECT Passenger.id AS p2, name AS passengerName2, trip AS tr2
FROM Passenger
INNER JOIN Pass_in_trip
ON Passenger.id = Pass_in_trip.passenger
GROUP BY Passenger.id, name, trip) AS UP2
ON UP1.tr1 = UP2.tr2
WHERE (p1<p2) GROUP BY passengerName1, passengerName2
HAVING (COUNT(tr1)>1);
Задание 65. Необходимо вывести рейтинг для комнат, которые хоть раз арендовали, как среднее значение рейтинга отзывов округленное до целого вниз.
SELECT room_id, FLOOR(AVG(rating)) AS rating
FROM Reservations
JOIN Reviews
ON Reservations.id=Reviews.reservation_id
GROUP BY room_id
ORDER BY rating DESC; -- Сортировка не обязательна по условию задачи
Задание 66. Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат.
Если комната не сдавалась, то количество дней и сумму вывести как 0.
SELECT home_type, address, IFNULL(SUM(TIMESTAMPDIFF(DAY,start_date,end_date)), 0) AS days, IFNULL(SUM(total), 0) AS total_fee
FROM Rooms
LEFT JOIN Reservations
ON Rooms.id=Reservations.room_id
WHERE (has_tv, has_internet, has_kitchen, has_air_con) = (1,1,1,1)
GROUP BY Rooms.id;
Спасибо за ответы, очень помогли:)