Create database School;
GO
use School;
GO

--Создание базовых таблиц.
create table Disc
(
	ID_disc int primary key identity(1,1),
	disc_Name nvarchar(20) not null
);

create table Groups
(
	ID_group int primary key identity,
	group_name nvarchar(20) not null
);

create table Stud
(
	ID_stud  int primary key identity,
	stud_fio nvarchar(30) not null,
	stud_group int not null
	CONSTRAINT FK_Stud_Group FOREIGN KEY
	REFERENCES Groups (ID_group)
);

create table Teach
(
	ID_teach int,
	teach_fio nvarchar(30) not null,
	teach_disc int,
	teach_group int null,
	primary key (id_teach,teach_disc),
	CONSTRAINT FK_teach_disc FOREIGN KEY
	(teach_disc)
	REFERENCES Disc (ID_disc),
);

create table Schedule 
(
DoW int not null check (DoW > 0 and DoW < 8),
less_num int not null check (less_num > 0 and less_num < 7),
less_teach int,
less_disc int,
less_group int,
CONSTRAINT FK_less_group FOREIGN KEY
(less_group)
REFERENCES Groups (id_group),
CONSTRAINT FK_id_teach_teach_disc FOREIGN KEY
(less_teach,less_disc)
REFERENCES Teach (id_teach,teach_disc),
unique (DoW,less_num,less_teach),
unique (DoW,less_num,less_group),
);
GO

--Создание процедур добавления учителей и учеников, для реализации ограничений по кол-ву учеников в классе,
--и назначения классного руководства преподователю без уроков в расписании.

--Для промышленной БД, логично было бы реалиховать добавление всех данных через процедуры с проверками необходимых условий,
--но так как, наша база предназначина только для проверки, я решил не реализовывать лишние процедуры.

create procedure add_student
    @name nvarchar(30), 
    @group int 
AS 
    SET NOCOUNT ON;
	if (select COUNT(1) from Stud where stud_group = @group) < 30
	begin
		insert into Stud values (@name,@group)
	end;
	else
	begin
		print N'too many students in this group';	
	end;
GO

create procedure add_teacher
	@id int,
	@name nvarchar(30),
	@disc int,
	@group int = null
as
	SET NOCOUNT ON;
	if @group is not null
	begin	
		if exists (select 1 from Schedule where less_teach = @id)
		begin
			insert into Teach values
			(@id,@name,@disc,@group)
		end;
		else
		print N'Этот учитель не может быть классным руководителем.'
	end
	else
		insert into Teach values (@id,@name,@disc,@group)
go


--Добавление тестовых данных в базу.

insert into Disc values 
(N'Eng'),(N'Rus'),(N'Math');

insert into Groups values
(N'A'),(N'B'),(N'C');

exec add_teacher 1,N'Иванов И.И.',2;
exec add_teacher 2,N'teach2',1;
exec add_teacher 2,N'teach2',3;
exec add_teacher 3,N'teach3',3;
exec add_teacher 4,N'teach4',1;

exec add_student N'stud1',1;
exec add_student N'stud2',2;
exec add_student N'stud3',3;
exec add_student N'stud4',1;
exec add_student N'stud5',2;
exec add_student N'stud6',3;
exec add_student N'stud7',1;
exec add_student N'stud8',2;
exec add_student N'stud9',3;
exec add_student N'stud10',1;

insert into Schedule values
(1,1,2,1,1),
(1,2,1,2,2),
(1,3,2,3,3),
(1,4,3,3,1),
(1,5,1,2,3),
(1,6,1,2,1),
(1,1,1,2,2),
(2,6,3,3,1),
(2,5,3,3,1);
go

use School;
go

--Запрос 1 на кол-во учеников изучающих Math.
select COUNT (id_stud) as Math_students from Stud 
where stud_group in
(
	select distinct less_group from Schedule 
	where less_disc in 
	(
		select id_disc from Disc where disc_Name = N'Math'
	)
)


--Запрос 2 на кол-во астрономических часов преподователя Иванова И.И
select COUNT (1)*2/3 as N'Иванов ИИ часов в неделю'
from
(
select dow, less_num,less_group  from Schedule 
where less_teach =
(
	select distinct ID_teach from Teach where teach_fio = N'Иванов И.И.'
)
) as Q


--Запрос 3 на топ 3 учителей по кол-ву обучаемых учеников.
DECLARE @MyCounter int;
SET @MyCounter = 1;

create table tmp (teacher int, students int)

WHILE @MyCounter <= (select MAX (id_teach)from teach)
begin;
	INSERT INTO tmp VALUES
	(@MyCounter,
		(
		select count(id_stud) as 'students of teacher' from stud where stud_group in
		(
			select less_group from Schedule 
			where less_teach = @mycounter
			union 
			select teach_group from Teach  
			where ID_teach = @mycounter
		)
		)
	)
set @MyCounter = @MyCounter + 1;
end;
GO

SELECT distinct top 3 teach_fio, students 
FROM tmp
inner join Teach 
on tmp.teacher = teach.ID_teach 
order by students desc;
GO
DROP TABLE tmp;
GO

	

--Создание базовых таблиц.
create table Disc
(
	ID_disc int primary key identity(1,1),
	disc_Name nvarchar(20) not null
);

create table Groups
(
	ID_group int primary key identity(1,1),
	group_name nvarchar(20) not null
);

create table Stud
(
	ID_stud  int primary key identity(1,1),
	stud_fio nvarchar(30) not null,
	stud_group int not null
	CONSTRAINT FK_Stud_Group FOREIGN KEY
	REFERENCES Groups (ID_group)
);

create table Teach
(
	ID_teach int primary key identity(1,1),
	teach_fio nvarchar(30) not null unique,
	teach_group int null,
);

create table Teach_Disc
(
TD_teach int not null,
TD_disc int not null,
constraint FK_TD_teach_ID_teach foreign key
(TD_teach)references Teach (ID_teach)
on delete cascade,
constraint FK_TD_disc_ID_disc foreign key
(TD_disc)references Disc(ID_disc),
unique (TD_teach,TD_disc)
);


create table Schedule 
(
DoW int not null check (DoW > 0 and DoW < 8),
less_num int not null check (less_num > 0 and less_num < 7),
less_teach int not null,
less_disc int not null,
less_group int not null,
CONSTRAINT FK_less_group FOREIGN KEY
(less_group)
REFERENCES Groups (id_group),
CONSTRAINT FK_td_teach_td_disc FOREIGN KEY
(less_teach,less_disc)
REFERENCES Teach_Disc (td_teach,td_disc),
unique (DoW,less_num,less_teach),
unique (DoW,less_num,less_group)
);
GO

--Создание процедур добавления учителей и учеников, для реализации ограничений по кол-ву учеников в классе,
--и назначения классного руководства преподователю без уроков в расписании.

--Для промышленной БД, логично было бы реалиховать добавление всех данных через процедуры с проверками необходимых условий,
--но так как, наша база предназначина только для проверки, я решил не реализовывать лишние процедуры.

create procedure add_student
    @name nvarchar(30), 
    @group int 
AS 
    SET NOCOUNT ON;
	if (select COUNT(1) from Stud where stud_group = @group) < 30
	begin
		insert into Stud values (@name,@group)
	end;
	else
	begin
		print N'too many students in this group';	
	end;
GO

create procedure add_teacher
	@name nvarchar(30),
	@group int = null
as
	SET NOCOUNT ON;
	if @group is not null
	begin	
		if exists (select 1 from Schedule where less_teach = (select MAX(ID_teach)+1 from Teach))
		begin
			insert into Teach values
			(@name,@group)
		end;
		else
		print N'Этот учитель не может быть классным руководителем.'
	end
	else
		insert into Teach values (@name,@group)
go

create procedure upd_teacher
	@Name nvarchar(30),
	@group int = null
as
	set nocount on;
	if @group is not null
	begin
		if exists (select 1 from Schedule where less_teach in (select ID_teach from Teach where teach_fio = @Name))
		begin
			update Teach set teach_group = @group 
			where ID_teach = (select ID_teach from Teach where teach_fio = @Name)
		end;
		else 
		print N'Этот учитель не может быть классным руководителем.'
	end
go

--Добавление тестовых данных в базу.

insert into Disc values 
(N'Eng'),(N'Rus'),(N'Math');

insert into Groups values
(N'A'),(N'B'),(N'C');

exec add_teacher N'Иванов И.И.';
exec add_teacher N'teach2';
exec add_teacher N'teach3';
exec add_teacher N'teach4';


insert into Teach_Disc values
(1,2),(2,1),(2,3),(3,3),(4,1)


exec add_student N'stud1',1;
exec add_student N'stud2',2;
exec add_student N'stud3',3;
exec add_student N'stud4',1;
exec add_student N'stud5',2;
exec add_student N'stud6',3;
exec add_student N'stud7',1;
exec add_student N'stud8',2;
exec add_student N'stud9',3;
exec add_student N'stud10',1;

insert into Schedule values
(1,1,2,1,1),
(1,2,1,2,2),
(1,3,2,3,3),
(1,4,3,3,1),
(1,5,1,2,3),
(1,6,1,2,1),
(1,1,1,2,2),
(2,6,3,3,1),
(2,5,3,3,1);
go

exec upd_teacher N'Иванов И.И.',1