0dQdrQ7U

1drop database if exists school;
2
3create database school;
4use school;
5
6create table Students(
7	id integer auto_increment primary key,
8	num integer not null,
9    name varchar(128) not null,
10    class varchar(3) not null,
11    birthday datetime,
12    exam_result numeric(4,3)
13);
14
15create table StudentGrades(
16	grade numeric(4, 3) not null,
17    number integer not null,
18    class varchar(3) not null,
19    subject varchar(20) not null,
20    date datetime not null,
21    primary key(number, class, subject, date)
22);
23
24select * from Students;
25insert into Students(num, name, class, birthday, exam_result) values (2, 'Gosho', '8b', '2017-09-15 12:31', 6);
26insert into Students(num, name, class, birthday, exam_result) values (1, 'Aleksandur', '8b', '2017-09-15 12:30', 5);
27insert into Students(num, name, class, birthday, exam_result) values (2, 'Antonio', '8v', '2017-06-29 12:30', 3);
28insert into Students(num, name, class, birthday, exam_result) values (3, 'Kurcho', '8b', '2017-09-15 12:32', 2);
29insert into Students(num, name, class, birthday, exam_result) values (2, 'kokobe', '8g', '2017-09-19 12:32', 4.51);
30insert into Students(num, name, class, birthday, exam_result) values (2, 'Alex Laskin', '8a', '2017-08-07 12:32', 3);
31insert into Students(num, name, class, birthday, exam_result) values (3, 'Alex Damaskin', '8a', '2017-08-07 12:22', 5);
32
33
34update Students set class = '9a' where exam_result >= 4 AND class = '8a' ;
35
36
37insert into StudentGrades(number, class, subject, date, grade) select num, class, 'Matmatika', '2018-09-15 23:59', exam_result from Students where exam_result >= 4.50;
38-- insert into StudentGrades(number, class, subject, date, grade) select num, class, 'Biologia', '2018-09-15 23:59', exam_result from Students where exam_result >= 4.50;
39
40select num,name,grade,subject,date from Students s left join  StudentGrades g on s.num = g.number and s.class = g.class;