Подскажите ключ к решению

@Timoved · Регистрация: 29.03.2019

Студворк — интернет-сервис помощи студентам

В таблице Bank хранится информация об отделах, дате и прибыли

Таблица Department содержит данные о сотрудниках, их начальниках, отделе в котором работают сотрудники и даты найму сотрудника на работу. Начальник является ссылкой от сотрудника

В таблице Employee – сотрудники, дата и зарплата

Таблица Currency содержит символьный код валюты, дату и значение курса

1. Найти все отделы банка, которые за 2017 год получили прибыль больше 10000 тыс.руб
2. Найти отделы, который получил минимальную прибыль за 2017 год
3. Найти отделы, у которых прибыль меньше средней по всему банку
4. Найти последнюю прибыль и перевести ее в USD
5. Найти сколько сотрудников получают среднюю зп < 1000р
6. Вывести всех начальников отдела, который получил максимальную прибыль за 2017 год
7. Найти начальника с максимальной зарплатой
8. Посчитать средний курс доллара в разрезе месяцев
9. Найти сотрудников у непосредственного начальника и вывести тех, чьи зп больше средней
10. Найти отделы с максимальной суммарной зп начальников

@AndreyVorobey · 08.04.2019, 14:01

первый.
второй говорит, что Employee является внешним ключом для Chief

@Timoved · 08.04.2019, 14:43 **[ТС]**

AndreyVorobey, хотелось бы, чтобы помогли разобраться с первым запросом.
Найти все отделы банка, которые за 2017 год получили прибыль больше 10000 тыс.руб.
Как просуммировать прибыль по каждому отделу? То есть, чтобы для начала выводился отдел и прибыль за всё время.
Далее ведь можно суммировать с условием

T-SQL
1
WHERE Date LIKE '%.2017'

, ну, или поискать функцию вывода года и сравнивать с 2017. А далее уже использовать

T-SQL
1
SELECT Department FROM Bank WHERE Profit > 10000

, но нужен подзапрос. То есть, простым запросом никак нельзя обойтись?

Добавлено через 7 минут

T-SQL
1
SELECT Department As Название, SUM(Profit) As Прибыль FROM Bank WHERE Date LIKE '%.2017' GROUP BY Department;

Добавлено через 27 секунд
Может, можно как-то иначе и красивее переделать?

Добавлено через 45 секунд
Это пока вывод прибыли по каждому отделу банка.

@AndreyVorobey · 08.04.2019, 14:47

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

@Timoved · 08.04.2019, 14:50 **[ТС]**

Ага, значит, я себе задачу совсем усложнил. И запрос будет простым.

@AndreyVorobey · 08.04.2019, 14:52

я не знаю, что хранится в таблице, есть ли смысл в SUM(Profit)? там хранится инфа окончательная, или за каждый месяц по каждому отделу разная прибыль?

@Timoved · 08.04.2019, 14:53 **[ТС]**

Я почему-то считал, что прибыль не годовая, а именно прибыль какого-то месяца. Ну, например, отдел 1001 в марте (31.03.2017) получил прибыль 700 рублей, в апреле (30.04.2017) тот же отдел получил прибыль 800 рублей. В задании ведь не сказано, прибыль за год или за месяц. Думаю, за день прибыль не считается. Или всё же уточнить, какая именно прибыль годовая или месячная?

@AndreyVorobey · 08.04.2019, 14:57

Ну, например, отдел 1001 в марте (31.03.2017) получил прибыль 700 рублей, в апреле (30.04.2017) тот же отдел получил прибыль 800 рублей.

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

отдел 1001 за 2017 год получил прибыль 7 000 рублей, за 2018 год тот же отдел получил прибыль 20 000 рублей.

тогда нужно просто совместить условия из первого запроса с годом, и второго условия, где Profit > 10 000

@Timoved · 08.04.2019, 15:43 **[ТС]**

AndreyVorobey,

T-SQL
1
2
3
USE MyBank
INSERT INTO Department (Employee, Chief, Department, Hire_date)
VALUES (1001, 701, 101, '2015-09-25')

