rN41xdyv

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,
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);
59insert into class (name, idSpeciality) values ('немцы', 2);
60insert into class (name, idSpeciality) values ('программисты', 1);
61insert into class (name, idSpeciality) values ('нуль-физики', 1);
62insert into class (name, idSpeciality) values ('инженеры', 1);
63insert into class (name, idSpeciality) values ('эсперантисты', 2);
64
65insert into firstnames (firstName, gender) values ('Сергей', 0);
66insert into firstnames (firstName, gender) values ('Маруся', 1);
67insert into firstnames (firstName, gender) values ('Амит', 0);
68insert into firstnames (firstName, gender) values ('Борис', 0);
69insert into firstnames (firstName, gender) values ('Арина', 1);
70insert into firstnames (firstName, gender) values ('Никола', 0);
71insert into firstnames (firstName, gender) values ('Анна', 1);
72insert into firstnames (firstName, gender) values ('Иосиф', 0);
73insert into firstnames (firstName, gender) values ('Владимир', 0);
74insert into firstnames (firstName, gender) values ('Лиля', 1);
75
76insert into lastnames (lastName, gender) values ('Довлатов', 0);
77insert into lastnames (lastName, gender) values ('Татарович', 1);
78insert into lastnames (lastName, gender) values ('Дувдевани', 0);
79insert into lastnames (lastName, gender) values ('Гребенщиков', 0);
80insert into lastnames (lastName, gender) values ('Родионовна', 1);
81insert into lastnames (lastName, gender) values ('Тесла', 0);
82insert into lastnames (lastName, gender) values ('Ахматова', 1);
83insert into lastnames (lastName, gender) values ('Бродский', 0);
84insert into lastnames (lastName, gender) values ('Шахрин', 0);
85insert into lastnames (lastName, gender) values ('Брик', 1);
86
87select firstnames.firstName as 'Имя', lastnames.lastName as 'Фамилия', lastnames.gender as 'Пол' 
88from firstnames, lastnames
89where firstnames.gender = lastnames.gender;
90delimiter //
91CREATE PROCEDURE createpeople()
92BEGIN
93	DECLARE firstName CHAR(255);
94    DECLARE lastName CHAR(255);
95    DECLARE person CHAR(255);
96    DECLARE gender BOOL;
97    DECLARE done INT DEFAULT 0;
98    DECLARE nGr INT;
99  
100	DECLARE cur1 CURSOR 
101    FOR select firstnames.firstName as 'Имя', lastnames.lastName as 'Фамилия', lastnames.gender as 'Пол' 
102	from students.firstnames, students.lastnames
103	where firstnames.gender = lastnames.gender;
104    
105    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
106	OPEN cur1;
107 REPEAT 
108    FETCH cur1  into firstName, lastName, gender;
109    SET nGr = (select idClass from class order by rand() limit 1);
110    IF NOT done THEN
111		INSERT into student (name, gender, idClass) 
112        values (CONCAT(CONCAT(firstName, ' '), lastName), gender, nGr);
113    END IF;
114    
115  UNTIL done END REPEAT;
116	CLOSE cur1;
117END // 
118delimiter ;
119
120call createpeople();
121
122select name, gender, idClass from student;
123
124select student.name as Студент, student.gender as Пол, class.name as Группа, student.idClass as Номер_Группы
125from student 
126join class on student.idClass = class.idClass;