GVjH0v6s

1drop database if exists session_tests;
2Create DATABASE session_tests;
3use session_tests;
4
5
6 CREATE TABLE subjects(
7id int  auto_increment Primary key,
8name varchar(255) not null,
9dateOfAdding DATE
10);
11
12
13CREATE TABLE test(
14id int  AUTO_INCREMENT PRIMARY KEY,
15subjects_id int not null,
16dateOfTest date,
17timeOfTest enum('zimna','lqtna'),
18sredenUspeh double null,
19constraint foreign key(subjects_id) references subjects(id)
20
21
22);
23
24
25
26Create Table teachers(
27id int  auto_increment Primary key,
28name varchar(255) null,
29egn varchar(10)  null unique
30
31
32
33
34);
35
36create table teachers_subjects(
37teachers_id int,
38subjects_id int not null,
39constraint foreign key(teachers_id) references teachers(id),
40constraint foreign key(subjects_id) references subjects(id)
41
42
43);
44
45Create table students(
46id int  auto_increment Primary key,
47name varchar(255) not null,
48fakNomer varchar(9) not null unique,
49grupa varchar(255) not null,
50kurs varchar(255) not null
51
52
53
54);
55
56
57
58create table student_test(
59students_id int not null,
60test_id int not null,
61constraint foreign key(students_id) references students(id),
62constraint foreign key(test_id) references test(id)
63
64
65);
66
67
68
69
70INSERT INTO subjects (name,dateOfAdding) 
71VALUES ('fizika','1996-02-14'),
72('himiq','1968-02-14'),
73('mat','1999-02-14'),
74('history','1987-02-14'),
75('biologiq','2005-02-14'),
76('informatika','2005-02-14');
77
78
79
80insert into students values(NULL,'ivan petrov georgiev','501216046','11','2'),
81(NULL,'stoqn nedov petrov','121216036','10','1'),
82(NULL,'petar grozdev sevliev','501216036','10','1'),
83(NULL,'nedelina desova nikolova','478676045','11','2'),
84(NULL,'georgi stefanov takarov','438676045','11','2'),
85(NULL,'nani','838646045','11','2'),
86(NULL,'pavlin petrov mihailov', '418676045','11','2'),
87 (NULL,'valq ivanova pencheva', '443867604','11','2'),
88 (NULL,'petq stoqnova stoqnova', '458676045','11','2'),
89 (NULL,'aneliq petrova kocheva', '468676045','11','2'),
90 (NULL,'valentina valentinova valentinova', '428676045','11','2'),
91 (NULL,'yoana gacheva kaloqnova', '438867604','11','2'),
92 (NULL,'ivanna velkova velkova', '498676045','11','2');
93
94
95insert into test values(null,'3','1999-02-14','zimna','4'),
96(null,'5','2000-03-15','lqtna','3'),
97(null,'2','2000-03-15','lqtna','3'),
98(null,'4','2002-09-14','zimna','2.20'),
99(null,'5','2003-05-22','lqtna','4.60'),
100(null,'1','2003-05-22','lqtna','4.60'),
101(null,'6','2004-08-18','zimna','3');
102
103
104INSERT INTO teachers (name,egn) 
105VALUES ('georgi ivanov ivanov', '7509241245'),
106 ('vegeta nikolov','7409441245'),
107 ('ivan angelov ivanov', '7509441245'),
108 ('atanas mitkov nikolov', '7509341245'),
109 ('petur stoqnov ivanov', '7509641245');
110 
111
112 
113
114 
115 insert into student_test values(1,3),
116 (2,3),
117 (2,6),
118 (2,2),
119 (2,4),
120 (2,5),
121 (4,3),
122 (2,1),
123 (4,4),
124 (8,3),
125 (9,2),
126 (10,3),
127 (11,6),
128 (11,1),
129 (7,2),
130 (1,4);
131 
132 
133
134
135insert into teachers_subjects values(null,4),
136(1,3),
137(2,1),
138(5,3);
139
140#ZAD 2
141select * from students where id>=1 and id<=2;
142#zad 5 KURSOVA BA4KA
143select test.*,count(students.id) from test  join students 
144on test.id in (select test_id from student_test where  student_test.students_id=students.id) group by test.id;
145# zad 3 kursova BA4KA
146select avg(test.sredenUspeh),test.timeOfTest from test group by timeOfTest;
147
148#ZAD 4  #n1
149#select st.name,sb.name from students as st join subjects as sb on st.id in(select
150#students_id from student_subjects where student_subjects.subjects_id=sb.id) left join
151#students on students.id in(select students_id as st_id from student_subjects where student_subjects.subjects_id=sb.id);
152
153#zad 4 #n2
154select test.*,subjects.name,students.name from subjects join test on subjects.id=test.subjects_id right join students on subjects.id in(select test_id from student_test
155where student_test.students_id=students.id);