DROP DATABASE IF EXISTS school;
CREATE DATABASE school CHARSET 'utf8';
USE school;

CREATE TABLE Students(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	Name VARCHAR(150) NOT NULL,
	Num INTEGER NOT NULL,
	ClassNum INTEGER NOT NULL,
	ClassLetter CHAR(1) NOT NULL,
	Birthday DATE,
	EGN CHAR(10),
	EntranceExamResult NUMERIC(3,2)
);

Create Table H_Students (
	Id INTEGER NOT NULL,
	Name VARCHAR(150) NOT NULL,
	Num INTEGER NOT NULL,
	ClassNum INTEGER NOT NULL,
	ClassLetter CHAR(1) NOT NULL,
	Birthday DATE,
	EGN CHAR(10),
	EntranceExamResult NUMERIC(3,2),

	UId Integer Not null auto_increment primary key,
    EventType char(1) Not null,
    EventData dateTime not null
);

CREATE TABLE Subjects(
	Id INTEGER NOT NULL AUTO_INCREMENT,
	Name VARCHAR(100),

	PRIMARY KEY(Id)
);

CREATE TABLE StudentMarks(
	StudentId INTEGER NOT NULL,
	SubjectId INTEGER NOT NULL,
	ExamDate DATETIME NOT NULL,
	Mark NUMERIC(3,2) NOT NULL,

	PRIMARY KEY( StudentId, SubjectId, ExamDate ),
	FOREIGN KEY (StudentId) REFERENCES Students(Id),
	FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
);

CREATE TABLE MarkWords(
	RangeStart NUMERIC(3,2) NOT NULL,
	RangeEnd NUMERIC(3,2) NOT NULL,
	MarkAsWord VARCHAR(15),

	PRIMARY KEY(RangeStart, RangeEnd)
);

CREATE TABLE Teachers(
	Id INTEGER NOT NULL auto_increment,
    Name1 VARCHAR(100),
    Name2 VARCHAR(100),
    Name3 VARCHAR(100),
	fullName VARCHAR(100) ,



    primary key(Id)
);




INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );

INSERT INTO Teachers(Id,Name1, Name2, Name3) VALUES(1, 'Пенка', 'Пешова' , 'Станимирова');
INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Джани', 'Цачева', 'Панимирова');
INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Цеца', 'Александрова', 'Гиоцова');
INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Деца', 'Христова', 'Бегайте');
INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Цацка', 'Николаева', 'Първоумрелова');

INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
INSERT INTO Subjects(Name) VALUES( 'Литература' );
INSERT INTO Subjects(Name) VALUES( 'Математика' );
INSERT INTO Subjects(Name) VALUES( 'СУБД' );

INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );

INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );

-- SELECT Name, 'Учител' as position FROM Teachers
-- UNION
-- SELECT Name, concat(ClassNum, ClassLetter) from Students
-- WHERE ClassNum = 10 OR ClassNum = 12

--
-- (SELECT st.Name, AVG(sm.Mark)
-- From Students st
-- INNER JOIN StudentMarks sm ON sm.StudentId = st.Id
-- GROUP BY st.Name
-- ORDER BY AVG(sm.Mark) DESC limit 1)
-- union
-- (SELECT st.Name, AVG(sm.Mark)
-- From Students st
-- INNER JOIN StudentMarks sm ON sm.StudentId = st.Id
-- GROUP BY st.Name
-- ORDER BY AVG(sm.Mark) asc limit 1)

-- CREATE VIEW ClassMarks(
-- 	Class,
-- 	SubjectName,
-- 	AverageMark
-- )
-- AS
-- SELECT st.ClassNum, sb.Name, AVG(m.Mark)
-- FROM StudentMarks m
-- LEFT JOIN Students st
-- ON m.StudentId = st.Id
-- LEFT JOIN Subjects sb
-- ON sb.Id = m.SubjectId
-- GROUP BY st.ClassNum, sb.Id, sb.Name;
--
-- SELECT * FROM ClassMarks WHERE Class = 10


--
--  CREATE VIEW AverageMarks(
--  	StudentName,
--      ClassNum,
--      SubjectName,
--     AverageMark
--
-- )
-- AS
-- SELECT st.Name, st.ClassNum, sb.Name, AVG(sm.Mark)
-- FROM StudentMarks sm
-- LEFT JOIN Students st ON st.Id = sm.StudentId
-- LEFT JOIN Subjects sb ON sb.Id = sm.SubjectId
-- GROUP BY st.Id, sb.Id, sb.Name, st.Name;
--
-- SELECT * FROM AverageMarks

-- CREATE VIEW HighestAverageMarks(
--     SubjectName,
--     HighestAverage
-- )
-- AS
-- SELECT sb.Name, Max(AVG(sm.Mark))
-- FROM StudentMarks sm
-- LEFT JOIN Subjects sb ON sb.Id = sm.SubjectId
-- GROUP BY sb.Name;
--
-- Select * from HighestAverageMarks;


--
-- (SELECT sb.Name, AVG(sm.Mark)
-- From StudentMarks sm
-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Английски език'
-- GROUP BY sb.Name
-- ORDER BY AVG(sm.Mark) asc limit 1)
-- union
-- (SELECT sb.Name, AVG(sm.Mark)
-- From StudentMarks sm
-- inner join Students st on st.id = sm.StudentId
-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Литература'
-- GROUP BY sb.Name
-- ORDER BY AVG(sm.Mark) asc limit 1)
-- union
-- (SELECT sb.Name, AVG(sm.Mark)
-- From StudentMarks sm
-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Математика'
-- GROUP BY sb.Name
-- ORDER BY AVG(sm.Mark) asc limit 1)
-- union
-- (SELECT sb.Name, AVG(sm.Mark)
-- From StudentMarks sm
-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'СУБД'
-- GROUP BY sb.Name
-- ORDER BY AVG(sm.Mark) asc limit 1)
--

 create trigger InsertTrigger
 before insert on Students for each row
 insert into H_Students
 set Id = new.Id,
	Name = new.Name,
	Num = new.Num,
	ClassNum = new.ClassNum,
	ClassLetter = new.ClassLetter,
	Birthday = new.Birthday,
	EGN = new.EGN,
	EntranceExamResult = new.EntranceExamResult,
    EventType = 'I',
    EventData = NOW();

 create trigger UpdateTrigger
 before update on Students for each row
 insert into H_Students
 set Id = new.Id,
	Name = new.Name,
	Num = new.Num,
	ClassNum = new.ClassNum,
	ClassLetter = new.ClassLetter,
	Birthday = new.Birthday,
	EGN = new.EGN,
	EntranceExamResult = new.EntranceExamResult,
    EventType = 'U',
    EventData = NOW();

create trigger DeleteTrigger
 before delete on Students for each row
 insert into H_Students
 set Id = Old.Id,
	Name = old.Name,
	Num = old.Num,
	ClassNum = old.ClassNum,
	ClassLetter = old.ClassLetter,
	Birthday = old.Birthday,
	EGN = old.EGN,
	EntranceExamResult = old.EntranceExamResult,
    EventType = 'D',
    EventData = NOW();

INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 11, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );

update Students set Name = 'Зюмбюлка Петрова' where id = 11;

delete From Students where  id = 11;



INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );


Select *
From H_Students hs
where hs.id = 11
order by EventData desc limit 1;

Select hs.Name, hs.EventType, Min(hs.EventData)
From H_Students hs
where hs.id != 0
group by Name, EventType