Определить товары которые покупали более 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;

Примеры SELECT (Transact-SQL)

В этой статье приведены примеры использования инструкции SELECT .

В этой статье требуется AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества Microsoft SQL Server.

А. Использование SELECT для получения строк и столбцов

В следующем примере приведены три примера кода. В ходе выполнения первого примера кода возвращаются все строки (предложение WHERE не указано), а также все столбцы (используется звездочка, * ) таблицы Product базы данных AdventureWorks2022 .

USE AdventureWorks2022; GO SELECT * FROM Production.Product ORDER BY Name ASC; -- Alternate way. USE AdventureWorks2022; GO SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк (предложение WHERE не задано) и подмножества столбцов ( Name , ProductNumber , ListPrice ) таблицы Product базы данных AdventureWorks2022 . Дополнительно выведено название столбца.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк таблицы Product , для которых линейки продуктов начинаются символом R и для которых длительность изготовления не превышает 4 дней.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC; GO 

B. Использование SELECT с заголовками столбцов и вычислениями

В ходе выполнения следующего примера возвращаются все строки таблицы Product . В результате выполнения первого примера выдаются все объемы продаж и скидки по всем продуктам. Во втором примере вычисляется годовой доход от продажи каждого вида продукции.

USE AdventureWorks2022; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO 

Данный запрос вычисляет доход от продажи по каждому виду продукции для каждого заказа.

USE AdventureWorks2022; GO SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName ASC; GO 

C. Использование DISTINCT с SELECT

В приведенном ниже примере для предотвращения получения повторяющихся заголовков используется оператор DISTINCT .

USE AdventureWorks2022; GO SELECT DISTINCT JobTitle FROM HumanResources.Employee ORDER BY JobTitle; GO 

D. Создание таблиц с помощью SELECT INTO

В следующем примере в базе данных #Bicycles создается временная таблица tempdb .

USE tempdb; GO IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL DROP TABLE #Bicycles; GO SELECT * INTO #Bicycles FROM AdventureWorks2022.Production.Product WHERE ProductNumber LIKE 'BK%'; GO 

В данном примере создается постоянная таблица NewProducts .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO SELECT * INTO dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice < $100; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO 

Д. Использование сопоставленных вложенных запросов

Коррелированный запрос — это запрос, зависящий от результатов выполнения другого запроса. Этот запрос можно выполнять многократно, один раз для каждой строки, которая может быть выбрана внешним запросом.

В первом примере представлены семантически эквивалентные запросы для демонстрации различий в использовании ключевых слов EXISTS и IN . В обоих примерах приведены допустимые вложенные запросы, извлекающие по одному экземпляру продукции каждого наименования, для которых модель продукта — «long sleeve logo jersey» (кофта с длинными рукавами, с эмблемой), а значения столбцов ProductModelID таблиц Product и ProductModel совпадают.

USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name LIKE 'Long-Sleeve Logo Jersey%' ); GO -- OR USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN ( SELECT ProductModelID FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND Name LIKE 'Long-Sleeve Logo Jersey%' ); GO 

В следующем примере используется и извлекается IN один экземпляр первого имени и имени семьи каждого сотрудника, для которого указан 5000.00 бонус в SalesPerson таблице, и для которого идентификаторы сотрудников совпадают в Employee таблицах и SalesPerson таблицах.

USE AdventureWorks2022; GO SELECT DISTINCT p.LastName, p.FirstName FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN ( SELECT Bonus FROM Sales.SalesPerson AS sp WHERE e.BusinessEntityID = sp.BusinessEntityID ); GO 

Предыдущий вложенный запрос в этом операторе нельзя оценивать независимо от внешнего запроса. Он требует значения параметра Employee.EmployeeID , однако это значение меняется, когда ядро СУБД SQL Server обрабатывает строки в Employee .

Коррелированный вложенный запрос также может использоваться в предложении HAVING внешнего запроса. В данном примере осуществляется поиск моделей продуктов, для которых максимальная цена в каталоге в два раза превышает среднюю цену по нему.

USE AdventureWorks2022; GO SELECT p1.ProductModelID FROM Production.Product AS p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ( SELECT AVG(p2.ListPrice) * 2 FROM Production.Product AS p2 WHERE p1.ProductModelID = p2.ProductModelID ); GO 

