Технологии обработки экономической информации в среде ТП MS Excel

 

     Таблица Сведения

      2. С помощью  конструктора СУБД MS Access создадим две таблицы: таблицу с именем Справочник и таблицу с именем Сведения как указано на рисунках ниже. Определим типы данных каждого поля. 

      В таблице Справочник:

     поле [Код изделия] определим целым типом,

     поле [Наименование изделия] - символьным типом с размером 100 символов.

     поле [Код изделия] определим ключевым. 

     Рис. 1 - Таблица Справочник в режиме конструктора СУБД ACCESS 

      В таблице Сведения:

     поле  [Код изделия] определим целым типом,

     поле [Наименование предприятия]  - символьным типом с размером 100  символов,

     поля [План поставок, млн руб], [Фактически поставлено, млн руб], [Отклонение от плана, %] - вещественным типом.  

     Рис. 2 - Таблица Сведения в режиме конструктора СУБД ACCESS 

      

• Команда CREATE TABLE, определяющая структуру таблицы Справочник, на языке SQL ANSI имеет вид:

 

CREATE TABLE Справочник

([Код  изделия] INT CONSTRAINT Ключ PRIMARY KEY,

[Наименование  изделия] CHAR(100)); 

      

• Команда CREATE TABLE, определяющая структуру таблицы Сведения, на языке SQL ANSI имеет вид:

 

CREATE TABLE Сведения

([Код  изделия] INT,

[Наименование  предприятия] CHAR(50), 

[План  поставок, млн р] REAL,

[Фактически  поставлено, млн р] REAL,

[Отклонение от плана, %] REAL); 

      3. В режиме таблицы  СУБД ACCESS заполним таблицы конкретными значениями данных, исходя из их смысла. Поле, помеченное знаком* ([Отклонение от плана, %]), оставим незаполненным. В результате таблицы приобретут вид,  как показано на стр. 10 

      

• Команда заполнения базы данными INSERT INTO (для двух записей таблицы Справочник), записанная на языке SQL ANSI, имеет вид:

INSERT INTO Справочник  VALUES (1, “Шпроты в масле”);

INSERT INTO Справочник VALUES   (2, “Скумбрия в масле”); 

      

• Команда заполнения базы данными INSERT INTO (для двух записей таблицы Сведения), записанная на языке SQL ANSI, имеет вид:

 

INSERT INTO Сведения

([Код  изделия], [Наименование предприятия], [План поставок, млн руб], [Фактически поставлено, млн руб])

VALUES (3, ”ООО Золотая рыбка”, 10, 10);

INSERT INTO Сведения

([Код  изделия], [Наименование предприятия], [План поставок, млн руб], [Фактически поставлено, млн руб])

VALUES (4, ”ЗАО Инфудс ”, 190, 200); 

      4. Для того, чтобы с таблицами можно было работать как с единым целым, свяжем их, пользуясь инструментом Схема данных. Исходя из смысла базы данных, связь должна быть установлена по полю  [Код изделия] таблицы Справочник и полю  [Код изделия] таблицы Сведения (рис. 3). Это связь вида один ко многим, так  как одной записи таблицы Справочник может соответствовать несколько записей таблицы Сведения. 

Рис.3 –  Схема данных 
 

5. Составим  запросы к базе данных и реализуем их в СУБД Access:

 

      Запрос 1. Рассчитать значение поля [Отклонение от плана, %].  

      Значение  этого поля рассчитывается по формуле: 

[Отклонение  от плана, %] = [Фактически поставлено, млн руб]/

[План  поставок, млн руб]*100-100;

      Это запрос на обновление. Для его реализации необходимо активизировать вкладку Запросы ==> Создать ==> Конструктор==>  Меню Запрос ==> Обновление  ==> SQL. В окне SQL (рис.4) ввести текст запроса: 

      

Рис.4 –  Окно запроса на обновление 

      Затем выполнить его, нажав соответствующую кнопку на пиктографическом меню. В результате поле [Отклонение от плана, %] таблицы Сведения будет рассчитано в соответствии с введенной формулой (рис. 5). 

Рис.5 –  Таблица Сведения после выполнения запроса на обновление 

      Запрос 2.

      Показать  поставки с перевыполнением плана  более чем на 5%. Упорядочить по росту процента выполнения плана.  

      Текст запроса на SQL:

SELECT [Наименование предприятия], [Наименование изделия],                              

[План  поставок, млн руб], [Фактически поставлено, млн руб],

[Отклонение  от плана, %]

FROM Справочник, Сведения

WHERE Справочник.[Код изделия]=Сведения.[Код изделия]

AND ([Отклонение от плана, %]>5)

ORDER BY [Отклонение от плана, %];

     В результате выполнения запроса получим  таблицу:

 

      Создаем запрос в конструкторе.

 

      Запрос 3.

      Показать  поставки с фактической стоимостью товара от 40 до 90 млн. руб., упорядочив по росту стоимости.  

      Текст запроса на SQL:

SELECT [Наименование изделия], [Наименование предприятия],

[Фактически поставлено, млн руб]

FROM Справочник, Сведения

WHERE Справочник.[Код изделия]=Сведения.[Код изделия]

AND ([Фактически поставлено, млн руб] Between 40 And 90)

ORDER BY [Фактически поставлено, млн руб]; 

      В результате выполнения запроса получим  таблицу:

 

      Создаем запрос в конструкторе:

 

      Запрос 4.

      Показать  плановую и фактическую стоимость  по поставкам шпрот обществами с ограниченной ответственностью.  

      Текст запроса на SQL:

SELECT [Наименование изделия], [Наименование предприятия],

[План поставок, млн руб], [Фактически поставлено, млн руб]

FROM Справочник, Сведения

WHERE Справочник.[Код изделия] = Сведения.[Код изделия]

AND ([Наименование изделия] Like ("Шпроты*"))

AND ([Наименование предприятия] Like ("ООО*")); 

      В результате выполнения запроса получим  таблицу: