--Запросы

--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

where adres like 'фабричная%'

--ВСТРОЕНЫЕ ФУНКЦИИ

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

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

from dbo.act_ps

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

select COUNT (info_kod) as [количество объектов]

from dbo.info_os_object

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

select COUNT (act_number) as [акты за 2011]

from dbo.act_PS

where sign_date like '2011%'

--4)количество работ стоимостью 50000

select COUNT (service_price) as [услуги 50000]

from dbo.service

where service_price='50000'

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

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

from dbo.zakazchik

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

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

select sign_date, COUNT(act_number)as[Кол-во документов]

from act_PS

group by sign_date

--2.Вывести код должности, в которых более 1 сотрудников.

select job, COUNT(kod_worker)as[Кол-во сотрудников]

from dbo.worker

group by job

having COUNT(job)>1

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

select service_price, COUNT(servce_name)as[Кол-во услуг]

from dbo.service

group by service_price

having COUNT(service_price)>=50000

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

select job, COUNT(job)as[Кол-во сотрудников]

from dbo.worker

group by job

--5.Сколько раз каждый из сотрудников подписывал акт.

select response_person, COUNT(act_number)as[Кол-во актов]

from dbo.act_PS

group by response_person

--6. Сколько ремонтов осуществлено по каждому обьекту ОС.

select result, COUNT(act_number)as[Кол-во ремонтов]

from dbo.act_PS

group by result

--8. Сколько раз подписывал акт сотрудник с табельным номером 4003.

select response_person, COUNT(act_number)as[Кол-во актов]

from dbo.act_PS

group by response_person

having response_person='4003'

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

SELECT kod_job AS код_должности

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

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

--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

EXEC koddol @TAB_N=4006

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

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

--7. Возвращает номер акта по коду всех отремонтированных обьектов

Go

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

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

EXEC koddponworker @worker_N=4002

--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

EXEC kvalif_trebyem @kval_tred='прораб'

--Триггеры

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

use build2

go

create trigger dobavlenie

on dbo.job

for insert

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

go

insert into dbo.job values(9,'уборщик')

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

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

set job_name=('сантехник') where kod_job=9

--Представления

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

use build2

go

create view [kod_worker_job] as

select kod_worker as "Код сотрудника", worker_name as "ФИО", kod_job as "Должность"

from dbo.worker,dbo.job

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

--2. Выводит таблицу затраты и заполняет в ней столбцы тип работ и Обьект ОС.

go

create view [zatrat] as

select kod_zatrat, os_object, work_type, adittion

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

where dbo.money_lost.work_type=dbo.service.service_kod and dbo.money_lost.os_object=dbo.os_object.os_object_kod

Заключение

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

Список используемой литературы

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

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

14