3. Реализация системы

3.1. T-sql-определения регламентированных запросов

1. Вывести список выполненых заявок

CREATE VIEW [dbo].[Вывести список выполненых заявок] /*Указываем имя представления*/

AS

SELECT /*Указываем, какие поля будут выведены/* dbo.Квартиросъемщики.Фамилия, MONTH(dbo.Заявки.[Дата подачи заявки]) AS [Месяц подачи заявки], dbo.Квартиросъемщики.Имя,

dbo.Квартиросъемщики.Отчество, dbo.Заявки.Результат, dbo.Заявки.Описание

FROM /*Из каких таблицы*/ dbo.Квартиросъемщики INNER JOIN

dbo.Заявки ON dbo.Квартиросъемщики.[Номер квартиросъемщика] = dbo.Заявки.[Номер квартиросъемщика]

WHERE (dbo.Заявки.Результат = 'Готово') /*Указываем критерий отбора данных: все заявки которые имеют Результат = “Готово”.*/

2. Вывести информацию о сотрудниках с высшим

CREATE VIEW [dbo].[Вывести информацию о сотрудниках с высшим образованием] /*Указываем имя представления*/

AS

SELECT /*Указываем, какие поля будут выведены*/ [Номер отдела], Имя, Фамилия, Отчество, Должность, Образование, Пол, DATENAME(MONTH, [Дата рождения]) AS Месяц, [Стаж работы], Телефон

FROM dbo.Сотрудники/*Из каких таблицы*/

WHERE (Образование = 'Высшее') /*Указываем критерий отбора данных: все заявки которые имеют Результат = “Готово”.*/

3. Вывести информацию о договорах с истекшим сроком ;

CREATE VIEW [dbo].[Вывести информацию о договорах с истекшим сроком]

AS

SELECT dbo.Квартиросъемщики.Фамилия, dbo.Квартиросъемщики.Имя, dbo.Квартиросъемщики.Отчество, dbo.[Дома на обслуживании].Адрес, dbo.Договор.Начало,

dbo.Договор.Конец, dbo.Договор.[Номер квартиры], dbo.Услуги.[Название услуги]

FROM dbo.Договор INNER JOIN

dbo.[Дома на обслуживании] ON dbo.Договор.[Код дома] = dbo.[Дома на обслуживании].[Код дома] INNER JOIN

dbo.Квартиры ON dbo.[Дома на обслуживании].[Код дома] = dbo.Квартиры.[Код дома] INNER JOIN

dbo.Квартиросъемщики ON dbo.Квартиры.[Номер квартиросъемщика] = dbo.Квартиросъемщики.[Номер квартиросъемщика] INNER JOIN

dbo.Услуги ON dbo.Договор.[Номер услуги] = dbo.Услуги.[Номер услуги]

WHERE (dbo.Договор.Конец BETWEEN 2000 AND GETDATE())/*Указываем критерий отбора данных: все договора у которых конец договора лежит в промежутке от 2000г до сегодняшней даты.*/

4. Выдать информацию о сотруднике по фамилии;

CREATE VIEW [dbo].[Выдать информацию о сотруднике по фамилии]

AS

SELECT [Номер отдела], Фамилия, Имя, Отчество, Должность, Образование, Пол, Телефон, [Дата рождения], [Стаж работы]

FROM dbo.Сотрудники

WHERE (CHARINDEX('Кр', Фамилия) > 0) /*Указываем критерий отбора данных:выбрать сотрудников с фамилией начинающейся с ‘Кр’.*/

5. Информация о домах c общей площадью >100;

CREATE VIEW [dbo].[Информация о домах c общей площадью >100]

AS

SELECT [Колличество подъездов], Адрес, YEAR([Год постройки]) AS 'Год постройки', [Общая площадь], [Колличество квартир], [Жилая площадь], Подвал,

[Вид кровли], [Площадь кровли]

FROM dbo.[Дома на обслуживании] WHERE [Общая площадь]>100

/*Указываем критерий отбора данных: дома с общей площадью >100 к.м. */

6. Информация о сотруднике с самой маленькой зарплатой;

SELECT dbo.Сотрудники.Имя, dbo.Сотрудники.Фамилия, dbo.Сотрудники.[Стаж работы], dbo.Сотрудники.[Дата рождения], dbo.Сотрудники.Телефон,

dbo.Сотрудники.Пол, dbo.Сотрудники.Образование, dbo.Сотрудники.Отчество, dbo.Сотрудники.Должность, dbo.Сотрудники.[Номер отдела],

dbo.Зарплата.Зарплата

FROM dbo.Зарплата INNER JOIN

dbo.Сотрудники ON dbo.Зарплата.[Табельный номер] = dbo.Сотрудники.[Табельный номер]

WHERE (dbo.Зарплата.Зарплата =

(SELECT MIN(Зарплата_1.Зарплата) AS 'Минимальная зарплата' FROM dbo.Зарплата AS Зарплата_1 INNER JOIN

dbo.Сотрудники AS Сотрудники_1 ON Зарплата_1.[Табельный номер] = Сотрудники_1.[Табельный номер])) /*Указываем критерий отбора данных: используя подзапрос и функцию min выбираем сотрудника с минимальной зарплатой */

