use master;

go

if exists(select * from sys.databases where name='build2')

begin

drop database build2;

end

go

create database build2;

go

use build2;

go

create table zakazchik(kod_zakazchika int primary key, name varchar(100),nomer_licens int, INN int, phone varchar(15), adres varchar(50));

create table job (kod_job int primary key, job_name varchar(30));

create table worker (kod_worker int primary key, worker_name varchar (50), job int references job (kod_job));

create table build_org (INN int primary key, nomer_licens int, name varchar (20),phone varchar(15), adres varchar(100));

create table service_name (service_name_kod int primary key, service_name_name varchar(50));

create table service (service_kod int primary key, servce_name int references service_name(service_name_kod), service_info varchar(5000), service_price varchar(10) );

create table os_object(os_object_kod int primary key, os_object_name varchar(50));

create table info_os_object (info_kod int primary key, inventory_number varchar(15), passport_number varchar(15), produce_number varchar(15), os_object_name int references os_object(os_object_kod), fact_usage int);

create table money_lost (kod_zatrat int primary key, os_object int references os_object(os_object_kod),work_type int references service(service_kod), demolit_price varchar(10), repair_act_price varchar(10), mod_act_price varchar(10), fact_rep_price varchar(10), fact_mod_price varchar(10), fact_transp_price varchar(10), adittion varchar(5000) );

create table act_PS(act_number int primary key,ispolnit int references build_org(INN) ,response_person int references worker(kod_worker),zakazchik int references zakazchik(kod_zakazchika), sign_date date, finish_date date, end_price int references money_lost(kod_zatrat),result int references info_os_object(info_kod) );

6. Структура таблиц и данные

Покажем структуру таблиц в базе данных build2 (рис.3).

Рис. 3. Структура таблиц

7. Заполнение таблиц

Должности:

insert into dbo.job values

(001,'генеральный директор'),

(002, 'заместитель директора'),

(003,'главный архитектор'),

(004,'архитектор'),

(005,'прораб'),

(006,'главный бухгалтер'),

(007,'бухгалтер');

Обьекты ОС:

insert into os_object values

(101,'цех'),

(102,'склад'),

(103,'лаборатория'),

(104,'гараж'),

(105,'примыкающее сооружение'),

(106,'туннель'),

(107,'автодорога'),

(108,'электросеть'),

(109,'теплосеть'),

(110,'цеховое оборудование');

Название услуг:

insert into dbo.service_name values

(201,'ремонт'),

(202,'модернизация'),

(203,'замена'),

(204,'реконструкция'),

(205,'демонтаж'),

(206,'обсуживание'),

(207,'строительство'),

(208,'дизан'),

(209,'проэктирование'),

(210,'продажа');

Описание услуг:

insert into dbo.service values

(1001,201,'перекрытий','50000'),

(1002,201,'внутренняя отделка','30000'),

(1003,201,'наружняя отделка','60000'),

(1004,202,'перекрытий','51000'),

(1005,202,'несущих конструкций','85000'),

(1006,203,'электропроводки','22000'),

(1007,203,'сантехники','12000'),

(1008,204,'памятника архитектуры','120000'),

(1009,205,'здания','600000'),

(1010,205,'подсобных помещений','200000'),

(1011,206,'построенных обьектов','20000'),

(1012,206,'теплосетей','25000'),

(1013,207,'бассейнн','400000'),

(1014,207,'автодорога','1000000'),

(1015,208,'зданий','50000'),

(1016,209,'парковок','55000'),

(1017,209,'телекоммуникационных сетей','110000');

Описание Обьектов ОС:

insert into dbo.info_os_object values

(2001,'987986214','32654984','32165498479',101,5),

(2002,'679833568','13134616','13576757365',101,58),

(2003,'342311752','3223126373','32156613',102,37),

(2004,'247245245','1345136547','243568456',102,101),

(2005,'835624634','13477623525','3684635',103,76),

(2006,'315646560','354343543','498749876',103,3),

(2007,'13624789','23456234','5745867',104,43),

(2008,'858763987','956948076','3945689',104,25),

(2009,'898450234','68583835625','23452646',105,6),

(2010,'3452456','674674','23434753',105,1),

(2011,'42562457','425756853','3245624',106,141),

(2012,'425624623','547456834','2133412345',106,59),

(2013,'3422534','56760068','567850',107,6),

(2014,'2456245','2467563','23246',107,31),

(2015,'52624526','24573567','3426245',108,5),

(2016,'57358635','24562467','78464568',108,17),

(2017,'42564357536','245735673','246245624',109,76),

(2018,'487689','688765446','9879871598',109,46),

(2019,'65465','87981','21498',110,9),

(2020,'65465','65465','289496',110,15);

база данные триггер таблица

Информация о заказчиках:

insert into dbo.zakazchik values

(3001,'ЦПКиО','675437','345643','2549431','центр 7'),

(3002,'Сибавто','531343','685673','2311534','ватная 37'),

(3003,'ТЦ АУРА','5638224','7845675','3543265','военная 6'),

(3004,'ФГБУ Новосибирская МВЛ','67356','246356824','2236742','серебренниковская 5'),

(3005,'ЗАО ЗапСибВет','4356734','425624','57245','фабричная 2'),

(3006,'ИП Андреев','84583','243527','34635767','прямая 297');

Сотрудники:

insert into dbo.worker values

(4001,'Иванов Иван иванович',1),

(4002,'петров петр петрович',2),

(4003,'поликарпов поликарп поликарпович',3),

(4004,'анатольев анатолий анатольевич',4),

(4005,'андреев андрей андреевич',4),

(4006,'константинов константин константиновч',5),

(4007,'борисов борис борисович',6),

(4008,'максимов максим максимович',7),

(4009,'васильев василий васильевич',7);

Данные о сторителной фирме:

insert into dbo.build_org values

(86579987,8546534,'Строим Нормально','2556677','марс 8');

Данные о затратах:

insert into dbo.money_lost values

(5001,101,1001,'-','50000','-','48000','-','10000','замена металлоконтрукций перекрытия крыши'),

(5002,101,1003,'10000','60000','-','32000','-','12000','отделка фассада цеха'),

(5003,103,1002,'5000','30000','-','27000','-','5000','стены и потолок'),

(5004,106,1006,'-','22000','-','20000','-','-','замена освещения'),

(5005,107,1014,'-','1000000','-','1200000','-','300000','строительство дорои с асфальтовым покрытие');

Акт Приема-сдачи:

insert into dbo.act_PS values

(6001,86579987,4003,3002,'2011.03.12','2011.04.03',5001,2003),

(6002,86579987,4003,3003,'2009.08.23','2012.01.09',5002,2010),

(6003,86579987,4004,3005,'2011.07.18','2011.08.17',5003,2009),

(6004,86579987,4006,3006,'2010.01.14','2010.06.11',5005,2011);

8. Простые запросы

--1)вывести фио сотрудников долность которых - бухгалтер

select worker_name

from dbo.worker

where job='7';

--2)вывести информацию о строительной фирме

select *

from dbo.build_org;

--3)вывести код сотрудника c фио петров петр петрович

select kod_worker

from dbo.worker

where worker_name='петров петр петрович'

--4)вывести фио сотрудника код которого - 4007

select worker_name

from dbo.worker

where kod_worker='4007'

--5)вывести объекты ОС инвентарный номер которых начинается с "8"

select *

from dbo.info_os_object

where inventory_number like '8%'

--6)вывести объекты ОС названия которых соответствуют коду "106"

select *

from dbo.info_os_object

where os_object_name='106'

--7)вывести обьекты ОС срок использования которых = "5"

select *

from dbo.info_os_object

where fact_usage= '5'

--8)вывести обьекты ОС срок использования которых > "50"

select *

from dbo.info_os_object

where fact_usage>'50'

--9) вывести обьекты ОС срок использования которых < "10"

select *

from dbo.info_os_object

where fact_usage< '10'

--10) вывести имя о заказчике которы йнаходится на улице "фабричная"

select name

from dbo.zakazchik

9. Встроеные функции

--1)вывести количество составленных актов

--2)кличество объектов ОС

--3) кличество актов составленных в 2011 году

--5)количество клиентов фирмы

select COUNT (kod_zakazchika) as [количество клиентов]

from dbo.zakazchik

10. Сложные функции (group by & having)

--1.Вывести количество составленных документов на каждую дату подписания актов.

--3.Вывести код услуги, стоимость которых больше или равно 50000.

--4.Вывести коды должностей и количество сотрудников, работающих по этой должности.

--10. Показать профессию на которой работает 1 сотрудник

FROM dbo.worker,dbo.job

where (select COUNT(dbo.job.kod_job) from dbo.job WHERE dbo.worker.job=dbo.job.kod_job)=1

GROUP BY kod_job

