3682SAS / лаб4

CREATE TABLE STUDENT(
nom_za4etki number (10) constraint PK_Stud primary key
,nom_pasporta number (10)
,FIO_studenta nvarchar2 (15) NOT NULL
,nom_group number (5) NOT NULL
,nom_speciality number (6) constraint nomer_spec check(nom_speciality between 10000 and 999999)
,CONSTRAINT UNIQ_FIO_stud UNIQUE(nom_za4etki,FIO_studenta)
)

CREATE TABLE DISCIP (
nazvanie nvarchar2 (20)constraint PK_discip primary key
,kolvo_4asov number (3) constraint kol_4as check (kolvo_4asov >=0)
,FIO_prepoda nvarchar2 (15) NOT NULL
,nom_za4etki number (10) NOT NULL
,napravlenie nvarchar2 (20) constraint napravl check (napravlenie in ('гуманитарное', 'техническое', 'естественное'))
,step_discip number (2) constraint step_disc check (step_discip between 1 and 10)
,constraint FK_Stud foreign key (nom_za4etki)
REFERENCES STUDENT
)

select * from STUDENT

insert into STUDENT values ('150036', '3821425596', 'Петров И.А.', '56474', '210105');
insert into STUDENT values ('150037', '4646245724', 'Сидоров В.А.', '56435', '210109');
insert into STUDENT values ('150038', '8564527436', 'Кузнецов А.С.', '56454', '210108');
insert into STUDENT values ('150039', '8716571565', 'Андреев С.В.', '56854', '210116');

select * from DISCIP

insert into DISCIP values ('ТАУ','158', 'Лебедев Ю.М.', '150036', 'техническое', '7');
insert into DISCIP values ('Социология', '70', 'Лопоухов М.С.', '150037', 'гуманитарное', '3');
insert into DISCIP values ('Химия', '56', 'Мониторва А.И.', '150038', 'естественное', '2');
insert into DISCIP values ('АС', '180', 'Саюн В.М.', '150039', 'техническое', '10');

select * from STUDENT

select * from DISCIP

drop table STUDENT

drop table DISCIP