7. Информация об услуги со стоимостью выше средней;

CREATE VIEW [dbo].[Информация о услуги со стоимостью выше средней]

AS

SELECT [Название услуги], [Стоимость услуги]

FROM dbo.Услуги

WHERE ([Стоимость услуги] > (SELECT AVG([Стоимость услуги]) FROM dbo.Услуги AS Услуги)) /*Указываем критерий отбора данных: используя подзапрос и функцию avg выбираем услуги со стоимостью выше средней */

8. Максимальная зарплата каждого сотрудника;

CREATE VIEW [dbo].[Максимальная зарплата каждого сотрудника]

AS

SELECT TOP (100) PERCENT dbo.Сотрудники.Фамилия AS Фамилия_, MAX(dbo.Зарплата.Зарплата) AS [Заработная плата]

/*находим максимальную зарплату сотрудника функцией max */

FROM dbo.Зарплата INNER JOIN

dbo.Сотрудники ON dbo.Зарплата.[Табельный номер] = dbo.Сотрудники.[Табельный номер] INNER JOIN

dbo.Отделы ON dbo.Сотрудники.[Номер отдела] = dbo.Отделы.[Номер отдела]

GROUP BY dbo.Сотрудники.Фамилия/*группируем записи по фамилии сотрудника */

9. Общая сумма квартир;

CREATE VIEW [dbo].[Общая сумма квартир]

AS

SELECT SUM([Колличество квартир]) AS [Общая сумма квартир]

/*осуществляем подсчет количества квартир функцией sum/*

FROM dbo.[Дома на обслуживании]

10. Поданные данные по воде за последние 150 дней;

SELECT dbo.Квартиросъемщики.Фамилия, dbo.Квартиросъемщики.Имя, dbo.[Данные со счетчиков].Вода, dbo.[Данные со счетчиков].Дата

FROM dbo.[Данные со счетчиков] INNER JOIN

dbo.Квартиросъемщики ON dbo.[Данные со счетчиков].[Номер квартиросъемщика] = dbo.Квартиросъемщики.[Номер квартиросъемщика]

WHERE (dbo.[Данные со счетчиков].Дата BETWEEN

(SELECT DATEADD(DAY, - 150, GETDATE()) AND GETDATE())

/*Указываем критерий отбора данных: записи Дата которых лежит в промежутке от -150 дней до сегодняшней даты */

11. Подсчитать количество сотрудников с высшим образованием;

CREATE VIEW [dbo].[Подсчитать количество сотрудников с высшем образованием] AS

SELECT COUNT([Табельный номер]) AS Количество_Сотрудников, Образование /*осуществляем подсчет количества сотрудников функцией count/*

FROM dbo.Сотрудники

WHERE (Образование = N'Высшее') /*Указываем критерий отбора данных: выбираем сотрудников у которых Образование = ‘Высшее’*/

12. Поиск информации о сотруднике по первой букве фамилии;

CREATE VIEW [dbo].[Поиск информации о сотруднике по первой букве фамилии] AS SELECT Фамилия, Имя, [Номер отдела], Должность, Образование, Пол, Телефон, [Дата рождения], [Стаж работы]

FROM dbo.Сотрудники

WHERE (LEFT(Фамилия, 1) = 'К') /*Указываем критерий отбора данных: выбираем сотрудников у которых фамилия начинается на ‘К’ функцией Left*/

13. Разбиение зарплаты по группам;

CREATE VIEW [dbo].[Разбиение зарплаты по группам]

AS

SELECT dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, dbo.Зарплата.Зарплата,

CASE NTILE(4) OVER (ORDER BY Зарплата.Зарплата ASC)

WHEN 1 THEN 'Маленькая' WHEN 2 THEN 'Средняя'

WHEN 3 THEN 'Большая' WHEN 4 THEn 'Огромная'

END AS 'Величина зарплаты' /*делаем разбивку всех записей по величине зарплаты функцией Ntile*/

FROM dbo.Зарплата INNER JOIN

dbo.Сотрудники ON dbo.Зарплата.[Табельный номер] = dbo.Сотрудники.[Табельный номер]

14. Список должников c задолжностью >100000;

CREATE VIEW [dbo].[Список должников c задолжностью >100000]

AS SELECT dbo.Квартиросъемщики.Фамилия, dbo.Квартиросъемщики.Имя, dbo.Квартиросъемщики.Отчество, DAY(dbo.Должники.[Последний срок оплаты]) AS 'День последнего срока оплаты', MONTH(dbo.Должники.[Последний срок оплаты]) AS 'Месяц последнего срока оплаты', YEAR(dbo.Должники.[Последний срок оплаты]) AS 'Год последнего срока оплаты', dbo.Должники.Пеня, dbo.Должники.Задолженость

FROM dbo.Должники INNER JOIN dbo.Квартиросъемщики ON dbo.Должники.[Номер квартиросъемщика] = dbo.Квартиросъемщики.[Номер квартиросъемщика]

WHERE dbo.Должники.Задолженость /*Указываем критерий отбора данных: выбираем должников с задолженностью > 100 000 т.р.*/