Методические указания к лабораторным работам по курсу

«Управление данными»

Для проведения занятий используется свободно распространяемое ПО:

• MySQL Server;

• DreamCoder for MySQL.

Задание к каждой лабораторной работе оформлено в виде SQL – скрипта, который надо ввести в соответствующем окне приложения DreamCoder for MySQL. Инструкции учащимся оформлены в виде закомментированных (/*…*/) участков кода скриптов. Также, однострочными комментариями (--) выделены операторы SQL, которые надо выполнять в том случае, если есть необходимость выполнить весь скрипт или его часть повторно.

Замечание. Операторы языка SQL желательно набирать заглавными буквами, даже если в тексте методички иногда могут встретиться операторы, набранные строчными буквами.

ЛР №1

-- DROP DATABASE lab_base;

/*

1. Откройте приложение “DreamCoder for MySQL”, выберите пункт меню Database -> Connect,

введите в поля соответствующие значения.

2. Создайте новую базу данных MySQL, выполнив запрос

CREATE DATABASE имя_базы;

*/

CREATE DATABASE lab_base;

--engine = MyIsam Default charset= cp1251;

/*

3. Выберите пункт меню Database -> Disconnect, затем присоединитесь к созданной базе данных, выбрав Database -> Connect, указав в поле Database имя_базы и сняв флажок Show all databases.

4. Создайте оператором SQL таблицу Salespeople с полями:

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

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

sname Текстовое, не пустое Имя продавца

city Текстовое Город

comm Десятичное, два знака после точки Комиссионные, меньше 1

*/

CREATE TABLE salespeople

(snum INT,

sname TEXT NOT NULL,

city TEXT,

comm DECIMAL(5,5),

PRIMARY KEY(snum));

/*

5. Заполните таблицу Salespeople с помощью оператора INSERT.

*/

insert INTO salespeople

(snum, sname, city, comm) VALUES

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

(2, "Федор Захапов", "Нижний Новгород", "0.15"),

(3, "Максим Хитров", "Нижний Новгород", "0.14"),

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

/*

6. Создайте оператором SQL таблицу Customers с полями:

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

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

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

city Текстовое Город

rating Целое Рейтинг

snum Целое Идентификатор продавца

*/

CREATE TABLE customers

(cnum INT,

cname TEXT NOT NULL,

city TEXT,

rating INT,

snum INT,

PRIMARY KEY(cnum));

/*

7. Заполните таблицу Customers с помощью оператора INSERT.

*/

insert INTO customers

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

(1, "Василий Дубов", "Нижний Новгород", 60, 2),

(2, "Алексей Шмыга", "Нижний Новгород", 830, 3),

(3, "Лариса Тупакова", "Нижний Новгород", 520, 3),

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

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

/*

8. Создайте оператором SQL таблицу Orders с полями:

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

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

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

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

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

snum Целое Идентификатор продавца

*/

CREATE TABLE orders

(onum INT,

odate DATE,

amt DECIMAL(12,2),

cnum INT,

snum INT,

PRIMARY KEY(onum));

/*

9. Заполните таблицу Orders с помощью оператора INSERT.

*/

insert INTO orders

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

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

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

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

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

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

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

/*

======================== SQL-запросы ========================

1. Написать запрос, выбирающий заказы за июнь 2007 г. стоимостью более 3400 р.

*/

SELECT * FROM orders

WHERE odate BETWEEN "2007-06-1" AND "2007-07-1" AND amt > "3400";

/*

2. Написать запрос, выдающий информацию о покупателях, обслуживаемых

продавцами с комиссионными более 0,12.

*/

SELECT * FROM customers, salespeople

where customers.snum = salespeople.snum AND comm > "0.12";

/*

3. Написать запрос, выдающий список покупателей с рейтингом более 100,

проживающих не в Нижнем Новгороде.

*/

SELECT * FROM customers

WHERE rating > "100" AND city != "Нижний Новгород";

/*

4. Написать запрос, выдающий для каждого покупателя наибольшую стоимость

его заказа.

*/

SELECT max(amt), customers.* FROM orders, customers

WHERE customers.cnum = orders.cnum

GROUP BY amt DESC;

/*

5. Написать запрос, выдающий для каждого покупателя наименьшую стоимость

его заказа.

*/

SELECT min(amt), customers.* FROM orders, customers

WHERE customers.cnum = orders.cnum

GROUP BY amt ASC;

/*

6. Написать запрос, выдающий итоги по сумме заказов на каждый день.

*/

SELECT odate, SUM(amt) FROM orders

