/*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 nVARCHAR(255) 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 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е задание:*/