SHARE
TWEET

Untitled

a guest Nov 21st, 2019 92 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 H_Students (
  17.     Id INTEGER NOT NULL,
  18.     Name VARCHAR(150) NOT NULL,
  19.     Num INTEGER NOT NULL,
  20.     ClassNum INTEGER NOT NULL,
  21.     ClassLetter CHAR(1) NOT NULL,
  22.     Birthday DATE,
  23.     EGN CHAR(10),
  24.     EntranceExamResult NUMERIC(3,2),
  25.    
  26.     UId Integer Not null auto_increment primary key,
  27.     EventType char(1) Not null,
  28.     EventData dateTime not null
  29. );
  30.  
  31. CREATE TABLE Subjects(
  32.     Id INTEGER NOT NULL AUTO_INCREMENT,
  33.     Name VARCHAR(100),
  34.    
  35.     PRIMARY KEY(Id)
  36. );
  37.  
  38. CREATE TABLE StudentMarks(
  39.     StudentId INTEGER NOT NULL,
  40.     SubjectId INTEGER NOT NULL,
  41.     ExamDate DATETIME NOT NULL,
  42.     Mark NUMERIC(3,2) NOT NULL,
  43.    
  44.     PRIMARY KEY( StudentId, SubjectId, ExamDate ),
  45.     FOREIGN KEY (StudentId) REFERENCES Students(Id),
  46.     FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
  47. );
  48.  
  49. CREATE TABLE MarkWords(
  50.     RangeStart NUMERIC(3,2) NOT NULL,
  51.     RangeEnd NUMERIC(3,2) NOT NULL,
  52.     MarkAsWord VARCHAR(15),
  53.    
  54.     PRIMARY KEY(RangeStart, RangeEnd)
  55. );
  56.  
  57. CREATE TABLE Teachers(
  58.     Id INTEGER NOT NULL auto_increment,
  59.     Name1 VARCHAR(100),
  60.     Name2 VARCHAR(100),
  61.     Name3 VARCHAR(100),
  62.     fullName VARCHAR(100) ,
  63.    
  64.  
  65.    
  66.     primary key(Id)
  67. );
  68.  
  69.  
  70.  
  71.  
  72. INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
  73. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
  74. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
  75. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
  76. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
  77.  
  78. INSERT INTO Teachers(Id,Name1, Name2, Name3) VALUES(1, 'Пенка', 'Пешова' , 'Станимирова');
  79. INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Джани', 'Цачева', 'Панимирова');
  80. INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Цеца', 'Александрова', 'Гиоцова');
  81. INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Деца', 'Христова', 'Бегайте');
  82. INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Цацка', 'Николаева', 'Първоумрелова');
  83.  
  84. INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
  85. INSERT INTO Subjects(Name) VALUES( 'Литература' );
  86. INSERT INTO Subjects(Name) VALUES( 'Математика' );
  87. INSERT INTO Subjects(Name) VALUES( 'СУБД' );
  88.  
  89. INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
  90. INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
  91. INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
  92. INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
  93. INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
  94. INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
  95. INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
  96.  
  97. INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
  98. INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
  99. INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
  100. INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
  101. INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
  102.  
  103. -- SELECT Name, 'Учител' as position FROM Teachers
  104. -- UNION
  105. -- SELECT Name, concat(ClassNum, ClassLetter) from Students
  106. -- WHERE ClassNum = 10 OR ClassNum = 12
  107.  
  108. --
  109. -- (SELECT st.Name, AVG(sm.Mark)
  110. -- From Students st
  111. -- INNER JOIN StudentMarks sm ON sm.StudentId = st.Id
  112. -- GROUP BY st.Name
  113. -- ORDER BY AVG(sm.Mark) DESC limit 1)
  114. -- union
  115. -- (SELECT st.Name, AVG(sm.Mark)
  116. -- From Students st
  117. -- INNER JOIN StudentMarks sm ON sm.StudentId = st.Id
  118. -- GROUP BY st.Name
  119. -- ORDER BY AVG(sm.Mark) asc limit 1)
  120.  
  121. -- CREATE VIEW ClassMarks(
  122. --  Class,
  123. --  SubjectName,
  124. --  AverageMark
  125. -- )
  126. -- AS
  127. -- SELECT st.ClassNum, sb.Name, AVG(m.Mark)
  128. -- FROM StudentMarks m
  129. -- LEFT JOIN Students st
  130. -- ON m.StudentId = st.Id
  131. -- LEFT JOIN Subjects sb
  132. -- ON sb.Id = m.SubjectId
  133. -- GROUP BY st.ClassNum, sb.Id, sb.Name;
  134. --
  135. -- SELECT * FROM ClassMarks WHERE Class = 10
  136.  
  137.  
  138. --
  139. --  CREATE VIEW AverageMarks(
  140. --      StudentName,
  141. --      ClassNum,
  142. --      SubjectName,
  143. --     AverageMark
  144. --      
  145. -- )
  146. -- AS
  147. -- SELECT st.Name, st.ClassNum, sb.Name, AVG(sm.Mark)
  148. -- FROM StudentMarks sm
  149. -- LEFT JOIN Students st ON st.Id = sm.StudentId
  150. -- LEFT JOIN Subjects sb ON sb.Id = sm.SubjectId
  151. -- GROUP BY st.Id, sb.Id, sb.Name, st.Name;
  152. --  
  153. -- SELECT * FROM AverageMarks
  154.  
  155. -- CREATE VIEW HighestAverageMarks(
  156. --     SubjectName,
  157. --     HighestAverage
  158. -- )
  159. -- AS
  160. -- SELECT sb.Name, Max(AVG(sm.Mark))
  161. -- FROM StudentMarks sm
  162. -- LEFT JOIN Subjects sb ON sb.Id = sm.SubjectId
  163. -- GROUP BY sb.Name;
  164. --
  165. -- Select * from HighestAverageMarks;
  166.  
  167.  
  168. --
  169. -- (SELECT sb.Name, AVG(sm.Mark)
  170. -- From StudentMarks sm
  171. -- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Английски език'
  172. -- GROUP BY sb.Name
  173. -- ORDER BY AVG(sm.Mark) asc limit 1)
  174. -- union
  175. -- (SELECT sb.Name, AVG(sm.Mark)
  176. -- From StudentMarks sm
  177. -- inner join Students st on st.id = sm.StudentId
  178. -- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Литература'
  179. -- GROUP BY sb.Name
  180. -- ORDER BY AVG(sm.Mark) asc limit 1)
  181. -- union
  182. -- (SELECT sb.Name, AVG(sm.Mark)
  183. -- From StudentMarks sm
  184. -- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Математика'
  185. -- GROUP BY sb.Name
  186. -- ORDER BY AVG(sm.Mark) asc limit 1)
  187. -- union
  188. -- (SELECT sb.Name, AVG(sm.Mark)
  189. -- From StudentMarks sm
  190. -- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'СУБД'
  191. -- GROUP BY sb.Name
  192. -- ORDER BY AVG(sm.Mark) asc limit 1)
  193. --
  194.  
  195.  create trigger InsertTrigger
  196.  before insert on Students for each row
  197.  insert into H_Students
  198.  set Id = new.Id,
  199.     Name = new.Name,
  200.     Num = new.Num,
  201.     ClassNum = new.ClassNum,
  202.     ClassLetter = new.ClassLetter,
  203.     Birthday = new.Birthday,
  204.     EGN = new.EGN,
  205.     EntranceExamResult = new.EntranceExamResult,
  206.     EventType = 'I',
  207.     EventData = NOW();
  208.  
  209.  create trigger UpdateTrigger
  210.  before update on Students for each row
  211.  insert into H_Students
  212.  set Id = new.Id,
  213.     Name = new.Name,
  214.     Num = new.Num,
  215.     ClassNum = new.ClassNum,
  216.     ClassLetter = new.ClassLetter,
  217.     Birthday = new.Birthday,
  218.     EGN = new.EGN,
  219.     EntranceExamResult = new.EntranceExamResult,
  220.     EventType = 'U',
  221.     EventData = NOW();
  222.    
  223. create trigger DeleteTrigger
  224.  before delete on Students for each row
  225.  insert into H_Students
  226.  set Id = Old.Id,
  227.     Name = old.Name,
  228.     Num = old.Num,
  229.     ClassNum = old.ClassNum,
  230.     ClassLetter = old.ClassLetter,
  231.     Birthday = old.Birthday,
  232.     EGN = old.EGN,
  233.     EntranceExamResult = old.EntranceExamResult,
  234.     EventType = 'D',
  235.     EventData = NOW();
  236.  
  237. INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 11, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
  238.  
  239. update Students set Name = 'Зюмбюлка Петрова' where id = 11;
  240.  
  241. delete From Students where  id = 11;
  242.  
  243.  
  244.  
  245. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
  246. INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
  247.  
  248.  
  249. Select *
  250. From H_Students hs
  251. where hs.id = 11
  252. order by EventData desc limit 1;
  253.  
  254. Select hs.Name, hs.EventType, Min(hs.EventData)
  255. From H_Students hs
  256. where hs.id != 0
  257. group by Name, EventType
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