create table student (
zach_num number,
sr_bal number (3,2),
name char (20),
surname (20),
faculty char (50),
birthday  date,
budzet bool
);

alter table student (
modify name char (30)
modify budzet char (3),
add hostel char (3),
add kourse integer,
add group_number number
);

drop table student;

--------------------------- Создание------------------------
create table FACULTY (
FacNo integer (5),
Name char (20),
Building char (5)
);

create table DEPARTMENT (
DepNo integer,
FacNo integer (50),
Name varchar2 (50),
Building number (2),
Fund number (7,2),
);

create table TEACHER (
TchNo integer
DepNo integer
Name varchar2 (50)
Post varchar2 (20)
Tel char (7)
Hiredate date
);

create table SGROUP (
GrpNo integer,
DepNo integer,
Course char (1),
Num char (3),
Quantity number (2)
);

create table SUBJECT (
SbjNo integer,
Building char (5)
);

);

create table ROOM (
RomNo integer,
Num number (4),
Seats number (3)
);

create table LECTURE
TchNo integer,
GrpNo integer,
SbjNo integer,
RomNo integer,
Type varchar2 (25),
Day char (10),
week number (1)
);

------------------------------- Редактирование-------------------------------

alter table FACULTY (
modify FacNo varchar2 (50),
add Dean varchar2 (50),
add Fund number (7,2)
);

alter table DEPARTMENT (
modify Building char (5)
);

alter table TEACHER (
add Salary number (6,2),
add Commission number (6,2)
);

alter table SGROUP (
modify Course number (1),
modify Num number (3),
add Rating number (3)
);

alter table ROOM (
add Floor number (2),
add Rating char (3)
);

alter table LECTURE (
add Lesson number (1),
modify Day char (3),
);

drop table FACULTY;
drop table DEPARTMENT;
drop table TEACHER;
drop table SGROUP;
drop table ROOM;
drop table LECTURE;

create table FACULTY (
facno integer CONSTRAINT facno_in PRIMARY KEY,
Name varchar2 (50) CONSTRAINT names_var UNIQUE NOT NULL,
DEAN varchar2 (50) CONSTRAINT dean_var UNIQUE,
building char (5)
fund number (7) CONSTRAINT CHECK  (fund > 0),
);

create table DEPARTMENT (
depno integer CONSTRAINT depni_int PRIMARY KEY,
facno integer CONSTRAINT facno_int REFERENCES FACULTY(FacNo),
name varchar2 (50) CONSTRAINT name_var NOT NULL,
head varchar2 (50),
building char (5),
fund number (7,2) CONSTRAINT fund_num CHECK  (fund > 0),
CONSTRAINT     tch_chk_sal_ink2 name,head UNIQUE
);

create table TEACHER (
tchno integer CONSTRAINT tchno_int PRIMARY KEY,
depno integer CONSTRAINT depno_int REFERENCES DEPARTAMENT(DepNo),
name varchar2 (50) CONSTRAINT name_var NOT NULL,
post varchar2 (20) CONSTRAINT CHECK (post=assistant OR post=counselor OR post=assistant OR post=professor),
tel char (7),
hiredate date CONSTRAINT thrhire_dat CHECK (hiredate >= TO_DATE('01.01.1950', 'DD.MM.YYYY')NOT NULL,
salary number (6,2) CONSTRAINT salary_num CHECK (salary>0),
commission number (6,2) CONSTRAINT commission_num CHECK(commission>0),
CONSTRAINT TEBALETEACH CHECK(((commission+commission)<salary)AND ((commission+salary)<2000)
);

create table SGROUP (
grpno integer CONSTRAINT grpno_int PRIMARY KEY,
depno integer CONSTRAINT depno_int REFERENCES DEPARTAMENT(DepNo),
course number (1) CONSTRAINT course_num CHECK (course=1 OR course=4 OR course=2 OR course=3 OR course=5),
num number (3),
quantity table number (2) CONSTRAINT quantity_num CHECK (quantity BETWEEN 0 AND 50),
curator integer CONSTRAINT curator_int REFERENCES TEACHER(TchNo),
Reating number (3) CONSTRAINT Reating_int  (Rating BETWEEN 0 AND 100),
CONSTRAINT SGR DepNo,NuM UNIQUE
);

create table SUBJECT (
subject integer CONSTRAINT SUB PRIMARY KEY,
name varchar2 (50) CONSTRAINT name_var NOT NULL
);

create table ROOM (
romno integer CONSTRAINT romno_int PRIMARY KEY,
num number (4) CONSTRAINT num_numb NOT NULL,
seats number (3) CONSTRAINT seats_num  (seats BETWEEN 0 AND 300),
floor number (2) CONSTRAINT floor_num (floor BETWEEN 0 AND 16),
building char (5) CONSTRAINT building_char NOT NULL,
CONSTRAINT ROOOM  num, building  UNIQUE
);

create table LECTURE(
tchno integer CONSTRAINT LECTEACH TEACHER(TchNo)ON DELETE  SET NULL,
grpno integer CONSTRAINT LECGR SGROUP(GrpNo)ON DELETE CASCADE,
sbjno integer CONSTRAINT LECSBJ SUBJECT(SbjNo)ON DELETE CASCADE,
romno integer CONSTRAINT LECROM ROOM(RomNo)ON DELETE SET NULL ,
type varchar2 (25) CONSTRAINT LECTYPE CHECK (type=lecture OR type=lab OR type=seminar OR type=practise )NOT NULL,
day char (3) CONSTRAINT LECDAY CHECK (day=mon OR day=tue OR day=wed OR day=whu OR day=fri)NOT NULL,
week number (1) CONSTRAINT LECWEEK CHECK (week=1 OR week=2)NOT NULL,
lesson number (1) CONSTRAINT LECLES (lesson BETWEEN 1 AND 8)NOT NULL,
CONSTRAINT LECT GrpNo, Day, Week, Lesson UNIQUE
);