GROUP BY odate ASC;

/*

7. Написать запрос, выдающий даты заказов и максимальную стоимость заказов

за этот день.

*/

SELECT odate, MAX(amt) FROM orders

GROUP BY odate ASC;

/*

8. Написать запрос, выдающий даты заказов и количество заказов за этот день.

*/

SELECT odate, COUNT(amt) FROM orders

GROUP BY odate ASC;

/*

9. Написать запрос, выдающий список городов и максимальный рейтинг покупателей

в городе.

*/

SELECT city, MAX(rating) FROM customers

GROUP BY city;

/*

10. Написать запрос, выдающий количество заказов у продавцов.

*/

SELECT salespeople.sname, COUNT(orders.snum) FROM salespeople, orders

WHERE salespeople.snum = orders.snum

GROUP BY sname;

/*

11. Написать запрос, выдающий список покупателей с рейтингом выше среднего.

*/

SELECT cname, rating FROM customers

WHERE rating > (SELECT AVG(rating) FROM customers)

GROUP BY cname;

/*

12. Написать запрос, выдающий покупателей с максимальным рейтингом.

*/

SELECT cname, rating FROM customers

WHERE rating = (SELECT MAX(rating) FROM customers);

/*

13. Написать запрос, выдающий список заказов с фамилиями покупателей

и продавцов.

*/

SELECT orders.*, salespeople.sname, customers.cname FROM orders

JOIN salespeople on orders.snum = salespeople.snum

JOIN customers on orders.cnum = customers.cnum;

/*

14. Написать запрос, выдающий список продавцов, имеющих покупателей

с рейтингом более 100.

*/

SELECT salespeople.sname, customers.* FROM salespeople, customers

WHERE salespeople.snum = customers.snum AND customers.rating > 100

/*

15. Написать запрос, выдающий фамилии покупателей (и их рейтинги),

обслуживаемых продавцами с комиссионными более 0,11 (выдать также фамилии

продавцов и их комиссионные).

*/

SELECT cname, rating, sname, comm FROM salespeople, customers

WHERE salespeople.snum = customers.snum AND salespeople.comm > 0.11 GROUP BY rating DESC;

/*

16. Написать запрос, выдающий суммы стоимостей заказов по продавцам.

*/

SELECT salespeople.*, SUM(orders.amt) from salespeople, orders

WHERe salespeople.snum = orders.snum

GROUP BY snum;

/*

17. Написать запрос, выдающий суммы стоимостей заказов по покупателям.

*/

SELECT customers.*, SUM(orders.amt) from customers, orders

WHERE customers.cnum = orders.cnum

GROUP BY cnum;

/*

18. Написать запрос, увеличивающий комиссионные на 0,01 всем продавцам,

имеющим заказы.

*/

UPDATE salespeople, orders SET comm = comm * 1.01

WHERE salespeople.snum = orders.snum

/*

19. Написать запрос, увеличивающий комиссионные на 0,02 всем продавцам в нижнем Новгороде.

*/

UPDATE salespeople SET comm = comm * 1.02

WHERE salespeople.city = "Нижний Новгород"

/*

20. Установить comm=0.1 всем продавцам, не имеющим заказов.

*/

UPDATE salespeople SET comm = 0.1

WHERE salespeople.snum not in (select snum from orders);

ЛР №2

-- DROP DATABASE lab_base2;

/*

1. Откройте приложение "DreamCoder for MySQL", выберите пункт меню Database -> Connect, введите в поля соответствующие значения:

2. Создайте новую базу данных MySQL, выполнив запрос CREATE DATABASE имя_базы; в окне "SQL editor".

*/

create database lab_base2;

/*

3. Выберите пункт меню Database -> Disconnect, затем присоединитесь к созданной базе данных, выбрав Database -> Connect, указав в поле Database имя_базы и сняв флажок Show all databases.

4. Создайте оператором sql таблицу Salespeople с полями:

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

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

sname Текстовое, не пустое Имя продавца

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

comm Десятичное, два знака после точки, по умолчанию 0.1 Комиссионные, меньше 1

======================== Создание таблиц ========================

*/

DROP TABLE IF EXISTS salespeople, customers, orders;

CREATE table salespeople

(snum int not null auto_increment,

sname varchar(30) not null,

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

comm decimal(3,3),

primary key (snum)) engine = MyIsam Default charset = utf8;

/*

5. Заполните таблицу Salespeople с помощью оператора insert.

*/

Insert into salespeople

(snum, sname, city, comm) VALUES

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

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

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

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

