Автоматизация работы строительной фирмы

 where материалы.Количество_материала  in (10,20) /*выбор материала количество которого 10 либо 20 */ 

  /*4. Вывод информации о сотрудниках, с фамилией заканчивающейся на «ов»*/

  CREATE VIEW Информация_Сотрудники  /*Указываем имя представления*/

  AS

  SELECT             /*Указываем, какие поля будут выведены*/        

  Сотрудники.Табельный_номер, Сотрудники.ФИО, Сотрудники.Должность, Сотрудники.Номер_паспорта, Сотрудники.Оклад, Сотрудники.Адрес,

   Сотрудники.Стаж,Сотрудники.Номер_бригады,Сотрудники.Номер_соц_страховки

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

  WHERE Сотрудники.ФИО  LIKE '%ов%'/*Выберает сотрудников с фамилией заканчивающейся на «ов»*/ 

  /*5 Вывод информации о материале, количество, которого находится в диапазоне от 30 до 50.*/

  CREATE VIEW Информация_Количество_материалов   /*Указываем имя представления*/

  AS

  SELECT                      /*Указываем, какие поля будут выведены*/        

  материалы.Код_материала,материалы.Наименование_материала,

  материалы.Стоимость_материалов,материалы.Количество_материала

  from материалы                  /*Из какой таблицы*/                                

  WHERE материалы.Количество_материала BETWEEN 30 AND 50 /*Выбор материала количество которого находится в диапазоне от 30 до 50*/ 

  /*6. Вывод количества клиентов.*/

  Create view Количество_клиентов  /*Указываем имя представления*/

  As

  Select   /*Указываем, какие поля будут выведены*/                                   

  количество_клиентов=count(клиенты.Номер_клиента) /*Подсчет количества*/

  from клиенты  /*Из какой таблицы*/       

    /*7. Вывод стоимости услуги с максимальной стоимостью.*/

    Create view Максимальная_услуга_по_стоимости   /*Указываем имя представления*/

    as

  Select    /*Указываем, какие поля будут выведены*/                                   

  Максимальная_стоимость=max(услуги.Стоимость_услуги) /*Поиск максимальной стоимости услуги*/ 

    from услуги /*Из какой таблицы*/       

    /*8. Вывод информации о бригаде с минимальным количеством сотрудников*/

    CREATE VIEW минимальное_количество_в_бригаде  /*Указываем имя представления*/

    AS

    SELECT   /*Указываем, какие поля будут выведены*/

    Бригады.Номер_бригады,

    Бригады.ФИО_бригадира,Бригады.Количество_в_бригаде

    from Бригады   /*Из какой таблицы*/     

    WHERE Бригады.Количество_в_бригаде= (select Min(Количество_в_бригаде) from Бригады)/*Выбор бригады, в которой минимальное количество сотрудников*/ 
 

    /*9. Вывод информации о сотрудникес окладом большем чем 1600000 или равным 1200000*/

    CREATE VIEW Инфо_оклад_сотрудников  /*Указываем имя представления*/

    AS

    SELECT  /*Указываем, какие поля будут выведены*/

    Сотрудники.Табельный_номер,Сотрудники.ФИО,Сотрудники.Номер_бригады,Сотрудники.Должность,Сотрудники.Оклад

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

    WHERE Сотрудники.Оклад > 1600000 or Сотрудники.Оклад =1200000 /*Выбор сотрудника , с окладом более 1600000 или равным 1200000*/ 

    /*10.Вывод информации о стоимости всех материалов.*/

    Create view Стоимость_всех_материалов    /*Указываем имя представления*/

    As

    Select   /*Указываем, какие поля будут выведены*/

    Общая_стоимость_материалов=sum(материалы.Количество_материала*материалы.Стоимость_материалов)   /*Высчитываем общую стоимость материалов*/     

    from материалы    /*Из какой таблицы*/       

     /*11. Вывод информации о услугах стоимость, которых меньше либо равно средней.*/

     Create view услуга_со_стоимостью_ниже_средней   /*Указываем имя представления*/

     As

     Select   /*Указываем, какие поля будут выведены*/

     услуги.Номер_услуги,услуги.Услуга,услуги.Стоимость_услуги

     From услуги    /*Из какой таблицы*/     

     where услуги.Стоимость_услуги <=(select avg(Стоимость_услуги) From услуги)      /*Выбор услуги, где стоимость меньше либо равна средней стоимости услуг*/

       

     /*12. Вывод информации о заказах, которые зданы*/

     CREATE VIEW Заказы_которые_зданы   /*Указываем имя представления*/

     AS

     SELECT                      /*Указываем, какие поля будут выведены*/

       заказы.Номер_заказа, заказы.Адрес_объекта,  заказы.Дата_здачи

     FROM заказы                /*Из какой таблицы*/                        

     WHERE заказы.Дата_здачи< Getdate()/*Выбор заказа у которого срок здачи меньше текущей даты*/ 

     /*13. Вывод информации о заказах, которые будут зданы через 30 дней*/

     CREATE VIEW Объекты_которые_будут_зданы_через_30_дней   /*Указываем имя представления*/

     AS

     SELECT                   /*Указываем, какие поля будут выведены*/  

     заказы.Номер_заказа, заказы.Адрес_объекта, заказы.Дата_здачи

     FROM заказы           /*Из какой таблицы*/                                           

     WHERE заказы.Дата_здачи<DateAdd(day,30,заказы.Дата_здачи) and заказы.Дата_здачи>Getdate()   /*Выбор заказа у которого срок здачи истечет через 30 дней*/       

     /*14. Вывод информации о клиентах,у  которых заказ более 60 дней*/

     CREATE VIEW Клиенты_с_заказом_больше_60_дней  /*Указываем имя представления*/

     AS

     SELECT          /*Указываем, какие поля будут выведены*/              

     клиенты.Номер_клиента,клиенты.Дата_заказа,клиенты.Ответственное_лицо

     FROM  клиенты          /*Из какой таблицы*/                                                                    

     WHERE  DateDiff(day,клиенты.Дата_заказа,Getdate())>60    /*Выбор клиента с заказом более 60 дней*/           

     /*15. Вывод информации о клиентах*/

     CREATE VIEW Информация_о_клиентах   /*Указываем имя представления*/

     AS

     SELECT               /*Указываем, какие поля будут выведены*/                      

     клиенты.Номер_клиента,клиенты.Ответственное_лицо,

     День=Day( клиенты.Дата_заказа),Месяц=Month( клиенты.Дата_заказа),Год=Year( клиенты.Дата_заказа)

     FROM клиенты      /*Из какой таблицы*/                                                                                                                       
 
 

     3.2 T-SQL-определения триггеров

    /*2. Триггер, который записывает при  добавлении записей в таблицу  «услуги» в отдельную таблицу  информацию о дате добавления, пользователе.*/

    CREATE TRIGGER proverka_Uslugi /*Обьявляем имя триггера*/

    ON услуги /*Указываем имя таблицы, с которой будет связан триггер*/

    FOR INSERT/*Указываем операцию, на которую будет срабатывать триггер

    (на  вставку)*/

    AS

    INSERT INTO

    /* вставка записи в таблицу TrUskugi */

    TrUskugi(Номер_услуги,Услуга,Стоимость_услуги,Имя_пользователя,Дата_

    добавления)

    /* выбираем, какие записи вставить  в таблицу */

    SELECT Номер_услуги,Услуга,Стоимость_услуги,

    SYSTEM_USER,

    getdate()

    FROM inserted 

    /*Перед  созданием такого триггера необходимо  создать таблицу TrUskugi,

    куда  будет производится запись:*/

    CREATE TABLE TrUskugi /*Обьявляем имя таблицы*/ (

    [Номер_услуги][int] NULL , /*Обьявляем полей таблицы*/

    [Услуга] [varchar](20) NOT NULL , /*Обьявляем полей таблицы*/

    [Стоимость_услуги][int] NULL , /*Обьявляем полей таблицы*/

    [Имя_пользователя] [varchar] (50) NULL , /*Обьявляем полей таблицы*/

    [Дата_добавления] [datetime] NULL /*Обьявляем полей таблицы*/

    ) ON [PRIMARY] 

    Аналагичные хранимые процедуры:

    proverka_Sakazi 3 - Триггер, который записывает при добавлении записей в таблицу «заказы» в отдельную таблицу информацию о дате добавления, пользователе.

    proverka_Postav 4 - Триггер, который записывает при добавлении записей в таблицу «поставщики» в отдельную таблицу информацию о дате добавления, пользователе.

    proverka_Mater 5 - Триггер, который записывает при добавлении записей в таблицу «услуги» в отдельную таблицу информацию о дате добавления, пользователе. 

    /*1. Триггер, запрещающий ввод значения  в поле «дата_заказа», таблицы

    Заказы если оно превышает номер текущего года.*/

    create TRIGGER proverka_Clientov/*Обьявляем имя триггера*/

    ON клиенты/*Указываем имя таблицы, с которой будет связан триггер*/

    FOR INSERT

    AS

    DECLARE @@t  int/*Объявляем переменную*/

    Set @@t=5/*Инициализируем переменную*/

    IF NOT EXISTS (SELECT * FROM клиенты, inserted

    WHERE клиенты.Дата_заказа = inserted.Дата_заказа) /*Проверка разницы

    между текущей датой и датой рождения заказа*/

    Set @@t=0

    IF EXISTS (SELECT * FROM клиенты, inserted

    WHERE  inserted.Дата_заказа>GETDATE())

    Set @@t=0 /*Если условие выполняется, то обнуляем переменную*/

    If @@t=0/*Если ошибка*/

    BEGIN

    PRINT 'Неверно введена дата заказа'

    ROLLBACK TRANSACTION

    END 

    /*6. Триггер, который записывает при удаление записей из таблицы «услуги» в отдельную таблицу информацию об удаленных записях.*/

    CREATE TRIGGER T_uslugi_1

    ON услуги /*Связываем с таблицей*/

    FOR DELETE

    AS

    INSERT INTO uslugi_ud  /*Указываем таблицу,  куда будет производиться 

    запись*/

    (Номер_услуги,Услуга,Стоимость_услуги) /*Перечисляем поля таблицы*/

    SELECT /*Перечисляем вносимые значения*/

    Номер_услуги,Услуга,Стоимость_услуги

    FROM deleted /*deleted – временная таблица,  куда заносятся удаляемые 

    данные*/ 

    /*Перед  созданием такого триггера необходимо создать таблицу

    sotrudnik_ud, куда будет производится запись:*/

     CREATE TABLE sotrudnik_ud (

     [Номер_услуги][int] NULL ,

     [Услуга] [varchar](20) NOT NULL ,

     [Стоимость_услуги][int] NULL

     ) ON [PRIMARY] 

    Аналагичные хранимые процедуры: