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

Определить товары которые покупали более 1 раза

  • автор:

Определить товары, которые еще никто не покупал SQL

Необходимо написать запрос, который выведет наименование и цену товаров, которые ещё никто не покупал (должен быть 1 товар). Нужно использовать в запросе NULL. Пишу такой запрос, но ничего не выводит:

SELECT product_name, price FROM orders JOIN orders_products ON orders.order_id = orders_products.order_id JOIN products ON orders_products.product_id = products.product_id JOIN buyers ON orders.buyer_id = buyers.buyer_id WHERE products.product_id IS NULL; 

Если написать IS NOT NULL, то выводятся все записи, кроме той, которой должна быть при IS NULL.
Отслеживать
задан 14 окт 2022 в 7:59
107 3 3 серебряных знака 12 12 бронзовых знаков

Необходимо написать запрос, который выведет наименование и цену товаров, которые ещё никто не покупал (должен быть 1 товар). Первые две таблицы для этого запроса не нужны в принципе. Задача решается элементарно путём использования WHERE NOT EXISTS. Нужно использовать в запросе NULL. Ооо! так это домашнее задание, оказывается? ну тогда LEFT JOIN WHERE IS NULL.

14 окт 2022 в 8:21
@Akina С LEFT тоже ничего не выводится.
14 окт 2022 в 8:53

@Akina Вот так написал: SELECT product_name, price FROM products LEFT JOIN orders_products ON products.product_id = orders_products.product_id WHERE products.product_id IS NULL;

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 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';

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT , FROM и WHERE . Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.

Автор статьи, перевод которой мы сегодня публикуем, говорит, что она направлена на решение двух задач:

  1. Изучение механизмов, которые выходят за пределы базового знания SQL.
  2. Рассмотрение нескольких практических задач по работе с SQL.

Вопрос №1: второе место по зарплате

Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников ( Employee ) записи, содержащей вторую по размеру заработную плату.

Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200 . Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null .

+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

▍Решение А: использование IFNULL и OFFSET

Вот основные механизмы, которые будут использованы в данном варианте решения задачи:

  • IFNULL(expression, alt) : эта функция возвращает свой аргумент expression в том случае, если он не равен null . В противном случае возвращается аргумент alt . Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
  • OFFSET : этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).
SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ), null) as SecondHighestSalary FROM Employee LIMIT 1

▍Решение B: использование MAX

В запросе, представленном ниже, используется функция MAX . Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.

SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary != (SELECT MAX(salary) FROM Employee)

Вопрос №2: дублирующиеся адреса электронной почты

Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.

+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+

▍Решение А: COUNT в подзапросе

Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1 . Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.

SELECT Email FROM ( SELECT Email, count(Email) AS count FROM Person GROUP BY Email ) as email_count WHERE count > 1

▍Решение B: выражение HAVING

  • HAVING : это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY .
SELECT Email FROM Person GROUP BY Email HAVING count(Email) > 1

Вопрос №3: растущая температура

Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

+---------+------------------+------------------+ | Id(INT) | RecordDate(DATE) | Temperature(INT) | +---------+------------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------------+------------------+

▍Решение: DATEDIFF

  • DATEDIFF : эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.
SELECT DISTINCT a.Id FROM Weather a, Weather b WHERE a.Temperature > b.Temperature AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Вопрос №4: самая высокая зарплата в подразделении

В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе ( Id ) сотрудника, о его имени ( Name ), о зарплате ( Salary ) и о подразделении компании, где он работает ( Department ).

+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+

Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+

▍Решение: команда IN

Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR . Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’ WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения ( Department ), имя сотрудника ( Employee ) и его заработную плату ( Salary ). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения ( DepartmentID ) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.Id WHERE (DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )

Вопрос №5: пересаживание учеников

Мэри — учительница в средней школе. У неё есть таблица seat , хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора WHEN

SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT CASE WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND MAX(id) FROM seat) THEN id WHEN id%2 = 1 THEN id + 1 ELSE id - 1 END AS id, student FROM seat ORDER BY id

Итоги

Мы разобрали несколько задач по SQL, попутно обсудив некоторые продвинутые средства, которые можно использовать при составлении SQL-запросов. Надеемся, то, что вы сегодня узнали, пригодится вам при прохождении собеседований по SQL и окажется полезным в повседневной работе.

P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.

Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?

  • Блог компании RUVDS.com
  • Занимательные задачки
  • SQL
  • Карьера в IT-индустрии

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

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