В этом примере используются два сопоставленных вложенных запроса для поиска имен сотрудников, которые продали определенный продукт.

USE AdventureWorks2022; GO SELECT DISTINCT pp.LastName, pp.FirstName FROM Person.Person pp INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN ( SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN ( SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42' ) ) ); GO 

F. Использование GROUP BY

В следующем примере находится общий объем продаж для каждого заказа в базе данных.

USE AdventureWorks2022; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ORDER BY SalesOrderID; GO 

Так как в запросе используется предложение GROUP BY , то для каждого заказа выводится только одна строка, содержащая общий объем продаж.

G. Использование GROUP BY с несколькими группами

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

USE AdventureWorks2022; GO SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY ProductID, SpecialOfferID ORDER BY ProductID; GO 

H. Использование GROUP BY и WHERE

В следующем примере после извлечения строк, содержащих цены каталога, превышающие $1000 , происходит их разделение на группы.

USE AdventureWorks2022; GO SELECT ProductModelID, AVG(ListPrice) AS [Average List Price] FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID; GO 

I. Использование GROUP BY с выражением

В следующем примере производится группировка с помощью выражения. Можно сгруппировать по выражению, если выражение не включает агрегатные функции.

USE AdventureWorks2022; GO SELECT AVG(OrderQty) AS [Average Quantity], NonDiscountSales = (OrderQty * UnitPrice) FROM Sales.SalesOrderDetail GROUP BY (OrderQty * UnitPrice) ORDER BY (OrderQty * UnitPrice) DESC; GO 

J. Использование GROUP BY с ORDER BY

В следующем примере для каждого типа продуктов вычисляется средняя цена, а также осуществляется сортировка полученных результатов по возрастанию.

USE AdventureWorks2022; GO SELECT ProductID, AVG(UnitPrice) AS [Average Price] FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY AVG(UnitPrice); GO 

K. Использование предложения HAVING

В первом из приведенных ниже примеров показывается использование предложения HAVING с агрегатной функцией. В нем производится группировка строк таблицы SalesOrderDetail по коду продукта, а также удаляются строки, соответствующие продуктам, для которых средний объем заказа не превышает пяти. Во втором примере показывается использование предложения HAVING без агрегатной функции.

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID; GO 

В данном запросе внутри предложения LIKE используется предложение HAVING .

USE AdventureWorks2022; GO SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumber HAVING CarrierTrackingNumber LIKE '4BD%' ORDER BY SalesOrderID ; GO 

L. Использование HAVING и GROUP BY

В следующем примере показано использование предложений GROUP BY , HAVING , WHERE и ORDER BY в одной инструкции SELECT . В результате его выполнения в группах и сводных значениях не учитываются строки, соответствующие продуктам с ценами выше $25 и средним объемом заказов ниже 5. Также осуществляется сортировка результатов по ProductID .

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) >5 ORDER BY ProductID; GO 

M. Использование HAVING с СУММ и AVG

В следующем примере производится группировка строк таблицы SalesOrderDetail по коду продукта, а затем выводятся только те группы, для которых общий объем продаж составляет более $1000000.00 , а средний объем заказа не превышает 3 .

USE AdventureWorks2022; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3; GO 

Чтобы просмотреть продукты с общим объемом продаж, превышающих $2000000.00 , используйте следующий запрос:

USE AdventureWorks2022; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00; GO 

Если вы хотите убедиться в наличии не менее 1500 элементов, участвующих в вычислениях для каждого продукта, используйте HAVING COUNT(*) > 1500 для устранения продуктов, возвращающих итоги для меньшего количества 1500 проданных элементов. Этот запрос выглядит следующим образом.

USE AdventureWorks2022; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500; GO 

О. Использование указания оптимизатора INDEX

В следующем примере показаны два способа использования указания оптимизатора INDEX . В первом примере показано, как принудительно принудить оптимизатора использовать некластеризованный индекс для получения строк из таблицы. Во втором примере выполняется проверка таблицы с помощью индекса 0.

USE AdventureWorks2022; GO SELECT pp.FirstName, pp.LastName, e.NationalIDNumber FROM HumanResources.Employee AS e WITH (INDEX (AK_Employee_NationalIDNumber)) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Force a table scan by using INDEX = 0. USE AdventureWorks2022; GO SELECT pp.LastName, pp.FirstName, e.JobTitle FROM HumanResources.Employee AS e WITH (INDEX = 0) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO 

M. Использование OPTION и подсказок GROUP

В следующем примере демонстрируется совместное использование предложений OPTION (GROUP) и GROUP BY .

USE AdventureWorks2022; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10); GO 

