sqlquery1.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 PRIMARY KEY, 
						country_name varchar(30))
insert into Countries (id_country,country_name) values ('1','Ukraine')
insert into Countries (id_country,country_name) values ('2','Russia')
insert into Countries (id_country,country_name) values ('3','Belorussia')
insert into Countries (id_country,country_name) values ('4','Kazahstan')
insert into Countries (id_country,country_name) values ('5','Turkey')

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 PRIMARY KEY,
					 city_name varchar(30))
insert into Cities (id_city,city_name) values ('1','Dnipropetrovsk')
insert into Cities (id_city,city_name) values ('2','Kharkiv')
insert into Cities (id_city,city_name) values ('3','Moscow')
insert into Cities (id_city,city_name) values ('4','Kiev')
insert into Cities (id_city,city_name) values ('5','Minsk')


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 PRIMARY KEY, 
					  street_name varchar(30))
insert into Streets (id_street,street_name) values ('1','Kazakova')
insert into Streets (id_street,street_name) values ('2','Gagarina')
insert into Streets (id_street,street_name) values ('3','Lenina')
insert into Streets (id_street,street_name) values ('4','Gorkogo')
insert into Streets (id_street,street_name) values ('5','Shevchenko')

END
--ELSE
--DROP TABLE Streets


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Adres')
BEGIN
create table Adres (id_person int PRIMARY KEY, 
					id_country int REFERENCES Countries, 
					id_city int REFERENCES Cities, 
					id_street int REFERENCES Streets, 
					house varchar(10), 
					flat int)
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('1','1','1','1','13','806')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('2','1','2','2','93','706')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('3','1','4','4','13','46')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('4','1','1','1','13','403')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('5','1','2','2','30','86')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('6','1','4','2','38','8')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('7','1','1','1','73','84')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('8','1','2','4','23','86')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('9','1','4','1','53','806')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('10','2','3','1','13','809')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('11','1','1','1','13','204')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('12','1','2','5','53','46')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('13','3','5','3','73','56')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('14','1','4','1','93','36')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('15','1','1','5','83','56')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('16','2','3','4','73','36')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('17','1','2','5','3','76')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('18','1','4','4','1','46')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('19','1','1','1','13','36')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('20','3','5','3','37','46')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('21','1','2','4','47','89')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('22','1','4','3','36','66')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('23','1','1','1','13','806')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('24','3','5','4','35','66')
insert into Adres (id_person,id_country,id_city,id_street,house,flat) values ('25','1','2','1','34','75')

