Создание и заполнение базы данных "Агент", страница 3

  produce_id – идентификатор для связи с таблицей “producers”

weight – вес товара

outputdate – дата выпуска товара

Структура таблицы Производители" (producers.dbf)

Где

produce_id – идентификатор для связи с таблицей “goods”

producer – производитель товара

Структура таблицы "Поставщики" (supplers.dbf)

Где

suppler_id – идентификатор для связи с таблицей “goods”

suppler – поставщик товара

Структура таблицы  “место продажи" (sale_place.dbf)

Где

place_id – идентификатор для связи с таблицей “agent”

sale_place– место продажи

Тексты программ

1.  Сведения о товаре

select goods_name, model, suppler, producer, weight, outputdate;

                        sale_date, cost, num;

                        from agent;

                           inner join goods on goods.goods_id=agent.goods_id;             

                           inner join producers on producers.produce_id=goods.produce_id;

                           inner join supplers on supplers.suppler_id=goods.suppler_id;

2.  Все сведения об агентах:

select distinct surname, nname, secondname, workperiod,;

rateofpay, street, house_n, flat_n, phone, mob_phone;

from agent;

inner join agent_info on agent.agent_id=agent_info.agent_id;

3.  Отсортированный список товара для каждого из агентов

select goods_name, model, suppler, producer, weight, outputdate, cost ,sale_date, num, sale_place;

                  from agent;

                     inner join goods on agent.goods_id=goods.goods_id;

                     inner join producers on producers.produce_id=goods.produce_id;

                     inner join supplers on supplers.suppler_id=goods.suppler_id;

                     inner join sale_place on agent.place_id=sale_place.place_id;

                     where sale_place=thisform.sale_place.value;

                     into cursor cur_sort               

DO CASE

      CASE Thisform.sort_goods.Value = 1

                  index on outputdate to cur_sort

                  browse

      CASE Thisform.sort_goods.Value  = 2

                  index on model to cur_sort

                  browse

      CASE Thisform.sort_goods.Value  = 3

                  index on weight to cur_sort

                  browse

      CASE Thisform.sort_goods.Value  = 4

                  index on cost to cur_sort

                  browse

      CASE Thisform.sort_goods.Value  = 5

                  index on sale_date to cur_sort

                  browse

      CASE Thisform.sort_goods.Value  = 6

                  index on num to cur_sort

                  browse

ENDCASE

4.  Все  товары с заданной датой выпуска по заданному агенту и стоимости

select surname, goods_name, model, suppler, producer, weight, outputdate, cost, num;

                  from agent;

                     inner join goods on goods.goods_id=agent.goods_id;             

                     inner join producers on producers.produce_id=goods.produce_id;

                     inner join supplers on supplers.suppler_id=goods.suppler_id;

                                                     where year(outputdate)=thisform.yy1.value AND;

                                                                   month(outputdate)=thisform.mm1.value AND;

                                                                   day(outputdate)=thisform.dd1.value AND;

                                                                   surname=thisform.enter_agent.value AND;

                                                                   cost>=thisform.cost3.value AND;

                                                                    cost<=thisform.cost4.value

5.  Макс, мин, средняя стоимость товара по  заданному агенту и в целом.