SQL ACADEMY ответы и решения заданий (часть 3, задания 45-66)

Ниже представлены решения заданий № 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;