Определить группы товаров которые не приобретались в 2005 году
Перейти к содержимому

Определить группы товаров которые не приобретались в 2005 году

  • автор:

SQL ACADEMY ответы и решения заданий (часть 2, задания 23-44)

SQL Academy (ответы и решения заданий 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%';

SQL получение записей не входящих в другие таблицы

Елена Талалаева Ученик (180) Аглая Шниц, equipment — это группа товаров, ответ который мы должны получить. То есть в одну группу входят разные товары, нам нужно найти ту группу , из которой не покупался ни один товар, а по вашему запросу , если хотя бы один товар из группы товаров не покупался, она выдает эту группу в ответ

Задачи¶

Тут я складываю решение задач по SQL, может кому-то понадобится.

Именно для PostgreSQL есть отдельный раздел тут

Cсылки на задачи¶

  • SQL. Занимательные задачи — отличный список задач с схемой HR отдела. Затрагивает очень много кейсов — от фильтрация, группировка, агрегация. Мастхев для прорешивания в первую очередь.
  • SQL-EX — гигабазовый онлаин тренажер с >500 задач.
  • SQL Academy — ещё один онлаин тренажер.
  • Leetcode — Задачи по БД на литкоде.
  • Интерактивный тренажер по SQL, Stepik — подойдет прям для новичков
  • SQLBolt

Решения¶

Задачи с sql-academy.org¶

  1. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
    Задача на знание встроенных функций, решается при помощи TIMEDIFF :
 SELECT town_to, TIMEDIFF(time_in, time_out) as flight_time FROM trip WHERE town_from = 'Paris' 
 SELECT * FROM trip WHERE time_out BETWEEN "1900-01-01T10:00:00" AND "1900-01-01T14:00:00" 
 SELECT name FROM passenger WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM passenger); 
 SELECT member_name FROM FamilyMembers ORDER BY birthday LIMIT 1 
 SELECT good_name, unit_price FROM Payments JOIN Goods ON Goods.good_id = Payments.good JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type WHERE GoodTypes.good_type_name = "delicacies" ORDER BY unit_price DESC LIMIT 1 
 SELECT fm.status, fm.member_name, SUM(p.amount * p.unit_price) as costs FROM FamilyMembers as fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods as g ON g.good_id = p.good JOIN GoodTypes AS gt ON gt.good_type_id = g.type WHERE gt.good_type_name = "entertainment" GROUP BY fm.status, fm.member_name 
 SELECT g.good_name FROM Payments as p JOIN Goods as g ON p.good = g.good_id GROUP BY g.good_name HAVING COUNT(good_name) > 1 
 SELECT name FROM Passenger GROUP BY name HAVING COUNT(name) > 1 
 SELECT name, count(pt.id) as count FROM Pass_in_trip AS pt JOIN Passenger AS p ON pt.passenger = p.id GROUP BY pt.passenger ORDER BY count desc, name asc 
 SELECT member_name, status, SUM(p.amount * p.unit_price) as costs FROM Payments AS p JOIN FamilyMembers AS fm ON fm.member_id = p.family_member WHERE p.date BETWEEN "2005-01-01" AND "2005-12-31" GROUP BY p.family_member 
 SELECT fm.member_name, sum(p.amount * p.unit_price) AS costs FROM Payments AS p JOIN FamilyMembers AS fm ON fm.member_id = p.family_member WHERE p.date BETWEEN "2005-06-01" AND "2005-06-30" GROUP BY p.family_member 
 SELECT good_name FROM Goods AS g WHERE g.good_id NOT IN ( SELECT good FROM Payments AS p WHERE YEAR(p.date) = 2005 ) 
 SELECT good_type_name FROM GoodTypes WHERE good_type_name NOT IN ( SELECT DISTINCT gt.good_type_name FROM Payments as p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes AS gt ON g.type = gt.good_type_id WHERE YEAR(p.date) = 2005 ) 
 SELECT gt.good_type_name, SUM(p.amount * p.unit_price) as costs FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes AS gt ON g.type = gt.good_type_id WHERE YEAR(p.date) = 2005 GROUP BY gt.good_type_id 
 SELECT DISTINCT p.name FROM Trip AS t JOIN Pass_in_trip AS pt ON t.id = pt.trip JOIN Passenger AS p ON pt.passenger = p.id WHERE t.town_to = "Moscow" AND t.plane = "TU-134" 
 SELECT pt.trip, count(pt.trip) as count from Pass_in_trip AS pt GROUP BY pt.trip ORDER BY count(pt.trip) DESC 
 SELECT * FROM FamilyMembers WHERE member_name LIKE "% Quincey" 
 SELECT AVG(p.unit_price) as cost FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id WHERE g.good_name IN ("red caviar", "black caviar") 
 SELECT COUNT(classroom) as count FROM Schedule WHERE date = "2019-09-02" 
 SELECT MIN(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE)) AS year FROM Student 
 SELECT FLOOR(AVG(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE))) AS age FROM FamilyMembers 
 SELECT s.name as subjects FROM Subject AS s JOIN Schedule AS sc ON sc.subject = s.id JOIN teacher AS t On sc.teacher = t.id WHERE t.last_name = "Romashkin" 
 SELECT DISTINCT TIMEDIFF( ( SELECT end_pair FROM Timepair WHERE id = 4 ), ( SELECT start_pair FROM Timepair WHERE id = 2 ) ) as time from Timepair 
 SELECT last_name from Teacher AS t JOIN Schedule AS sc ON sc.teacher = t.id JOIN Subject as s ON sc.subject = s.id WHERE s.name = "Physical Culture" ORDER BY t.last_name 
 SELECT MAX( TIMESTAMPDIFF(YEAR, s.birthday, CURRENT_DATE) ) as max_year FROM Student_in_class AS sc JOIN Class AS c ON sc.class = c.id JOIN Student AS s ON sc.student = s.id WHERE c.name LIKE "10%" 
 SELECT classroom FROM Schedule GROUP BY classroom HAVING COUNT(classroom) = ( SELECT COUNT(classroom) FROM Schedule GROUP BY classroom ORDER BY classroom DESC LIMIT 1 ) 
 SELECT DISTINCT name FROM Class AS c JOIN Schedule AS sc ON sc.class = c.id JOIN Teacher AS t ON sc.teacher = t.id WHERE t.last_name = "Krauze" 
 SELECT count(id) as count FROM Schedule WHERE date = "2019-08-30" AND teacher = ( SELECT id FROM Teacher WHERE last_name = "Krauze" ) 
 SELECT c.name as name, count(c.id) as count FROM Student_in_class AS sc JOIN Class AS c ON sc.class = c.id JOIN Student AS s ON sc.student = s.id GROUP BY c.name ORDER BY count(c.id) DESC 
 SELECT COUNT(student) * 100 / ( SELECT COUNT(student) FROM Student_in_class ) as percent FROM Student_in_class AS si JOIN Class AS c ON si.class = c.id WHERE c.name = "10 A" 
 SELECT FLOOR(COUNT(id) * 100 / ( SELECT COUNT(id) FROM Student )) as percent FROM Student WHERE YEAR(birthday) = 2000 
 SELECT sc.teacher FROM Schedule AS sc JOIN Class AS c ON c.id = sc.class WHERE c.name LIKE "11%" GROUP BY sc.teacher HAVING COUNT(DISTINCT c.name) = 2 
 DELETE FROM Company WHERE Company.id IN ( SELECT company FROM Trip GROUP BY company HAVING COUNT(id) = (SELECT MIN(count) FROM (SELECT count(*) as count FROM Trip GROUP BY Trip.company) as min ) ) 
 INSERT INTO Reviews SET id = (SELECT COUNT(*)+1 FROM Reviews as a), reservation_id = ( SELECT Reservations.id FROM Reservations JOIN Users ON Users.id = Reservations.user_id JOIN Rooms ON Rooms.id = Reservations.room_id WHERE Users.name = "George Clooney" AND Rooms.address = "11218, Friel Place, New York" ), rating = 5 
 UPDATE Timepair SET start_pair = start_pair + INTERVAL 30 MINUTE, end_pair = end_pair + INTERVAL 30 MINUTE 
 SELECT Rooms.* FROM Rooms JOIN Reservations ON Reservations.room_id = Rooms.id WHERE WEEK(Reservations.start_date, 1) = 12 AND YEAR(start_date) = 2020 
 SELECT REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).') as domain, COUNT(REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).')) as count FROM Users GROUP BY domain ORDER BY count DESC, domain 
 SELECT Rooms.home_type, Rooms.address, IFNULL(SUM(DATEDIFF(Reservations.end_date, Reservations.start_date)), 0) as days, IFNULL(SUM(total), 0) as total_fee FROM Rooms LEFT JOIN Reservations ON Rooms.id = Reservations.room_id WHERE Rooms.has_tv = true AND has_internet = true AND has_kitchen = true AND has_air_con = true GROUP BY Rooms.id 
 SELECT owner_id, IFNULL(SUM(Reservations.total), 0) as total_earn FROM Rooms LEFT JOIN Reservations ON Reservations.room_id = Rooms.id GROUP BY owner_id 
 SELECT room_id, COUNT(room_id) as count FROM Reservations GROUP BY room_id HAVING MOD(count, 2) 
 SELECT YEAR(start_date) as year, MONTH(start_date) as month, COUNT(*) as amount FROM Reservations GROUP BY YEAR(start_date), MONTH(start_date) ORDER BY YEAR(start_date) ASC 

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

