Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
Всем привет. Я бы не сказал что у меня плохо с фантазией, но как то самому себе запросы придумывать сложно. А задали нам написать 16 запросов к существующей БД, которая выгляди вот так:
Картинка с другого сайта.
Помогите с постановкой задачи, вернее поставьте мне задачу :).
Если что мной были уже реализованы следующие запросы:
1)Первые 10 продуктов(name, id) с ценой больше 100 и которые содержат "laptop" в имени
2)Все продукты(name, id) где есть хотя бы один заказ на 100
3)Все продукты которые купил клиент с id=2
4)Первые 5 продуктов(name, id, *Оборот) *Оборот вычисляемое поле, где *Оборот общая стоимость заказов, отсортированные по имени
5)Вторая страница продуктов в категории "computers" отсортированные по имени, где на страницу выходит по 5 результатов
6)Вторая страница продуктов(name, id, *Оборот) с *Оборотом > 10000 отсортированные по алфавитному порядку, где на страницу выходит по 5 результатов
7) (Имя, кол-во заказов) по id=2 Товара
8) (Имя, Фамилия, ЕГН(Личный номер)) где имя клиентов начинается с букв {K,L,M,N}

Помогите ещё 8 придумать, желательно не банальные, всё таки хочется потренироваться, ну и что бы не были точь в точь мной уже придуманные.
10 апр 16, 19:22    [19039262]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe,

Категории, обеспечивающие в сумме 80% оборота
Все клиенты, покупавшие компьютеры на день рождения
Для каждой компании вывести категорию, попавшую в максимальное количество заказов

Хватит пока? :)
10 апр 16, 19:34    [19039297]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy,
1) Как быть с под запросом?
Вернее как вытащить общую сумму всех заказов?
Так не получается
SELECT * FROM Products
INNER JOIN Orders ON Orders.ProductID = Products.ID
INNER JOIN Categories ON Products.CategoryID = Categories.ID
WHERE Products.Price * Orders.Quentity >
(
	SELECT SUM(Products.Price * Orders.Quentity)*0.8 -- 80%
);


2) К сожалению у меня нету поля где указывается когда был куплен товар.
10 апр 16, 20:41    [19039545]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe
iljy,
1) Как быть с под запросом?
Вернее как вытащить общую сумму всех заказов?
Так не получается

Надо писать полный подзапрос, с указанием FROM и т.д. Либо смотреть на оконную функцию SUM() over(), тут должно ее хватить.


unsafe
2) К сожалению у меня нету поля где указывается когда был куплен товар.

А, да, не заметил. А добавить не хотите? Хранить дату заказа вполне себе оправдано. Ну или вариант - все клиенты, покупавшие компьютер не менее 2 раз.
10 апр 16, 20:51    [19039582]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy,
Хм, WHERE в моём случае надо было заменить на HAVING так как, мы должны сначала сгруппировать наши заказы по индетификатору товара, сумироватьа уже потом смотреть % от общей прибыли.
Проверил, вроде бы работает как надо, может есть более оптимальный метод записи :)
SELECT Products.ID, Products.Name, Categories.Name FROM Products
INNER JOIN Orders ON Orders.ProductID = Products.ID
INNER JOIN Categories ON Products.CategoryID = Categories.ID
GROUP BY Products.ID, Products.Name, Categories.Name
HAVING SUM(Products.Price * Orders.Quentity) >
(
	SELECT SUM(Products.Price * Orders.Quentity)*0.5 -- 80%
	FROM Products, Orders
	WHERE Orders.ProductID = Products.ID
);

К сожалению не могу добавить, у нас на троих одна база, стажируемся.
10 апр 16, 21:34    [19039715]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
Хотя, кажется...нет..не работает как надо...
10 апр 16, 21:36    [19039730]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe,

я не совсем то имел ввиду :) у вас получилось "вывести категорию, которая дает больше 50% общего оборота". А я имел ввиду категории, которые В СУММЕ дают 80% общего оборота. Для определенности, суммировать категории в порядке убывания оборота. Т.е., допустим, по категориям имеем:

1-я 1000
2-я 800
3-я 400
4-я 200


Общий оборот 2400, вы должны вывести категории, которые в сумме дают не менее 1920, т.е. 1,2,3
10 апр 16, 21:41    [19039755]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe
Хотя, кажется...нет..не работает как надо...


Ну вернее да, вы по продуктам сумму считаете, не по категориям.
10 апр 16, 21:42    [19039765]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy,
Но, первый под запрос возвращает вроде как больше одного значения...
SELECT Categories.Name FROM Categories
WHERE
(
	SELECT SUM(Products.Price * Orders.Quentity) AS Total
	FROM Products
	INNER JOIN Orders ON Orders.ProductID = Products.ID
	GROUP BY Products.ID
)
>
(
	SELECT SUM(Products.Price * Orders.Quentity)*0.8 -- 80%
	FROM Products, Orders
	WHERE Orders.ProductID = Products.ID
);
10 апр 16, 21:44    [19039779]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe
iljy,
Но, первый под запрос возвращает вроде как больше одного значения...