T-SQL
1
2
3
4
5
6
7
8
9
CREATE TABLE Department
(
Employee INT NOT NULL,
Chief INT NOT NULL,
Department INT NOT NULL UNIQUE,
Hire_date DATE NOT NULL,
PRIMARY KEY (Employee),
FOREIGN KEY (Chief) REFERENCES Department(Employee) 
);

Сообщение 547, уровень 16, состояние 0, строка 2
Конфликт инструкции INSERT с ограничением FOREIGN KEY SAME TABLE "FK__Departmen__Chief__7F2BE32F". Конфликт произошел в базе данных "MyBank", таблица "dbo.Department", column 'Employee'.
Выполнение данной инструкции было прервано.
Как поправить?

@AndreyVorobey · 08.04.2019, 16:15

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

@Timoved · 08.04.2019, 16:48 **[ТС]**

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

Добавлено через 10 минут
А как сделать так, чтобы автоматически прописывались значение шефа? Как бы главное - грамотно составить структуры таблиц, то есть спроектировать базу данных. Получается, что поле начальник не должно заполняться. Оно должно ссылаться на поле работника.

@AndreyVorobey · 08.04.2019, 17:01

я бы осмелился эту модель переработать. еще раз, разберите, что за что в модели отвечает.
с таблицей Department я лично не совсем могу разобраться:
1. потому что таблица отвечает за хранение информации об отделе, но в ней почему-то первичный ключ - работник, а не номер отдела.
2. каким образом у одного работника может быть много начальников?
в общем, не могу понять, то ли это я ку-ку совсем, то ли это специально лажу подсунули, то ли кто-то не разбирается, как разбивать в нормальности и разделении сущностей.

@Timoved · 08.04.2019, 18:06 **[ТС]**

AndreyVorobey, вообще-то моя цель спроектировать запросы. Их десять. Думаю, в этой картинке не все верно. Я тоже думал, как у одного работника может быть более одного начальника? Однако, у одного начальника может быть несколько работников. Как это обозначить? Никто не просил срректировать базу данных. Но без проектирования базы данных можно неверные запросы составить. Я имею в виду, если запросы не проверять на исполнение на реальной базе данных. Для того, чтобы проверять запросы, надо грамотно спроектировать саму базу данных. И вот тоже не понятно, что хранится в поле date. Видимо, всё-таки дата, а не год.

Добавлено через 38 минут
Таблица Department содержит данные о сотрудниках, их начальниках, отделе в котором работают сотрудники и даты найму сотрудника на работу. Начальник является ссылкой от сотрудника
Это указано в задании. Как это отразить в бд?

Добавлено через 1 минуту
Возможно, имеют в виду, что начальник - это и есть сотрудник. Возможно.

@AndreyVorobey · 08.04.2019, 18:23

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

@Timoved · 08.04.2019, 19:53 **[ТС]**

AndreyVorobey, то есть, не корректно составлено задание?

Добавлено через 1 минуту
В таком случае, можно ли его изменить? Или этот вопрос следует задать тому, кто задал данное задание?

@AndreyVorobey · 09.04.2019, 09:06

думаю, что нужно изменить подход к реализации, а не задание.

@Meridian21 · 09.04.2019, 09:59

Timoved, судя по картинке из первого поста, я думаю, стоит начать с модификации существующих таблиц и связей. Очевидно, есть очень спорные моменты. Из-за "кривой" схемы, запросы, в лучшем случае, будут работать очень медленно.
Но это актуально, если это какая то самостоятельная работа, а не "вот тебе задача - решай".

@Timoved · 09.04.2019, 11:37 **[ТС]**

Meridian21, AndreyVorobey, вот потому и прошу помочь - подсказать, как грамотно составить структуру базы данных. Чтобы потом можно было было на основании их писать уже требуемые запросы.

@AndreyVorobey · 09.04.2019, 15:49

Мне нравится такая структура базы, может быть можно что-нибудь модернизировать. По-быстрому разнес отдельные сущности по разным таблицам:

Таблица Currency (наименование валюты):
1. Currency_id
2. Currency_Name

Таблица Rate (курсы валют на дату):
1. Date_Rate
2. Rate
3. Currency_id

Таблица Department (справочник отделов):
1. Department_id
2. Department_Name

Таблица Employee (сотрудники):
1. Department_id
2. Employee_id
3. Employee_FullName
4. Hire_Date

Таблица Chief (начальники):
1. Department_id
2. Chief_id
3. Chief_FullName
4. Hire_Date

Таблица Salary (зарплата):
1. Date_Salary
2. Summ_Salary
3. Employee_Id
4. Chief_Id

Таблица Profit (прибыль отдела):
1. Date_Profit
2. Summ_Profit
3. Currency_Id_Profit
4. Department_Id

@pincet · 09.04.2019, 16:13

начальник и подчиненный - это работник, со ссылкой на начальника

@Timoved · 09.04.2019, 18:42 **[ТС]**

AndreyVorobey, не очень нравится структура. По причине повторяющихся элементов. Может, я не прав.
Обосновываю.
Таблица Department (справочник отделов):
1. Department_id
2. Department_Name
3. Chief_FullName - ссылка на работника из таблицы сотрудники.

Таблица Chief (начальники): - не нужна как бы.
Таблица Salary (зарплата):
1. Date_Salary
2. Summ_Salary
3. Employee_Id
4. Chief_Id - этот пункт не нужен как бы уже.

Добавлено через 39 секунд
AndreyVorobey, и ещё. Как быть всё-таки: переделывать под описанные сущности или всё-таки как в задании?