O. Использование указания запроса UNION

В следующем примере используется указание запроса MERGE UNION .

USE AdventureWorks2022; GO SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e1 UNION SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e2 OPTION (MERGE UNION); GO 

P. Использование UNION

При выполнении следующего примера в результирующий набор включается содержимое столбцов ProductModelID и Name таблиц ProductModel и Gloves .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO -- Here is the simple union. USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

В. Использование SELECT INTO с UNION

При выполнении следующего примера предложение INTO во второй инструкции SELECT указывает, что в таблице с именем ProductResults содержится итоговый результирующий набор объединения заданных столбцов таблиц ProductModel и Gloves . Таблица Gloves была создана в результате выполнения первой инструкции SELECT .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.ProductResults', 'U') IS NOT NULL DROP TABLE dbo.ProductResults; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO USE AdventureWorks2022; GO SELECT ProductModelID, Name INTO dbo.ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO SELECT ProductModelID, Name FROM dbo.ProductResults; 

R. Использование UNION двух операторов SELECT с ORDER BY

При использовании предложения UNION необходимо соблюдать порядок следования определенных параметров. В следующем примере показаны случаи правильного и неверного использования UNION в двух инструкциях SELECT , в которых необходимо переименовать столбцы на выходе.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO /* INCORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO /* CORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

S. Использование UNION трех инструкций SELECT для отображения эффектов ALL и круглых скобок

В следующих примерах используются UNION для объединения результатов трех таблиц, которые имеют одинаковые пять строк данных. В первом примере используется предложение UNION ALL , в результате чего выдаются все 15 строк. Второй пример используется без ALL исключения повторяющихся UNION строк из объединенных результатов трех SELECT операторов и возвращает пять строк.

В третьем примере с первым предложением ALL используется ключевое слово UNION , а во втором предложении UNION вместо ключевого слова ALL используются скобки. Второй UNION обрабатывается сначала, так как он находится в скобках, и возвращает пять строк, так как ALL параметр не используется и дубликаты удаляются. Эти пять строк объединяются с результатами первого SELECT с помощью UNION ALL ключевое слово. В данном случае повторяющиеся строки двух множеств, состоящих из пяти строк, не удаляются. Окончательный результат состоит из 10 строк.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.EmployeeOne', 'U') IS NOT NULL DROP TABLE dbo.EmployeeOne; GO IF OBJECT_ID('dbo.EmployeeTwo', 'U') IS NOT NULL DROP TABLE dbo.EmployeeTwo; GO IF OBJECT_ID('dbo.EmployeeThree', 'U') IS NOT NULL DROP TABLE dbo.EmployeeThree; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeOne FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeTwo FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeThree FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Union ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL ( SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree ); GO 

Связанный контент

  • CREATE TRIGGER (Transact-SQL)
  • CREATE VIEW (Transact-SQL)
  • DELETE (Transact-SQL)
  • EXECUTE (Transact-SQL)
  • Выражения (Transact-SQL)
  • INSERT (Transact-SQL)
  • LIKE (Transact-SQL)
  • Операторы set — UNION (Transact-SQL)
  • Операторы set — EXCEPT и INTERSECT (Transact-SQL)
  • UPDATE (Transact-SQL)
  • WHERE (Transact-SQL)
  • PathName (Transact-SQL)
  • SELECT — предложение INTO (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

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-индустрии

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.

Решение заданий из тренажера sql-academy.org

Yunique33/sql-academy-solutions

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Go to file

Folders and files

Last commit message
Last commit date

Latest commit

History

View all files

Repository files navigation

Решение заданий из тренажера SQL Academy

  1. Вывести имена всех людей, которые есть в базе данных авиакомпаний (сайт)
SELECT name FROM passenger;
  1. Вывести названия всеx авиакомпаний (сайт)
SELECT name FROM Company;
  1. Вывести все рейсы, совершенные из Москвы (сайт)
SELECT * FROM trip WHERE town_from = 'Moscow';
  1. Вывести имена людей, которые заканчиваются на "man" (сайт)
SELECT name FROM passenger WHERE name LIKE '%man';
  1. Вывести количество рейсов, совершенных на TU-134 (сайт)
SELECT COUNT(*) AS count FROM trip WHERE plane = 'TU-134';
  1. Какие компании совершали перелеты на Boeing (сайт)
SELECT DISTINCT cp.name FROM company cp JOIN trip tr ON cp.id = tr.company WHERE plane = 'Boeing';
  1. Вывести все названия самолётов, на которых можно улететь в Москву (Moscow) (сайт)
SELECT DISTINCT plane FROM trip WHERE town_to = 'Moscow';
  1. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт? (сайт)
SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM trip WHERE town_from = 'Paris';
  1. Какие компании организуют перелеты из Владивостока (Vladivostok)? (сайт)
SELECT name FROM trip tr JOIN company cp ON tr.company = cp.id WHERE town_from = 'Vladivostok';
  1. Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г. (сайт)
SELECT * FROM trip WHERE DATE(time_out) = '1900-01-01' AND TIME_FORMAT(time_out, '%H:%i') >= '10:00' AND TIME_FORMAT(time_out, '%H:%i')  '14:00';
  1. Выведите пассажиров с самым длинным ФИО. Пробелы, дефисы и точки считаются частью имени. (сайт)
SELECT name FROM passenger WHERE LENGTH(name) = ( SELECT MAX(LENGTH(name)) FROM passenger);
  1. Вывести id и количество пассажиров для всех прошедших полётов (сайт)
SELECT trip, COUNT(*) AS count FROM passenger ps JOIN Pass_in_trip pt ON ps.id = pt.passenger GROUP BY trip;
  1. Вывести имена людей, у которых есть полный тёзка среди пассажиров (сайт)
SELECT name FROM passenger GROUP BY name HAVING COUNT(*) > 1;
  1. В какие города летал Bruce Willis (сайт)
SELECT town_to FROM passenger ps JOIN Pass_in_trip pt ON ps.id = pt.passenger JOIN trip tr ON tr.id = pt.trip WHERE name = 'Bruce Willis';
  1. Выведите дату и время прилёта пассажира Стив Мартин (Steve Martin) в Лондон (London) (сайт)
SELECT time_in FROM trip tr JOIN Pass_in_trip pt ON tr.id = pt.trip JOIN passenger ps ON pt.passenger = ps.id WHERE name = 'Steve Martin' AND town_to = 'London';
  1. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет. (сайт)
SELECT name, COUNT(name) AS count FROM passenger ps JOIN Pass_in_trip pt ON ps.id = pt.passenger JOIN trip tr ON pt.trip = tr.id GROUP BY name ORDER BY count DESC, name ASC;
  1. Определить, сколько потратил в 2005 году каждый из членов семьи. В результирующей выборке не выводите тех членов семьи, которые ничего не потратили. (сайт)
SELECT member_name, status, SUM(unit_price * amount) AS costs FROM FamilyMembers fm JOIN Payments ps ON fm.member_id = ps.family_member WHERE YEAR(DATE) = 2005 GROUP BY member_name, status;
  1. Узнать, кто старше всех в семьe (сайт)
SELECT member_name FROM FamilyMembers ORDER BY birthday ASC LIMIT 1;
  1. Определить, кто из членов семьи покупал картошку (potato) (сайт)
SELECT status FROM FamilyMembers fm JOIN Payments ps ON fm.member_id = ps.family_member JOIN Goods gs ON ps.good = gs.good_id WHERE good_name = 'potato' GROUP BY status;
  1. Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму (сайт)
SELECT status, member_name, (amount * unit_price) AS costs FROM FamilyMembers fm JOIN Payments ps ON fm.member_id = ps.family_member JOIN Goods gs ON ps.good = gs.good_id JOIN GoodTypes gt ON gs.type = gt.good_type_id WHERE good_type_name = 'entertainment';
  1. Определить товары, которые покупали более 1 раза (сайт)
SELECT good_name FROM Goods gs JOIN Payments ps ON gs.good_id = ps.good GROUP BY good HAVING COUNT(*) > 1;
  1. Найти имена всех матерей (mother) (сайт)
SELECT member_name FROM FamilyMembers WHERE status = 'mother';
  1. Найдите самый дорогой деликатес (delicacies) и выведите его цену
    (сайт)
SELECT good_name, unit_price FROM Goods gs JOIN GoodTypes gt ON gs.type = gt.good_type_id JOIN Payments ps ON gs.good_id = ps.good WHERE good_type_name = 'delicacies' ORDER BY unit_price DESC LIMIT 1;
  1. Определить кто и сколько потратил в июне 2005 (сайт)
SELECT member_name, (amount * unit_price) AS costs FROM FamilyMembers fm JOIN Payments ps ON fm.member_id = ps.family_member WHERE YEAR(date) = 2005 AND MONTH(date) = 6;
  1. Определить, какие товары не покупались в 2005 году (сайт)
SELECT good_name FROM Goods WHERE good_id NOT IN ( SELECT good FROM Payments WHERE YEAR(date) = 2005 );
  1. Определить группы товаров, которые не приобретались в 2005 году (сайт)
SELECT good_type_name FROM GoodTypes WHERE good_type_id NOT IN ( SELECT type FROM Goods gs JOIN Payments ps ON gs.good_id = ps.good WHERE YEAR(date) = 2005 GROUP BY good_id );
  1. Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму (сайт)
SELECT good_type_name, SUM(amount * unit_price) AS costs FROM GoodTypes gt JOIN Goods gs ON gt.good_type_id = gs.type JOIN Payments ps ON gs.good_id = ps.good WHERE YEAR(date) = 2005 GROUP BY good_type_name;
  1. Сколько рейсов совершили авиакомпании из Ростова (Rostov) в Москву (Moscow) ? (сайт)
SELECT COUNT(*) AS COUNT FROM Trip WHERE town_from = 'Rostov' AND town_to = 'Moscow';
  1. Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134 (сайт)
SELECT name FROM Passenger ps JOIN Pass_in_trip pt ON ps.id = pt.passenger JOIN Trip tr ON pt.trip = tr.id WHERE plane = 'TU-134' AND town_to = 'Moscow' GROUP BY name;
  1. Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности. (сайт)
SELECT trip, COUNT(passenger) AS count FROM Pass_in_trip GROUP BY trip ORDER BY count DESC;
  1. Вывести всех членов семьи с фамилией Quincey. (сайт)
SELECT * FROM FamilyMembers WHERE member_name LIKE '% Quincey';
  1. Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону. (сайт)
SELECT FLOOR( AVG(TIMESTAMPDIFF(YEAR, birthday, CURRENT_TIMESTAMP)) ) AS age FROM FamilyMembers;
  1. Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar). (сайт)
SELECT AVG(unit_price) AS cost FROM Payments ps JOIN Goods gs ON ps.good = gs.good_id WHERE good_name = 'red caviar' OR good_name = 'black caviar';
  1. Сколько всего 10-ых классов (сайт)
SELECT COUNT(name) AS count FROM Class WHERE name LIKE '10 %';
  1. Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ? (сайт)
SELECT COUNT(DISTINCT classroom) AS count FROM Student_in_class sc JOIN Class cl ON sc.class = cl.id JOIN Schedule sh ON sh.class = cl.id WHERE DATE_FORMAT(date, '%e.%m.%Y') = '2.09.2019';
  1. Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)? (сайт)
SELECT * FROM Student WHERE address RLIKE 'Pushkina';
  1. Сколько лет самому молодому обучающемуся ? (сайт)
SELECT TIMESTAMPDIFF(YEAR, birthday, CURRENT_TIMESTAMP) AS year FROM Student ORDER BY year ASC LIMIT 1;
  1. Сколько Анн (Anna) учится в школе ? (сайт)
SELECT COUNT(*) AS count FROM Student WHERE first_name = 'Anna';
  1. Сколько обучающихся в 10 B классе ? (сайт)
SELECT COUNT(*) AS count FROM Student_in_class sc JOIN Class cl ON sc.class = cl.id WHERE name = '10 B';
  1. Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ? (сайт)
SELECT name AS subjects FROM Subject sj JOIN Schedule sc ON sj.id = sc.subject JOIN Teacher tc ON tc.id = sc.teacher WHERE last_name = 'Romashkin' AND first_name LIKE 'P%' AND middle_name LIKE 'P%';
  1. Во сколько начинается 4-ый учебный предмет по расписанию ? (сайт)
SELECT start_pair FROM Timepair WHERE id = 4;
  1. Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет? (сайт)
SELECT TIMEDIFF(MAX(end_pair), MIN(start_pair)) AS time FROM Timepair WHERE id BETWEEN 2 AND 4;
  1. Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Отсортируйте преподавателей по фамилии в алфавитном порядке. (сайт)
SELECT last_name FROM Teacher tc JOIN Schedule sc ON tc.id = sc.teacher JOIN Subject sj ON sj.id = sc.subject WHERE name = 'Physical Culture' ORDER BY last_name;
  1. Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ? (сайт)
SELECT TIMESTAMPDIFF(YEAR, birthday, CURRENT_TIMESTAMP) AS max_year FROM Student st JOIN Student_in_class sc ON sc.student = st.id JOIN Class cl ON cl.id = sc.class WHERE name LIKE '10 %' ORDER BY max_year DESC LIMIT 1;
  1. Какие кабинеты чаще всего использовались для проведения занятий? Выведите те, которые использовались максимальное количество раз. (сайт)
SELECT classroom FROM Schedule GROUP BY classroom HAVING count(classroom) = ( SELECT COUNT(*) AS count FROM Schedule GROUP BY classroom ORDER BY count DESC LIMIT 1 );
  1. В каких классах введет занятия преподаватель "Krauze" ? (сайт)
SELECT name FROM Schedule sc JOIN Teacher tc ON tc.id = sc.teacher JOIN Class cl ON cl.id = sc.class WHERE last_name = 'Krauze' GROUP BY name;
  1. Сколько занятий провел Krauze 30 августа 2019 г.? (сайт)
SELECT COUNT(*) AS count FROM Schedule sc JOIN Teacher tc ON tc.id = sc.teacher WHERE DATE_FORMAT(date, '%e %M %Y') = '30 August 2019' AND last_name = 'Krauze';
  1. Выведите заполненность классов в порядке убывания (сайт)
SELECT name, COUNT(student) AS count FROM Class cl JOIN Student_in_class sc ON sc.class = cl.id GROUP BY name ORDER BY count DESC;
  1. Какой процент обучающихся учится в "10 A" классе? Выведите ответ в диапазоне от 0 до 100 без округления, например, 96.0201. (сайт)
SELECT COUNT(*) * 100 / ( SELECT COUNT(*) FROM Student_in_class ) AS percent FROM Student_in_class sc JOIN Class cs ON cs.id = sc.class WHERE name = '10 A';
  1. Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону. (сайт)
SELECT FLOOR( COUNT(*) * 100 / ( SELECT COUNT(*) FROM Student_in_class ) ) AS percent FROM Student_in_class sc JOIN Student st ON st.id = sc.student WHERE YEAR(birthday) = 2000;
  1. Добавьте товар с именем "Cheese" и типом "food" в список товаров (Goods). (сайт)
INSERT INTO Goods SET good_id = ( SELECT COUNT(*) + 1 FROM Goods AS gs ), good_name = 'Cheese', type = ( SELECT good_type_id FROM GoodTypes WHERE good_type_name = 'food' );
  1. Добавьте в список типов товаров (GoodTypes) новый тип "auto". (сайт)
INSERT INTO GoodTypes SET good_type_id = ( SELECT COUNT(*) + 1 FROM GoodTypes AS gt ), good_type_name = 'auto';
  1. Измените имя "Andie Quincey" на новое "Andie Anthony". (сайт)
UPDATE FamilyMembers SET member_name = 'Andie Anthony' WHERE member_name = 'Andie Quincey';
  1. Удалить всех членов семьи с фамилией "Quincey". (сайт)
DELETE FROM FamilyMembers WHERE member_name LIKE '% Quincey';
  1. Удалить компании, совершившие наименьшее количество рейсов. (сайт)
DELETE FROM company WHERE id IN ( SELECT company FROM trip GROUP BY company HAVING COUNT(*) = ( SELECT COUNT(*) AS count FROM trip GROUP BY company ORDER BY count LIMIT 1 ) );
  1. Удалить все перелеты, совершенные из Москвы (Moscow). (сайт)
DELETE FROM trip WHERE town_from = 'Moscow';
  1. Перенести расписание всех занятий на 30 мин. вперед. (сайт)
UPDATE Timepair SET start_pair = ADDTIME(start_pair, '00:30:00'), end_pair = ADDTIME(end_pair, '00:30:00');
  1. Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу "11218, Friel Place, New York", от имени "George Clooney" (сайт)
INSERT INTO Reviews SET id = ( SELECT COUNT(*) + 1 FROM Reviews rw ), reservation_id = ( SELECT rs.id FROM Reservations rs JOIN Rooms rm ON rm.id = rs.room_id JOIN Users us ON rs.user_id = us.id WHERE address = '11218, Friel Place, New York' AND name = 'George Clooney' ), rating = 5;
  1. Вывести пользователей,указавших Белорусский номер телефона ? Телефонный код Белоруссии +375. (сайт)
SELECT * FROM Users WHERE phone_number LIKE '+375 %';
  1. Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов. (сайт)
SELECT teacher FROM Schedule sc JOIN Class cl ON sc.class = cl.id WHERE name LIKE '11 %' GROUP BY teacher HAVING COUNT(DISTINCT name) = 2;
  1. Выведите список комнат, которые были зарезервированы хотя бы на одни сутки в 12-ую неделю 2020 года. В данной задаче в качестве одной недели примите период из семи дней, первый из которых начинается 1 января 2020 года. Например, первая неделя года — 1–7 января, а третья — 15–21 января. (сайт)
SELECT Rooms.* FROM Reservations JOIN Rooms ON Rooms.id = Reservations.room_id WHERE WEEK(start_date, 1) = 12 AND YEAR(start_date) = 2020;
  1. Вывести в порядке убывания популярности доменные имена 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;
  1. Выведите отсортированный список (по возрастанию) фамилий и имен студентов в виде Фамилия.И. (сайт)
SELECT CONCAT(last_name, '.', LEFT(first_name, 1), '.') AS name FROM Student ORDER BY name;
  1. Вывести количество бронирований по каждому месяцу каждого года, в которых было хотя бы 1 бронирование. Результат отсортируйте в порядке возрастания даты бронирования. (сайт)
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, month;
  1. Необходимо вывести рейтинг для комнат, которые хоть раз арендовали, как среднее значение рейтинга отзывов округленное до целого вниз. (сайт)
SELECT room_id, FLOOR(AVG(rating)) AS rating FROM Reservations rs JOIN Reviews rw ON rw.reservation_id = rs.id GROUP BY room_id;
  1. Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат. (сайт)
SELECT home_type, address, IFNULL(SUM(total / rs.price), 0) AS days, IFNULL(SUM(total), 0) AS total_fee FROM Rooms rm LEFT JOIN Reservations rs ON rs.room_id = rm.id WHERE has_tv = 1 AND has_internet = 1 AND has_kitchen = 1 AND has_air_con = 1 GROUP BY home_type, address;
  1. Вывести время отлета и время прилета для каждого перелета в формате "ЧЧ:ММ, ДД.ММ - ЧЧ:ММ, ДД.ММ", где часы и минуты с ведущим нулем, а день и месяц без. (сайт)
SELECT CONCAT( DATE_FORMAT(time_out, '%H:%i, %e.%c'), ' - ', DATE_FORMAT(time_in, '%H:%i, %e.%c') ) AS flight_time FROM Trip;
  1. Для каждой комнаты, которую снимали как минимум 1 раз, найдите имя человека, снимавшего ее последний раз, и дату, когда он выехал (сайт)
SELECT rs.room_id, name, date AS end_date FROM ( SELECT room_id, MAX(end_date) AS date FROM Reservations GROUP BY room_id ) rs JOIN Reservations rsv ON rs.room_id = rsv.room_id AND rs.date = rsv.end_date JOIN Users us ON rsv.user_id = us.id;
  1. Вывести идентификаторы всех владельцев комнат, что размещены на сервисе бронирования жилья и сумму, которую они заработали (сайт)
SELECT owner_id, IFNULL(SUM(total), 0) AS total_earn FROM Rooms rm LEFT JOIN Reservations rs ON rm.id = rs.room_id GROUP BY owner_id;
  1. Необходимо категоризовать жилье на economy, comfort, premium по цене соответственно = 200. В качестве результата вывести таблицу с названием категории и количеством жилья, попадающего в данную категорию (сайт)
SELECT CASE WHEN price  100 THEN 'economy' WHEN price > 100 AND price  200 THEN 'comfort' WHEN price >= 200 THEN 'premium' END AS category, COUNT(price) AS count FROM Rooms GROUP BY category;
  1. Найдите какой процент пользователей, зарегистрированных на сервисе бронирования, хоть раз арендовали или сдавали в аренду жилье. Результат округлите до сотых. (сайт)
SELECT ROUND( ( SELECT COUNT(*) FROM ( SELECT DISTINCT owner_id FROM Rooms rm JOIN Reservations rs ON rm.id = rs.room_id UNION SELECT user_id FROM Reservations ) active_users ) * 100 / ( SELECT COUNT(*) FROM Users ), 2 ) AS percent;
  1. Выведите среднюю стоимость бронирования для комнат, которых бронировали хотя бы один раз. Среднюю стоимость необходимо округлить до целого значения вверх. (сайт)
SELECT room_id, CEILING(AVG(price)) AS avg_price FROM Reservations GROUP BY room_id;
  1. Выведите id тех комнат, которые арендовали нечетное количество раз (сайт)
SELECT room_id, COUNT(*) AS count FROM Reservations GROUP BY room_id HAVING count % 2 != 0;
  1. Выведите идентификатор и признак наличия интернета в помещении. Если интернет в сдаваемом жилье присутствует, то выведите «YES», иначе «NO». (сайт)
SELECT id, IF(has_internet = 1, 'YES', 'NO') AS has_internet FROM Rooms;
  1. Выведите фамилию, имя и дату рождения студентов, кто был рожден в мае. (сайт)
SELECT last_name, first_name, birthday FROM Student WHERE MONTHNAME(birthday) = 'May';
  1. Вывести имена всех пользователей сервиса бронирования жилья, а также два признака: является ли пользователь собственником какого-либо жилья (is_owner) и является ли пользователь арендатором (is_tenant). В случае наличия у пользователя признака необходимо вывести в соответствующее поле 1, иначе 0. (сайт)
SELECT name, IF( id IN ( SELECT owner_id FROM Rooms ), 1, 0 ) AS is_owner, IF( id IN ( SELECT user_id FROM Reservations ), 1, 0 ) AS is_tenant FROM Users;
  1. Создайте представление с именем "People", которое будет содержать список имен (first_name) и фамилий (last_name) всех студентов (Student) и преподавателей(Teacher) (сайт)
CREATE VIEW People AS SELECT first_name, last_name FROM Student UNION SELECT first_name, last_name FROM Teacher;
  1. Выведите всех пользователей с электронной почтой в «hotmail.com» (сайт)
SELECT * FROM Users WHERE email RLIKE '@hotmail.com';
  1. Выведите поля id, home_type, price у всех комнат из таблицы Rooms. Если комната имеет телевизор и интернет одновременно, то в качестве цены в поле price выведите цену, применив скидку 10% (сайт)
SELECT id, home_type, IF(has_tv AND has_internet, price * 0.9, price) AS price FROM Rooms;
  1. Создайте представление «Verified_Users» с полями id, name и email, которое будет показывает только тех пользователей, у которых подтвержден адрес электронной почты. (сайт)
CREATE VIEW Verified_Users AS SELECT id, name, email FROM Users WHERE email_verified_at IS NOT NULL;

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

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