Работа в Oracle Database Express Edition

 

В результате в таблице Детали заказов (TABLE_ Detali_zakazov) будут заполнены следующие поля:

 и т.д., в зависимости от  количества введенных данных.

Все поля таблицы Заказы (TABLE_ZAKAZ) будут заполнены.

 

Примечание. При повторном запуске программы, она добавляет новые данные к уже существующим данным в таблице заказов. Если необходимо заново сформировать таблицу заказов, то необходимо сначала удалить из нее все записи с помощью запроса на удаление (См. ниже).

 

4. Следующим действием необходимо в таблице Детали заказов заполнить столбец Цена за единицу (Cena_za_ed), перенеся данные из таблицы Товары (Tovary). Будем считать цены за единицу товара в таблице Товары оптовыми, а розничные цены установим на 20% выше оптовых. Вычислим значение розничных цен в таблице Детали заказов (TABLE_ Detali_zakazov).

Для этого необходимо выполнить следующий запрос.

• Вкладка Запросы®Создать®Конструктор. Из списка выбрать таблицу Tovary.
• Кнопка Закрыть.
• На панели инструментов выбрать Запрос®Обновление.
• На панели инструментов выбрать Вид®Режим SQL.
• Записать запрос:

UPDATE TABLE_Detali_zakazov INNER JOIN Tovary ON TABLE_Detali_

zakazov.ID_tovara=Tovary.ID_tovara SET TABLE_Detali_zakazov.Cena_za_ed = Tovary!Cena_za_ed*1.2;

• Выполнить запрос, нажать кнопку на панели инструментов: Запуск !
• Сохранить запрос под названием Запрос на обновление-Цена за ед.

 

• Запрос на вычисление Цены_Всего (Vsego_cena):

1. вкладка Запросы/Создать/В режиме конструктора.

2. добавить таблицу TABLE_Detali_zakazov.
3. щелкнуть правой кнопкой на поле: Тип запроса/Обновление.

4. В графу Поле вставить поле Vsego_cena, щелкнув два раза по имени поля в таблице.
5. В строке Обновление щелкнуть правой кнопкой мыши, выбрать Построить.
6. В открывшемся окне Построитель выражений выбрать Таблицы/ TABLE_Detali_zakazov/ Cena_za_ed, выбрать операцию умножения - (* ) ,  далее выбрать Kolichestvo и нажмите ОК.

 

В окне построителя выражение будет  выглядеть следующим образом:

 

TABLE_Detali_zakazov!Cena_za_ed*TABLE_Detali_zakazov!Kolichestvo

 

7. Сохраните запрос Цена всего.
8. В режиме SQL запрос будет выглядеть следующим образом:

 

UPDATE TABLE_Detali_zakazov SET TABLE_Detali_zakazov.Vsego_cena = TABLE_Detali_zakazov!Cena_za_ed*TABLE_Detali_zakazov!Kolichestvo;

 

Примечание: Для переключения в  режим SQL надо открыть запрос в конструкторе, потом нажать кнопку меню Вид\SQL.

 

В результате этого запроса будет  вычислено и заполнено данными  поле Vsego_cena.

 

5. Добавить кнопку для внесения данных в поле «Skidka» таблицы TABLE_Detali_zakazov из таблицы Table_Skidka.

Для этого открыть форму в режиме Конструктора, добавить из панели элементов кнопку, она будет иметь имя Кнопка1,  в окне редактора Visual Basic набрать следующую программу:

 

Листинг программы для кнопки  Расчет скидки, для таблицы (TABLE_Detali_zakazov):

 

' Программа 2.

Private Sub Кнопка1_Click()

Dim dbs As Database, r1 As Recordset, r2 As Recordset

Dim Skidki(2, 10) As Integer

Set dbs = CurrentDb

Set r1 = dbs.OpenRecordset("TABLE_Detali_Zakazov")

Set r2 = dbs.OpenRecordset("Table_Skidka")

n = r2.RecordCount 'Количество строк в таблице Скидки

'nzz = InputBox("n=" + Str(n))

r2.MoveFirst

k = 1

While k <= n

Skidki(1, k) = r2!kolich_tovara

Skidki(2, k) = r2!Skidka_v_procent

If k < n Then r2.MoveNext

k = k + 1

Wend

Skidki(1, k) = 1000 'предельное значение для кол.товара

r1.MoveFirst

Do While Not r1.EOF ' Цикл по табл.Заказы

z = r1!Kolichestvo

k = 1

While (z > Skidki(1, k)) And (k < n)

    k = k + 1

Wend

s = (r1!Vsego_cena) * Skidki(2, k) \ 100

