SgWviagr

1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school CHARSET 'utf8';
3USE school;
4
5CREATE TABLE Students(
6	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
7	Name VARCHAR(150) NOT NULL,
8	Num INTEGER NOT NULL,
9	ClassNum INTEGER NOT NULL,
10	ClassLetter CHAR(1) NOT NULL,
11	Birthday DATE,
12	EGN CHAR(10),
13	EntranceExamResult NUMERIC(3,2)
14);
15
16CREATE TABLE Subjects(
17	Id INTEGER NOT NULL AUTO_INCREMENT,
18	Name VARCHAR(100),
19	
20	PRIMARY KEY(Id)
21);
22
23CREATE TABLE StudentMarks(
24	StudentId INTEGER NOT NULL,
25	SubjectId INTEGER NOT NULL,
26	ExamDate DATETIME NOT NULL,
27	Mark NUMERIC(3,2) NOT NULL,
28	
29	PRIMARY KEY( StudentId, SubjectId, ExamDate ),
30	FOREIGN KEY (StudentId) REFERENCES Students(Id),
31	FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
32);
33
34CREATE TABLE MarkWords(
35	RangeStart NUMERIC(3,2) NOT NULL,
36	RangeEnd NUMERIC(3,2) NOT NULL,
37	MarkAsWord VARCHAR(15),
38	
39	PRIMARY KEY(RangeStart, RangeEnd)
40);
41
42CREATE TABLE Teachers (
43	Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
44    Name VARCHAR(30)
45);
46
47INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
48INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
49INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
50INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
51INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
52
53INSERT INTO Teachers(Name) VALUES( 'Сокол Александров');
54INSERT INTO Teachers(Name) VALUES( 'Ивайло Георгиев');
55INSERT INTO Teachers(Name) VALUES( 'Симеон Георгиев Набългарски');
56INSERT INTO Teachers(Name) VALUES( 'Иван Иванов');
57INSERT INTO Teachers(Name) VALUES( 'Петър Георгиев');
58
59INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
60INSERT INTO Subjects(Name) VALUES( 'Литература' );
61INSERT INTO Subjects(Name) VALUES( 'Математика' );
62INSERT INTO Subjects(Name) VALUES( 'СУБД' );
63
64INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
65INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
66INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
67INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
68INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
69INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
70INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
71
72INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
73INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
74INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
75INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
76INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
77
78SELECT st.Name, CONCAT(st.ClassNum, st.ClassLetter) as position
79FROM Students st
80WHERE st.ClassNum = 10 or st.ClassNum = 12
81UNION
82SELECT teacher.Name, 'Учител'
83FROM Teachers teacher
84ORDER BY 1;