Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 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)
- );
- 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 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 st.Name, sb.Name, m.ExamDate,
- CASE
- WHEN m.Mark < 3 THEN Concat('Слаб (',m.Mark,')')
- WHEN m.Mark >= 3 AND m.Mark < 3.50 THEN Concat('Среден (',m.Mark,')')
- WHEN m.Mark >= 3.50 AND m.Mark < 4.50 THEN Concat('Добър (',m.Mark,')')
- WHEN m.Mark >= 4.50 AND m.Mark < 5.50 THEN Concat('Много добър (',m.Mark,')')
- WHEN m.Mark >= 5.50 AND m.Mark <= 6 THEN Concat('Отличен (',m.Mark,')')
- END
- FROM StudentMarks m
- INNER JOIN Subjects sb ON sb.id = m.SubjectId
- inner JOIN Students st ON st.id = m.StudentId
- ORDER BY m.Mark DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement