Форум пользователей MySQL

DROP TABLE IF EXISTS subj,student,mark;

/*SUBJect_Table*/
CREATE TABLE subj(
id smallint auto_increment,
PRIMARY KEY(id),
sname varchar(15) not null,
teacher varchar(15) not null);

INSERT INTO subj(sname,teacher)
VALUES('Mathematics','Zvezdeckii T.I.'),('Programming','Soproniuk T.M.'),('Physics','Fesiv I.V');

/*STUDENT_Table*/
CREATE TABLE student(
id smallint auto_increment,
PRIMARY KEY(id),
fname varchar(15) not null,
lname varchar(15) not null);

INSERT INTO student(fname,lname)
VALUES('Verstyuk','Sergey'),('Habuza','Vitaliy'),('Grabchak','Michael'),
('Zaleski','Julius'),('Rakov','Sergey'),('Petrenko','Vitaliy'),('Rapatui','Alexander'),
('Romaniuk','Yuri'),('Hliborob','Ivan'),('Schesnyak','Andrey');

/*MARK_Table*/
CREATE TABLE mark(
id smallint auto_increment,
PRIMARY KEY(id),
student_id smallint,
subj_id smallint,
val smallint,
dateof date);

INSERT INTO mark(student_id,subj_id,val,dateof)
VALUES(1,1,9,'2010.09.11'),(2,1,10,'2010.09.13'),(3,1,7,'2010.09.11'),
(4,1,2,'2010.09.15'),(5,1,6,'2010.09.12'),(6,1,3,'2010.09.12'),
(7,1,7,'2010.09.11'),(8,1,2,'2010.09.11'),(9,1,2,'2010.09.15'),
(10,1,5,'2010.09.13'),
(1,2,7,'2010.09.26'),(2,2,2,'2010.09.27'),(3,2,9,'2010.09.26'),
(4,2,8,'2010.09.29'),(5,2,2,'2010.09.29'),(6,2,5,'2010.09.26'),
(7,2,7,'2010.09.27'),(8,2,8,'2010.09.27'),(9,2,8,'2010.09.28'),
(10,2,6,'2010.09.28'),
(1,3,2,'2010.09.14'),(2,3,2,'2010.09.14'),(3,3,2,'2010.09.14'),
(4,3,7,'2010.09.14'),(5,3,6,'2010.09.16'),(6,3,7,'2010.09.16'),
(7,3,8,'2010.09.14'),(8,3,7,'2010.09.16'),(9,3,10,'2010.09.16'),
(10,3,2,'2010.09.16');

INSERT INTO mark(student_id,subj_id,val,dateof)
VALUES(1,1,6,'2010.10.16'),(2,1,7,'2010.10.16'),(3,1,5,'2010.10.11'),
(5,1,7,'2010.10.11'),(6,1,7,'2010.10.12'),
(7,1,2,'2010.10.11'),(8,1,5,'2010.10.11'),(9,1,2,'2010.10.16'),
(1,2,9,'2010.10.25'),(3,2,10,'2010.10.24'),
(5,2,5,'2010.10.25'),
(7,2,8,'2010.10.27'),(8,2,7,'2010.10.27'),(9,2,9,'2010.10.25'),
(10,2,8,'2010.10.24'),
(2,3,5,'2010.10.12'),(3,3,7,'2010.10.13'),
(4,3,8,'2010.10.12'),(6,3,6,'2010.10.16'),
(7,3,7,'2010.10.14'),(8,3,9,'2010.10.14'),
(10,3,6,'2010.10.16');

INSERT INTO mark(student_id,subj_id,val,dateof)
VALUES(1,1,6,'2011.01.11'),(2,1,8,'2011.01.13'),(3,1,7,'2011.01.11'),
(5,1,8,'2010.09.12'),(6,1,5,'2011.01.12'),
(8,1,9,'2010.09.11'),(9,1,6,'2011.01.15'),
(1,2,7,'2011.01.26'),(3,2,8,'2011.01.26'),
(4,2,7,'2011.01.29'),(5,2,5,'2011.01.29'),(6,2,5,'2011.01.26'),
(7,2,8,'2011.01.27'),(8,2,7,'2011.01.27'),
(10,2,7,'2011.01.28'),
(3,3,7,'2011.01.14'),
(4,3,7,'2011.01.14'),(6,3,8,'2011.01.16'),
(7,3,9,'2011.01.14'),(8,3,8,'2011.01.16'),(9,3,9,'2011.01.16');

SELECT CONCAT(s.fname,' ',s.lname) AS Student, subj.sname AS Subjct, m.val, m.dateof
FROM student AS s, mark AS m, subj
WHERE m.student_id=s.id AND m.subj_id=subj.id /* AND m.dateof<'2010.11.01' */
ORDER BY Subjct;
 

SELECT CONCAT(s.fname,' ',s.lname) AS Student, subj.sname AS Subjct, m.val, m.dateof
FROM student AS s, mark AS m, subj
WHERE m.student_id=s.id AND m.subj_id=1 /* AND m.dateof<'2010.11.01' */
ORDER BY Subjct;