MwCx9pta

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
35show tables;
36describe student;
37insert into speciality (name, faculty) values ('физик-математик','физико-математический');
38insert into speciality (name, faculty) values ('иностранец','иностранных языков');
39
40insert into class (name, idSpeciality) values ('англичане', 2);
41insert into class (name, idSpeciality) values ('ивритяне', 2);
42insert into class (name, idSpeciality) values ('руссияне', 2);
43insert into class (name, idSpeciality) values ('физики', 1);
44insert into class (name, idSpeciality) values ('математики', 1);
45
46insert into student (name, gender, birthdate, stip, idClass) 
47values ('Сергей Довлатов', 0, '1941-09-03', 1990, 2);
48insert into student (name, gender, birthdate, stip, idClass) 
49values ('Маруся Татарович', 1, '1947-05-30', 300, 1);
50insert into student (name, gender, birthdate, stip, idClass) 
51values ('Амит Дувдевани', 0, '1974-11-07', 1994, 2);
52insert into student (name, gender, birthdate, stip, idClass) 
53values ('Борис Демидович', 0, '1906-03-02', 10000, 5);
54insert into student (name, gender, birthdate, stip, idClass) 
55values ('Никола Тесла', 0, '1856-07-10', 1943, 4);
56insert into student (name, gender, birthdate, stip, idClass) 
57values ('Анна Ахматова', 1, '1889-06-23', 1966, 3);
58insert into student (name, gender, birthdate, stip, idClass) 
59values ('Борис Гребенщиков', 0, '1953-11-27', 100, 5);
60insert into student (name, gender, birthdate, stip, idClass) 
61values ('Арина Родионовна', 1, '1758-04-21', 800, 3);
62insert into student (name, gender, birthdate, stip, idClass) 
63values ('Владимир Шахрин', 0, '1959-06-22', 2016, 4);
64insert into student (name, gender, birthdate, stip, idClass) 
65values ('Джоанна Стингрей', 1, '1960-07-03', 1111, 1);
66insert into student (name, gender, birthdate, stip, idClass) 
67values ('Иосиф Бродский', 0, '1940-05-24', 2801, 2);
68insert into student (name, gender, birthdate, stip, idClass) 
69values ('Совья Ковалевская', 1, '1850-01-15', 1891, 5);
70insert into student (name, gender, birthdate, stip, idClass) 
71values ('Пол МакКартни', 0, '1942-06-18', 9000, 1);
72insert into student (name, gender, birthdate, stip, idClass) 
73values ('Стивен Хокинг', 0, '1942-01-08', 600000, 4);
74insert into student (name, gender, birthdate, stip, idClass) 
75values ('Валентина Терешкова', 1, '1937-03-06', 7900, 3);
76insert into student (name, gender, birthdate, stip, idClass) 
77values ('Аркадий Стругацкий', 0, '1925-08-28', 6000, 2);
78insert into student (name, gender, birthdate, stip, idClass) 
79values ('Елизавета Королева', 1, '1926-04-21', 1630, 1);
80insert into student (name, gender, birthdate, stip, idClass) 
81values ('Ольга Княгиня', 1, '890-07-11', 969, 3);
82insert into student (name, gender, birthdate, stip, idClass) 
83values ('Вильгельм Рентген', 0, '1845-03-27', 1923, 4);
84insert into student (name, gender, birthdate, stip, idClass) 
85values ('Йоко Оно', 1, '1933-02-18', 1980, 5);
86
87/*select student.name as Студент, class.name as Группа, speciality.name as Специальность, speciality.faculty as Факультет
88from student, class, speciality where
89class.idClass = student.idClass and class.idSpeciality = speciality.idSpeciality;*/
90
91
92select student.name as Студент, class.name as Группа, speciality.name as Специальность, speciality.faculty as Факультет
93from student 
94join class on student.idClass = class.idClass
95join speciality on class.idSpeciality = speciality.idSpeciality;