ноненоеое. БД.Иэоз 66-21.Чуфаринов.Н.А.. Министерство общего и профессионального образования российской федерации

• Название: Министерство общего и профессионального образования российской федерации
• Анкор: ноненоеое
• Дата: 22.12.2022
• Размер: 4.3 Mb.
• Имя файла: БД.Иэоз 66-21.Чуфаринов.Н.А..docx
• Тип: Лабораторная работа; #858790
• страница: 6 из 9

Лабораторная работа 7

Задание:

1. 
   Создать контрольные ограничения на уровне поля, описанные выше, для вашей информационной таблицы в вашей БД;
   

create table uzb1

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like '%.%' or fio not like '%-%'),

d_rogd char(2) check (d_rogd between '01' and '31'),

m_rogd char(2) check (m_rogd between '01' and '12'),

g_rogd char(2) check (g_rogd between '00' and '99'),

pol char(1) check (pol like 'M' or pol like 'Ж'),

sem_polog_k char(1) check (sem_polog_k in ('0', '1', '2', '3')),

spec char(6) check (spec between '000000' and '190046'),

kat_obuch_k char(2) check (kat_obuch_k between '01' and '17'),

vuz_k integer check (vuz_k between 128955 and 8199999),

mp char(2) check (mp between '01' and '12'),

gp char(2) check (gp between '00' and '99'),

mo char(2) check (mo between '01' and '12'),

gok char(2) check (gok between '00' and '99'))

insert into uzb1

(nomer,fio,d_rogd ,m_rogd,g_rogd)

values

(convert(integer,1), 'Сергеев Сергей Сергеевич', '12', '03', '70')



insert into uzb1

(nomer,fio,d_rogd ,m_rogd,g_rogd)

values

(convert(integer,2), 'Анатольев Анатолий Анатольевич', '32', '03', '70')

Сообщение 547, уровень 16, состояние 0, строка 1

Конфликт инструкции INSERT с ограничением CHECK "CK__uzb1__d_rogd__0BC6C43E". Конфликт произошел в базе данных "Uzb", таблица "dbo.uzb1", column 'd_rogd'.

Выполнение данной инструкции было прервано.

2. 
   Создать следующие контрольные ограничения на уровне таблицы:
   

1. 
   значение поля «Год рождения» меньше значения поля «Год приема», которое, в свою очередь, меньше значения поля «Год окончания»;
   
2. 
   если значение поля «Код категории обучения» равно ‘07’, ‘08’ или ‘09’ (аспирант, аспирант-заочник или ординатура), то первые два символа в значении поля «Специальность» больше ‘00’; в остальных случаях (по всем другим значениям поля «Код категории обучения») первые два символа значения поля «Специальность» равны ‘00’;
   
3. 
   если значение поля «Пол» равно «Ж», то фамилия в поле «Фамилия Имя Отчество» должна заканчиваться на букву «А», иначе, если значение поля «Пол» равно «М», то фамилия в поле «Фамилия Имя Отчество» не должна заканчиваться на букву «А»;
   

create table uzb2

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like '%.%' or fio not like '%-%'),

d_rogd char(2) check (d_rogd between '01' and '31'),

m_rogd char(2) check (m_rogd between '01' and '12'),

g_rogd char(2) check (g_rogd between '00' and '99'),

pol char(1) check (pol like 'М' or pol like 'Ж'),

sem_polog_k char(1) check (sem_polog_k in ('0', '1', '2', '3')),

spec char(6) check (spec between '000000' and '190046'),

kat_obuch_k char(2) check (kat_obuch_k between '01' and '17'),

vuz_k integer check (vuz_k between 128955 and 8199999),

mp char(2) check (mp between '01' and '12'),

gp char(2) check (gp between '00' and '99'),

mo char(2) check (mo between '01' and '12'),

gok char(2) check (gok between '00' and '99'),

constraint god_const check

(

(convert (integer, gp) > convert (integer, g_rogd)) and

(convert (integer, gok) > convert (integer, gp))

),

constraint kat_obuch_const check

(

((kat_obuch_k in ('07', '08', '09')) and

(spec not like '00%'))

or

((kat_obuch_k not in ('07', '08', '09')) and

(spec like '00%'))

),

constraint pol_const check

(

((pol = 'Ж') and

(fio not like '%[б-я][ ]%[а-я][ ]%[a-я]'))

or

((pol = 'М') and

(fio like '%[б-я][ ]%[а-я][ ]%[a-я]'))

)

)

A.)

insert into uzb2 (fio, g_rogd, gp, gok) values ('Владимирский Владимир Владимирович', '70', '69', '68')

Сообщение 547, уровень 16, состояние 0, строка 1

Конфликт инструкции INSERT с ограничением CHECK "god_const". Конфликт произошел в базе данных "Uzb", таблица "dbo.uzb2".

Выполнение данной инструкции было прервано.

insert into uzb2 (fio, g_rogd, gp,gok) values ('Владимирский Владимир Владимирович', '70', '90', '95')



B.)

insert into uzb2 (fio,kat_obuch_k,spec) values ('Иванов Иван иванович', '08', '001234')