END
--ELSE
--DROP TABLE Adres


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Person')
BEGIN
create table Person (id_person int PRIMARY KEY REFERENCES Adres ,
					 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)
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('1','Kyrgyzova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('2','Duma','Karina','Valentinovna','1','2 Aug 87','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('3','Kulichenko','Petr','Afanasjevich','0','25 Aug 89','8-0592-35-18-46','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('4','Ivanenko','Oleg','Georgievich','0','23 Jul 88','8-0532-36-55-26','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('5','Zubr','Pavel','Petrovich','0','2 Feb 89','8-056-774-17-26','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('6','Ivanov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('7','Kulibaba','Irina','Valentinovna','1','2 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('8','Petrov','Petr','Petrovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('9','Lawina','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('10','Sergeev','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('11','Procenko','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('12','Kucjko','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('13','Zasteba','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('14','Pavlov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('15','Karacjuba','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('16','Wava','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('17','Kalinova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('18','Prosolov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('19','Kazakova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('20','Zaharov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('21','Popova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('22','Bokov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('23','Jdanova','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('24','Kotov','Ivan','Ivanovich','0','23 Sep 88','8-0532-36-57-86','1')
insert into Person (id_person,st_surname,st_name,st_pname,st_sex,st_birthday,st_phone,st_military) values ('25','Kapusta','Khrystyna','Valentinovna','1','28 Aug 88','8-0562-38-17-26','0')

END
--ELSE
--DROP TABLE Person


if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Grouppa')
BEGIN
create table Grouppa (id_group smallint PRIMARY KEY,
					  group_name varchar(10))
insert into Grouppa (id_group,group_name) values ('1','KS-05')
insert into Grouppa (id_group,group_name) values ('2','KI-05')
insert into Grouppa (id_group,group_name) values ('3','KM-08')
insert into Grouppa (id_group,group_name) values ('4','KP-06')
insert into Grouppa (id_group,group_name) values ('5','KT-04')


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 PRIMARY KEY,
					   form_name varchar(10))
insert into Form_edu (id_form,form_name) values ('1','Dnevnoe')
insert into Form_edu (id_form,form_name) values ('2','Vechernee')
insert into Form_edu (id_form,form_name) values ('3','Zaochne')
insert into Form_edu (id_form,form_name) values ('4','Extern')

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 PRIMARY KEY , 
					  id_person int NOT NULL REFERENCES Person,
					  id_group smallint NOT NULL REFERENCES Grouppa,
					  id_form smallint NOT NULL REFERENCES Form_edu,
					  finance bit)
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('1','1','1','1','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('2','2','1','1','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('3','3','1','1','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('4','4','1','4','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('5','5','2','2','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('6','6','2','2','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('7','7','2','2','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('8','8','3','3','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('9','9','3','3','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('10','10','3','3','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('11','11','3','3','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('12','12','3','3','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('13','13','3','3','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('14','14','3','3','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('15','15','4','1','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('16','16','4','1','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('17','17','4','1','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('18','18','5','4','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('19','19','5','1','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('20','20','5','1','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('21','21','5','4','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('22','22','5','1','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('23','23','5','1','0')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('24','24','5','4','1')
insert into Student (id_stud, id_person,id_group, id_form, finance) values ('25','25','5','1','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 smallint PRIMARY KEY,
							subject_name varchar(30))
insert into Subject_names (id_subject,subject_name) values ('1','fizika')
insert into Subject_names (id_subject,subject_name) values ('2','algebra')
insert into Subject_names (id_subject,subject_name) values ('3','geometrija')
insert into Subject_names (id_subject,subject_name) values ('4','OPAM')
insert into Subject_names (id_subject,subject_name) values ('5','SQL')
insert into Subject_names (id_subject,subject_name) values ('6','IKG')
insert into Subject_names (id_subject,subject_name) values ('7','C++')
insert into Subject_names (id_subject,subject_name) values ('8','English')
insert into Subject_names (id_subject,subject_name) values ('9','Ukr.mova')
insert into Subject_names (id_subject,subject_name) values ('10','UML')
insert into Subject_names (id_subject,subject_name) values ('11','CASE-tech')
insert into Subject_names (id_subject,subject_name) values ('12','Elektrotech')
insert into Subject_names (id_subject,subject_name) values ('13','Schemotech')
insert into Subject_names (id_subject,subject_name) values ('14','Assembler')
insert into Subject_names (id_subject,subject_name) values ('15','Teor.ver')
insert into Subject_names (id_subject,subject_name) values ('16','Networks')
insert into Subject_names (id_subject,subject_name) values ('17','Algorithms')
insert into Subject_names (id_subject,subject_name) values ('18','Image Processing')
insert into Subject_names (id_subject,subject_name) values ('19','Signal Processing')
insert into Subject_names (id_subject,subject_name) values ('20','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 PRIMARY KEY, 
						 ball_name tinyint, 
						 bolon_mark_name varchar(10),
						 zach_nezach bit)
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('1',NULL,NULL,'0')--nezach
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('2',NULL,NULL,'1')--zach
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('3','1','E',NULL)--1
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('4','2','D',NULL)--2
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('5','3','C',NULL)--3
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('6','4','B',NULL)--4
insert into Mark_names (id_mark,ball_name,bolon_mark_name,zach_nezach) values ('7','5','A',NULL)--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 ,