Insert into salespeople

(snum, sname, city, comm) VALUES

(1, "Иван Жадов", "Нижний Новгород", "0.12"),

(2, "Федор Захапов", default, "0.15"),

(3, "Максим Хитров", default, "0.14"),

(default, "Алиса Тырь", "Бор", "0.10");

/*

3. Создайте и заполните таблицу Customers с полями (при создании таблицы укажите

ENGINE=InnoDB):

Имя поля Тип данных Описание

cnum Целое, ключевое Идентификатор покупателя

cname Текстовое, не пустое Имя покупателя

city Текстовое, по умолчанию «Нижний Новгород» Город

rating Целое, по умолчанию 1 Рейтинг

snum Целое, внешний ключ к полю Salespeople.snum Идентификатор продавца

*/

CREATE TABLE customers

(cnum int not null auto_increment,

cname varchar(30) not null,

city varchar(20) not null default "Нижний Новгород",

rating int not null default "1",

snum int,

primary key(cnum),

foreign key(snum) references salespeople(snum)

ON DELETE CASCADE ON UPDATE CASCADE)

engine = InnoDB Default charset = utf8;

INSERT INTO customers

(cnum, cname, city, rating, snum) VALUES

(1, "Василий Дубов", default, 60, 2),

(default, "Алексей Шмыга", default, 830, 3),

(default, "Лариса Тупакова", default, 520, 3),

(default, "Петр Быков", "Бор", 30, 4),

(default, "Антон Тотуттотам", "Бор", 120, 4);

/*

4. Создайте и заполните таблицу Orders с полями (при создании таблицы укажите

ENGINE=InnoDB):

Имя поля Тип данных Описание

onum Целое, ключевое Идентификатор заказа

odate Дата Дата заказа

amt Десятичное Стоимость

cnum Целое, внешний ключ к полю Customers.cnum Идентификатор покупателя

snum Целое, внешний ключ к полю Salespeople.snum Идентификатор продавца

*/

CREATE TABLE orders

(onum int not null auto_increment,

odate DATE,

amt DECIMAL(12,2),

cnum int,

snum int,

primary key(onum),

foreign key(cnum) references customers(cnum)

ON DELETE CASCADE ON UPDATE CASCADE,

foreign key(snum) references salespeople(snum)

ON DELETE CASCADE ON UPDATE CASCADE)

engine = InnoDB Default charset = utf8;

INSERT INTO orders

(onum, odate, amt, cnum, snum) VALUES

(default, "2006-11-2", "610", 1, 2),

(default, "2006-11-2", "8255.5", 2, 3),

(default, "2007-06-15", "5160", 3, 3),

(default, "2008-5-5", "310", 4, 4),

(default, "2009-11-15", "1200", 5, 4),

(default, "2009-11-17", "15", 5, 4);

/*

======================== Проверка целостности ========================

5. Проверьте обеспечение ссылочной целостности данных в созданных таблицах.

*/

UPDATE salespeople set snum = 12 where snum = 2;

UPDATE customers set cnum = 22 where cnum = 2;

UPDATE orders set onum = 32 where onum = 2;

DELETE from salespeople where sname = "Алиса Тырь";

DELETE from customers where cname = "Василий Дубов";

DELETE from orders where onum = 3;

/*

======================== VIEW ========================

6. Создайте следующие представления (VIEW):

• Список всех заказов с полями: onum, odate, amt, cname, sname;

*/

CREATE view view_1 as

SELECT onum, odate, amt, cname, sname FROM orders

join customers on orders.cnum = customers.cnum

join salespeople on orders.snum = salespeople.snum

GROUP BY onum;

/*

• Список правильных заказов: под правильными заказами понимаются заказы, в которых

соблюдено соответствие идентификаторов покупателей и продавцов (как указано в таблице Custsomers);

*/

CREATE view view_2 as

SELECT onum, odate, amt, cname, sname from orders

JOIN salespeople on orders.snum = salespeople.snum

JOIN customers on orders.cnum = customers.cnum

WHERE salespeople.snum = customers.snum and orders.snum = customers.snum;

/*

• Список неправильных заказов.

*/

CREATE view view_3 as

SELECT onum, odate, amt, cname, sname from orders

JOIN salespeople on orders.snum = salespeople.snum

JOIN customers on orders.cnum = customers.cnum

WHERE salespeople.snum != customers.snum and orders.snum != customers.snum;

/*

7. Проверьте изменение данных через созданные представления.

*/

UPDATE orders set snum = 2 where snum = 3;

UPDATE customers set snum = 3 where snum = 4;

/*

8. Выполните INSERT-запрос к любой из таблиц, заключив эти запросы в конструкцию

START TRANSACTION; … ROLLBACK;.

*/

START TRANSACTION;