bNAPCSBj

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
42INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
43INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
44INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
45INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
46INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
47
48INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
49INSERT INTO Subjects(Name) VALUES( 'Литература' );
50INSERT INTO Subjects(Name) VALUES( 'Математика' );
51INSERT INTO Subjects(Name) VALUES( 'СУБД' );
52
53INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
54INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
55INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
56INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
57INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
58INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
59INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50);
60
61INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
62INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
63INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
64INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
65INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
66
67-- -----------------------------------------------------------------
68
69-- SELECT CAST(Mark AS CHAR(100))
70-- FROM StudentMarks
71-- ORDER BY Mark DESC;
72-- 
73-- SELECT CAST(MarkAsWord AS SIGNED)
74-- FROM MarkWords
75-- ORDER BY MarkAsWord;
76-- 
77-- SELECT CAST(EGN AS CHAR(1))
78-- FROM Students;
79-- 
80-- -- -----------------------------------------------------------------
81-- 
82-- SELECT Mark,
83-- CASE
84-- 	WHEN Mark >= 2.00 AND Mark < 2.50  THEN 'Poor'
85--     WHEN Mark >= 2.50 AND Mark < 3.50  THEN 'Average'
86--     WHEN Mark >= 3.50 AND Mark < 4.50  THEN 'Good'
87--     WHEN Mark >= 4.50 AND Mark < 5.50  THEN 'Very good'
88--     WHEN Mark >= 5.50 AND Mark <= 6.00 THEN 'Excellent'
89-- END
90-- FROM StudentMarks;
91
92-- ---------------------------------------------------------------------------------
93SELECT CAST(Name as CHAR(100))
94FROM Students
95ORDER BY Name;
96
97SELECT CAST(MarkAsWord as SIGNED)
98FROM MarkWords
99ORDER BY MarkAsWord;
100
101SELECT CAST(Mark as signed)
102FROM StudentMarks
103ORDER BY Mark;
104
105SELECT Mark,
106CASE
107	when Mark >= 2.00 AND Mark < 2.50 then 'Looser'
108    when Mark >= 2.50 AND Mark < 3.50 then 'Smaller looser'
109    when Mark >= 3.50 AND Mark < 4.50 then 'Average looser'
110    when Mark >= 4.50 AND Mark < 5.50 then 'Second is the first to loose'
111    when Mark >= 5.50 AND Mark <= 6.00 then 'The most successful bastard'
112END
113FROM StudentMarks;