SxNcVgbc

1CREATE DATABASE IF NOT EXISTS students DEFAULT CHARACTER SET UTF8;
2USE students;
3
4CREATE TABLE  speciality (
5	idSpeciality INT AUTO_INCREMENT,
6	name VARCHAR(255),
7    faculty VARCHAR(255),
8    PRIMARY KEY (idSpeciality)
9    
10) CHARSET UTF8 ENGINE=INNODB;
11
12CREATE TABLE class (
13	idClass INT AUTO_INCREMENT,
14    idSpeciality INT,
15	name VARCHAR(255),
16    PRIMARY KEY (idClass),
17    FOREIGN KEY (idSpeciality)
18    REFERENCES speciality(idSpeciality)
19    
20) CHARSET UTF8 ENGINE=INNODB;
21
22CREATE TABLE student (
23	idStudent INT AUTO_INCREMENT,
24	idClass INT DEFAULT 0,
25	name VARCHAR(255),
26	birthdate DATE,
27	gender BOOL,
28	stip DECIMAL,
29	PRIMARY KEY (idStudent),
30	FOREIGN KEY (idClass) 
31	REFERENCES class(idClass)
32	ON DELETE CASCADE
33) CHARSET UTF8 ENGINE=INNODB;
34
35CREATE TABLE firstnames (
36	idFirstnames INT AUTO_INCREMENT,
37    firstName VARCHAR(255),
38    gender BOOL,
39    PRIMARY KEY(idFirstnames)
40) CHARSET UTF8 ENGINE=INNODB;
41
42CREATE TABLE lastnames (
43	idLastnames INT AUTO_INCREMENT,
44    lastName VARCHAR(255),
45    gender BOOL,
46    PRIMARY KEY(idLastnames)
47) CHARSET UTF8 ENGINE=INNODB;
48
49show tables;
50describe student;
51insert into speciality (name, faculty) values ('физик-математик','физико-математический');
52insert into speciality (name, faculty) values ('иностранец','иностранных языков');
53
54insert into class (name, idSpeciality) values ('англичане', 2);
55insert into class (name, idSpeciality) values ('ивритяне', 2);
56insert into class (name, idSpeciality) values ('руссияне', 2);
57insert into class (name, idSpeciality) values ('физики', 1);
58insert into class (name, idSpeciality) values ('математики', 1);
59
60delimiter //
61CREATE TRIGGER add_check BEFORE INSERT ON student
62	FOR EACH ROW
63    BEGIN    
64    DECLARE idCl INT;
65	
66   IF NEW.idClass = 0 THEN
67	IF (SELECT COUNT(*) FROM student) = 0 THEN
68		SET NEW.idClass = 1;
69    END IF;	
70    ELSE 
71	SET idCl = (SELECT class.idClass FROM class
72		JOIN student ON student.idClass = class.idClass
73		GROUP BY class.idClass ORDER BY COUNT(student.idClass) LIMIT 1);
74        
75	SET NEW.idClass = idCl;
76	END IF;
77    
78	END; //
79delimiter ;
80
81insert into student (name, gender, birthdate, stip, idClass) 
82values ('Маруся Татарович', 1, '1947-05-30', 300, 1);
83
84select student.name as Студент, class.name as Группа, speciality.name as Специальность, speciality.faculty as Факультет
85from student 
86join class on student.idClass = class.idClass
87join speciality on class.idSpeciality = speciality.idSpeciality;