Это вопрос? Запустите и посмотрите, в чем проблема? Если хотите получить сумму для категории, то подзапрос должен быть корелированный, с привязкой к категории, и без GROUP BY, зачем вам раздельные суммы по продуктам?
Кстати, а версия сервера какая?
10 апр 16, 21:54    [19039809]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy,
Теперь работает!
На счёт сервера:
автор
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

SELECT Categories.Name, SUM(Products.Price * Orders.Quentity) AS Total FROM Products
INNER JOIN Orders ON Orders.ProductID = Products.ID
INNER JOIN Categories ON Products.CategoryID = Categories.ID
GROUP BY Categories.Name
HAVING SUM(Products.Price * Orders.Quentity) >
(
	SELECT SUM(Products.Price * Orders.Quentity)*0.8 -- 80%
	FROM Products, Orders
	WHERE Orders.ProductID = Products.ID
);
10 апр 16, 22:03    [19039832]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe
iljy,
Теперь работает!


Да, так лучше. Но все равно не совсем то, что я имел ввиду 8)

unsafe
На счёт сервера:
автор
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)



Значит вам доступны оконные агрегатные функции, и можно сделать нарастающий итог по результату группировки. В качестве подсказки
select *, SUM(s) over(order by s desc, Categories.ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ),
    SUM(s) over()
from (
	SELECT Categories.ID, Categories.Name,
	  SUM(Products.Price * Orders.Quentity) s
	FROM Products
	INNER JOIN Orders ON Orders.ProductID = Products.ID
	INNER JOIN Categories ON Products.CategoryID = Categories.ID
	GROUP BY Categories.ID, Categories.Name
)t
10 апр 16, 22:25    [19039914]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy,
автор
Msg 4104, Level 16, State 1, Line 134
The multi-part identifier "Categories.ID" could not be bound.
10 апр 16, 22:50    [19040058]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe
iljy,
автор
Msg 4104, Level 16, State 1, Line 134
The multi-part identifier "Categories.ID" could not be bound.


Ну оно же вам строку показывает, уберите префикс таблицы внутри OVER, я забыл когда копировал.
10 апр 16, 23:15    [19040188]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy, Оконные функции как я посмотрю вещь зверская, но я ими совсем не пользуюсь в силу не знания.
Вот третье Ваше задание выгляди весьма сложным, я уже встрял на джоинах, буду пытаться.
Спасибо за задания, спокойной ночи.
10 апр 16, 23:23    [19040219]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe
iljy, Оконные функции как я посмотрю вещь зверская, но я ими совсем не пользуюсь в силу не знания.
Вот третье Ваше задание выгляди весьма сложным, я уже встрял на джоинах, буду пытаться.
Спасибо за задания, спокойной ночи.


Зато какая вкусная! ;) А в остальном - лиха беда начало. Тренируйтесь :)
10 апр 16, 23:38    [19040266]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe,

кстати, для тренировки есть очень неплохие задания на сайте sql-ex.ru
10 апр 16, 23:41    [19040274]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
unsafe
Member

Откуда:
Сообщений: 55
iljy,
Спасибо за сайт!
Помогите пожалуйста с:
автор
Для каждой компании вывести категорию, попавшую в максимальное количество заказов

Выходит вроде бы 4 джоина, проблема в том что я не знаю как их правильно записать...
Пробовал с where, но явно каламбур получается
SELECT Company.Name, Products.Name FROM Products, ClientCompanies, Orders, Company, Categories
	WHERE 
	(
		ClientCompanies.CompanyID = Company.ID
		AND
		Orders.ClientCompanyID = ClientCompanies.ID
		AND
		Orders.ProductID = Products.ID
		AND		
		Products.CategoryID = Categories.ID
	)	
	GROUP BY Company.Name, Products.Name
11 апр 16, 21:43    [19044819]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe,

ну смотрите, делаем по порядку:

1. Надо получить таблицу, содержащую связанные между собой компании и категории. Для этого надо последовательно сджойнить ClientCompanies + Orders + Products. Получится широкая таблица, содержащая CategoryID и CompanyID - уникальные ключи, поэтому джойнить Categories и Companies нам пока ни к чему.
2. Теперь по этой таблице считаем, в какое количество заказов для каждой компании попадает каждая категория. Это группировка, по каким полям - сами догадаетесь, а для подсчета количества заказов очень поможет функция COUNT(DISTINCT Orders.ID). Догадаетесь, почему нельзя просто посчитать строки?
3. У нас есть набор, содержащий ИД категорий и компаний, и количество заказов для них. И теперь нам очень поможет оконная ранжирующая функция RANK() OVER(PARTITION BY CompanyID ORDER BY OrdersCount DESC). Строки, относящиеся к отдельной компании, в порядке убывания количества заказов.

Последующие шаги сами догадаетесь
11 апр 16, 21:54    [19044843]     Ответить | Цитировать Сообщить модератору
 Re: Придумайте запросы.  [new]
iljy
Member

Откуда:
Сообщений: 8711
unsafe,

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

Orders o JOIN Products p ON o.ProductID = p.ID
       JOIN ClientCompanies c ON o.ClientCompanyID = c.ID
11 апр 16, 21:57    [19044846]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить