SQL вложенные запросы в EXISTS, IN, FROM, SELECT. Практика SQL

[@grommaks]

Ссылка на тренажер www.w3schools.com/sql/trysql.asp?filename=trysql_asc
ДЗ 📖:
1) Используя подзапросы получить информацию о поставщиках у которых номенклатура превышает 3 позиции
2) Собрать информацию по поставщикам и вывести показатель продаж по поставщику
3) К запросу из пункта 2 добавить показатель объема продаж в количестве товара по поставщику
4) Получить поставщиков у которых сумма прибыли полученной от их товаров выше средней суммы по всем поставщикам
ВНИМАНИЕ!!! ДЗ можно прикрепить под моим комментом или с дополнительным текстом, а не только лишь запросами. UA-cam удаляет чисто запросы без текста и не дает мне возможности разрешить их к публикации. Если будут проблемы, то пишите, будем думать как обходить блокировки youtube

[@Сергей-ы6е2ы]

1) задание не ясное...
SELECT SupplierName, COUNT(ProductName) FROM Suppliers S
JOIN Products P ON S.SupplierID=P.SupplierID
GROUP BY SupplierName
HAVING COUNT(ProductName) > 3
так мы видим кто поставляет больше 3 продуктов

[@grommaks]

@@Сергей-ы6е2ы все верно, но нужно не через джойны, а через подзапросы сделать )

[@Валентин-и7з9ю]

Задание 1
SELECT * From Suppliers S
WHERE S.SupplierID IN (
SELECT P.SupplierID FROM [Products] P
GROUP BY SupplierID
HAVING Count(ProductID) > 3
)
Задание 4: 2 и 3 не вполне понятно сформулированы (что подразумевается под показателями продаж?)
SELECT SupplierID FROM (
SELECT P.SupplierID, P.ProductID, SUM(OD.Quantity) AS ProductSales, P.Price FROM [Products] P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
GROUP BY P.ProductID
ORDER BY P.SupplierID
)
GROUP BY SupplierID
HAVING SUM(ProductSales * Price) > (
SELECT AVG(SupplierProfit) FROM (
SELECT SupplierID, SUM(ProductSales * Price) AS SupplierProfit FROM (
SELECT P.SupplierID, P.ProductID, SUM(OD.Quantity) AS ProductSales, P.Price FROM [Products] P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
GROUP BY P.ProductID
ORDER BY P.SupplierID
)
GROUP BY SupplierID
)
)

[@Zero-h2f2m]

4 Задание
SELECT S.SupplierID, S.SupplierName, Round(SUM(Quantity*Price),2) as Total FROM Products P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
JOIN Suppliers S ON P.SupplierID = S.SupplierID
GROUP BY S.SupplierID
HAVING Total > (SELECT AVG(Total)
FROM
(
SELECT Round(SUM(Quantity*Price),2) as Total FROM Products P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
JOIN Suppliers S ON P.SupplierID = S.SupplierID
GROUP BY S.SupplierID
))

[@АлинаКардаш-б1ы]

1) С бумажкой(написанием основной логики на бумаге) оказалось разобраться значительно проще
SELECT *,(
SELECT COUNT(*) FROM Products P
WHERE (S.SupplierID=P.SupplierID)
) AS CountPrSup From Suppliers S
WHERE CountPrSup>3
2-3) не придумала как сделать через подзапрос + не понятно что такое показатель продаж
SELECT OD.ProductID, SUM(OD.Quantity), P.SupplierID FROM OrderDetails OD
JOIN Products P ON P.ProductID=OD.ProductID
Group BY P.SupplierID
4)не нравится что выводиться среднее в каждой строке, но работает
SELECT *,(SELECT AVG(SCost) AS AVGSCost FROM (
SELECT OD.ProductID, OD.Quantity,P.Price,S.SupplierID, SUM(OD.Quantity*P.Price) AS SCost FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
JOIN Suppliers S ON S.SupplierID = P.SupplierID
Group BY S.SupplierID
))AS AVGSCost From
(SELECT OD.ProductID, OD.Quantity,P.Price,S.SupplierID, SUM(OD.Quantity*P.Price) AS SCost FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
JOIN Suppliers S ON S.SupplierID = P.SupplierID
Group BY S.SupplierID)
WHERE SCost>AVGSCost)

[@guzelkhazieva9392]

2-3. SELECT *, (
SELECT COUNT (*)
FROM Products P
WHERE P.SupplierID=S.SupplierID
) AS SKU, (
SELECT SUM(OD.Quantity)
FROM OrderDetails OD
JOIN Products P ON P.ProductID=OD.ProductID
WHERE P.SupplierID=S.SupplierID
) AS QTY, (
SELECT SUM(P.Price*OD.Quantity)
FROM OrderDetails OD
JOIN Products P ON P.ProductID=OD.ProductID
WHERE P.SupplierID=S.SupplierID
) AS TotalUSD, (
SELECT SUM(P.Price*OD.Quantity)/SUM(OD.Quantity)
FROM OrderDetails OD
JOIN Products P ON P.ProductID=OD.ProductID
WHERE P.SupplierID=S.SupplierID
) AS PerPCS
FROM Suppliers S

