sqlquery1.sql

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

insert into Mark (id_stud,id_subject,id_mark) values ('24','11','4')
insert into Mark (id_stud,id_subject,id_mark) values ('24','12','5')
insert into Mark (id_stud,id_subject,id_mark) values ('24','13','6')
insert into Mark (id_stud,id_subject,id_mark) values ('24','14','7')
insert into Mark (id_stud,id_subject,id_mark) values ('24','15','4')
insert into Mark (id_stud,id_subject,id_mark) values ('24','16','5')
insert into Mark (id_stud,id_subject,id_mark) values ('24','17','6')
insert into Mark (id_stud,id_subject,id_mark) values ('24','18','7')
insert into Mark (id_stud,id_subject,id_mark) values ('24','19','5')
insert into Mark (id_stud,id_subject,id_mark) values ('24','20','5')

insert into Mark (id_stud,id_subject,id_mark) values ('25','1','4')
insert into Mark (id_stud,id_subject,id_mark) values ('25','2','5')
insert into Mark (id_stud,id_subject,id_mark) values ('25','3','6')
insert into Mark (id_stud,id_subject,id_mark) values ('25','4','7')
insert into Mark (id_stud,id_subject,id_mark) values ('25','5','4')
insert into Mark (id_stud,id_subject,id_mark) values ('25','6','5')
insert into Mark (id_stud,id_subject,id_mark) values ('25','7','6')
insert into Mark (id_stud,id_subject,id_mark) values ('25','8','7')
insert into Mark (id_stud,id_subject,id_mark) values ('25','9','5')
insert into Mark (id_stud,id_subject,id_mark) values ('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 PRIMARY KEY, 
					  step_name varchar(15))
insert into Stepeni (id_step,step_name) values ('1','magistr')
insert into Stepeni (id_step,step_name) values ('2','kandidat')
insert into Stepeni (id_step,step_name) values ('3','doctor')
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 PRIMARY KEY, 
					  zvan_name varchar(15))
insert into Zvanija (id_zvan,zvan_name) values ('1','docent')
insert into Zvanija (id_zvan,zvan_name) values ('2','professor')
insert into Zvanija (id_zvan,zvan_name) values ('3','akademik')
insert into Zvanija (id_zvan,zvan_name) values ('4','akademik')
END
--ELSE
--DROP TABLE Zvanija

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Doljnosti')
BEGIN
create table Doljnosti (id_doljn tinyint PRIMARY KEY, 
						doljn_name varchar(20))
insert into Doljnosti (id_doljn,doljn_name) values ('1','prepodavatel')
insert into Doljnosti (id_doljn,doljn_name) values ('2','st.prepodavatel')
insert into Doljnosti (id_doljn,doljn_name) values ('3','asistent')
insert into Doljnosti (id_doljn,doljn_name) values ('4','docent')
insert into Doljnosti (id_doljn,doljn_name) values ('5','zav.kafedri')
END
--ELSE
--DROP TABLE Doljnosti

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Teachers')
BEGIN
create table Teachers (id_teach int 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_doljn tinyint REFERENCES Doljnosti)
insert into Teachers (id_teach,t_surname,t_name,t_pname,id_step,id_zvan,id_doljn) values ('1','Egorov','Artem','Aleksandrovich','1','3','1')
insert into Teachers (id_teach,t_surname,t_name,t_pname,id_step,id_zvan,id_doljn) values ('2','Vovk','Segrey','Mihajlovich','2','1','2')
insert into Teachers (id_teach,t_surname,t_name,t_pname,id_step,id_zvan,id_doljn) values ('3','Dolgov','Valeriy','Mihajlovich','3','2','3')
insert into Teachers (id_teach,t_surname,t_name,t_pname,id_step,id_zvan,id_doljn) values ('4','Volkovskij','Oleg','Stepanovich','2','1','2')
insert into Teachers (id_teach,t_surname,t_name,t_pname,id_step,id_zvan,id_doljn) values ('5','Sokolova','Natalja','Olegovna','1',NULL,'1')
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 PRIMARY KEY, 
						article_name varchar(50))
insert into Articles (id_article,article_name) values ('1','Nonquasratc Regularization')
insert into Articles (id_article,article_name) values ('2','Image filtration')
insert into Articles (id_article,article_name) values ('3','System modeling')
insert into Articles (id_article,article_name) values ('4','Programming methods')
insert into Articles (id_article,article_name) values ('5','Neural Networks')
insert into Articles (id_article,article_name) values ('6','UML and Buisness Modeling')
insert into Articles (id_article,article_name) values ('7','C++ and Program Interfaces')
insert into Articles (id_article,article_name) values ('8','Image addaptation')
insert into Articles (id_article,article_name) values ('9','Support Vector Machines for image clusterization')
insert into Articles (id_article,article_name) values ('10','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 PRIMARY KEY, 
					tethis_name varchar(50), 
					konf_name varchar(50))
insert into Tethis (id_tethis,tethis_name,konf_name) values ('1','Sound Restoration T','T')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('2','Nonquasratc Regularization T','TT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('3','Image filtration T','TTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('4','System modeling T','TTTTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('5','Programming methods T','TTTTTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('6','Neural Networks T','TTTTTTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('7','UML and Buisness Modeling','TTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('8','C++ and Program Interfaces T','TTTTTTTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('9','Image addaptation T','TTTT')
insert into Tethis (id_tethis,tethis_name,konf_name) values ('10','Support Vector Machines for image clusterization T','TTTT')
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 (id_article,id_article_author,article_author_type) values ('1','1','1')
insert into Article_author (id_article,id_article_author,article_author_type) values ('1','1','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('1','2','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('2','2','1')
insert into Article_author (id_article,id_article_author,article_author_type) values ('2','5','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('3','6','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('3','2','1')
insert into Article_author (id_article,id_article_author,article_author_type) values ('4','2','1')
insert into Article_author (id_article,id_article_author,article_author_type) values ('4','4','1')
insert into Article_author (id_article,id_article_author,article_author_type) values ('4','8','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('5','3','1')
insert into Article_author (id_article,id_article_author,article_author_type) values ('5','17','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('6','21','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('7','25','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('8','18','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('9','9','0')
insert into Article_author (id_article,id_article_author,article_author_type) values ('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 (id_tethis,id_tethis_author,tethis_author_type) values ('1','1','1')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('1','11','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('1','15','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('2','5','1')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('2','18','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('2','21','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('3','25','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('3','20','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('4','5','1')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('4','6','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('4','13','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('5','17','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('5','12','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('6','2','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('7','4','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('8','15','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('9','9','0')
insert into Tethis_author (id_tethis,id_tethis_author,tethis_author_type) values ('10','3','0')
END
--ELSE
--DROP TABLE Tethis_author


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Kurs')
BEGIN
create table Kurs (id_stud int REFERENCES Student, 
							kurs tinyint)
insert into Kurs (id_stud, kurs) values ('1','4')
insert into Kurs (id_stud, kurs) values ('2','4')
insert into Kurs (id_stud, kurs) values ('3','4')
insert into Kurs (id_stud, kurs) values ('4','4')
insert into Kurs (id_stud, kurs) values ('5','4')
insert into Kurs (id_stud, kurs) values ('6','4')
insert into Kurs (id_stud, kurs) values ('7','4')
insert into Kurs (id_stud, kurs) values ('8','1')
insert into Kurs (id_stud, kurs) values ('9','1')
insert into Kurs (id_stud, kurs) values ('10','1')
insert into Kurs (id_stud, kurs) values ('11','1')
insert into Kurs (id_stud, kurs) values ('12','1')
insert into Kurs (id_stud, kurs) values ('13','1')
insert into Kurs (id_stud, kurs) values ('14','1')
insert into Kurs (id_stud, kurs) values ('15','3')
insert into Kurs (id_stud, kurs) values ('16','3')
insert into Kurs (id_stud, kurs) values ('17','3')
insert into Kurs (id_stud, kurs) values ('18','5')
insert into Kurs (id_stud, kurs) values ('19','5')
insert into Kurs (id_stud, kurs) values ('20','5')
insert into Kurs (id_stud, kurs) values ('21','5')
insert into Kurs (id_stud, kurs) values ('22','5')
insert into Kurs (id_stud, kurs) values ('23','5')
insert into Kurs (id_stud, kurs) values ('24','5')
insert into Kurs (id_stud, kurs) values ('25','5')
END
--ELSE
--DROP TABLE Kurs

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Specialnosti')
BEGIN
create table Specialnosti (id_spec int PRIMARY KEY, 
							spec_name varchar(50))
insert into Specialnosti (id_spec, spec_name) values ('1','KS')
insert into Specialnosti (id_spec, spec_name) values ('2','KI')
insert into Specialnosti (id_spec, spec_name) values ('3','KM')
insert into Specialnosti (id_spec, spec_name) values ('4','KP')
insert into Specialnosti (id_spec, spec_name) values ('5','KT')
END
--ELSE
--DROP TABLE Specialnosti


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Group_Specialnost')
BEGIN
create table Group_Specialnost (id_group smallint REFERENCES Grouppa, 
							id_spec int REFERENCES Specialnosti)
insert into Group_Specialnost (id_group,id_spec) values ('1','1')
insert into Group_Specialnost (id_group,id_spec) values ('2','2')
insert into Group_Specialnost (id_group,id_spec) values ('3','3')
insert into Group_Specialnost (id_group,id_spec) values ('4','4')
insert into Group_Specialnost (id_group,id_spec) values ('5','5')
END
--ELSE
--DROP TABLE Group_Specialnost