r1.Edit

r1!Skidka = s

r1.Update

r1.MoveNext

Loop

End Sub

______________________________

• Закрыть редактор VBA, закрыть конструктор формы и запустить форму.

 

• Запрос на обновление: вычисление величины цены товара со скидкой (с помощью SQL или в режиме Конструктора с помощью Построителя):

 

UPDATE Table_Detali_zakazov SET Cena_so_skidkoi = Vsego_cena-Skidka;

(в каждой строчке поля  Cena_so_skidkoi будет вычислена разность полной цены и скидки).

 

6. Выполнить запросы на удаление.
• Запрос на удаление данных из таблицы TABLE_ZAKAZ:

 

• Запрос®Конструктор® TABLE_Zakaz. Правой кнопкой мыши щелкнуть по полю и выбрать Вид запроса®Удаление.
• Вид®Режим SQL.

 

DELETE *

FROM TABLE_Zakaz;

 

• Запрос на удаление данных из таблицы TABLE_Detali_zakazov:

DELETE *

FROM TABLE_Detali_Zakazov;

 

7. Добавить в таблицу TABLE_ZAKAZ новое поле ИТОГО, в котором надо рассчитать общую стоимость всего заказа. Для этого надо выбрать данные из таблицы TABLE_Detali_zakazov (объединить данные, относящиеся к одному заказу, из таблицы деталей заказов, и найти сумму со скидкой для всех товаров, входящих в заказ).

 

Подсказка:  Задайте структуру временной таблицы Temp c полями: Id_zakaza, Itogo, куда с помощью запроса перемести суммарные данные из таблицы TABLE_Detali_zakazov c помощью запроса с группировкой данных по полю Id_zakaza:

INSERT INTO Temp ( Id_zakaza, Itogo )

SELECT id_zakaza, Sum(Cena_so_skidkoi)

FROM TABLE_Detali_zakazov

GROUP BY Id_zakaza;

 

Потом  выполните еще один запрос на обновление для переноса значения из поля Итого (Itogo) таблицы Temp  в аналогичное поле таблицы Table_Zakaz.

 

III. Формирование данных для филиала 2.

 

После выполнения п.7 будет  сформировано две таблицы фактов, содержащие столько записей, сколько было указано заказов при запуске первого сценария (кнопка 0). Однако обе таблицы содержат данные, относящиеся только к одному филиалу.

Необходимо дополнить  полученные таблицы данными о  продажах во втором филиале. Для выполнения этого необходимо повторить все указанные операции, перечисленные в части II лабораторной работы, для второго филиала. Чтобы номера заказов, выполненные во втором филиале, не совпадали с номерами заказов, необходимо, чтобы поле Id_zakaza в обеих таблицах фактов начинались с числа, превышающего число записей в таблице Table_Zakaz. Для этого необходимо:

• добавить кнопку для формирования заказов в филиале 2;
• скопировать листинг Программы 1.
• в программе параметр i изменить на i+1000.

 

 

Лабораторная работа № 7.

Оценка эффективности  работы филиалов

 

Задание. Импортировать все таблицы, созданные в лабораторной работе №6, в среду Oracle.

!. Имя пользователя должно совпадать с фамилией студента.

Запустить Oracle Database 10g Express Edition ® Oracle Database Home Page, вкладка SQL SQL Commands. Выполнить запросы к созданной БД с целью оценки эффективности работы филиалов.

 

Перечень SQL запросов, необходимых  для выполнения работы:

 

1. Подсчитать общую сумму заказов по годам для каждого из филиалов.
2. Подсчитать прибыль предприятия по годам для каждого из филиалов. Для этого, учитывая разницу между оптовыми и розничными ценами подсчитать сначала разницу между заработанными и затраченными средствами, потом вычесть издержки, связанные с зарплатами продавцов. Считать зарплату продавцов одинаковой и равной 10000 руб. в мес. Количество продавцов определяется таблицами Продавцы филиалов.
3. Выбрать лучший филиал по общей выручке за 2008 год.
4. Определить лучшего продавца по каждому из филиалов за 2008 год.
5. Подсчитать среднюю выручку на одного продавца в каждом из филиалов.
6. Определить 3 самых ходовых товара по каждому из филиалов и по всему предприятию в целом.
7. Определить самые плохо продаваемые товары за 2008 год.
8. Сделать выводы и выработать рекомендации о развитии продаж в будущем году.
9. Сохраните весь набор полученных SQL-запросов в один файл.
10. Выполнить письменный отчет по всей работе, включив данные (скриншоты) базовых таблиц, тексты запросов, результаты выполнения.