/*CREATE TABLE Doctor(
id BIGINT IDENTITY(1,1) NOT NULL,
fio nVARCHAR(255) NOT NULL,
department nVARCHAR(255),
constraint pk_doctor PRIMARY KEY(id));

CREATE TABLE Patient(
id BIGINT IDENTITY(1,1) NOT NULL,
fio CHAR(30) NOT NULL,
sex CHAR(7) null,
birth_dt DATE NULL,
constraint pk_patient PRIMARY KEY(id),
check (sex='мужской' or sex='женский'));

create table Diagnosis(
id bigint identity(1,1) not null,
name nvarchar(255) not null,
code char(7),
constraint code_length check (len(code) between 4 and 7),
constraint pk_diagnosis primary key(id)
);

create table Case1(
patient_id bigint not null,
main_diag_id bigint not null,
start_dt datetime null,
end_dt datetime default getDate(),
class_val int,
constraint start_dt_check check(start_dt<=getDate()),
constraint class_val_check check(class_val between 1 and 3),
constraint patient_diag_pk primary key(patient_id,main_diag_id),
constraint fk_patient foreign key(patient_id)
references Patient(id),
constraint fk_diag foreign key(main_diag_id)
references Diagnosis(id));

create table Step(
patient_id bigint not null,
main_diag_id bigint not null,
diagnosis_id bigint not null,
doctor_id bigint not null,
days_off int,
constraint days_check check(days_off>0),
constraint doctor_id_fk foreign key(doctor_id) references Doctor(id),
constraint patient_diag_doctor_pk primary key(patient_id,main_diag_id,doctor_id),
constraint patient_fk foreign key (patient_id)
references Patient(id),
constraint main_diag_fk foreign key (main_diag_id)
references Diagnosis(id),
constraint diagnosis_fk foreign key(diagnosis_id)
references Diagnosis(id));

1е задание:
alter table Doctor add nauchnaya_stepen varchar(30),
constraint stepen_check check (nauchnaya_stepen='специалист 1й степени' or nauchnaya_stepen='кандидат наук' or nauchnaya_stepen='специалист 2й степени');

2е задание:
alter table Case1 drop constraint start_dt_check;
alter table Case1 add constraint start_dt_check2 check(start_dt between '01.01.2014' and '01.12.2014' and end_dt<'31.12.2014');

3е задание:
alter table Doctor drop constraint doctor_id_fk;
alter table Doctor drop constraint pk_doctor;
alter table Doctor drop column id;
alter table Step add doctor_number bigint identity(1,1) not null;
alter table Step add constraint number_doctor_fk foreign key(doctor_number) references Doctor(fio);

4е задание:

insert into Patient (fio,sex,birth_dt) values
('Иванов Иван Иванович','мужской','20.10.1990'),
('Иванова Таня','женский','20.12.1980'),
('Табуреткин Александр Иванович','мужской','10.10.1990');

5е задание:
delete from Case1 where start_dt>'31.04.2014 0:00:00' and start_dt<'01.07.2014 0:00:00';
insert into Diagnosis ([name], code) values
('name1', 'ktyu'),
('name2','kenr'),
('name3', 'gion');
insert into Case1 (patient_id,main_diag_id,start_dt) values
(1,1, '12.04.2014'),
(2,2, '12.05.2014'),
(3,3, '01.05.2014');*/