SQL Academy (ответы и решения заданий 1-22)

SQL Academy (ответы и решения заданий 1-22)

ВКонтакте WhatsApp Pinterest Facebook Email

Ниже представлены наши варианты решения заданий из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org). Ответы на 66 заданий представленные в онлайн тренажере разбиты на 3 части. Здесь, в первой части, представлены ответы на первые 22 задания.

Задание 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 COUNT(*) AS count FROM Trip WHERE plane = 'TU-134';

Задание 6. Какие компании совершали перелеты на Boeing

SELECT DISTINCT name FROM Company JOIN Trip ON Company.id=Trip.company WHERE plane = 'Boeing';

Задание 7. Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)

SELECT DISTINCT plane FROM Trip WHERE town_to = 'Moscow';

Задание 8. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?

SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM Trip WHERE town_from = 'Paris';

Задание 9. Какие компании организуют перелеты с Владивостока (Vladivostok)?

SELECT DISTINCT name FROM Company JOIN Trip ON Company.id=Trip.company WHERE town_from = 'Vladivostok';

Задание 10. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.

SELECT * FROM Trip WHERE time_out BETWEEN '1900-01-01 10:00:00' AND '1900-01-01 14:00:00';

Задание 11. Вывести пассажиров с самым длинным именем

SELECT name FROM Passenger WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM Passenger);