[@vanderdt2]

Ничерта не понятно, но очень интересно )

[@eugenecyp]

2. SELECT * ,(
SELECT SUM(Price * Quantity) AS Total FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID)
AS Sales FROM Suppliers S
______

[@АбылайШинтемиров]

А можно было использовать with?

[@ТарасЖурба-л7у]

Привет, спасибо за урок. Максим, подскажите пожалуйста каким способом вы б решили четвертую задачу?
1. SELECT * FROM Suppliers
WHERE SupplierID IN(
SELECT SupplierID FROM Products
GROUP BY SupplierID
HAVING COUNT(SupplierID)>3
)
2.3. SELECT *, (
SELECT sum(Quantity) FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID
) AS Quantity_goods, (
SELECT sum(Quantity*Price) FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID
) AS Amount
FROM Suppliers S
4. select *, (
SELECT sum(Price*Quantity) as Total FROM OrderDetails OD
join Products P on P.ProductID = OD.ProductID
where P.SupplierID = SS.SupplierID
group by P.SupplierID
) as Profit from Suppliers SS
where Profit > (
select avg(Total2) from (
SELECT sum(Price*Quantity) as Total2 FROM OrderDetails OD
join Products P on P.ProductID = OD.ProductID
group by P.SupplierID
)
)

[@Void-ws8jz]

Кое кто говорил, что самый сложный урок это агрегатные функции)) я бы поспорил)

[@grommaks]

Ааа, ну да) самый сложный это вложенные запросы)

[@apostolmira8979]

@@grommaks дальше больше))

[@aNDy-qh1em]

@Максим Гром, здравствуйте, снимите ролик про рекурсию в SQL. Как по мне, так эта тема слабо раскрыта в ютубе. В качестве примера было бы интересно решение задачи является ли сотрудник2 иерархическим руководителем сотрудника1 когда мы не знаем сколько между ними уровней.

[@grommaks]

Интересная задача, подумаю над реализацией, но на практике никогда не решал такую в SQL

[@aNDy-qh1em]

@@grommaks Столкнулся с этой задачей на работе при определении доступа к инфо по сотруднику. Интересно именно решать такие задачи запросом, без встроенных функций. Примерно как здесь:ua-cam.com/video/7GpyHedM4pw/v-deo.html
Только думаю, Вы смогли бы доступней изложить. )

[@ДмитрийФиленков-ш9т]

@Максим Гром, здравствуйте, не могу разобраться с влож. запр. в from, не понимаю в чем ошибка
select sum(sum) from (select count(sum) from product where sum>300)as s;
ERROR 1054 (42S22): Unknown column 'sum' in 'field list'
ошибка явно в выборе суммы столбца (Sum), когда ставлю * все прекрасно работает

[@grommaks]

Count работает со звездочкой замечательно, а вот из подзапроса наверно стоит написать count(*) as sum
Тогда внешний запрос может обращаться к колонке sum в подзапросе, ошибка должна уйти

[@linakohan7299]

Решила первое задание таким образом, но не уверенна, что данное решение верно.
SELECT (
SELECT SupplierName FROM Suppliers
WHERE Suppliers.SupplierID = Products.SupplierID
) as SupplierName, COUNT (ProductName) as ProductCount FROM Products
GROUP BY SupplierName
HAVING ProductCount > 3

[@grommaks]

Решение подходит, но тут есть проблема, если надо будет не только SupplierName то придется добавить еще один подзапрос
Я бы предпочел получить всех Suppliers у которых номенклатура больше 3х товаров
SELECT * FROM Suppliers
WHERE SupplierID IN (SELECT SupplierID FROM Products GROUP BY SupplierID HAVING COUNT(*) > 3)
Если группировать запросы не нравится, то можно по другому
SELECT * FROM Suppliers
WHERE (SELECT COUNT(*) FROM Products WHERE Products.SupplierID = Suppliers.SupplierID) > 3

[@diatm1506]

Будут по tipeorm reletionship видео? Отлично вложенные запросы не менее важны чем join-ы. Я понял что именно не могу понять что в sql что в mongo жаль тут код нельзя писать. Это формирование вложенных коллекций или если расматривать api в виде json обект вложенный в обект. Я же не должен хранить в cтроке sql images ячейку например json img1, img2 или наполнять с помощью 2 запросов к post и image и дальнейшего наполнения в цыкле массива обекта это всё должны решать join? Например
post_id
title
Images array ll object id 1, img1; id 2, img2
likes_avatars array ll object id 1, user_id 1, img_id 2; id 2, user_id 5, img_id 3
Мне же не нужно цыклы и 4 запроса к img, likes, post, users чтобы сформировать такую вложенную структуру подзапросы это здорово count likes where post id равно likes_post_id

[@grommaks]

join формирует собранную структуру в виде плоского списка
ORM часто просто делает два запроса, первый для постов, второй для всех картинок полученных постов. Потом в коде раскидываются взаимосвязи.
Бывает что хранимая процедура делает это несколькими запросами но за одно обращение к БД (это в проектах где нужно руками писать оптимальные запросы)
Из sql можно извлечь json, но это исключение чем правило, в описанном случае использование по 1 запросу на каждую таблицу (тип сущности), в случае с typeorm там будет возможность это сделать одним вызовом, а все остальное произойдет под капотом ORM при условии правильно прописанных связей между таблицами
P.S. typeorm как правило сделает 4ре запроса для получения всех записей и это нормально)
P.S.S. по typeorm reletionship видео пока не планируется

[@shamblog]

1) Решение первого задания ( возможо верное)
SELECT * FROM (
SELECT SupplierID, COUNT(ProductID) AS ProductCount FROM Products P
GROUP BY SupplierID
HAVING COUNT(ProductID) > 3
)

[@grommaks]

В этом запросе мы получаем SupplierID, в задании нужно получить информацию о Supplies, то есть в результате получить список всех кто имеет такой ID из таблицы Suppliers
Решение не полное, еще надо доработать

[@shamblog]

Решения задания 2,3,4 и 5, надеюсь правильно понял и решил
SELECT *, (
SELECT COUNT(ProductID) AS ProductCount FROM Products P
WHERE S.SupplierID = P.SupplierID
GROUP BY SupplierID
) AS ProductCount,
(
SELECT SUM(Price * Quantity) AS Total FROM Products P
JOIN OrderDetails OD ON OD.ProductID = P.ProductID
WHERE S.SupplierID = P.SupplierID
GROUP BY SupplierID
) AS Total,
(
SELECT SUM(Quantity) AS Total FROM Products P
JOIN OrderDetails OD ON OD.ProductID = P.ProductID
WHERE S.SupplierID = P.SupplierID
GROUP BY SupplierID
) AS Quantity
FROM Suppliers S
, (
SELECT AVG(Total) AS AVG FROM (SELECT SupplierID, SUM(Price * Quantity) AS Total FROM Products P
JOIN OrderDetails OD ON OD.ProductID = P.ProductID

GROUP BY SupplierID )
) AS AVG
WHERE AVG.AVG > Total

[@shamblog]

@@grommaks Сейчас я исправил и вывел всю информацию о поставщике
SELECT * FROM (
SELECT SupplierID, SupplierName, ContactName, Address, City,
PostalCode, Country, Phone, COUNT(ProductID) AS ProductCount
FROM Products P
JOIN Suppliers S ON S.SupplierID = P.SupplierID
GROUP BY S.SupplierID
HAVING COUNT(ProductID) > 3
)
PS:) Спасибо за классные уроки. Продолжай делать полезный контент.

[@grommaks]

@@shamblog идея была избежать использования join в этом задании)

[@shamblog]

@@grommaks
Все, теперь точно конечный вариант
SELECT * , (
SELECT COUNT(ProductID) AS ProductCount FROM Products P
WHERE S.SupplierID = P.SupplierID
GROUP BY SupplierID
HAVING COUNT(ProductID) > 3
) AS ProductCount
FROM Suppliers S
WHERE ProductCount IS NOT NULL

[@vinogradishev]

Красава, хорошее видео! Спасибо.

[@llERAZER]

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

[@bayzik15]

2-3 задание (Не уверен, что правильно):
SELECT S.SupplierID, S.SupplierName, Price, QP
FROM Suppliers S
JOIN (SELECT P.SupplierID AS column_1, SUM(P.Price) AS Price, COUNT(OD.Quantity) AS QP FROM OrderDetails OD
JOIN Products P ON OD.ProductID=P.ProductID
GROUP BY column_1) AS T2 ON S.SupplierID=column_1
GROUP BY S.SupplierID

[@Rave36484]

Этот плейлист - первое мое знакомство с sql, надеюсь правильно 👷
1)
SELECT *, (
SELECT COUNT(*) AS Co FROM Products P
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID
HAVING Co > 3
) AS Compilation FROM Suppliers S
WHERE Compilation IS NOT NULL
2)
SELECT *, (
SELECT SUM(Price * Quantity) AS SupTotal FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID
) AS Sales FROM Suppliers S
3)
SELECT *, (
SELECT SUM(
(
SELECT SUM(Quantity) FROM OrderDetails OD
WHERE OD.ProductID = P.ProductID
GROUP BY ProductID
)
) FROM Products P
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID
) AS Amount FROM Suppliers S
4)
SELECT *, (
SELECT SUM(Price * Quantity) AS SupTotal FROM OrderDetails OD
JOIN Products P ON P.ProductID = OD.ProductID
WHERE P.SupplierID = S.SupplierID
GROUP BY P.SupplierID
) AS Sales FROM Suppliers S
WHERE Sales > (
SELECT AVG((
SELECT SUM(Price * Quantity) FROM OrderDetails ODavg
JOIN Products Pavg ON Pavg.ProductID = ODavg.ProductID
WHERE Pavg.SupplierID = Savg.SupplierID
GROUP BY Pavg.SupplierID
)) FROM Suppliers Savg
)