WsLHckV5

1create table DocType (
2    id_doctype int(20) auto_increment not null,
3    doc_type varchar(20),
4    primary key (id_doctype)
5);
6
7create table Organization(
8    id_org int(10) auto_increment not null,
9    org_name varchar(20),
10    index_n int(10),
11    city varchar(20),
12    address_org varchar(20),
13    phone varchar(20),
14    fax varchar(20),
15    email varchar(20),
16    primary key (id_org)
17);
18
19create table otdel(
20    id_otdel int(10) auto_increment not null,
21    otdel_name varchar(20),
22    boss varchar(20),
23    phone varchar(20),
24    primary key (id_otdel)
25);
26
27
28create table incoming (
29    Id_incoming int (11) auto_increment not null,
30    kod_type int (20),
31    kod_otd int(10),
32    kod_org int (10),
33    document varchar (20),
34    source_number int(10),
35    sender varchar(20),
36    date_send date,
37    reciever varchar(20),
38    recieve_date date,
39    foreign key (kod_type) references DocType(id_doctype),
40    foreign key (kod_otd) references otdel(id_otdel),
41    foreign key (kod_org) references Organization(id_org), 
42    primary key (Id_incoming)
43);
44
45create table upcoming (
46    Id_upcoming int (11) auto_increment,
47    kod_type int (20),
48    kod_org int(10),
49    kod_otd int (10),
50    document varchar(20),
51    sender varchar(20),
52    date_send date,
53    reciever varchar(20),
54    foreign key (kod_type) references DocType(id_doctype),
55    foreign key (kod_otd) references otdel(id_otdel),
56    foreign key (kod_org) references Organization(id_org), 
57    primary key (Id_upcoming)
58);
59
60
61insert into DocType (doc_type) values
62    ('mail'),
63    ('check'),
64    ('act'),
65    ('project')
66;
67
68insert into Organization (org_name, index_n, city, address_org, phone, fax, email) values
69    ('IP Petrov', 1, 'Moscow', 'tverskaya', '999994', '1', 'email@petrov.com'),
70    ('IP Ivanov', 2, 'Moscow', 'tverskaya', '999995', '1', 'email@ivanov.com'),
71    ('IP Sidorov', 3, 'Moscow', 'tverskaya', '999996', '1', 'email@sidorov.com'),
72    ('ZAO опхопжиртрест', 3, 'Moscow', 'tverskaya', '999997', '1', 'email@noreply.com'),
73    ('PAO опхопжиртрест', 2, 'Moscow', 'tverskaya', '999998', '1', 'email@noreply.net');
74
75insert into otdel (otdel_name, boss, phone) values
76    ('продажи', 'неудачник1', '899999'),
77    ('маркетинг', 'неудачник2', '799999'),
78    ('разработки', 'неудачник3', '699999'),
79    ('работа с клиентами', 'неудачник4', '599999'),
80    ('кадры', 'неудачник5', '499999');
81
82insert into incoming (kod_type, kod_org, kod_otd, document, source_number, sender, date_send, reciever, recieve_date) values
83    (1, 1, 1, 'act', 1, 'неудачник1', '2015-09-05', 'неудачник2', '2015-09-05'),
84    (2, 2, 2, 'check', 1, 'неудачник2', '2015-09-06', 'неудачник1', '2015-09-06'),
85    (3, 3, 3, 'status', 1, 'неудачник3', '2015-09-07', 'неудачник4', '2015-09-07'),
86    (4, 4, 4, 'spam', 1, 'неудачник4', '2015-09-08', 'неудачник3', '2015-09-08'),
87    (5, 5, 5, 'flood', 1, 'неудачник5', '2015-09-09', 'неудачник1', '2015-09-09');
88
89insert into upcoming (kod_type, kod_org, kod_otd, document, sender, date_send, reciever) values
90    (1, 1, 1, 'act', 'неудачник1', '2015-09-05', 'неудачник2'),
91    (2, 2, 2, 'check', 'неудачник2', '2015-09-06', 'неудачник1'),
92    (3, 3, 3, 'status', 'неудачник3', '2015-09-07', 'неудачник4'),
93    (4, 4, 4, 'spam', 'неудачник4', '2015-09-08', 'неудачник3'),
94    (5, 5, 5, 'flood', 'неудачник5', '2015-09-09', 'неудачник1');
95
96select * from incoming;
97select otdel_name as Отдел from otdel;
98select * from Organization where index_n > 2;
99select Organization.city, DocType.doc_type from Organization, DocType;
100select count(*) AS KOLVO from upcoming;