/*

6. Создайте оператором sql таблицу Customers с полями:

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

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

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

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

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

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)) engine = MyIsam Default charset = utf8;

/*

7. Заполните таблицу Customers с помощью оператора INSERT.

*/

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);

/*

8. Создайте оператором SQL таблицу Orders с полями:

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

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

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

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

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

snum Целое Идентификатор продавца

*/

CREATE TABLE orders

(onum int not null auto_increment,

odate DATE,

amt DECIMAL(12,2),

cnum int,

snum int,

PRIMARY KEY(onum)) engine = MyIsam Default charset = utf8;

/*

9. Заполните таблицу Orders с помощью оператора INSERT.

*/

INSERT INTO orders

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

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

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

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

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

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

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

/*

======================== Триггеры ========================

10. С помощью триггеров обеспечить ссылочную целостность таблиц Salespeople и Customers по полю snum, таблиц Salespeople, Customers и Orders по полям cnum и snum, а также проверку значения comm<1. SQL-запросы

*/

DROP trigger if exists salespeople_delete;

CREATE trigger salespeople_delete

before delete on salespeople

for each row

begin

UPDATE customers set customers.snum = null

where old.snum = customers.snum;

UPDATE orders set orders.snum = null

where old.snum = orders.snum;

end;

-- delete from salespeople where snum = 4;

DROP trigger if exists customers_delete;

CREATE trigger customers_delete

before delete on customers

for each row

begin

UPDATE orders set orders.cnum = null

where old.cnum = orders.cnum;

end;

-- delete from customers where cnum = 1;

DROP trigger if exists salespeople_update;

CREATE trigger salespeople_update

after update on salespeople

for each row

begin

if new.snum != old.snum then

update customers set customers.snum = new.snum

where customers.snum = old.snum;

update orders set orders.snum = new.snum

where orders.snum = old.snum;

end if;

end;

-- update salespeople set snum = 20 where snum = 2;

DROP trigger if exists customers_update;

CREATE trigger customers_update

after update on customers

for each row

begin

if new.cnum != old.cnum then

update orders set orders.cnum = new.cnum

where orders.cnum = old.cnum;

end if;

end;

-- update customers set cnum = 22 where cnum = 2;

DROP trigger if exists comm_1;

CREATE trigger comm_1

before update on salespeople

for each row

begin

if new.comm > 0.3 then

set new.comm = 0.3;

end if;

end;

-- update salespeople set comm = 0.5 where snum = 1;

/*

======================== SQL-запросы ========================

1. Написать запрос, выбирающий номера и стоимости заказов за определённую дату

и подводящий итог по суммарной стоимости заказов за эту дату.

*/

SELECT onum, amt from orders

where odate = "2006-11-2"

union

select "amount:", sum(orders.amt) from orders

where odate = "2006-11-2";

/*

2. Написать запрос, выбирающий номера и стоимости заказов за сегодняшний день

(использовать функцию NOW()) и подводящий итог по суммарной стоимости

заказов за сегодняшний день.

*/

SELECT onum, amt from orders

where odate = date (now())

union

select "amount:", sum(orders.amt) from orders

where odate = date (now());

/*

3. Написать запрос, выдающий список всех продавцов (в том числе не имеющих

заказов) и суммы стоимостей их заказов.

*/

SELECT sname, sum(orders.amt) from salespeople

left JOIN orders on orders.snum = salespeople.snum

GROUP by sname

-- или так ^^

SELECT sname,

(SELECT sum(amt)

from orders

where snum = salespeople.snum)

from salespeople

GROUP by sname

/*

4. Написать запрос, выдающий список всех покупателей (в том числе не имеющих

заказов) и суммы стоимостей их заказов.

*/

SELECT cname, sum(orders.amt) from customers

left JOIN orders on orders.cnum = customers.cnum

group by cname

-- или так

SELECT cname,

(select sum(amt)

from orders where

cnum=customers.cnum)

FROM customers

GROUP by cname

ЛР №3

-- DROP DATABASE lab_base3;

/*

1. Откройте приложение “DreamCoder for MySQL”, выберите пункт меню Database -> Connect, введите в поля соответствующие значения.

2. Создайте новую базу данных MySQL, выполнив запрос CREATE DATABASE имя_базы;

*/

CREATE DATABASE lab_base3;

--engine = MyIsam Default charset= cp1251;

/*

3. Выберите пункт меню Database -> Disconnect, затем присоединитесь к созданной базе данных, выбрав Database -> Connect, указав в поле Database имя_базы и сняв флажок Show all databases.