sqlquery2.sql

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

--constraint na form_edu
alter table Student  
	add constraint check_form check ((id_form=1) or (id_form=2) or(id_form=3) or(id_form=4))
--Students in hostel 
select st_surname, substring(st_name,1,1) as st_name, substring(st_pname,1,1) as st_pname 
	from Person
where id_person = any 
	(select id_person 
		from Adres 
			where id_country= any
		(select id_country 
			from Countries
				where country_name='Ukraine')
		and id_city= any
		(select id_city 
			from Cities
				where city_name='Dnipropetrovsk')
		and id_street= any
		(select id_street 
			from Streets
				where street_name='Kazakova')
		and	house = 13
	)
		
--Military students
select st_surname, substring(st_name,1,1) as st_name, substring(st_pname,1,1) as st_pname 
	from Person
where   
	st_military=1 and (YEAR(CURRENT_TIMESTAMP)-YEAR(st_birthday))>=18 and (YEAR(CURRENT_TIMESTAMP)-YEAR(st_birthday))<25

--Students from KS
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'id_person_fio ')
drop table id_person_fio

if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'id_group_name ')
drop table id_group_name

select id_person as id_pers, st_surname, substring(st_name,1,1) as st_name, substring(st_pname,1,1) as st_pname 
into id_person_FIO	
from Person
		where id_person = any 
		(select id_person 
			from Student
			where id_stud = any
				(select all id_stud 
					from Student
						where id_group=any
							(select id_group
								from Grouppa
									where substring(group_name,1,2)='KS')))
select st_surname, st_name, st_pname, id_group 
into id_group_name
from id_person_FIO JOIN Student
	ON id_person_FIO.id_pers=Student.id_person 

drop table id_person_fio

select st_surname, st_name, st_pname,
	(YEAR(CURRENT_TIMESTAMP)-CAST ((substring(group_name,4,2))as int)-2000+1) as cur_kurs
from id_group_name JOIN Grouppa
	ON id_group_name.id_group=Grouppa.id_group

drop table id_group_name


--subjects and marks
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'id_sub_mark ')
drop table id_sub_mark

if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sub_id_mark ')
drop table sub_id_mark

select id_subject, id_mark
into id_sub_mark
	from Mark
where id_stud=3

select subject_name, id_mark 
into sub_id_mark 
	from Subject_names JOIN id_sub_mark 
		ON Subject_names.id_subject=id_sub_mark.id_subject

drop table id_sub_mark

select subject_name, ball_name,bolon_mark_name
from sub_id_mark JOIN Mark_names
		ON sub_id_mark.id_mark=Mark_names.id_mark

drop table sub_id_mark

--paper's owners
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'art_name_auth_type')
drop table art_name_auth_type
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'art_name_stud')
drop table art_name_stud
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'article_FIO')
drop table article_FIO
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'article_group')
drop table article_group
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '_article_')
drop table _article_

select article_name, id_article_author, article_author_type
into art_name_auth_type
from Articles JOIN Article_author
	ON Articles.id_article=Article_author.id_article

select article_name, id_article_author as id_pers
into art_name_stud
from art_name_auth_type
	where article_author_type=0

drop table art_name_auth_type

select st_surname, substring(st_name,1,1) as st_name, substring(st_pname,1,1) as st_pname,
		article_name, id_pers 
into article_FIO	
from Person JOIN art_name_stud
	ON Person.id_person=art_name_stud.id_pers

drop table art_name_stud

select st_surname, st_name, st_pname, article_name, id_group
into article_group
from article_FIO JOIN Student
	ON article_FIO.id_pers=Student.id_person

drop table article_FIO

select st_surname, st_name, st_pname, article_name as paper, group_name, 'A' as id_type
into _article_
from article_group JOIN Grouppa
	ON article_group.id_group=Grouppa.id_group

drop table article_group

-------------------
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'teth_name_auth_type')
drop table teth_name_auth_type
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'teth_name_stud')
drop table teth_name_stud
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tethis_FIO')
drop table tethis_FIO
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tethis_group')
drop table tethis_group
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '_tethis_')
drop table _tethis_

select tethis_name, id_tethis_author, tethis_author_type
into teth_name_auth_type
from Tethis JOIN Tethis_author
	ON Tethis.id_tethis=Tethis_author.id_tethis

select tethis_name, id_tethis_author as id_pers
into teth_name_stud
from teth_name_auth_type
	where tethis_author_type=0

drop table teth_name_auth_type

select st_surname, substring(st_name,1,1) as st_name, substring(st_pname,1,1) as st_pname,
		tethis_name, id_pers 
into tethis_FIO	
from Person JOIN teth_name_stud
	ON Person.id_person=teth_name_stud.id_pers

drop table teth_name_stud

