creat_table_new.sql

to study the example of creation of tables, formative DB, and filling with their information. Using

					id_mark tinyint REFERENCES Mark_names)

insert into Mark 
select 1, (select id_subject from Subject_names where subject_name= 'SQL'), (select id_mark from Mark_names where ball_name=5)
union select  1 ,(select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=4) 
union select  1 , (select id_subject from Subject_names where subject_name='geometrija'), (select id_mark from Mark_names where ball_name=5)
union select  17 , (select id_subject from Subject_names where subject_name='UML'), (select id_mark from Mark_names where ball_name=3) 
union select  1 ,(select id_subject from Subject_names where subject_name='algebra'), (select id_mark from Mark_names where ball_name=4) 
union select  3 , (select id_subject from Subject_names where subject_name='geometrija'), (select id_mark from Mark_names where ball_name=5) 
union select  4 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5) 
union select  8 , (select id_subject from Subject_names where subject_name= 'TAU'), (select id_mark from Mark_names where ball_name=4)  
union select  13 , (select id_subject from Subject_names where subject_name='C++'), (select id_mark from Mark_names where ball_name=5) 
union select  6 , (select id_subject from Subject_names where subject_name='C++'), (select id_mark from Mark_names where ball_name=4)  

union select  2 , (select id_subject from Subject_names where subject_name='algebra'), (select id_mark from Mark_names where ball_name=3) 
union select  2 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=3)  
union select  7 , (select id_subject from Subject_names where subject_name='algebra'), (select id_mark from Mark_names where ball_name=4) 
union select  2 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5) 
union select  3 , (select id_subject from Subject_names where subject_name='algebra'), (select id_mark from Mark_names where ball_name=3) 
union select  2 , (select id_subject from Subject_names where subject_name= 'SQL'), (select id_mark from Mark_names where ball_name=3) 
union select 12 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5) 
union select  2 , (select id_subject from Subject_names where subject_name= 'TAU'), (select id_mark from Mark_names where ball_name=5) 
union select  20 , (select id_subject from Subject_names where subject_name= 'SQL'), (select id_mark from Mark_names where ball_name=3)  
union select  2 , (select id_subject from Subject_names where subject_name='UML'), (select id_mark from Mark_names where ball_name=5)  

union select  3 , (select id_subject from Subject_names where subject_name= 'SQL'), (select id_mark from Mark_names where ball_name=5)
union select  3 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5)
union select  7 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5) 
union select  15 , (select id_subject from Subject_names where subject_name= 'TAU'), (select id_mark from Mark_names where ball_name=2) 
union select  3 , (select id_subject from Subject_names where subject_name= 'Assembler'), (select id_mark from Mark_names where ball_name=3)
union select  15 , (select id_subject from Subject_names where subject_name='C++'), (select id_mark from Mark_names where ball_name=5) 
union select  3 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5)  
union select  22 , (select id_subject from Subject_names where subject_name='fizika'), (select id_mark from Mark_names where ball_name=5) 
union select  4 , (select id_subject from Subject_names where subject_name='C++'), (select id_mark from Mark_names where ball_name=4) 
union select  3 , (select id_subject from Subject_names where subject_name= 'Assembler'), (select id_mark from Mark_names where ball_name=4) 



END
--ELSE
--DROP TABLE Mark


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Stepeni')
BEGIN
create table Stepeni (id_step tinyint identity PRIMARY KEY, 
					  step_name varchar(20) default NULL)
insert into Stepeni 
select 'kandidat nauk'
union select 'doctor nauk'
--union select NULL
END
--ELSE
--DROP TABLE Stepeni

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Zvanija')
BEGIN
create table Zvanija (id_zvan tinyint identity PRIMARY KEY, 
					  zvan_name varchar(30) default NULL)
insert into Zvanija 
select 'docent po kafedre'
union select 'professor po kafedre'
union select 'starshii nauchnii sotrudnik'
union select 'professor po spezialnosti'
--union select NULL

END
--ELSE
--DROP TABLE Zvanija



if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DoljnostiPrep')
BEGIN
create table DoljnostiPrep (id_doljnPrep tinyint identity PRIMARY KEY, 
						doljnPrep_name varchar(20))
insert into DoljnostiPrep
select 'prepodavatel'
union select 'st.prepodavatel'
union select 'asistent'
union select 'docent'
union select 'professor'
END
--ELSE
--DROP TABLE DoljnostiPrep




if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DoljnostiAdmin')
BEGIN
create table DoljnostiAdmin (id_doljnAdmin tinyint identity PRIMARY KEY, 
						doljnAdmin_name varchar(20) default NULL)
insert into DoljnostiAdmin
select 'zav. kafedri'
union select 'decan'
union select 'zam. decan'
union select 'rector'
union select 'prorector'
--union select NULL
END
--ELSE
--DROP TABLE DoljnostiAdmin




if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Teachers')
BEGIN
create table Teachers (id_teach int identity PRIMARY KEY , 
					   t_surname varchar(50), 
					   t_name varchar(50), 
					   t_pname varchar(50), 
					   id_step tinyint REFERENCES Stepeni, 
					   id_zvan tinyint REFERENCES Zvanija, 
					   id_doljnPrep tinyint REFERENCES DoljnostiPrep,
					   id_doljnAdmin tinyint REFERENCES DoljnostiAdmin)
insert into Teachers 
select 'Egorov','Artem','Aleksandrovich',NULL,NULL,(select id_doljnPrep from DoljnostiPrep where doljnPrep_name='st.prepodavatel'), NULL
union select 'Vovk','Segrey','Mihajlovich', (select id_step from Stepeni where step_name='kandidat nauk'), (select id_zvan from Zvanija where zvan_name='professor po kafedre'), (select id_doljnPrep from DoljnostiPrep where doljnPrep_name='docent'), (select id_doljnAdmin from DoljnostiAdmin where doljnAdmin_name = 'zav. kafedri')
union select 'Dolgov','Valeriy','Mihajlovich',(select id_step from Stepeni where step_name='kandidat nauk'),NULL,(select id_doljnPrep from DoljnostiPrep where doljnPrep_name='professor'), (select id_doljnAdmin from DoljnostiAdmin where doljnAdmin_name = 'decan')
union select 'Volkovskij','Oleg','Stepanovich',NULL,1,(select id_doljnPrep from DoljnostiPrep where doljnPrep_name='docent'), NULL
union select 'Sokolova','Natalja','Olegovna',NULL,NULL,(select id_doljnPrep from DoljnostiPrep where doljnPrep_name='st.prepodavatel'), NULL
END
--ELSE
--DROP TABLE Teachers



if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Articles')
BEGIN
create table Articles (id_article int identity PRIMARY KEY, 
						article_name varchar(50))
insert into Articles 
select 'Nonquasratc Regularization'
union select 'Image filtration'
union select 'System modeling'
union select 'Programming methods'
union select 'Neural Networks'
union select 'UML and Buisness Modeling'
union select 'C++ and Program Interfaces'
union select 'Image addaptation'
union select 'Support Vector Machines for image clusterization'
union select 'Sound Restoration'
END
--ELSE
--DROP TABLE Articles



if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tethis')
BEGIN
create table Tethis (id_tethis int identity PRIMARY KEY, 
					tethis_name varchar(50)
					)
insert into Tethis 
select 'Sound Restoration T'
union select 'Nonquasratc Regularization T'
union select 'Image filtration T'
union select 'System modeling T'
union select 'Programming methods T'
union select 'Neural Networks T'
union select 'UML and Buisness Modeling'
union select 'C++ and Program Interfaces T'
union select 'Image addaptation T'
union select 'Support Vector Machines for image clusterization T'
END
--ELSE
--DROP TABLE Tethis
 

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Article_author')
BEGIN
create table Article_author (id_article int REFERENCES Articles , 
							 id_article_author int, 
							 article_author_type bit)

insert into Article_author 
 select 1,1,1
 union select 1,1,0
 union select 1,2,0
 union select 2,2,1
 union select 2,5,0
 union select 3,6,0
 union select 3,2,1
 union select 4,2,1
 union select 4,4,1
 union select 4,8,0
 union select 5,3,1
 union select 5,17,0
 union select 6,21,0
 union select 7,25,0
 union select 8,18,0
 union select 9,9,0
 union select 10,14,0  

END
--ELSE
--DROP TABLE Article_author


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tethis_author')
BEGIN
create table Tethis_author (id_tethis int REFERENCES Tethis, 
							id_tethis_author int, 
							tethis_author_type bit)
insert into Tethis_author 
 select 1 , 1 , 1 
union select 1 , 11 , 0 
union select 1 , 15 , 0 
union select 2 , 5 , 1 
union select 2 , 18 , 0 
union select 2 , 21 , 0 
union select 3 , 25 , 0 
union select 3 , 20 , 0 
union select 4 , 5 , 1 
union select 4 , 6 , 0 
union select 4 , 13 , 0 
union select 5 , 17 , 0 
union select 5 , 12 , 0 
union select 6 , 2 , 0 
union select 7 , 4 , 0 
union select 8 , 15 , 0 
union select 9 , 9 , 0 
union select 10 , 3 , 0 

END
--ELSE
--DROP TABLE Tethis_author