jL7buZTj

1Проекция
2
3SELECT DISTINCT `fio` FROM  `lskn_guests`
4
5
6Выборка
7
8SELECT * FROM `lskn_class_rooms` WHERE `price`>50
9
10
11Декартово произведение
12
13SELECT * FROM `lskn_class_rooms`,`lskn_rooms`
14
15
16
17Соединение
18
19SELECT * FROM `lskn_class_rooms` JOIN `lskn_rooms` ON `lskn_rooms`.`class`=`lskn_class_rooms`.`class`
20
21
22
23Продублировал таблицу `lskn_guests` в `lskn_guests_copy` 
24
25
26CREATE TABLE IF NOT EXISTS `lskn_guests_copy` (
27  `passport` varchar(10) NOT NULL,
28  `moving_date` datetime NOT NULL,
29  `room_id` int(3) NOT NULL,
30  `fio` varchar(40) NOT NULL,
31  `gender` char(1) NOT NULL DEFAULT 'м',
32  `organization` int(40) NOT NULL,
33  `eviction_date` datetime NOT NULL,
34  `sign` int(1) NOT NULL,
35  PRIMARY KEY (`passport`,`moving_date`),
36  KEY `room_id` (`room_id`)
37) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
38INSERT INTO `lskn_guests_copy` SELECT * FROM `lskn_guests`;
39
40Пересечение
41
42SELECT * FROM `lskn_guests` WHERE `lskn_guests`.`room_id` IN (SELECT `lskn_guests_copy`.`room_id` FROM `lskn_guests_copy`)
43
44
45Объединение
46
47(SELECT * FROM `lskn_guests` UNION
48(SELECT * FROM `lskn_guests_copy`)
49
50
51
52Вычитание
53
54
55SELECT * FROM `lskn_guests` WHERE `lskn_guests`.`room_id` NOT IN (SELECT `lskn_guests_copy`.`room_id` FROM `lskn_guests_copy`)