Сообщение 547, уровень 16, состояние 0, строка 1

Конфликт инструкции INSERT с ограничением CHECK "kat_obuch_const". Конфликт произошел в базе данных "Uzb", таблица "dbo.uzb2".

Выполнение данной инструкции было прервано.

insert into uzb2 (fio,kat_obuch_k,spec) values ('Иванов Иван иванович', '08', '011234')

insert into uzb2 (fio,kat_obuch_k,spec) values ('Семёнов Семён Семёнович', '01', '031234')

Сообщение 547, уровень 16, состояние 0, строка 8

Конфликт инструкции INSERT с ограничением CHECK "kat_obuch_const". Конфликт произошел в базе данных "Uzb", таблица "dbo.uzb2".

Выполнение данной инструкции было прервано.

insert into uzb2 (fio,kat_obuch_k,spec) values ('Семёнов Семён Семёнович', '01', '001234')



C.)

insert into uzb2 (fio,pol) values ('Петров Пётр Петрович', 'М')

insert into uzb2 (fio,pol) values ('Петрова Пётр Петрович', 'М')

Сообщение 547, уровень 16, состояние 0, строка 5

Конфликт инструкции INSERT с ограничением CHECK "pol_const". Конфликт произошел в базе данных "Uzb", таблица "dbo.uzb2".

insert into uzb2 (fio,pol) values ('Петрова Пётр Петрович', 'Ж')

insert into uzb2 (fio,pol) values ('Петров Пётр Петрович', 'Ж')

Сообщение 547, уровень 16, состояние 0, строка 10

Конфликт инструкции INSERT с ограничением CHECK "pol_const". Конфликт произошел в базе данных "Uzb", таблица "dbo.uzb2".

Выполнение данной инструкции было прервано.

3. 
   Создать правило для вашей информационной таблицы, описанное выше.
   

create rule rule1 as @kat_obuch in ('01', '02', '03', '04', '05', '07', '08', '10', '11', '17')

sp_bindrule 'rule1', 'uzb1.[kat_obuch_k]'

