create table.sql

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

union select  11 , 12 , 5  
union select  11 , 13 , 3  
union select  11 , 14 , 2  
union select  11 , 15 , 4  
union select  11 , 16 , 5  
union select  11 , 17 , 5  
union select  11 , 18 , 5  
union select  11 , 19 , 5  
union select  11 , 20 , 5  

union select  12 , 11 , 4  
union select  12 , 12 , 5  
union select  12 , 13 , 3  
union select  13 , 14 , 3  
union select  13 , 15 , 4  
union select  13 , 16 , 5  
union select  13 , 17 , 5  
union select  13 , 18 , 2  
union select  13 , 19 , 5  
union select  13 , 20 , 5  

union select  14 , 1 , 4  
union select  14 , 2 , 5  
union select  14 , 3 , 3  
union select  14 , 4 , 2  
union select  14 , 5 , 4  
union select  14 , 6 , 5  
union select  14 , 7 , 3  
union select  14 , 8 , 2  
union select  14 , 9 , 5  
union select  14 , 10 , 5  

union select  15 , 11 , 4  
union select  15 , 12 , 5  
union select  15 , 13 , 3  
union select  15 , 14 , 2  
union select  15 , 15 , 4  
union select  15 , 16 , 5  
union select  15 , 17 , 4  
union select  15 , 18 , 3  
union select  15 , 19 , 5  
union select  15 , 20 , 5  

union select  16 , 11 , 4  
union select  16 , 12 , 5  
union select  16 , 13 , 2  
union select  16 , 14 , 1  
union select  16 , 15 , 4  
union select  16 , 16 , 5  
union select  16 , 17 , 3  
union select  16 , 18 , 3  
union select  16 , 19 , 5  
union select  16 , 20 , 5  

union select  17 , 1 , 4  
union select  17 , 2 , 5  
union select  17 , 3 , 4  
union select  17 , 4 , 4  
union select  17 , 5 , 4  
union select  17 , 6 , 5  
union select  17 , 7 , 3  
union select  17 , 8 , 2  
union select  17 , 9 , 5  
union select  17 , 10 , 5  

union select  18 , 11 , 4  
union select  18 , 12 , 5  
union select  18 , 13 , 3  
union select  18 , 14 , 3  
union select  18 , 15 , 4  
union select  18 , 16 , 5  
union select  18 , 17 , 4  
union select  18 , 18 , 4  
union select  18 , 19 , 5  
union select  18 , 20 , 5  

union select  19 , 1 , 4  
union select  19 , 2 , 5  
union select  19 , 3 , 5  
union select  19 , 4 , 5  
union select  19 , 5 , 4  
union select  19 , 6 , 5  
union select  19 , 7 , 5  
union select  19 , 8 , 5  
union select  19 , 9 , 5  
union select  19 , 10 , 5  

union select  20 , 11 , 4  
union select  20 , 12 , 5  
union select  20 , 13 , 2  
union select  20 , 14 , 3  
union select  20 , 15 , 4  
union select  20 , 16 , 5  
union select  20 , 17 , 2  
union select  20 , 18 , 2  
union select  20 , 19 , 5  
union select  20 , 20 , 5  

union select  21 , 1 , 4  
union select  21 , 2 , 5  
union select  21 , 3 , 5  
union select  21 , 4 , 1  
union select  21 , 5 , 4  
union select  21 , 6 , 5  
union select  21 , 7 , 4  
union select  21 , 8 , 4  
union select  21 , 9 , 5  
union select  21 , 10 , 5  

union select  22 , 11 , 4  
union select  22 , 12 , 5  
union select  22 , 13 , 4  
union select  22 , 14 , 4  
union select  22 , 15 , 4  
union select  22 , 16 , 5  
union select  22 , 17 , 4  
union select  22 , 18 , 4  
union select  22 , 19 , 5  
union select  22 , 20 , 5  

union select  23 , 1 , 4  
union select  23 , 2 , 5  
union select  23 , 3 , 4  
union select  23 , 4 , 4  
union select  23 , 5 , 4  
union select  23 , 6 , 5  
union select  23 , 7 , 3  
union select  23 , 8 , 3  
union select  23 , 9 , 5  
union select  23 , 10 , 5  

union select  24 , 11 , 4  
union select  24 , 12 , 5  
union select  24 , 13 , 3  
union select  24 , 14 , 4  
union select  24 , 15 , 4  
union select  24 , 16 , 5  
union select  24 , 17 , 3  
union select  24 , 18 , 4  
union select  24 , 19 , 5  
union select  24 , 20 , 5  

union select  25 , 1 , 4  
union select  25 , 2 , 5  
union select  25 , 3 , 3  
union select  25 , 4 , 4  
union select  25 , 5 , 2  
union select  25 , 6 , 5  
union select  25 , 7 , 4  
union select  25 , 8 , 3  
union select  25 , 9 , 5  
union select  25 , 10 , 5

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,1, NULL
union select 'Vovk','Segrey','Mihajlovich', 1, 1, 4, 1
union select 'Dolgov','Valeriy','Mihajlovich',2,NULL,5, 2
union select 'Volkovskij','Oleg','Stepanovich',NULL,1,1, NULL
union select 'Sokolova','Natalja','Olegovna',NULL,NULL,1, 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