Новые блоги и статьи Все статьи Все блоги /
Беседа с ИИ о программистах, недопускающих к созданию и правке кода генеративные ИИ и причины этого zorxor 21.09.2026 Раньше я радовался или получал некоторые эмоции, пусть небольшие, но всё же, от самого процесса написания кода, рекомпиляции и запуска, видя постепенное развитие программы и прочее. А теперь лень. . .	Мобильное приложение ColorStep pavlinmavlin 17.09.2026 Реализовал приложение Красный, Зеленый, Синий в Unity3d + c#. Название изменил на ColorStep. Приложение прошло модерацию и теперь доступно для скачивания. Делал его сам, шаг за шагом — и вот,. . .	Запрет дублирования строк в табличной части Maks 13.09.2026 Реализация из решения ниже выполнена на нетиповом справочнике "Нормы ТО" с табличной часть "Виды ТО", разработанного в КА2, со следующими реквизитами: - ВидТО (СправочникСсылка. ВидыТО); - ВидГСМ. . .	Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Jin X 06.09.2026 Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Работая с форумом и нейросетями в браузере часто хочется что-то подкорректировать или добавить какого-то функционала. Ниже прикреплён. . .
Программа опроса у.з. расходомера SLS-720F Argus19 02.09.2026 Программа опроса у. з. расходомера SLS-720F Программа опрашивает один раз в минуту три ультразвуковых расходомера SLS-720F через интерфейс RS-485 по протоколу Modbus RTU. Опрашиваются регистры. . .	Hyper-V: Компьютер должен поддерживать доверенный платформенный модуль 2.0. Maks 31.08.2026 При установке Windows 11 на виртуальную машину Hyper-V 2-го поколения вылезла такая ошибка: Решение: в параметрах виртуальной машины, в разделе "Безопасность" (Security) активировать флаг. . .	Архитектура биовида Стива в Майнкрафте: Зачем бонобо кубический каннибализм anaschu 30.08.2026 Кубический Вагинокапитализм в Minecraft: Математический инвариант ОДУ и рок Стивов-бонобо Главная задача разработанной «Модели Всего» — наглядно продемонстрировать наличие системной «судьбы». . .	Оттачиваю умение писать js программы. russiannick 30.08.2026 Проектом выходного дня стало написание Книги шифров Виженера. Итогом стала версия 200, синий туман. Синий туман назван так, потому что замораживает текст под собой. Нажатие синих кнопок управляют. . .

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61

	Подскажите ключ к решению 02.04.2019, 19:21. Показов 3282. Ответов 40 Метки sql, банк (Все метки) В таблице Bank хранится информация об отделах, дате и прибыли Таблица Department содержит данные о сотрудниках, их начальниках, отделе в котором работают сотрудники и даты найму сотрудника на работу. Начальник является ссылкой от сотрудника В таблице Employee – сотрудники, дата и зарплата Таблица Currency содержит символьный код валюты, дату и значение курса 1. Найти все отделы банка, которые за 2017 год получили прибыль больше 10000 тыс.руб 2. Найти отделы, который получил минимальную прибыль за 2017 год 3. Найти отделы, у которых прибыль меньше средней по всему банку 4. Найти последнюю прибыль и перевести ее в USD 5. Найти сколько сотрудников получают среднюю зп < 1000р 6. Вывести всех начальников отдела, который получил максимальную прибыль за 2017 год 7. Найти начальника с максимальной зарплатой 8. Посчитать средний курс доллара в разрезе месяцев 9. Найти сотрудников у непосредственного начальника и вывести тех, чьи зп больше средней 10. Найти отделы с максимальной суммарной зп начальников Миниатюры 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	08.04.2019, 14:01
	первый. второй говорит, что Employee является внешним ключом для Chief 1

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	08.04.2019, 14:47
	не совсем понятно, для чего суммировать прибыль? второй запрос подразумевает, что в таблице имеются записи с уже итоговой годовой прибылью. 1

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	08.04.2019, 14:50 [ТС]
	Ага, значит, я себе задачу совсем усложнил. И запрос будет простым. 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	08.04.2019, 14:52
	я не знаю, что хранится в таблице, есть ли смысл в SUM(Profit)? там хранится инфа окончательная, или за каждый месяц по каждому отделу разная прибыль? 0

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	08.04.2019, 14:53 [ТС]
	Я почему-то считал, что прибыль не годовая, а именно прибыль какого-то месяца. Ну, например, отдел 1001 в марте (31.03.2017) получил прибыль 700 рублей, в апреле (30.04.2017) тот же отдел получил прибыль 800 рублей. В задании ведь не сказано, прибыль за год или за месяц. Думаю, за день прибыль не считается. Или всё же уточнить, какая именно прибыль годовая или месячная? 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	08.04.2019, 16:15
	все верно, внешний ключ должен соответствовать первичному ключу, из-за чего и организуется связь. в данном случае Chief должен быть равен Employee. если честно, сомнительная модель у Вас в приципе, откуда первоначальный пример брали? 0

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	08.04.2019, 16:48 [ТС]
	AndreyVorobey, такое задание и картинку выдали в качестве тестового задания. И думай теперь, что писать в разных полях. Ну, тип date, значит, должен быть дд.мм.гггг. Добавлено через 10 минут А как сделать так, чтобы автоматически прописывались значение шефа? Как бы главное - грамотно составить структуры таблиц, то есть спроектировать базу данных. Получается, что поле начальник не должно заполняться. Оно должно ссылаться на поле работника. 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	08.04.2019, 17:01
	я бы осмелился эту модель переработать. еще раз, разберите, что за что в модели отвечает. с таблицей Department я лично не совсем могу разобраться: 1. потому что таблица отвечает за хранение информации об отделе, но в ней почему-то первичный ключ - работник, а не номер отдела. 2. каким образом у одного работника может быть много начальников? в общем, не могу понять, то ли это я ку-ку совсем, то ли это специально лажу подсунули, то ли кто-то не разбирается, как разбивать в нормальности и разделении сущностей. 0

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	08.04.2019, 18:06 [ТС]
	AndreyVorobey, вообще-то моя цель спроектировать запросы. Их десять. Думаю, в этой картинке не все верно. Я тоже думал, как у одного работника может быть более одного начальника? Однако, у одного начальника может быть несколько работников. Как это обозначить? Никто не просил срректировать базу данных. Но без проектирования базы данных можно неверные запросы составить. Я имею в виду, если запросы не проверять на исполнение на реальной базе данных. Для того, чтобы проверять запросы, надо грамотно спроектировать саму базу данных. И вот тоже не понятно, что хранится в поле date. Видимо, всё-таки дата, а не год. Добавлено через 38 минут Таблица Department содержит данные о сотрудниках, их начальниках, отделе в котором работают сотрудники и даты найму сотрудника на работу. Начальник является ссылкой от сотрудника Это указано в задании. Как это отразить в бд? Добавлено через 1 минуту Возможно, имеют в виду, что начальник - это и есть сотрудник. Возможно. 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	08.04.2019, 18:23
	начальник - это отдельная сущность, как и сотрудник, и он должен быть вынесен в другую таблицу. дата найма сотрудника должна быть привязана к таблице Employee, а не к Department. чтобы смоделировать схему базы данных, нужно понять суть задачи, а для этого нужно сесть и разобраться, что куда к чему. 0

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	08.04.2019, 19:53 [ТС]
	AndreyVorobey, то есть, не корректно составлено задание? Добавлено через 1 минуту В таком случае, можно ли его изменить? Или этот вопрос следует задать тому, кто задал данное задание? 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	09.04.2019, 09:06
	думаю, что нужно изменить подход к реализации, а не задание. 0

