· 7 years ago · Oct 04, 2018, 04:40 PM
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 Positions (
43 Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
44 PositionName VARCHAR(200)
45);
46
47CREATE TABLE Teachers (
48 Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
49 FirstName VARCHAR(200) NOT NULL,
50 SecondName VARCHAR(200) NOT NULL,
51 LastName VARCHAR(200) NOT NULL,
52 PositionId INTEGER NOT NULL,
53 FOREIGN KEY(PositionId) REFERENCES Positions(Id)
54
55);
56
57
58INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 10, 'а', '1999-02-28', NULL );
59INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ИÑидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
60INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 12, 'б', '2000-05-01', NULL );
61INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
62INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ÐлекÑандър Момчев', 1, 8, 'а', '2002-06-11', NULL );
63
64
65
66INSERT INTO Subjects(Id, Name) VALUES( 11, 'ÐнглийÑки език' );
67INSERT INTO Subjects(Name) VALUES( 'Литература' );
68INSERT INTO Subjects(Name) VALUES( 'Математика' );
69INSERT INTO Subjects(Name) VALUES( 'СУБД' );
70
71INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
72INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
73INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
74INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
75INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
76INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
77INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
78
79INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
80INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
81INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
82INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
83INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
84
85INSERT INTO Positions(PositionName) VALUES('директор');
86INSERT INTO Positions(PositionName) VALUES('замеÑник-директор');
87INSERT INTO Positions(PositionName) VALUES('преподажател');
88INSERT INTO Positions(PositionName) VALUES('пенÑионер');
89
90INSERT INTO Teachers(FirstName, SecondName, LastName, PositionId) VALUES('Trendafil', 'Nikolaev', 'Gurlev', 1);
91INSERT INTO Teachers(FirstName, SecondName, LastName, PositionId) VALUES('KOki', 'Mokiev', 'Anastasov', 2);
92INSERT INTO Teachers(FirstName, SecondName, LastName, PositionId) VALUES('Mihail', 'Mechovski', 'Mihailov', 3);
93INSERT INTO Teachers(FirstName, SecondName, LastName, PositionId) VALUES('Roberto', 'Arabashiev', 'Hans', 4);
94
95SELECT Name, 'student' FROM Students
96UNION ALL
97SELECT concat(FirstName, ' ', SecondName,' ', LastName), PositionName FROM Teachers as t
98INNER JOIN Positions AS p
99ON p.Id = t.PositionId
100ORDER BY 1 ASC;