SHARE
TWEET

Untitled

a guest Sep 19th, 2019 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP DATABASE IF EXISTS school;
  2. CREATE DATABASE school CHARSET 'utf8';
  3. USE school;
  4.  
  5. CREATE 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.  
  16. CREATE TABLE Subjects(
  17.     Id INTEGER NOT NULL AUTO_INCREMENT,
  18.     Name VARCHAR(100),
  19.    
  20.     PRIMARY KEY(Id)
  21. );
  22.  
  23. CREATE 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.  
  34. CREATE 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.  
  42. INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
  43. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
  44. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
  45. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
  46. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
  47.  
  48. INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
  49. INSERT INTO Subjects(Name) VALUES( 'Литература' );
  50. INSERT INTO Subjects(Name) VALUES( 'Математика' );
  51. INSERT INTO Subjects(Name) VALUES( 'СУБД' );
  52.  
  53. INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
  54. INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
  55. INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
  56. INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
  57. INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
  58. INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
  59. INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
  60.  
  61. INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
  62. INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
  63. INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
  64. INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
  65. INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
  66.  
  67. SELECT st.Name, sb.Name, m.ExamDate,
  68.     CASE
  69.         WHEN m.Mark < 3 THEN Concat('Слаб (',m.Mark,')')
  70.         WHEN m.Mark >= 3 AND m.Mark < 3.50 THEN Concat('Среден (',m.Mark,')')
  71.         WHEN m.Mark >= 3.50 AND m.Mark < 4.50 THEN Concat('Добър (',m.Mark,')')
  72.         WHEN m.Mark >= 4.50 AND m.Mark < 5.50 THEN Concat('Много добър (',m.Mark,')')
  73.         WHEN m.Mark >= 5.50 AND m.Mark <= 6 THEN Concat('Отличен (',m.Mark,')')
  74.     END
  75. FROM StudentMarks m
  76. INNER JOIN Subjects sb ON sb.id = m.SubjectId
  77. inner JOIN Students st ON st.id = m.StudentId
  78. ORDER BY m.Mark DESC;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top