USE master                                                                                                                                                                                                                                                       USE master
GO

IF DB_ID('LAB8') IS NOT NULL
DROP DATABASE LAB8
GO

CREATE DATABASE IU9_LAB8
ON (NAME = Lab8, FILENAME = 'C:\DB\Lab8\LAB8.mdf',
  SIZE = 10, MAXSIZE = UNLIMITED, FILEGROWTH = 5%)
LOG ON
 (NAME = Lab8_log, FILENAME = 'C:\DB\Lab8\LAB8.ldf',
  SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );
GO



USE IU9_LAB8
GO
CREATE TABLE Студент
(
	[Номер_зачетки] varchar(6) NOT NULL PRIMARY KEY,
	Фамилия varchar(20) NOT NULL,
	Имя varchar(20) NOT NULL,
	Отчество varchar(20) NOT NULL,
	Пол char(7) NULL,
	Статус_в_группе varchar(20) NULL DEFAULT 'Студент',
	Адрес_проживания varchar(40) NULL DEFAULT 'Не указан',
	Номер_телефона char(11) NULL DEFAULT 'Не указан',
	Email varchar(30) NULL DEFAULT 'Не указан',
	Возраст int NOT NULL
)
GO


INSERT INTO Студент (Номер_зачетки,Фамилия,Имя,Отчество,Пол,Возраст) VALUES ('14У468','Демидов','Андрей','Евгеньевич','мужской',20);
INSERT INTO Студент (Номер_зачетки,Фамилия,Имя,Отчество,Пол,Email,Возраст) VALUES ('14У533','Кудрявцев','Иван','Анатольевич','мужской','desprta667@yandex.ru',19);
INSERT INTO Студент (Номер_зачетки,Фамилия,Имя,Отчество,Пол,Статус_в_группе,Номер_телефона,Email,Возраст)VALUES ('15У345','Иванова','Анна','Семеновна','женский','Староста','89853656292','anna_iv@yandex.ru',21);
INSERT INTO Студент (Номер_зачетки,Фамилия,Имя,Отчество,Пол,Адрес_проживания,Номер_телефона,Email,Возраст)VALUES ('15А854','Петров','Игорь','Иванович','мужской','улица Байкальская,дом 29,кв.30','89852461315','irjuk@mail.ru',20);
INSERT INTO Студент (Номер_зачетки,Фамилия,Имя,Отчество,Пол,Статус_в_группе,Адрес_проживания,Номер_телефона,Email,Возраст) VALUES ('15Ю543','Кузнецова','Алеся','Евгеньевна','женский','Зам.старосты','Улица Ленина,дом 15,кв.66','89854795456','ales_kuz@yandex.ru',21);

SELECT * FROM Студент

USE IU9_LAB8
GO
CREATE TABLE Дисциплина
(
	Название_дисциплины varchar(40) NOT NULL PRIMARY KEY,
	Семестр int NOT NULL,
	Количество_модулей int NULL DEFAULT 3
)
GO

INSERT INTO Дисциплина (Название_дисциплины,Семестр) VALUES ('Математический анализ',1)
INSERT INTO Дисциплина (Название_дисциплины,Семестр) VALUES ('Дискретная математика',2)
INSERT INTO Дисциплина (Название_дисциплины,Семестр) VALUES ('Алгебра',3)
INSERT INTO Дисциплина (Название_дисциплины,Семестр) VALUES ('Компьютерная графика',4)
INSERT INTO Дисциплина (Название_дисциплины,Семестр,Количество_модулей) VALUES ('Базы данных',5,2)
INSERT INTO Дисциплина (Название_дисциплины,Семестр,Количество_модулей) VALUES ('Архитектура ЭВМ',2,2)

SELECT * FROM Дисциплина

USE IU9_LAB8
GO
CREATE TABLE Экзамен
(
	Дата date NOT NULL,
	Номер_зачетки_студента varchar(6) NOT NULL REFERENCES Студент(Номер_зачетки) ON DELETE CASCADE,
	Название_дисциплины varchar(40) NOT NULL REFERENCES Дисциплина(Название_дисциплины) ON DELETE CASCADE,
	Оценка int NOT NULL,
	CONSTRAINT FK_EX PRIMARY KEY (Дата,Номер_зачетки_студента,Название_дисциплины)
)
GO

SELECT * FROM Дисциплина
SELECT * FROM Студент
SELECT * FROM Экзамен

-- smalldatetime = '1955-12-13 12:43:10'

INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20150114','14У468','Математический анализ',5);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20150114','14У533','Математический анализ',4);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20150118','15А854','Математический анализ',3);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20150620','14У468','Дискретная математика',4);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20150620','14У533','Дискретная математика',3);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20150913','15Ю543','Дискретная математика',5);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20160115','14У468','Алгебра',5);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20160115','15У345','Алгебра',5);
INSERT INTO Экзамен (Дата,Номер_зачетки_студента,Название_дисциплины,Оценка) VALUES ('20160115','15Ю543','Алгебра',5);


SELECT * FROM Экзамен

USE IU9_LAB8;
GO
CREATE TABLE Преподаватель
(
	Фамилия varchar(20) NOT NULL,
	Имя varchar(20) NOT NULL,
	Отчество varchar(20) NOT NULL,
	Стаж int NULL,
	Адрес varchar(40) NULL DEFAULT 'Не указан',
	Email varchar(30) NULL DEFAULT 'Не указан',
	CONSTRAINT PK_PREP PRIMARY KEY(Фамилия,Имя,Отчество)
)
GO

INSERT INTO Преподаватель(Фамилия,Имя,Отчество,Стаж) VALUES ('Подобряев','Александр','Иванович',15)
INSERT INTO Преподаватель(Фамилия,Имя,Отчество,Стаж,Email) VALUES ('Куликова','Ольга','Викторовна',7,'kulikova_olg@mail.ru')
INSERT INTO Преподаватель(Фамилия,Имя,Отчество,Стаж) VALUES ('Скороходов','Иван','Матвеевич',20)

USE IU9_LAB8
GO
CREATE TABLE Ведение_предмета
(
	Статус varchar(30) NOT NULL,
	Фамилия varchar(20) NOT NULL,
	Имя varchar(20) NOT NULL,
	Отчество varchar(20) NOT NULL,
	Название_дисциплины varchar(40) NOT NULL REFERENCES Дисциплина(Название_дисциплины),
	Количество_групп int NULL DEFAULT 1,
	CONSTRAINT PK_SUBJ PRIMARY KEY (Статус,Фамилия,Имя,Отчество),
	CONSTRAINT FK_SUBJ FOREIGN KEY (Фамилия,Имя,Отчество) REFERENCES Преподаватель(Фамилия,Имя,Отчество)
)
GO

INSERT INTO Ведение_предмета (Статус,Фамилия,Имя,Отчество,Название_дисциплины,Количество_групп)
	VALUES ('Семинарист','Подобряев','Александр','Иванович','Математический анализ',2);
INSERT INTO Ведение_предмета (Статус,Фамилия,Имя,Отчество,Название_дисциплины,Количество_групп)
	VALUES ('Семинарист','Куликова','Ольга','Викторовна','Дискретная математика',3);
INSERT INTO Ведение_предмета (Статус,Фамилия,Имя,Отчество,Название_дисциплины)
	VALUES ('Лектор','Скороходов','Иван','Матвеевич','Алгебра');

SELECT * FROM Ведение_предмета

USE IU9_LAB8;
GO
CREATE TABLE Преподавательский_состав
(
	Дата date NOT NULL,
	Номер_зачетки varchar(6) NOT NULL,
	Название_дисциплины varchar(40) NOT NULL,
	Фамилия varchar(20) NOT NULL,
	Имя varchar(20) NOT NULL,
	Отчество varchar(20) NOT NULL,
	CONSTRAINT FK_PR FOREIGN KEY (Фамилия,Имя,Отчество) REFERENCES Преподаватель(Фамилия,Имя,Отчество),
	CONSTRAINT FK_PR_2 FOREIGN KEY (Дата,Номер_зачетки,Название_дисциплины) REFERENCES Экзамен(Дата,Номер_зачетки_студента,Название_дисциплины)
)
GO

INSERT INTO Преподавательский_состав (Дата,Номер_зачетки,Название_дисциплины,Фамилия,Имя,Отчество)
	VALUES ('20150114','14У468','Математический анализ','Подобряев','Александр','Иванович');
INSERT INTO Преподавательский_состав (Дата,Номер_зачетки,Название_дисциплины,Фамилия,Имя,Отчество)
	VALUES ('20160115','15У345','Алгебра','Скороходов','Иван','Матвеевич');

SELECT * FROM Преподавательский_состав

--------------------------------------------------------------------------------------------------------------------
-- Создание хранимой процедуры

USE IU9_LAB8
GO
CREATE PROCEDURE dbo.createCursor
@the_cursor CURSOR VARYING OUTPUT
AS
	SET @the_cursor = CURSOR
	FORWARD_ONLY STATIC FOR
	SELECT Номер_зачетки,Имя,Фамилия,Отчество FROM Студент
OPEN @the_cursor
GO

USE IU9_LAB8;
GO
DECLARE @MyCursor CURSOR
EXEC dbo.createCursor @the_cursor = @MyCursor OUTPUT
FETCH NEXT FROM @MyCursor;
WHILE @@FETCH_STATUS = 0
	BEGIN
		FETCH NEXT FROM @MyCursor
		--PRINT 'yes';
	END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

USE IU9_LAB8
GO
DROP PROCEDURE dbo.createCursor
GO


---------------------------------------------------------------------------------------------------------------------
-- Модификация хранимой процедуры


USE IU9_LAB8
GO
CREATE FUNCTION dbo.CreateInitials (@fnameInit CHAR,@snameInit CHAR,@tnameInit CHAR)
RETURNS VARCHAR(6) AS
BEGIN
	DECLARE @Initials VARCHAR(6)
	SET @Initials = CONCAT(@fnameInit,'.',@snameInit,'.',@tnameInit,'.')
	RETURN @Initials
END
GO
/*Аналогично заполняется только первой буквой*/