@Meridian21 Zero day 267 / 231 / 78 Регистрация: 16.12.2015 Сообщений: 943 Записей в блоге: 4
	09.04.2019, 09:59
	Timoved, судя по картинке из первого поста, я думаю, стоит начать с модификации существующих таблиц и связей. Очевидно, есть очень спорные моменты. Из-за "кривой" схемы, запросы, в лучшем случае, будут работать очень медленно. Но это актуально, если это какая то самостоятельная работа, а не "вот тебе задача - решай". 0

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	09.04.2019, 11:37 [ТС]
	Meridian21, AndreyVorobey, вот потому и прошу помочь - подсказать, как грамотно составить структуру базы данных. Чтобы потом можно было было на основании их писать уже требуемые запросы. 0

@AndreyVorobey 2810 / 1679 / 885 Регистрация: 14.04.2015 Сообщений: 5,723
	09.04.2019, 15:49
	Мне нравится такая структура базы, может быть можно что-нибудь модернизировать. По-быстрому разнес отдельные сущности по разным таблицам: Таблица Currency (наименование валюты): 1. Currency_id 2. Currency_Name Таблица Rate (курсы валют на дату): 1. Date_Rate 2. Rate 3. Currency_id Таблица Department (справочник отделов): 1. Department_id 2. Department_Name Таблица Employee (сотрудники): 1. Department_id 2. Employee_id 3. Employee_FullName 4. Hire_Date Таблица Chief (начальники): 1. Department_id 2. Chief_id 3. Chief_FullName 4. Hire_Date Таблица Salary (зарплата): 1. Date_Salary 2. Summ_Salary 3. Employee_Id 4. Chief_Id Таблица Profit (прибыль отдела): 1. Date_Profit 2. Summ_Profit 3. Currency_Id_Profit 4. Department_Id 0

@pincet 1655 / 1154 / 173 Регистрация: 23.07.2010 Сообщений: 6,914
	09.04.2019, 16:13
	начальник и подчиненный - это работник, со ссылкой на начальника 0

@Timoved 0 / 0 / 0 Регистрация: 29.03.2019 Сообщений: 61
	09.04.2019, 18:42 [ТС]
	AndreyVorobey, не очень нравится структура. По причине повторяющихся элементов. Может, я не прав. Обосновываю. Таблица Department (справочник отделов): 1. Department_id 2. Department_Name 3. Chief_FullName - ссылка на работника из таблицы сотрудники. Таблица Chief (начальники): - не нужна как бы. Таблица Salary (зарплата): 1. Date_Salary 2. Summ_Salary 3. Employee_Id 4. Chief_Id - этот пункт не нужен как бы уже. Добавлено через 39 секунд AndreyVorobey, и ещё. Как быть всё-таки: переделывать под описанные сущности или всё-таки как в задании? 0