11. Хранимые процедуры

--1.Какую должность занимает каждый сотрудник.

go

create procedure doljnost_sotrudnika

as

select dbo.worker.worker_name, dbo.job.job_name

from dbo.worker, dbo.job

where dbo.worker.job=dbo.job.kod_job

go

exec doljnost_sotrudnika

--2. список обьектов отремонтированных в конкретную дату

go

create procedure act @r date

as

select dbo.os_object.os_object_kod, dbo.act_PS.sign_date,dbo.act_PS.finish_date, dbo.act_PS.result

from dbo.os_object,dbo.act_PS

where dbo.act_PS.result=dbo.os_object.os_object_kod and sign_date=@r

go

exec act'2011.03.12'

--3.По табельному номеру определить профессию

GO

CREATE PROCEDURE prof_vibran_sotryd12 @kod int

AS

SELECT kod_worker,worker_name,job_name

FROM dbo.worker, dbo.job

WHERE dbo.worker.job= dbo.job.kod_job and dbo.worker.kod_worker=@kod

go

EXEC prof_vibran_sotryd12 @kod=4004

--4.Возвращает должность по коду сотрудника

Go

create procedure work

AS

select dbo.worker.kod_worker,dbo.job.kod_job

from dbo.worker,dbo.job

where dbo.worker.kod_worker=dbo.job.kod_job

Go

exec work

--5.Возвращает код должности и ФИО по табельному номеру

GO

CREATE PROCEDURE koddol @TAB_N int

AS

SELECT dbo.job.kod_job,kod_worker,worker_name

FROM dbo.job,dbo.worker

WHERE dbo.worker.job=dbo.job.kod_job and dbo.worker.kod_worker=@TAB_N

go

GO

CREATE PROCEDURE n_fio @job int

AS

SELECT kod_worker,dbo.job.kod_job,worker_name

FROM dbo.worker,dbo.job

WHERE dbo.worker.job=dbo.job.kod_job and dbo.job.kod_job=@job

go

EXEC n_fio @job=4

create procedure nact

AS

select dbo.act_PS.act_number,dbo.os_object.os_object_kod

from dbo.act_PS,dbo.os_object

where dbo.act_PS.act_number=dbo.os_object.os_object_kod

Go

exec nact

--8.Показать данные о акта

GO

CREATE PROCEDURE actsigndate @actn_N int

AS

SELECT act_number,response_person,result

FROM dbo.act_PS,dbo.worker,dbo.os_object

WHERE dbo.act_PS.response_person=dbo.worker.worker_name and dbo.act_PS.result=dbo.os_object.os_object_name and dbo.act_PS.act_number=@actn_N

go

EXEC actsigndate @actn_N=6001

--9.Показать табельный номер и код должности

GO

CREATE PROCEDURE koddponworker @worker_N int

AS

SELECT kod_worker,kod_job

FROM dbo.worker,dbo.job

--10.Возвращает табельный номер и ФИО по квалификации

GO

CREATE PROCEDURE kvalif_trebyem @kval_tred varchar (50)

AS

SELECT kod_worker,worker_name

FROM dbo.worker,dbo.job

WHERE dbo.worker.job=dbo.job.kod_job and

dbo.job.job_name=@kval_tred

go

--1.При добавлении новой записи выводит сообщение:"В таблицу Должности внесена новая запись!"

for insert

as print 'В таблицу Должности внесена новая запись!'

go

go

create trigger udalenie

on dbo.job

for delete

as print 'Из таблицы Работник удалена запись!'

go

delete from dbo.job where kod_job=3

--3.При изменении таблицы выводит сообщение:"Таблица Должности изменена!"

go

create trigger izmenenie

on dbo.job

for update

as print 'Таблица Должности изменена!'

go

update dbo.job

--1.Выводит таблицу Работник и заполняет в ней столбцы Наименование должности и Наименование структурного подразделения, соответственно из таблиц Должность и Структурное подразделение.

use build2

go

create view [kod_worker_job] as

create view [zatrat] as

select kod_zatrat, os_object, work_type, adittion

from dbo.money_lost,dbo.service, dbo.os_object

Заключение

1. Петкович Д. Microsoft SQL Server 2008. Руководство для начинающих. Пер. с англ. - СПб.: БХВ - Петербург, 2009. - 752 с.: ил.

2. Дунаев В.В. Базы данных язык SQL - СПб.: БХВ Петербург, 2006. - 288с.

Размещено на Allbest.ru