USE IU9_LAB8
GO
ALTER PROCEDURE dbo.CreateCursor
@the_cursor CURSOR VARYING OUTPUT
AS
	SET @the_cursor = CURSOR FOR
	SELECT Номер_зачетки,Фамилия,Имя,Отчество,Возраст,Инициалы = dbo.CreateInitials(Имя,Фамилия,Отчество) FROM Студент
	OPEN @the_cursor
GO

DECLARE @cursor CURSOR;
EXEC dbo.CreateCursor @the_cursor = @cursor OUTPUT;
FETCH NEXT FROM @cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH NEXT FROM @cursor;
END
CLOSE @cursor;
DEALLOCATE @cursor;
GO

USE IU9_LAB8
GO
DROP FUNCTION dbo.Course
GO

USE IU9_LAB8
GO
DROP FUNCTION dbo.CreateInitials
GO

USE IU9_LAB8
GO
DROP FUNCTION dbo.TransferInDays
GO

-------------------------------------------------------------------------------------------------------------------
/*Вторая процедура*/

USE IU9_LAB8
GO
CREATE FUNCTION dbo.adult_or_not (@age INT)
RETURNS INT
AS
	BEGIN
		DECLARE @result INT
		IF (@age < 18)
			SET @result = 0
		ELSE
			SET @result = 1
		RETURN @result
	END
GO

USE IU9_LAB8
GO
CREATE PROCEDURE dbo.print_proc AS
DECLARE @cursor CURSOR
DECLARE @ID VARCHAR(6)
DECLARE @fname VARCHAR(30)
DECLARE @sname VARCHAR(30)
DECLARE @tname VARCHAR(30)
DECLARE @age INT
DECLARE @initials VARCHAR(6)

EXEC dbo.CreateCursor @the_cursor = @cursor OUTPUT
FETCH NEXT FROM @cursor INTO @ID, @sname, @fname, @tname,@age, @initials
PRINT 'Номер_зачетки,ФИО и возраст: "' + @ID + ' ' + @sname + ' ' + @fname + ' ' + @tname + ' ' + CAST(@age as varchar(2)) + '"'
WHILE @@FETCH_STATUS = 0
BEGIN
	IF (dbo.adult_or_not(@age) = 1)
		PRINT 'Студент ' + @sname + ' ' + @fname + ' ' + @tname + ' с номером зачетной книжки ' + @ID + ' является совершеннолетним.'
	ELSE
		PRINT 'Студент ' + @sname + ' ' + @fname + ' ' + @tname + ' с номером зачетной книжки ' + @ID + ' не является совершеннолетним.'
	FETCH NEXT FROM @cursor INTO @ID,@sname,@fname,@tname,@age,@initials
END
CLOSE @cursor
DEALLOCATE @cursor
GO

EXEC dbo.print_proc
GO

USE IU9_LAB8
GO
DROP PROCEDURE dbo.print_proc
GO

-------------------------------------------------------------------------------------------------------------------------------------
/*Модификация второй процедуры*/

USE IU9_LAB8
GO
CREATE FUNCTION dbo.student_table ()
RETURNS TABLE
AS
	RETURN (SELECT Номер_зачетки,Фамилия,Имя,Отчество,Возраст FROM Студент)
GO

USE IU9_LAB8
GO
ALTER PROCEDURE dbo.print_proc
AS
DECLARE @the_cursor CURSOR
DECLARE @ID VARCHAR(6)
DECLARE @fname VARCHAR(20)
DECLARE @sname VARCHAR(20)
DECLARE @tname VARCHAR(20)
DECLARE @age INT
DECLARE @initials VARCHAR(6)
SET @the_cursor = CURSOR FOR SELECT Номер_зачетки,Фамилия,Имя,Отчество,Возраст,Инициалы = dbo.CreateInitials(Фамилия,Имя,Отчество) from dbo.student_table()
OPEN @the_cursor
FETCH NEXT FROM @the_cursor INTO @ID, @sname, @fname, @tname,@age, @initials
PRINT 'Номер_зачетки,ФИО и возраст: "' + @ID + ' ' + @sname + ' ' + @fname + ' ' + @tname + ' ' + CAST(@age as varchar(2)) + '"'
WHILE @@FETCH_STATUS = 0
BEGIN
	IF (dbo.adult_or_not(@age) = 1)
		PRINT 'Студент ' + @sname + ' ' + @fname + ' ' + @tname + ' с номером зачетной книжки ' + @ID + ' является совершеннолетним.'
	ELSE
		PRINT 'Студент ' + @sname + ' ' + @fname + ' ' + @tname + ' с номером зачетной книжки ' + @ID + ' не является совершеннолетним.'
	FETCH NEXT FROM @the_cursor INTO @ID,@sname,@fname,@tname,@age,@initials
END
CLOSE @the_cursor
DEALLOCATE @the_cursor
GO

EXEC dbo.print_proc
GO


USE IU9_LAB8
GO
DROP PROCEDURE dbo.print_proc
GO

USE IU9_LAB8
GO
DROP FUNCTION dbo.student_table
GO
*/