insert into uzb1 (fio,kat_obuch_k) values ('Иванов Иван Иванович’, '09')

Сообщение 513, уровень 16, состояние 0, строка 1

Вставка или обновление столбца конфликтует с правилом, наложенным предыдущей инструкцией CREATE RULE. Выполнение этой инструкции прервано. Конфликт произошел в базе данных "Uzb", таблице "dbo.uzb1", столбце "kat_obuch_k".

Выполнение данной инструкции было прервано.

insert into uzb1 (fio,kat_obuch_k) values ('Иванов Иван Иванович’, '08')

+

Лабораторная работа №8

Выдать статистику по городам и специальностям вашего государ­ства. Отчет должен начинаться заголовком и цифрой - общим количеством записей по запросу. Далее совокупность таблиц, где каждая таблица на­чинается с заголовка: ГОРОД <название> - ВСЕГО <цифра> (где <цифра> не равна нулю), а заканчивается перечнем шифров специальностей с циф­рами, не равными нулю.

create procedure zapr1 as

declare @gor1 char(20)

declare @gor2 char(20)

declare @sp char(6)

declare @it1 integer

declare @it2 integer

declare @itall integer

select count(*) as 'Общее число' from uzb

declare cur cursor for

select vuz_gorod.nazvanie_goroda, spec, count(*)

from uzb join vuz_gorod on vuz_k=vuz_gorod.cod_vuza

group by vuz_gorod.nazvanie_goroda, spec

order by vuz_gorod.nazvanie_goroda

select @it2=0

select @itall=0

open cur

fetch cur into @gor1,@sp,@it1

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении первого FETCH'

close cur

return

end

if (@@fetch_status=-1)

begin

print 'Данные неверны'

close cur

return

end

select @gor2=@gor1

print @sp+'-'+str(@it1)

select @it2=@it2+@it1

select @itall=@itall+1

while (@@fetch_status=0)

begin

fetch cur into @gor1, @sp, @it1

if (@gor1!=@gor2)

begin

print 'город '+@gor2+'-'+ str (@it2)

print '----------------------------------'

select @gor2=@gor1

select @it2=0

end

if (@@fetch_status=-1) break

print @sp + ' - ' +str(@it1)

select @it2=@it2+@it1

select @itall=@itall+l

end

close cur

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении FETCH'

return

end

print 'город ' + @gor1 + ' - ' +str(@it2)

print '----------------------------------'

print 'всего по запросу-'+str(@itall)

deallocate cur

return



Задание 2

Выдать статистику по городам и учебным заведениям вашего госу­дарства. Отчет должен начинаться соответствующим заголовком. Далее должна следовать совокупность таблиц, каждая из которых включает пе­речень учебных заведений одного города с цифрами, не равными нулю (последняя строка таблицы представляет собой: ГОРОД <название> - ВСЕГО <цифра>, где <цифра> ^= 0). Заканчивается отчет цифрой - общим коли­чеством записей по запросу.

create procedure zapr2 as

declare @gor1 char(20)

declare @gor2 char(20)

declare @v char(25)

declare @it1 integer

declare @it2 integer

declare @itall integer

select count(*) as 'Общее число' from uzb

declare cur cursor for

select vuz_gorod.nazvanie_goroda, vuz_gorod.nazvanie_vuza, count(*)

from uzb join vuz_gorod on vuz_k=vuz_gorod.cod_vuza

group by vuz_gorod.nazvanie_goroda,vuz_gorod.nazvanie_vuza

order by vuz_gorod.nazvanie_goroda

select @it2=0

select @itall=0

open cur

fetch cur into @gor1,@v,@it1

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении первого FETCH'

close cur

return

end

if (@@fetch_status=-1)

begin

print 'Данные неверны'

close cur

return

end

select @gor2=@gor1

print @v+'-'+str(@it1)

select @it2=@it2+@it1

select @itall=@itall+@it1

while (@@fetch_status=0)

begin

fetch cur into @gor1, @v, @it1

if (@gor1!=@gor2)

begin

print 'город '+@gor2+'-'+ str (@it2)

print '----------------------------------'

select @gor2=@gor1

select @it2=0

end

if (@@fetch_status=-1) break

print @v + ' - ' +str(@it1)

select @it2=@it2+@it1

select @itall=@itall+@it1

end

close cur

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении FETCH'

return

end

print 'город ' + @gor1 + ' - ' +str(@it2)

print '----------------------------------'

print 'всего по запросу-'+str(@itall)

deallocate cur

return



Задание 3

Выдать статистику по городам и категориям обучения вашего го­сударства. Отчет должен начинаться с заголовка и цифры - общего коли­чества записей по запросу. Далее совокупность таблиц, где каждая таб­лица начинается с заголовка: ГОРОД <название> - ВСЕГО <цифра>, где <цифра> ^= 0, а заканчивается перечнем категорий обучения с цифрами, не равными нулю.

create procedure zapr3 as

declare @gor1 char(20)

declare @gor2 char(20)

declare @ko char(16)

declare @it1 integer

declare @it2 integer

declare @itall integer

select count(*) as 'Общее число' from uzb

declare cur cursor for

select vuz_gorod.nazvanie_goroda,kat_obucheniya, count(*)

from uzb join vuz_gorod on vuz_k=vuz_gorod.cod_vuza

join kat_obuch on kat_obuch_k=kat_obuch.cod

group by vuz_gorod.nazvanie_goroda,kat_obuch.kat_obucheniya

order by vuz_gorod.nazvanie_goroda

select @it2=0

select @itall=0

open cur

fetch cur into @gor1,@ko,@it1

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении первого FETCH'

close cur

return

end

if (@@fetch_status=-1)

begin

print 'Данные не найдены'

close cur

return

end

select @gor2=@gor1

print @ko+'-'+str(@it1)

select @it2=@it2+@it1

select @itall=@itall+@it1

while (@@fetch_status=0)

begin

fetch cur into @gor1, @ko, @it1

if (@gor1!=@gor2)

begin

print 'город '+@gor2+'-'+ str (@it2)

print '----------------------------------'

select @gor2=@gor1

select @it2=0

end

if (@@fetch_status=-1) break

print @ko + ' - ' +str(@it1)

select @it2=@it2+@it1

select @itall=@itall+@it1

end

close cur

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении FETCH'

return

end

print 'город ' + @gor1 + ' - ' +str(@it2)

print '----------------------------------'

print 'всего по запросу-'+str(@itall)

deallocate cur

return



Задание 4

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

create procedure zapr4 as

declare @v1 char(20)

declare @v2 char(20)

declare @ko char(25)

declare @it1 integer

declare @it2 integer

declare @itall integer

select count(*) as 'Общее число' from uzb

declare cur cursor for

select vuz_gorod.nazvanie_vuza, kat_obuch.kat_obucheniya, count(*)

from uzb join vuz_gorod on vuz_k=vuz_gorod.cod_vuza

join kat_obuch on kat_obuch_k=kat_obuch.cod

group by vuz_gorod.nazvanie_vuza,kat_obuch.kat_obucheniya

order by vuz_gorod.nazvanie_vuza

select @it2=0

select @itall=0

open cur

fetch cur into @v1,@ko,@it1

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении первого FETCH'

close cur

return

end

if (@@fetch_status=-1)

begin

print 'Данные не найдены'

close cur

return

end

select @v2=@v1

print @ko+'-'+str(@it1)

select @it2=@it2+@it1

select @itall=@itall+@it1

while (@@fetch_status=0)

begin

fetch cur into @v1, @ko, @it1

if (@v1!=@v2)

begin

print 'учебное заведение '+@v2+'-'+ str (@it2)

print '----------------------------------'

select @v2=@v1

select @it2=0

end

if (@@fetch_status=-1) break

print @ko + ' - ' +str(@it1)

select @it2=@it2+@it1

select @itall=@itall+@it1

end

close cur

if (@@fetch_status=-2)

begin

print 'Ошибка при выполнении FETCH'

return

end

print 'учебное заведение ' + @v1 + ' - ' +str(@it2)

print '----------------------------------'

print 'всего по запросу-'+str(@itall)

deallocate cur

return