Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest Nov 15th, 2018 135 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  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. CREATE TABLE H_Students(
  43.     UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  44.     EventDate DATETIME NOT NULL,
  45.     EventType VARCHAR(10),
  46.     Id INTEGER NOT NULL,
  47.     Name VARCHAR(100) NOT NULL,
  48.     EGN VARCHAR(10)
  49. );
  50.  
  51. CREATE TABLE H_StudentMarks(
  52.     UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  53.     EventDate DATETIME NOT NULL,
  54.     EventType VARCHAR(10) NOT NULL,
  55.     StId INTEGER NOT NULL,
  56.     SuId INTEGER NOT NULL,
  57.     Mark NUMERIC(3,2) NOT NULL
  58. );
  59.  
  60. CREATE TABLE EventType(
  61.     Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  62.     Name VARCHAR(10)
  63. );
  64.  
  65. delimiter |
  66. CREATE TRIGGER StudentsUpdate BEFORE UPDATE ON Students
  67.   FOR EACH ROW
  68.   BEGIN
  69.     INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW() + 1,'UPDATE',NEW.Id,NEW.Name,NEW.EGN);
  70.   END
  71. |
  72. CREATE TRIGGER StudentsInsert AFTER INSERT ON Students
  73.   FOR EACH ROW
  74.   BEGIN
  75.     INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW(),'INSERT',NEW.Id,NEW.Name,NEW.EGN);
  76.   END
  77. |
  78. CREATE TRIGGER StudentsDelete BEFORE DELETE ON Students
  79.   FOR EACH ROW
  80.   BEGIN
  81.     INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW() + 2,'DELETE',Id,Name,EGN);
  82.   END
  83. |
  84. CREATE TRIGGER StudentMarksUpdate BEFORE UPDATE ON StudentMarks
  85.   FOR EACH ROW
  86.   BEGIN
  87.     INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark) VALUES (NOW()+1,'UPDATE',NEW.StudentId,NEW.SubjectId,NEW.Mark);
  88.   END
  89. |
  90. CREATE TRIGGER StudentMarksInsert AFTER INSERT ON StudentMarks
  91.   FOR EACH ROW
  92.   BEGIN
  93.     INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark) VALUES (NOW(),'INSERT',NEW.StudentId,NEW.SubjectId,NEW.Mark);
  94.   END
  95. |
  96.  
  97. CREATE TRIGGER StudentMarksDelete BEFORE DELETE ON StudentMarks
  98.   FOR EACH ROW
  99.   BEGIN
  100.     INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark) VALUES (NOW()+2,'INSERT',StudentId,SubjectId,Mark);
  101.   END
  102. |
  103. delimiter ;
  104.  
  105.  
  106. INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
  107. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
  108. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
  109. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
  110. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
  111.  
  112. UPDATE Students
  113. SET ClassLetter = 'г'
  114. WHERE Id > 100;
  115.  
  116.  
  117. INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
  118. INSERT INTO Subjects(Name) VALUES( 'Литература' );
  119. INSERT INTO Subjects(Name) VALUES( 'Математика' );
  120. INSERT INTO Subjects(Name) VALUES( 'СУБД' );
  121.  
  122. INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
  123. INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
  124. INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
  125. INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
  126. INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
  127. INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
  128. INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
  129.  
  130. INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
  131. INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
  132. INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
  133. INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
  134. INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
  135.  
  136. SELECT * FROM H_Students
  137. WHERE Id = 101;
  138.  
  139. SELECT * FROM H_Students
  140. WHERE Id = 102
  141. ORDER BY EventDate DESC
  142. LIMIT 1;
  143.  
  144. SELECT * FROM( SELECT * FROM H_Students ORDER BY EventDate DESC) as d
  145. GROUP BY Id;
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
 
Top