select st_surname, st_name, st_pname, tethis_name, id_group
into tethis_group
from tethis_FIO JOIN Student
	ON tethis_FIO.id_pers=Student.id_person

drop table tethis_FIO

select st_surname, st_name, st_pname, tethis_name as paper, group_name, 'T' as id_type
into _tethis_
from tethis_group JOIN Grouppa
	ON tethis_group.id_group=Grouppa.id_group

drop table tethis_group

--------

select * from _article_
UNION
select * from _tethis_

drop table _article_
drop table _tethis_ 

---- using common table expressions
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'res1')
drop table res1

if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'res2')
drop table res2


;with cte_1(subject_name, 
group_name)
--substring(group_name,1,2) as grouppa,
--( YEAR(CURRENT_TIMESTAMP)-CAST ((substring(group_name,4,2))as int)-2000+1) as cur_kurs)
AS
(
	select id_stud,group_name
	into res1
		from (Student JOIN Grouppa
			ON Student.id_group=Grouppa.id_group)
	
	select DISTINCT id_subject, group_name
	into res2
		from (res1 JOIN Mark
			ON res1.id_stud=Mark.id_stud)
	
	drop table res1

	select subject_name, group_name
	into res1
	from (res2 JOIN Subject_names
			ON res2.id_subject=Subject_names.id_subject)
	
	drop table res2
	--select * from res1
)select * from cte_1;

-- using cte without cte
select id_stud, group_name
	into res1
		from Student JOIN Grouppa
			ON Student.id_group=Grouppa.id_group
	
	select DISTINCT id_subject, group_name
	into res2
		from res1 JOIN Mark
			ON res1.id_stud=Mark.id_stud
	
	drop table res1

	select subject_name, group_name		
	into res1
	from res2 JOIN Subject_names
			ON res2.id_subject=Subject_names.id_subject
	
	drop table res2

select subject_name, 
substring(group_name,1,2) as grouppa,
( YEAR(CURRENT_TIMESTAMP)-CAST ((substring(group_name,4,2))as int)-2000+1) as cur_kurs
from res1

drop table res1

--
;with cte_2(id_group, group_name)
as
(select * from Grouppa
)
select * from cte_2
-----------
--Lab_4 - Cursors
-----------
if exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'table_for_cur1')
drop table table_for_cur1
select st_surname, substring(st_name,1,1) as st_name, substring(st_pname,1,1) as st_pname,
		 group_name,
		 (YEAR(CURRENT_TIMESTAMP)-CAST ((substring(group_name,4,2))as int)-2000+1)  as cur_kurs,
		id_subject, ball_name as mark
into table_for_cur1
from Person JOIN Student ON Person.id_person=Student.id_person
			JOIN Grouppa ON Student.id_group=Grouppa.id_group
			JOIN Mark ON Student.id_stud=Mark.id_stud
			JOIN Mark_names ON Mark.id_mark=Mark_names.id_mark


if (cursor_status('global', 'cursor1') <> -3) 
	deallocate global cursor1

declare cursor1 cursor
	global scroll keyset
for select st_surname,st_name, st_pname, group_name from table_for_cur1
			where cur_kurs=4 
declare @surname varchar(30),
		@name varchar(2),
		@pname varchar(2),
		@group varchar(10),
		@message VARCHAR(80)

open cursor1
--declare @i int
--set @i=5
--while (@i!=1)
fetch next from cursor1 into @surname,@name,@pname,@group 
while (@@fetch_status=0)
begin
select @message=@surname+'  '+@name+'  '+@pname+'  '+@group+';'
 PRINT @message

fetch next from cursor1 into @surname,@name,@pname,@group 

--fetch next from cursor1
--set @i=@i-1
end

close cursor1

deallocate global cursor1

-- FIO, grouppa doljnikov---------------------------------
if (cursor_status('global', 'cursor2') <> -3) 
	deallocate global cursor2

declare cursor2 cursor
	global scroll keyset
for select st_surname,st_name, st_pname, group_name,id_subject,mark from table_for_cur1
			--where cur_kurs=4 
declare @surname varchar(30),
		@name varchar(2),
		@pname varchar(2),
		@group varchar(10),
		@id_sub smallint,
		@mark tinyint,
		@message VARCHAR(100)
		

open cursor2

	fetch next from cursor2 into @surname,@name,@pname,@group,@id_sub,@mark 
	while (@@fetch_status=0)
	begin
			if (@mark<3)
			begin
				select @message=@surname+'  '+@name+'  '+@pname+'  '+@group+' '
						+cast(@id_sub as varchar)+'  '+cast(@mark as varchar)+';'
				PRINT @message
			end
		fetch next from cursor2 into @surname,@name,@pname,@group,@id_sub,@mark  
	end

close cursor2
deallocate global cursor2