UkUnE9mq

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5-- -----------------------------------------------------
6-- Schema mydb
7-- -----------------------------------------------------
8DROP SCHEMA IF EXISTS `mydb` ;
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
14USE `mydb` ;
15
16-- -----------------------------------------------------
17-- Table `mydb`.`k_staff`
18-- -----------------------------------------------------
19DROP TABLE IF EXISTS `mydb`.`k_staff` ;
20
21CREATE TABLE IF NOT EXISTS `mydb`.`k_staff` (
22  `staff_num` INT auto_increment,
23  `staff_name` VARCHAR(45) NOT NULL,
24  `staff_post` VARCHAR(45) NULL,
25  `staff_hiredate` DATE NULL,
26  `staff_termdate` DATE NULL,
27  `K_dept_dept_num` INT,
28  PRIMARY KEY (`staff_num`),
29  INDEX `fk_k_staff_K_dept1_idx` (`K_dept_dept_num` ASC),
30  CONSTRAINT `fk_k_staff_K_dept1`
31    FOREIGN KEY (`K_dept_dept_num`)
32    REFERENCES `mydb`.`K_dept` (`dept_num`)
33    ON DELETE NO ACTION
34    ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `mydb`.`K_dept`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `mydb`.`K_dept` ;
42
43CREATE TABLE IF NOT EXISTS `mydb`.`K_dept` (
44  `dept_num` INT auto_increment,
45  `dept_full_name` VARCHAR(45) NULL,
46  `dept_short_name` VARCHAR(10) NULL,
47  `k_staff_staff_num` INT,
48  PRIMARY KEY (`dept_num`),
49  INDEX `fk_K_dept_k_staff_idx` (`k_staff_staff_num` ASC),
50  CONSTRAINT `fk_K_dept_k_staff`
51    FOREIGN KEY (`k_staff_staff_num`)
52    REFERENCES `mydb`.`k_staff` (`staff_num`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `mydb`.`k_firm`
60-- -----------------------------------------------------
61DROP TABLE IF EXISTS `mydb`.`k_firm` ;
62
63CREATE TABLE IF NOT EXISTS `mydb`.`k_firm` (
64  `firm_num` INT auto_increment,
65  `firm_name` VARCHAR(45) NOT NULL,
66  `firm_addr` VARCHAR(45) NULL,
67  `firm_phone` VARCHAR(20) NULL,
68  PRIMARY KEY (`firm_num`))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `mydb`.`k_contract`
74-- -----------------------------------------------------
75DROP TABLE IF EXISTS `mydb`.`k_contract` ;
76
77CREATE TABLE IF NOT EXISTS `mydb`.`k_contract` (
78  `contract_num` INT auto_increment,
79  `contract_date` DATE NULL,
80  `contract_type` ENUM('A', 'B', 'C') NULL,
81  `k_firm_firm_num` INT NOT NULL,
82  `k_staff_staff_num` INT NOT NULL,
83  PRIMARY KEY (`contract_num`),
84  INDEX `fk_k_contract_k_firm1_idx` (`k_firm_firm_num` ASC),
85  INDEX `fk_k_contract_k_staff1_idx` (`k_staff_staff_num` ASC),
86  CONSTRAINT `fk_k_contract_k_firm1`
87    FOREIGN KEY (`k_firm_firm_num`)
88    REFERENCES `mydb`.`k_firm` (`firm_num`)
89    ON DELETE NO ACTION
90    ON UPDATE NO ACTION,
91  CONSTRAINT `fk_k_contract_k_staff1`
92    FOREIGN KEY (`k_staff_staff_num`)
93    REFERENCES `mydb`.`k_staff` (`staff_num`)
94    ON DELETE NO ACTION
95    ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `mydb`.`k_bill`
101-- -----------------------------------------------------
102DROP TABLE IF EXISTS `mydb`.`k_bill` ;
103
104CREATE TABLE IF NOT EXISTS `mydb`.`k_bill` (
105  `bill_num` INT auto_increment,
106  `bill_date` DATE NULL,
107  `bill_sum` DECIMAL(9,2) NULL,
108  `bill_term` DATE NULL,
109  `bill_peni` DECIMAL(6,2) NULL,
110  `k_contract_contract_num` INT NOT NULL,
111  PRIMARY KEY (`bill_num`),
112  INDEX `fk_k_bill_k_contract1_idx` (`k_contract_contract_num` ASC),
113  CONSTRAINT `fk_k_bill_k_contract1`
114    FOREIGN KEY (`k_contract_contract_num`)
115    REFERENCES `mydb`.`k_contract` (`contract_num`)
116    ON DELETE NO ACTION
117    ON UPDATE NO ACTION)
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `mydb`.`k_payment`
123-- -----------------------------------------------------
124DROP TABLE IF EXISTS `mydb`.`k_payment` ;
125
126CREATE TABLE IF NOT EXISTS `mydb`.`k_payment` (
127  `payment_num` INT,
128  `payment_date` DATE NULL,
129  `payment_sum` DECIMAL(9,2) NULL,
130  `k_bill_bill_num` INT NOT NULL,
131  PRIMARY KEY (`payment_num`, `k_bill_bill_num`),
132  INDEX `fk_k_payment_k_bill1_idx` (`k_bill_bill_num` ASC),
133  CONSTRAINT `fk_k_payment_k_bill1`
134    FOREIGN KEY (`k_bill_bill_num`)
135    REFERENCES `mydb`.`k_bill` (`bill_num`)
136    ON DELETE NO ACTION
137    ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `mydb`.`k_price`
143-- -----------------------------------------------------
144DROP TABLE IF EXISTS `mydb`.`k_price` ;
145
146CREATE TABLE IF NOT EXISTS `mydb`.`k_price` (
147  `price_num` INT auto_increment,
148  `price_name` VARCHAR(45) NOT NULL,
149  `price_sum` VARCHAR(45) NULL,
150  `price_type` VARCHAR(1) NULL,
151  PRIMARY KEY (`price_num`))
152ENGINE = InnoDB;
153
154
155-- -----------------------------------------------------
156-- Table `mydb`.`k_protokol`
157-- -----------------------------------------------------
158DROP TABLE IF EXISTS `mydb`.`k_protokol` ;
159
160CREATE TABLE IF NOT EXISTS `mydb`.`k_protokol` (
161  `kolvo` INT NULL,
162  `price_sum` DECIMAL(9,2) NOT NULL,
163  `k_bill_bill_num` INT NOT NULL,
164  `k_price_price_num` INT NOT NULL,
165  PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`),
166  INDEX `fk_k_protokol_k_price1_idx` (`k_price_price_num` ASC),
167  CONSTRAINT `fk_k_protokol_k_bill1`
168    FOREIGN KEY (`k_bill_bill_num`)
169    REFERENCES `mydb`.`k_bill` (`bill_num`)
170    ON DELETE NO ACTION
171    ON UPDATE NO ACTION,
172  CONSTRAINT `fk_k_protokol_k_price1`
173    FOREIGN KEY (`k_price_price_num`)
174    REFERENCES `mydb`.`k_price` (`price_num`)
175    ON DELETE NO ACTION
176    ON UPDATE NO ACTION)
177ENGINE = InnoDB;
178
179
180SET SQL_MODE=@OLD_SQL_MODE;
181SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
182SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
183
184INSERT INTO k_firm (firm_name, firm_addr)
185VALUES('Альфа', 'Москва');
186INSERT INTO k_firm (firm_name, firm_addr)
187VALUES('Бета', 'Казань');
188INSERT INTO k_firm (firm_name, firm_addr)
189VALUES('Гамма', 'Париж');
190INSERT INTO k_firm (firm_name, firm_addr)
191VALUES('Дельта', 'Лондон');
192INSERT INTO k_firm (firm_name, firm_addr)
193VALUES('Омега', 'Токио'); 
194
195INSERT INTO k_dept (dept_short_name, dept_full_name)
196VALUES('Sales', 'Отдел продаж');
197INSERT INTO k_dept (dept_short_name, dept_full_name)
198VALUES('Mart', 'Отдел маркетинга');
199INSERT INTO k_dept (dept_short_name, dept_full_name)
200VALUES('Cust', 'Отдел гарантийного обслуживания');
201
202INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
203VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
204INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
205VALUES('Петров', 2, '2010-10-13','Менеджер');
206INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
207VALUES('Сидоров', 3, '2005-12-01','Менеджер');
208INSERT INTO k_staff (staff_name, staff_hiredate, staff_post)
209VALUES('Семенов', '1990-01-01','Директор');
210INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
211VALUES('Григорьев', 3, '2008-12-19','Программист');
212
213INSERT INTO k_contract
214(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 1, 1,'2011-11-01');
215INSERT INTO k_contract
216(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 1, 2,'2011-10-01');
217INSERT INTO k_contract
218(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 1, 1,'2011-09-01');
219INSERT INTO k_contract
220(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 2, 2,'2011-11-15');
221INSERT INTO k_contract
222(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 2, 2,'2011-08-01');
223INSERT INTO k_contract
224(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 3, 1,'2011-07-15');
225INSERT INTO k_contract
226(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 4, 1,'2011-11-12');
227
228INSERT INTO k_bill
229(k_contract_contract_num, bill_date, bill_term, bill_sum)
230VALUES(1, '2011-11-12', '2011-12-12', 1000);
231INSERT INTO k_bill
232(k_contract_contract_num, bill_date, bill_term, bill_sum)
233VALUES(1, '2011-12-12', '2012-01-12', 2000);
234INSERT INTO k_bill
235(k_contract_contract_num, bill_date, bill_term, bill_sum)
236VALUES(1, '2012-01-12', '2012-02-12',2000);
237INSERT INTO k_bill
238(k_contract_contract_num, bill_date, bill_term, bill_sum)
239VALUES(2, '2011-12-12', '2012-01-12', 6000);
240INSERT INTO k_bill
241(k_contract_contract_num, bill_date, bill_term, bill_sum)
242VALUES(2, '2012-01-12', '2012-02-12', 2000);
243INSERT INTO k_bill
244(k_contract_contract_num, bill_date, bill_term, bill_sum)
245VALUES(3, '2012-01-12', '2012-02-12', 2500);
246INSERT INTO k_bill
247(k_contract_contract_num, bill_date, bill_term, bill_sum)
248VALUES(4, '2011-12-12', '2012-01-12', 1500);
249INSERT INTO k_bill
250(k_contract_contract_num, bill_date, bill_term, bill_sum)
251VALUES(5, '2011-12-12', '2012-01-12', 1200);
252INSERT INTO k_bill
253(k_contract_contract_num, bill_date, bill_term, bill_sum)
254VALUES(5, '2012-01-12', '2012-02-12', 10000);
255SELECT * FROM k_bill;
256
257UPDATE k_dept SET k_staff_staff_num=2
258WHERE dept_short_name='Mart';
259UPDATE k_dept SET k_staff_staff_num=3
260WHERE dept_short_name='Cust';
261UPDATE k_dept SET k_staff_staff_num=1
262WHERE dept_short_name='Sales';
263--
264-- Перед выполнением этого запроса необходимо снять галочку в настройках
265-- edit -> preferences -> sql editor проматываем вниз и снимаем галочку с последнего пункта(safe updates)
266-- Затем выходим и заходим снова в workbench. Пробуем выполнить запросы и всё работает
267--
268DELETE FROM k_firm WHERE firm_num=5;
269
270create view	k_contract1
271As
272SELECT k_contract.contract_num, k_contract.contract_date,
273k_contract.contract_type, k_contract.k_firm_firm_num,
274k_staff.staff_name
275FROM k_contract INNER JOIN
276k_staff ON k_contract.k_staff_staff_num = k_staff.staff_num
277WHERE k_dept_dept_num = 1;
278select * from k_contract1;
279
280UPDATE k_contract1 SET contract_date='2011-11-02'
281WHERE contract_num=1;
282
283SELECT * FROM k_contract1;
284
285
286-- представление для неоплаченных счетов
287create view owe_bills as 
288SELECT b.bill_num AS "Номер счета",
289b.bill_date AS "Дата счета",
290b.bill_sum AS "Сумма счета",
2910 AS "Сумма оплаты"
292FROM k_bill b
293WHERE b.bill_num NOT IN (SELECT k_bill_bill_num FROM k_payment);
294
295select * from owe_bills;
296
297-- Представление(необновляемое потому что есть столбец который просчитывается с помощью функции sum) для всех договоров
298create view all_contracts as
299SELECT CONCAT('Договор № ', CONVERT(k_contract_contract_num, CHAR), ' на сумму ') AS "Номер", SUM(bill_sum) AS "Сумма" FROM k_bill
300GROUP BY k_contract_contract_num
301UNION
302SELECT 'ИТОГО: ', SUM(bill_sum) FROM k_bill ORDER BY 1;
303
304select * from all_contracts;
305
306-- Информация о 5 последних выписанных счетах
307create view bill_info as 
308SELECT bill_num, bill_date
309FROM k_bill ORDER BY bill_date DESC LIMIT 5;
310
311select * from bill_info;
312
313-- Процедура делает тоже, что и представление выше, только при её вызове можно указать сколько строчек нас интересует
314delimiter //
315create procedure procedure1 (limit_val int)
316begin
317	SELECT bill_num, bill_date
318	FROM k_bill ORDER BY bill_date DESC LIMIT limit_val;
319end//
320
321call procedure1(3);
322
323-- Процедура выводит конкретный заказ
324delimiter //
325create procedure procedure2 (num_param int)
326begin
327 select * from k_contract where contract_num = num_param;
328end//
329
330call procedure2(2);