База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
 Скачать 2.33 Mb.
|
|
Пример 5. Чтобы увидеть эффект от сортировки по нескольким столбцам, выполним очередной запрос: SELECT Surname, Name, HomeTelephone FROM Employees ORDER BY Surname DESC, Name Обратим внимание, что две строки фамилий упорядочены в убывающем порядке, а имена — в возрастающем по умолчанию. 3.4.2 Применение предложения GROUP BY Когда мы применяем запрос (оператор SELECT), содержащий как агрегатные функции (AVG, COUNT, MIN, МАХ, или SUM), так и обычные столбцы, необходимо использовать предложение GROUP BY для группирования результирующего набора по каждому неа1регатному столбцу. При этом применяются три правила: • каждый не агрегированный столбец результирующего набора должен появиться в предложении GROUP BY; • предложение GROUP BY может ссылаться только на столбцы, которые упомянуты в предложении SELECT; • каждое предложение SELECT в запросе может иметь только одно предложение GROUP BY. Группы определяются как подмножества строк, соответствующих данному значению столбца, определённому в предложении GROUP BY. Пример. Группирование результирующего набора, содержащего агрегатные функции. Выполним следующий запрос, чтобы определить количество сотрудников по каждой стране: SELECT COUNT(CodeOrder), Client FROM Orders GROUP BY Client Результат должен иметь следующий вид: COUNT Client 1 E-Life 1 IMC Computers 1 Net Line 1 Olymp 1 Белкантон 2 Bepca 1 ИнтеллектеС 2 Соло 1 Эликон-М 3.4.3 Применение предложения HAVING Также как предложение WHERE уменьшает количество строк, возвращаемых оператором SELECT, предложение HAVING может уменьшить число строк, возвращаемых предложением GROUP BY. Подобно предложению WHERE предложение HAVING содержит условия поиска. Однако эти условия обычно применяются к агрегатным функциям, содержащимся в предложении SELECT. Пример 1. Управление запросом посредством GROUP BY и HAVING. Применим следующий запрос, чтобы получить список клиентов, заказавших товаров на общую сумму свыше 100, и упорядочим результат по клиентам: SELECT Client, SUM(Price) FROM Orders, Ordered WHERE Orders.CodeOrder=Ordered.CodeOrder GROUP BY Client HAVING SUM(Price) > 100 ORDER BY Client Результат должен иметь следующий вид: Client SUM Olymp 119 Соло 113 Пример 2. Сортировка результирующего набора по результату агрегатной функции. Но что, если в предшествующем примере мы захотели бы упорядочить результат не по клиентам, а по общей сумме заказов? Ведь в предложении ORDER BY должна быть ссылка на имя столбца из предложения SELECT, в то время как в нашем примере столбец, содержащий общую сумму заказов, представлен именем агрегатной функции. В гаком случае мы должны воспользоваться второй альтернативой синтаксиса предложения ORDER BY (ORDER BY [col_name | int]) — использовать порядковый номер столбца в предложении SELECT. Вернёмся к последнему запросу и изменим предложение ORDER BY в соответствии со следующим примером: SELECT Client, SUM(Price) FROM Orders, Ordered WHERE Orders.CodeOrder = Ordered .CodeOrder GROUP BY Client HAVING SUM(Price) > 100 ORDER BY 2 результирующий набор должен быть отсортирован по второму столбцу в возрастающем порядке: Client SUM Соло ИЗ Olymp 119 4 Контрольные вопросы 1) Объясните, что такое подзапрос и чем он отличается от запроса. 2) В каком предложении инструкции SELECT можно использовать подзапрос? 3) Можно ли применять подзапрос в Инструкции Transact-SQL INSERT? 4) Можно ли применять подзапрос в Инструкции Transact-SQL UPDATE? 5) Можно ли применять подзапрос в Инструкции Transact-SQL DELETE? 6) Для чего служит инструкция ORDER BY и как её определить визуальными средствами? 7) Для чего служит инструкция GROUP BY и как её определить визуальными средствами? 8) Перечислите состав агрегатных функций и укажите порядок их применения. ЛАБОРАТОРНАЯ РАБОТА № 6 СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ 1 Цель работы 1) Научиться создавать и использовать представления. 2) Создание и применение хранимых процедур 2 Задание 1) Изучить правила создания и применения представлений и выполнить предложенные примеры. 2) Изучить правила создание и применение хранимых процедур 3 Порядок выполнения работы 3.1 Создание представлений 3.2 Соединение таблиц командой JOIN 3.3 Cоздание и применение хранимых процедур 3.1 Создание представлений Представление (View) представляет собой виртуальную таблицу, содержащую выбранные строки и столбцы из одной или нескольких таблиц или других представлений. MS SQL Server хранит только определения представлений. Представление часто работает как секретное устройство, поскольку можно предоставить пользователям разрешение работать с представлением, а не с оригинальными таблицами. Таким образом, пользователи могут работать только с данными, имеющимися в представлении, в то время как остальные данные остаются недоступными. Представления обычно используют для сохранения часто применяемых запросов или наборов запросов к базе данных. Выборка из представления осуществляется так же, как из таблиц, хотя другие операции имеют ряд ограничений. В следующем упражнении будет использована инструкция CREATE; VIEW языка Transact-SQL для создания списка поставщиков со всеми поставляемыми ими товарами путём выборки Title, Address, Telephone из таблицы Suppliers, а также Category, Mark, Price, InWarehouse, Expected, MinimalStock, DeliveriesStopped из таблицы Goods. Так как представление сохраняется в базе данных, а запрос — нет, процесс создания нового представления отличается от процесса создания запроса. Для создания представления: 1. в обозревателе объектов щелкните правой кнопкой мыши узел Представления и в контекстном меню выберите пункт Добавить новое представление. 2. Продолжите конструировать представление так же, как запрос SELECT. Примечание. Однако в отличие от конструирования запроса SELECT, для представлений имеются некоторые ограничения. Дополнительные сведения см. в разделе Представления (п. 4.6. Конспекта лекций). Открыть представление можно с помощью команды Modify View или Open View. Открытие результатов представления в области «Результаты» конструктора представлений: 1. в обозревателе объектов щелкните правой кнопкой мыши представление и выберите Изменить представление. 2. Откроется окно конструктора запросов и представлений, область «Результаты» которого отображает данные, содержащиеся в представлении. Для отображения других областей в меню Конструктора запросов выберите Область, а затем щелкните область, которую хотите открыть. Открытие определения представления: в обозревателе объектов щелкните правой кнопкой мыши представление и выберите Проект (Изменить в версии с пакетом обновления 1 или более ранней версии). Примечание. По умолчанию, конструктор запросов и представлений открывает все свои области («SQL», «Критерии», «Диаграмма» и «Результаты»), но эту настройку можно изменить в диалоговом окне Параметры. Для открытия этих областей, если они закрыты, в меню Конструктора запросов и представлений укажите Область и выберите область, которую хотите открыть. Сохранение представления. При сохранении представления изменяется определение представления на сервере. В компоненте Database Engine используется новое определение представления при сохранении представления. Для сохранения представления: 1. откройте определение представления в конструкторе запросов и представлений и измените его. 2. В меню Файл выберите Сохранить view name, где view_name — имя открытого представления. Переименование представления. Изменить имя представления невозможно. Вместо этого необходимо создать новое представление с другим именем и скопировать в него определение старого представления. Это можно сделать посредством следующих шагов: 1. создайте новое представление с именем по вашему усмотрению. 2. Откройте старое представление в конструкторе запросов и представлений. 3. Скопируйте весь текст на панели SQL. 4. Вернитесь к новому представлению и вставьте текст. Внимание! При переименовании представления фрагменты кода и приложения, использующие это представление, могут привести к сбою. Это относится к запросам, представлениям, хранимым процедурам, пользовательским функциям и клиентским приложениям. Учтите, что возникновение ошибок будет происходить каскадно. Прежде чем переименовывать представление, следует хорошо продумать, к чему это может привести. Пример 1. Создание представления Supplier. Визуальными средствами создайте представление Supplier на основе таблиц Suppliers и Goods, определяемое следующей инструкцией: CREATE VIEW Supplier AS SELECT Title, Address, Telephone, Category, Mark, Price, InWarehouse, Expected, MinimalStock, DeliveriesStopped FROM Suppliers, Goods WHERE Goods.CodeSuppliers=Suppliers.CodeSuppliers Предложение WHERE говорит MS SQL Server как соединять строки таблиц между собой: столбец CodeSuppliers таблицы Goods является внешним ключом, который ссылается на столбец Code-Suppliers таблицы Suppliers. Оба столбца являются уникальными и не допускают неопределённых значений (NOT NULL), таким образом, значение столбца CodeSuppliers однозначно идентифицирует строку таблицы Suppliers. Просмотрите результат выполнения одним из вышеописанных способов. Пример 2. Визуальными средствами создайте представление MarkFromGoods на основе следующей инструкции: SELECT Mark FROM Goods WHERE Supplier = (SELECT Supplier FROM Goods WHERE Mark='Lexmark Z35') Пример 3. Визуальными средствами создайте, представление Clients SV Trading на основе нижеследующего запроса, который выбирает название товара и фирмы, заказавшей какой-либо товар фирмы «SV-Trading». SELECT Client, Goods FROM Orders, Ordered WHERE (Goods IN (SELECT Mark FROM Goods WHERE Supplier='SV-Trading')) AND Orders.CodeOrder=Ordered.CodeOrder Пример 4. Визуальными средствами создайте представление ALL_Goods с использованием квантора ALL на основе нижеследующей инструкции: SELECT Supplier, Mark, Price FROM Goods WHERE Price > ALL(SELECT Price FROM Goods WHERE Supplier='CD-Life') 3.2 Соединение таблиц командой JOIN INNER JOIN Внутреннее соединение INNER JOIN производит выборку только записей, которые соответствуют как в таблице А, так и в таблице В. SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name FULL OUTER JOIN SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name Полное внешнее объединение FULL OUTER JOIN производит выборку множество всех записей в таблице А и в таблице B, с соответствующими записями с обеих сторон при их наличии. Если совпадения нет, отсутствует сторона будет содержать NULL. LEFT OUTER JOIN SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name Левое внешнее объединение LEFT OUTER JOIN производит полный набор записей из таблицы А, с соответствующей записи (если таковые имеются) к Таблице B. Если совпадения нет, то правая часть будет содержать NULL. FULL OUTER JOIN с выбором уникальных записей SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null Для получения уникального набора записей в таблице A и в таблице B, мы выполняем полное внешнее соединение FULL OUTER JOIN, исключая отсутствующие записи при полной выборке с обеих сторон. CROSS JOIN Так же существует выборка перекрестного соединения, CROSS JOIN, с перебором все вариантов, которое не объяснить данными диаграммами SELECT * FROM TableA CROSS JOIN TableB Данное перекрестное соединение выбирает буквально "все к всему", в результате мы получим 4 x 4 = 16 записей, намного более чем в оригинале мы имеем в таблицах. Пример. Связать таблицы Client и Orders поля клиент таблицы Client с полями город, страна, телефон таблицы Orders SELECT dbo.Orders.Client, dbo.Clients.City, dbo.Clients.Telephone, dbo.Clients.Country FROM dbo.Clients INNER JOIN dbo.Orders ON dbo.Clients.CodeClient = dbo.Orders.CodeClient 3.3 Cоздание и применение хранимых процедур Создание хранимой процедуры предполагает решение следующих задач: определение типа создаваемой хранимой процедуры: временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера; планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров хранимой процедуры. Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ; разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур. Для создания хранимой процедуры откройте базу Borei, далее перейти к папке программирование, нажать правую контекстную кнопку мыши на Хранимые процедуры, Создать хранимую процедуру, ввести следующий код. Пример. Хранимая процедура вставки в базу Borei таблицы Goods USE [Borei] GO /****** Object: StoredProcedure [dbo].[InsertQuery] Script Date: 10/21/2015 09:42:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertQuery] AS SET NOCOUNT OFF; INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (46, 'Canon', 19, 2, 36.00, 22, 10, 10); 4 Контрольные вопросы 1) Объясните, чем отличается представление от запроса. 2) Для каких целей применяется представление? 3) Укажите порядок создания представления визуальными средствами. 4) Можно ли обновлять данные посредством представлений? 5) Можно ли создать представление на основе нескольких таблиц? ПРИЛОЖЕНИЯ Приложение 1 Сценарий заполнения данными базы данных BOREI Заполнение таблицы "Типы" SET IDENTITY_INSERT Types ON; INSERT INTO Types (CodeType, Category) VALUES (2, 'Принтеры'); INSERT INTO Types (CodeType, Category) VALUES (3, 'Материнские платы'); INSERT INTO Types (CodeType, Category) VALUES (4, 'Процессоры'); INSERT INTO Types (CodeType, Category) VALUES (5, 'Модули памяти'); INSERT INTO Types (CodeType, Category) VALUES (6, 'Видеокарты'); INSERT INTO Types (CodeType, Category) VALUES (7, 'Звуковые карты'); INSERT INTO Types (CodeType, Category) VALUES (8, 'Устройства ввода и указания'); INSERT INTO Types (CodeType, Category) VALUES (9, 'Колонки, наушники, микрофоны'); INSERT INTO Types (CodeType, Category) VALUES (10, 'Корпуса и блоки питания'); INSERT INTO Types (CodeType, Category) VALUES (11, 'Жесткие диски'); INSERT INTO Types (CodeType, Category) VALUES (12, 'Флоппидисководы'); INSERT INTO Types (CodeType, Category) VALUES (13, 'CD-ROM'); INSERT INTO Types (CodeType, Category) VALUES (14, 'Носители'); INSERT INTO Types (CodeType, Category) VALUES (15, 'Модемы'); INSERT INTO Types (CodeType, Category) VALUES (16, 'Сетевое оборудование'); SET IDENTITY_INSERT Types OFF; Заполнение таблицы "Поставщики" SET IDENTITY_INSERT Suppliers ON; INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (3, 'EtelSoft', 'Андрей Герасимов', 'Представитель', '707 Оксфорд', 'Анн-Арбор', '48104', 'США', '(313) 555-5753', '(313) 555-3349'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (4, 'Iven', 'Антон Сеткин', 'Главный менеджер', '9-8 Секимаи', 'Токио', '100', 'Япония', '(03) 3555-5011','(313) 555-3349'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (5, 'SV-Trading', 'Валерия Евенкова', 'Директор', '92 Сетсако', 'Осака', '545', 'Япония', '(06) 431 -7877'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (6, 'CD-Life', 'Наталия Отока', 'Главный менеджер', '74 ул. Роз', 'Мельбурн', '3058', 'Австралия', '(03) 444-2343', '(03) 444-6588'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (7, 'Stepfor', 'Павел Фокин', 'Представитель', 'ул. Королевского пути, 29', 'Манчестер', 'М14 GSD', 'Великобритания', '(161) 555-4448'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (8, 'B.S.T.Group', 'Евгений Шаматранов', 'Продавец', 'Каладоган 13', 'Тетеборг', 'S-345 67', 'Швеция', '031-987 65 43', '031-987 65 9'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (9, 'Biocom', 'Вячеслав Путеев', 'Главный менеджер','ул. Американская 12.890', 'Сан-Паулу', '5442', 'Бразилия', '(11) 555-4640'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (10, 'DAAS', 'Петр Моргунов', 'Менеджер по продажам', 'Тверская 5', 'Москва', '101785', 'Россия', '(095)998-4510' ); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (11, 'Конструктив', 'Федор Куполов', 'Представитель', 'ул. Данте 75', 'Равенна', '48100', 'Италия', '(0544) 60323', '(0544) 60603'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (12, 'Виола-Сервис', 'Виктор Кухарчук', 'Главный менеджер', 'ул. Хатлевеген, 5', 'Сандвикен', '1320', 'Норвегия', '(0)2-953010'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (13, 'Гвин-Медиа', 'Артем Столяров', 'Местный представитель', '3400-8 Авеню', 'Бенд', '97101', 'США', '(503) 555-993');INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (14, 'Senter', 'Александр Осипенко', 'Представитель', 'ул. Бровайдер, 231', 'Стокгольм', 'S-I23 45', 'Швеция', '08-12.3 45 67'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (15, 'Техинтерторг', 'Дарья Борщева', 'Агент по продажам', 'Частный Департамент', 'Бостон', '02134', 'США', '(617) 555-3267', '(617) 555- 3389'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (16, 'Ситипринт', 'Инна Риякевич', 'Совладелец', 'ул. Серашун. 471', 'Сингапур', '0512', 'Сингапур', '555-8787'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax) VALUES (17, 'Юнити Сервис', 'Алексей Жолнсрович', 'Менеджер по продажам', 'ул. Лингбиеилд', 'Лингби', '2800', 'Дания', '43844108', '43844115'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone) VALUES (18, 'ПК Сервис', 'Юрий Бартошек', 'Менеджер по продажам', 'ул. Войрон, 22', 'Монсо', '71300', 'Франция', '85-57-00-07'); INSERT INTO Suppliers (CodeSuppliers, Title, AddressTo, Post, Address, City, IIndex,Country, Telephone, Fax)VALUES (19, 'Астлайн', 'Руслан Сидкж', 'Бухгалтер', 'ул. Чессер, 148', 'Оге-Хиацинте', 'J2S 7S8', 'Канада', '(514) 555-2955', '(514) 555-292 '); SET IDENTITY_INSERT Suppliers OFF; Заполнение таблицы "Клиенты" SET IDENTITY INSERT Clients ON; INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES (1, 'Ovihkc', 'Андрей Ханавин', 'Координатор', 'ул. Бергуса, 8', 'Лулео', 'S-958 22', 'Швеция', '0921-12 34 65', '0921-12'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone) VALUES ('2', 'МАП Инфо', 'Виктория Асворд', 'Представитель', 'ул. Цикровая', 'Лондон', 'ЕС2 5N4T', 'Великобритания', '(171) 555-1212'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('3', 'Белкантон', 'Андрей Карпухин', 'Главный менеджер', 'ул. Гарсиа, 9993', 'Мехико', '05022', 'Мексика', '(5) 555-3392', '(5) 555-7293'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City,IIndex, Country, Telephone) VALUES ('4', 'Дамодара-Сервис', 'Лидия Кулаева', 'Совладелец', 'ул. Эдальго, 29', 'Берн', '3012', 'Швейцария', '0452-076545'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('5', 'E-Life', 'Роланд Мендель', 'Менеджер по продажам', 'Vi. Кировская. 6', 'Трасс', '8010', 'Авария', '7675-3425', '7675-3426'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone) VALUES ('6', 'IMC Computers', 'Мария Ларсон', 'Совладелец', 'ул. Ксргатая. 24', 'Nрекке', 'S-844 67', 'Швеция', '0695-34 67 2'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('7', 'Верса', 'Питер Франкен', 'Главный менеджер', 'Берлинская пл., 43', 'Мюнхен', '80805', 'Термания', '089-0877310', '089-087745'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('8', 'Comtris', 'Мария Хосе', 'Совладелец', 'ул. Палое. 5S', 'Каракас', '108', 'Венесуэла', '(2) 283-295 Г', '(2) 283-3397'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('9', 'Net Line', 'Марио Ионтес', 'Бухгалтер', 'ул. Ракко, 67','Рио-де- Жанейро', '05454-876', 'Бразилия', '(21) 555-009Г', '(21) 555-8765'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('10', 'NTTs', 'Карлос Хемандос', '11релсгавигель', 'ул. Карлос, 22', 'Сан-Кристобаль', '5022', 'Венесуэла', '(5) 555-1340', '(5) 555-1948'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, Country, Telephone, Fax) VALUES ('11', 'Интеллекте', 'Патрисия Кемма', 'Ученик продавца', 'Джонстоун шоссе, 8', 'Корк', 'Ирландия', '2967 542', '2967 3333'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('12', 'ZS', 'Максим Анищенко', 'Бухгалтер', 'Бодиварская, 52', 'Bаркисимею', '3508', 'Венесуэла', '(9) 331-6954', '(9) 331-7256'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('13', 'Медиа-софт', 'Джовани Ровелли', 'Главный .менеджер', 'ул. Людовика. 22', 'Бергамо', '24100', 'Италия', '035-640230', '035-640231'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('14', 'Эликон-М', 'Александр Боровик-', 'Представитель', 'ул. Беринговая. 2743', 'Анкоридж', '99508', 'США', '(907) 555-7584', '(907) 555- 2880'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone) VALUES ('45', 'Stop', 'Игнат Довидовский', 'Бухгалтер', 'Тачерстрасе, 10', 'Ккжевальд', '01307', 'Германия', '0372-035 188'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('16', 'Соло', 'Андрей Савельев', 'Ученик продавца', 'ул. Провинциальная, 124', 'Реджио-Эмилио', '42100', 'Италия', '0522-556721', '0522- 556722'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('17', 'Olymp', 'Юрий Макаров', 'Бухгалтер', 'ул. Виа, Г', 'Мадрид', '28001', 'Испания', '(91) 745 6200', '(91) 745 6210'); INSERT INTO Clients (CodeClient, Title, AddressTo, Post, Address, City, IIndex, Country, Telephone, Fax) VALUES ('18', 'Oki', 'Александр Гронский', 'Представитель', 'ул. Кастро. 414', 'Сан-Паулс', '05634-030', 'Бразилия', '(11) 555-2167', '(11) 555-2168'); IDENTITY. INSERT Clients OFF; Заполнение таблицы "Доставка" INSERT INTO Deliveries (CodeDelivery, Title, Telephone) VALUES (1, 'Ространс', '(017) 972-9831'); INSERT INTO Deliveries (CodeDelivery, Title, Telephone) VALUES (2, 'Почта', '(017) 124-3199'); INSERT INTO Deliveries (CodeDelivery, Title, Telephone) VALUES (3, 'Иное', '(017)211-9931'); Заполнение таблицы "Сотрудники" SET IDENTITY INSERT Employees ON; INSERT INTO Employees (CodeEmployee, SurName, Name, Post, Reference, DateBirth, DateHiring, Address, City, IIndex, Country, HomeTelephone, Additional, Submits) VALUES (2, 'Белова', 'Мария', 'Представитель', 'г-жа.', '08.12.1968', '01.05.1992', 'ул. Нефтяников, 14-4', 'Минск', '122981', 'Беларусь', '(017) 555-9857', '124-5467', 'Новиков, Павел'); INSERT INTO Employees (CodeEmployee, SurName, Name, Post, Reference, DateBirth, DateHiring,Address, City, IIndex, Country, HomeTelephone, Additional, Submits) VALUES (8, 'Крылова', 'Анна', 'Внутренний координатор', 'г-жа.', '09.01.1958', '05.03.1994' , 'ул. Лесная. 12-456','Минск', '105001', 'Беларусь', '(017)555-1189', '124-2344', 'Кротов. Андрей'); SET IDENTITY, INSERT Employees OFF; Заполнение таблицы "Товары" INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (1, 'Genius SP-G06', 8, 9, 9.00, 15, 10, 8); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (2, 'Наушники Dialog М-750НУ+микрофон', 8, 9, 9.00, 15, 10, 8); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (3, 'ATX 2.03 300W', 19, 10, 35.00, 15, 5, 10); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (4, 'ATX Midi Tower 350W', 7, 10, 55.00, 5, 8, 21); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (5, 'Блок питания ATX 235/250/300W', 5, 10, 14.00, 20, 10, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (6, 'HDD Maxtor 30Gb', 16, 11, 69.00, 10, 2, 8); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (7, 'HDD Seagate Baracuda 4 40Gb', 3,11, 87.00, 8, 4, 12); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (8, 'HDD IBM 60Gb', 13, 11, 9.00, 5, 6, 8); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (9, 'T E A C', 4, 12, 9.00, 15, 10, 10); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected) VALUES (11, 'Samsung 52x', 8, 13, 23.00, 5, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse) VALUES (12, 'TEAC 52x CD-522E', 3, 13, 42.00, 7); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse) VALUES (13, 'CD-RW/ROM TEAC', 3, 13, 42.00, 7); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (14, 'Verbatim', 3, 14, 0.28, 300, 100, 50); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (15, 'CD-R TDK 12x', 19, 14, 0.62, 200, 100, 50); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse) VALUES (16, 'ACORP 56EMS. USB', 5, 15, 40.00, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected) VALUES (17, 'HUB 10/t00Mbit', 3, 16, 32.00, 10, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse) VALUES (18, 'ACORP 56EMS. USB', 5, 15, 40.00, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected) VALUES (19, '3COM SOHO 100TX', 10, 16, 22.00, 15, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected) VALUES (20, 'HUB 10/t00Mbit', 3, 16, 32.00, 10, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (21, 'Switch 10/100Mbit', 13, 16, 32.00, 10, 10, 5); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (22, 'Кабель "витая пара" UTP cat.5', 4, 16, 0.18, 100, 100, 20); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (23, 'Canon LBP810', 3, 2, 185.00, 39, 5, 7); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (24, 'Accorp VlA266i815D', 7, 3, 61.00, 29, 10); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, DeliveriesStopped) VALUES (25, 'TNT2 M64 32Mb', 7, 6, 28.00, 'YES'); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (26, 'DDR 128Mb', 9, 5, 38.00, 34, 25); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, DeliveriesStopped) VALUES (27, 'Samsung 550B', 10, 2, 150.00, 20, 'YES'); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (28, 'Gigabyte GA-60XTA', 11,3, 64.00, 76, 30); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, DeliveriesStopped) VALUES (29, 'Creative Labs SB Life', 12, 7, 9.00, 26, 31); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (30, 'Mouse Logitech B69', 13, 8, 15.00, 10, 15); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse) VALUES (31, 'Epson EPL520', 6, 2, 90.00, 76); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (32, 'Celeron 1000MHz', 15, 4, 51.00, 26, 15); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, Expected, MinimalStock) VALUES (33, 'Intel P4 1700MHz Box', 14, 4, 137.00, 70, 20); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, Expected, InWarehouse, MinimalStock) VALUES (34, 'AMD Athlon 1333MHz', 6, 4, 53.00, 9, 40, 25); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (35, 'Samtron 76E', 16, 2, 180.00, 11, 15); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (36, 'Коврики пластиковые', 17, 8, 0.50, 112, 20); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (37, 'Chicony PS/2 820 2981', 19, 8, 6.00, 23, 30); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, DeliveriesStopped) VALUES (38, 'SDRAM 128Mb', 18, 5, 19.00, 26, 'YES'); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (39, 'Asus A7S333', 6, 3, 86.00, 35, 30); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (40, 'Creative Labs SB Life 5.1+FM radio', 3, 7, 39.00, 10, 10); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (41, 'GeForce 2 MX 400 64Mb', 3, 6, 36.00, 10, 15, 24); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (42, 'DIMM PC 133 128Mb Micron', 3, 5, 15.00, 21,10, 30); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, Expected, MinimalStock) VALUES (43, 'AND Duron 1000MHz', 9, 4, 36.00, 22, 10, 10); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, MinimalStock) VALUES (44, 'Lexmark Z35', 5, 2, 62.00, 12, 25); INSERT INTO Goods (CodeGoods, Mark, CodeSuppliers, CodeType, Price, InWarehouse, DeliveriesStopped) VALUES (45, 'TV-tuner Aver MediaTV Studio', 4, 6, 75.00, 29, 'YES'); SET IDENTITY_INSERT Goods OFF; Заполнение таблицы "Заказы" SET IDENTITY_INSERT Orders ON; INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11010, 16, 2, '01.05.1992', '05.03.1994', '01.05.1992', 2, 28.70, 'Соло', 'Тачеретрасс. 10', 'Лондон', 'WX3 6FW', 'Великобритания'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11011, 6, 8, '01.05.1992', '05.03.1994', '01.05.1992', 1, 1.21, 'IMC- Computers', 'ул. Мойте, 34', 'Берлин', '12209', 'Германия'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11012, 7, 2, '01.05.1992', '05.03.1994', '01.05.1992', 3, 243.00, 'Bepca', 'ул. Джардем, 32', 'Мюнхен', '80805', 'Германия'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11013, 17, 8, '01.05.1992', '05.03.1994', '01.05.1992', 1, 33.00, 'Olymp', 'ул.Каталаяа. 23', 'Мадрид', '2800Г', 'Испания'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11014, 14, 2, '01.05.1992', '05.03.1994', '01.05.1992', 3, 23.60, 'Эликон- М', 'ул. Беговая, 7', 'Бранденбург', '14776', 'Германия'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11015, 45, 8, '01.05.1992', '05.03.1994', '01.05.1992', 2, 4.62, 'Интеллекте', 'Бразильская пл. 442', 'Ставерен', '4110', 'Норвегия'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11016, 7, 2, '01.05.1992', '05.03.1994', '01.05.1992', 2, 33.80, 'Bepca', 'ул. Джардем. 32', 'Мюнхен', '80805', 'Германия'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11017, 5, 8, '01.05.1992', '05.03.1994', '01.05.1992', 2, 754.00, 'E-Life', 'ул. Мерхеместа, 369', 'Юджин', '97403', 'США'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11018, 16, 2, '01.05.1992', '05.03.1994', '01.05.1992', 2, 11.70, 'Соло', 'Тачерстрасс, 10', 'Лондон', 'WX3 6FW', 'Великобритания'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES(11019, 3, 8, '01.05.1992', '05.03.1994', '01.05.1992', 3, 3.17, 'Белкантон', 'ул. Карлос, 22', 'Буэнос-Айрес', '1010', 'Аргентина'); INSERT INTO Orders (CodeOrder, CodeClient, CodeEmployee, DateAccomodation, DatePurpose, DateExecution, CodeDelivery, CostDelivery, TitleAddressee, AddressAddressee, CityAddressee, IndexAddressee, CountryAddressee) VALUES (11020, 9, 8, '01.05.1992', '05.03.1994', '01.05.1992', 2, 43.30, 'Net Line', 'ул. Кэш. 32 Г', 'Сан-Пауло', '05432-043', 'Бразилия'); SET IDENTITY_INSERT Orders OFF; Заполнение таблицы "Заказано" INSERT INTO Ordered (CodeOrder, CodeGoods, Goods, Discount) VALUES (11010,32, 5, 0.15); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11010, 44, 4); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11011, 16, 30); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11011, 42, 8); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11011, 43, 2); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11012, 9, 14); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods, Discount) VALUES (11012, 26, 9, 0.05); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11013, 7, 9); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11013, 21, 10); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods, Discount) VALUES (11014, 2, 16,0.1); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods, Discount) VALUES (11015, 6,2, 0.15); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11016, 16, 40); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES ( 11017, 12, 12); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods) VALUES (11018,14, 80); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods, Discount) VALUES (11019, 25, 7,0.25); INSERT INTO Ordered (CodeOrder, CodeGoods, Goods, Discount) VALUES (11020, 30, 6, 0.15); Приложение 2 Сценарий обновления данных в базе данных BOREI UPDATE Goods SET Category=(SELECT Category FROM Types WHERE Goods.CodeType=Types.CodeType); UPDATE Orders SET Client=(SELECT Title FROM Clients WHERE Orders.CodeClient=Clients.CodeClient); UPDATE Orders SET Employee=(SELECT Surname +',' + Name FROM Employees WHERE Orders.CodeEmployee=Employees.CodeEmployee); UPDATE Orders SET Orders.Delivery=(SELECT Title FROM Deliveries WHERE Orders.CodeDelivery=Deliveries.CodeDeliveiy); UPDATE Ordered SET Ordered.Goods=(SELECT Mark FROM Goods WHERE Ordered. CodeGoods=Goods.CodeGoods); UPDATE Ordered SET Ordered.Price=(SELECT Price FROM Goods WHERE Ordered.CodeGoods=Goods.CodeGoods); |