Задание 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(name) > 1;

Задание 14. В какие города летал Bruce Willis?

SELECT DISTINCT town_to FROM Trip JOIN Pass_in_trip ON Trip.id=Pass_in_trip.trip JOIN Passenger ON Pass_in_trip.passenger=Passenger.id WHERE name = 'Bruce Willis';

Задание 15. Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London)?

SELECT time_in FROM Trip JOIN Pass_in_trip ON Trip.id=Pass_in_trip.trip JOIN Passenger ON Pass_in_trip.passenger=Passenger.id WHERE name='Steve Martin' AND town_to='London';

Задание 16. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.

SELECT name, COUNT(*) AS count FROM Passenger JOIN Pass_in_trip ON Passenger.id=Pass_in_trip.passenger GROUP BY passenger HAVING COUNT(trip) > 0 ORDER BY COUNT(trip) DESC, name;

Задание 17. Определить, сколько потратил в 2005 году каждый из членов семьи

SELECT member_name, status, SUM(amount*unit_price) AS costs FROM FamilyMembers JOIN Payments ON FamilyMembers.member_id=Payments.family_member WHERE YEAR(date) = 2005 GROUP BY member_name, status;

Задание 18. Узнать, кто старше всех в семьe

SELECT member_name FROM FamilyMembers WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);

Задание 19. Определить, кто из членов семьи покупал картошку (potato)

SELECT DISTINCT status FROM FamilyMembers JOIN Payments ON FamilyMembers.member_id=Payments.family_member JOIN Goods ON Payments.good=Goods.good_id WHERE good_name = 'potato';

Задание 20. Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму

SELECT fm.status, fm.member_name, SUM(p.amount*p.unit_price) 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 gt ON g.type=gt.good_type_id WHERE good_type_name = 'entertainment' GROUP BY fm.status, fm.member_name;

Задание 21. Определить товары, которые покупали более 1 раза

SELECT good_name FROM Goods JOIN Payments ON Goods.good_id=Payments.good GROUP BY good HAVING COUNT(good) > 1;

Задание 22. Найти имена всех матерей (mother)

SELECT member_name FROM FamilyMembers WHERE status = 'mother';

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *