creat_table_new.sql

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

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Countries')
BEGIN
create table Countries (id_country int identity  PRIMARY KEY, 
						country_name varchar(50))
insert into Countries 
select 'Ukraine'
union select 'Russia'
union select 'Belorussia'
union select 'Kazahstan'
union select 'China'

END
--ELSE
--DROP TABLE Countries


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Cities')
BEGIN
create table Cities (id_city int identity PRIMARY KEY,
					 city_name varchar(50))
insert into Cities 
select 'Dnipropetrovsk'
union select 'Kiev'
union select 'Moscow'
union select 'Lvov'
union select 'Pekin'

END
--ELSE
--DROP TABLE Cities


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Streets')
BEGIN
create table Streets (id_street int identity PRIMARY KEY, 
					  street_name varchar(50))
insert into Streets 
select 'Kazakova'
union select 'Gagarina'
union select 'Lenina'
union select 'Gorkogo'
union select 'Shevchenko'

END
--ELSE
--DROP TABLE Streets


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Person')
BEGIN
create table Person (id_person int identity PRIMARY KEY ,
					 st_surname varchar(50),
					 st_name varchar(50),
					 st_pname varchar(50), 
					 st_sex bit,
					 st_birthday datetime, 
					 st_phone varchar(15),
					 st_military bit,
					 id_country int REFERENCES Countries, 
				     id_city int REFERENCES Cities, 
					 id_street int REFERENCES Streets, 
					 house varchar(10),
					 flat int,
					 check(datediff(d,st_birthday, getdate())>0)
					 )
insert into Person 
select 'Mahnitskiy','Alexandr','Igorevich','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '34',5
union select 'Duma','Karina','Valentinovna','1','2 Aug 87','8-0562-38-17-26','0', (select id_country from Countries where country_name='Russia'),( select id_city from Cities where city_name='Kiev'), (select id_street from Streets where street_name='Shevchenko'), '75', 31
union select 'Kulichenko','Petr','Afanasjevich','0','25 Aug 89','8-0592-35-18-46','1',  (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Gorkogo'), '20b', 2
union select 'Ivanenko','Oleg','Georgievich','0','23 Jul 88','8-0532-36-55-26','1',(select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Kazakova'), '38', 16
union select 'Zubr','Pavel','Petrovich','0','2 Feb 89','8-056-774-17-26','1', (select id_country from Countries where country_name='Kazahstan'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '38', 8
union select 'Ivanov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Kazakova'), '43', 8
union select 'Kulibaba','Irina','Valentinovna','1','2 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Moscow'), (select id_street from Streets where street_name='Gorkogo'), '4', 8
union select 'Petrov','Petr','Petrovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '21', 4
union select 'Lawina','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Kazakova'), '38', 2
union select 'Sergeev','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Kazahstan'),( select id_city from Cities where city_name='Lvov'), (select id_street from Streets where street_name='Shevchenko'), '21', 8
union select 'Procenko','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Lenina'), '4', 7
union select 'Kucjko','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Russia'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '21', 8
union select 'Zasteba','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Russia'),( select id_city from Cities where city_name='Moscow'), (select id_street from Streets where street_name='Kazakova'), '2a', 2
union select 'Pavlov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Russia'),( select id_city from Cities where city_name='Moscow'), (select id_street from Streets where street_name='Shevchenko'), '21', 8
union select 'Karacjuba','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Kiev'), (select id_street from Streets where street_name='Shevchenko'), '3', 1
union select 'Wava','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '21', 43
union select 'Kalinova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '30b', 45
union select 'Prosolov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '21', 2
union select 'Kazakova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0',(select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Kiev'), (select id_street from Streets where street_name='Shevchenko'), '3', 56
union select 'Zaharov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Russia'),( select id_city from Cities where city_name='Lvov'), (select id_street from Streets where street_name='Shevchenko'), '21', 1
union select 'Popova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Ukrain'),( select id_city from Cities where city_name='Kiev'), (select id_street from Streets where street_name='Gagarina'), '2', 4
union select 'Bokov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='Belorussia'),( select id_city from Cities where city_name='Dnipropetrovsk'), (select id_street from Streets where street_name='Shevchenko'), '21', 8
union select 'Jdanova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='Belorussia'),( select id_city from Cities where city_name='Lvov'), (select id_street from Streets where street_name='Gagarina'), '8', 3
union select 'Kotov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1', (select id_country from Countries where country_name='China'),( select id_city from Cities where city_name='Pekin'), (select id_street from Streets where street_name='Lenina'), '21', 8
union select 'Kapusta','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0', (select id_country from Countries where country_name='China'),( select id_city from Cities where city_name='Pekin'), (select id_street from Streets where street_name='Lenina'), '32', 10

END
--ELSE
--DROP TABLE Person


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Specialnosti')
BEGIN
create table Specialnosti (id_spec smallint identity PRIMARY KEY, 
							spec_name varchar(50))
insert into Specialnosti 
select 'Information ruled technologies'
union select 'Computers networks and technologies'
union select 'Komputer phisics'
union select 'Phisics and electronics'
union select 'Biomedical technologies'
END
--ELSE
--DROP TABLE Specialnosti



if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Grouppa')
BEGIN
create table Grouppa (id_group int identity PRIMARY KEY,
					  group_name varchar(9),
					  id_spec smallint REFERENCES Specialnosti,
					  kurs tinyint default 1)
insert into Grouppa
select 'KS-05', (select id_spec from Specialnosti  where spec_name='Information ruled technologies'), 4
union select 'KI-05',  (select id_spec from Specialnosti  where spec_name='Computers networks and technologies'), 4
union select 'KM-08',  (select id_spec from Specialnosti  where spec_name='Komputer phisics'), 1
union select 'KP-06', (select id_spec from Specialnosti  where spec_name='Phisics and electronics'), 2
union select 'KT-04', (select id_spec from Specialnosti  where spec_name='Biomedical technologies'), 3


END
--ELSE
--DROP TABLE Grouppa


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Form_edu')
BEGIN
create table Form_edu (id_form smallint identity PRIMARY KEY,
					   form_name varchar(10))
insert into Form_edu 
select 'Dnevnoe'
union select 'Vechernee'
union select 'Zaochne'

END
--ELSE
--DROP TABLE Form_edu



if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Student')
BEGIN
create table Student (id_stud int identity PRIMARY KEY , 
					  id_person int  REFERENCES Person,
					  id_group int  REFERENCES Grouppa,
					  id_form smallint  REFERENCES Form_edu,
					  finance bit)
insert into Student 
select (select id_person from Person where st_surname='Mahnitskiy' and st_name='Alexandr' and st_pname='Igorevich'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Kulichenko'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Duma'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Kulichenko'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Vechernee'),1
union select (select id_person from Person where st_surname='Ivanenko'),(select id_group from Grouppa where group_name='KP-06'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Zubr'),(select id_group from Grouppa where group_name='KI-05'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Ivanov'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Kulibaba'),(select id_group from Grouppa where group_name='KP-06'),(select id_form from Form_edu where form_name='Vechernee'),1
union select (select id_person from Person where st_surname='Petrov'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Zaochne'),0
union select (select id_person from Person where st_surname='Lawina'),(select id_group from Grouppa where group_name='KM-08'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Sergeev'),(select id_group from Grouppa where group_name='KM-08'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Procenko'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Kucjko'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Vechernee'),0
union select (select id_person from Person where st_surname='Zasteba'),(select id_group from Grouppa where group_name='KT-04'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Pavlov'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Karacjuba'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Zaochne'),1
union select (select id_person from Person where st_surname='Kalinova'),(select id_group from Grouppa where group_name='KI-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Prosolov'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Kazakova'),(select id_group from Grouppa where group_name='KI-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Zaharov'),(select id_group from Grouppa where group_name='KT-04'),(select id_form from Form_edu where form_name='Vechernee'),1
union select (select id_person from Person where st_surname='Popova'),(select id_group from Grouppa where group_name='KI-05'),(select id_form from Form_edu where form_name='Zaochne'),0
union select (select id_person from Person where st_surname='Bokov'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Vechernee'),1
union select (select id_person from Person where st_surname='Jdanova'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0
union select (select id_person from Person where st_surname='Kotov'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),1
union select (select id_person from Person where st_surname='Kapusta'),(select id_group from Grouppa where group_name='KS-05'),(select id_form from Form_edu where form_name='Dnevnoe'),0

END
--ELSE
--DROP TABLE Student

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Subject_names')
BEGIN
create table Subject_names (id_subject int identity PRIMARY KEY,
							subject_name varchar(30))
insert into Subject_names
select 'fizika'
union select 'algebra'
union select 'geometrija'
union select 'OPAM'
union select 'SQL'
union select 'IKG'
union select 'C++'
union select 'English'
union select 'Ukr.mova'
union select 'UML'
union select 'CASE-tech'
union select 'Elektrotech'
union select 'Schemotech'
union select 'Assembler'
union select 'Teor.ver'
union select 'Networks'
union select 'Algorithms'
union select 'Image Processing'
union select 'Signal Processing'
union select 'TAU'
END
--ELSE
--DROP TABLE Subject_names


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Mark_names')
BEGIN
create table Mark_names (id_mark tinyint identity PRIMARY KEY, 
						 ball_name tinyint, 
						 bolon_mark_name varchar(10),
						 zach_nezach bit)
insert into Mark_names 
      select 1,'E',0--1
union select 2,'D',0--2
union select 3,'C',1--3
union select 4,'B',1--4
union select 5,'A',1--5
END
--ELSE
--DROP TABLE Mark_names


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Mark')
BEGIN
create table Mark (id_stud int REFERENCES Student , 
					id_subject int REFERENCES Subject_names ,