Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Министерство образования и науки Российской Федерации

НОВОСИБИРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

ЭКОНОМИКИ И УПРАВЛЕНИЯ «НИНХ»

Институт Прикладной информатики

Кафедра Экономической информатики

КУРСОВОЙ ПРОЕКТ

Разработка базы данных оптового магазина

Наименование направления

Прикладная информатика (в экономике)

Ф.И.О. студента: Колесникова Анастасия Александровна

Новосибирск 2013

Содержание

• Введение
• 1. Описание предметной области
• 2. Концептуальная модель
• 3. Реляционная модель базы данных
• 4. Модель данных в SQL Server 2008
• 5. Создание и заполнение таблиц
• 6. Структура таблиц и данные в таблицах
• 7. Объекты базы данных
• 7.1 Запросы
• 7.2 Процедуры
• 7.3 Представления
• 7.4 Триггеры
• 7.5 Курсор
• Заключение
• Список использованных источников
• Введение
• В настоящее время существует большое количество оптовых магазинов. Поддержка деятельности оптового магазина с целью автоматизировать слежение за процессами движения товара до покупателя. Информация о клиентах и товаре заносится в БД. При получении нового товара, ему присваивается свой индивидуальный код, в базе данных регистрируется следующая информация: код товара, наименование товара, количество, цена и т.д.

Цель данного курсового проекта: Приобретение практических навыков в Microsoft SQL Server, создавать и заполнять таблицы, разрабатывать представления и запросы.

Для создания базы данных используется программный продукт Microsoft SQL Server 2008 R2.

К задачам, поставленным в данной работе, относятся:

1) изучение процесса работы оптового магазина;

2) анализирование процесса составления графика поставок и продаж;

3) создание, на основе информации, полученной в ходе анализа, базы данных, хранящей информацию обо всех товарах, поставщиках и потребителях; датах приёма заказа, количестве товаров; а также вся информация о менеджерах, комплектующих, и, занимающихся этим заказом; график планируемых поставок и продаж.

1. Описание предметной области

Оптовый магазин косметики - это компания, занимающаяся оптовой продажей различных видов косметики. В данном случае это склад, на который поступает товар, составляется информация о нем и после отпускается на продажу.

В этом курсовом проекте будет рассматриваться процесс автоматизации продажи косметики, заказа и поставки. Данный магазин представляет наиболее известные, а также ведущие компании производящие косметику таких как: Christian Dior, Clinique, Davidoff, Dolce&Gabbana и др.

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

Организационная структура данного магазина представлена на рисунке 1.



Рисунок 1 - Организационная структура магазина

Опишем организационную структуру. В состав компании входят отделы бухгалтерии, продаж, транспортного отдела. В каждом отделе есть свой руководитель с определенным количеством подчиненных сотрудников. Во главе магазина находится директор, который следит за исполнительной деятельностью всех отделов. В данном курсовом проекте для автоматизации во внимание берутся специалисты отдела продаж, на которых ложится обязанность максимизировать прибыль посредством продажи косметики: менеджеры по продажам, руководитель отдела продаж. Менеджер по продажам занимается консультированием клиентов и непосредственно самой продажей косметики.

Оформление заказа осуществляет менеджер по продажам. В заказе указывает характеристику продукции и проверяет её наличие.

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

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

После выбора, клиент обращается к менеджеру по продажам. Менеджер по продажам оформляет заказ и выбивает чек, в котором указан бренд, средство, цена, дата продажи, сумма к оплате.

В случае, если в магазине не окажется того или иного средства или же не будет нужного количества, то клиент может оформить заявку.

2. Концептуальная модель

Для создания базы данных оптового магазина первоначально следует создать концептуальную модель. Концептуальная модель описывает сущности, атрибуты и связи между ними [4, стр.5].

Концептуальная модель базы данных оптового магазина приведена на рисунке 2.

Рисунок 2 - Концептуальная модель

Опишем данную концептуальную модель. Имеется поставщик (ПОСТАВЩИК), который поставляет товар (ТОВАР) и покупатель (ПОКУПАТЕЛЬ), который, непосредственно и покупает этот товар. Процесс совершение покупки осуществляется через менеджера (МЕНЕДЖЕР), который оформляет этот заказ и у которого есть все данные, как о поставщике, так и о товаре.

3. Реляционная модель базы данных

Реляционная модель представляет собой базу данных в виде множества взаимосвязанных отношений. В каждой связи одно отношение может выступать как основное, а другое отношение выступает в роли подчиненного [2, стр.28]. Таким образом, один кортеж основного отношения может быть связан с несколькими кортежами подчиненного отношения. Для поддержки этих связей оба отношения должны содержать наборы атрибутов, по которым они связаны [2,стр.30].

Представим нашу базу данных оптового магазина в виде реляционной модели путем указания названия таблицы и перечисления названий её столбцов (ключевые названия столбцов подчеркиванием):

1. ПОСТАВЩИК (код поставщика, наименование, код города, адрес, телефон)

2. ТОВАР (Код товара, название, количество, цена)

3. ПОКУПАТЕЛЬ (код покупателя, наименование, код города, адрес, телефон)

4. МЕНЕДЖЕР (код менеджера, ФИО менеджера, телефон, дата рождения)

5. ПОСТАВКА (код поставки, дата поставки, количество, код поставщик_товар)

6. ПРОДАЖА (код продажи, дата продажи, количество, код товар_покупатель)

7. ГОРОД (код города, название города)

8. ПОСТАВЩИК_ТОВАР (код поставщик_товар, код поставщика, код товара)

9. ТОВАР_ПОКУПАТЕЛЬ (код товар_покупатель, код товара, код покупателя)

10. ПРОДАЖА_МЕНЕДЖЕР (код продажа_менеджер, код менеджера, код продажи)

4. Модель данных в SQL Server 2008

Для реализации данной базы данных мы возьмем СУБД (Система управления базами данных) Microsoft SQL Server 2008.

Модель данных позволяет проследить связи между созданными в SQL Server 2008 таблицами. Благодаря таким связям может быть выполнен любой запрос на поиск нужной информации. Модель данных в SQL Server 2008 приведена на рисунке 3.

Рисунок 3 - Модель данных в SQL Server 2008

Модель данных базы данных оптового магазина в SQL Server 2008 представляет собой связанные, нормализованные до 3 нормальной формы таблицы с указанием ключевых полей.

5. Создание и заполнение таблиц

Создание базы данных в SQL Server производится при помощи оператора Create database. Для указания, с какой базой мы работаем используется оператор Use. Таблицы базы данных создаются с помощью команды CREATE TABLE. Значения в эту таблицу вводятся с помощью команды INSERT. Команда CREATE TABLE определяет имя таблицы и множество поименованных столбцов в указанном порядке. Для каждого столбца должен быть определен тип и размер [5, стр.37].

Создадим таблицы нашей базы данных.

Программный код создания таблиц:

use master;

go

--Удаление базы данных с именем magazin

if exists (select * from sys.databases where name='magazin')

begin

drop database magazin;

end

--Создание пустой базы данных c именем magazin

create database magazin;

go

use magazin

--Создаем таблицу ГОРОД--

create table GOROD

(

COD_GOROD numeric (5) primary key,

NAIMENOVANIE_GORODA varchar(200),

)

Структура данных в таблице «Город» (GOROD) представлена на

рисунке 4.

Рисунок 4 - Структура данных в таблице «Город» (GOROD)

use magazin

--Создаем таблицу ПОСТАВЩИК--

create table POSTAVSHIK

(

COD_POSTAVSHIK numeric(5) primary key,

NAIMENOVANIE_POSTAVSHIK varchar(200),

COD_GOROD numeric (5),

ADRES varchar(200),

TELEFON varchar(200)

foreign key (COD_GOROD) references GOROD

ON DELETE CASCADE)

Структура данных в таблице «Поставщик» (POSTAVSHIK) представлена на рисунке 5.

Рисунок 5 - Структура данных в таблице «Поставщик» (POSTAVSHIK)

--Создаем таблицу ТОВАР--

use magazin

create table TOVAR

(

COD_TOVARA numeric(7) primary key,

NAZVANIE varchar(20),

CENA money,

KOLVO smallint

)

Структура данных в таблице «Товар» (TOVAR) представлена на

рисунке 6.

Рисунок 6 - Структура данных в таблице «Товар» (TOVAR)

--Создаем таблицу ПОКУПАТЕЛЬ--

use magazin

create table POKUPATEL

(

COD_POKUPATEL numeric(4) primary key,

NAIMENOVANIE_POKUPATEL varchar(200),

COD_GOROD numeric (5),

ADRES varchar(200),

TELEFON varchar(200)

foreign key (COD_GOROD) references GOROD

ON DELETE CASCADE

)

Структура данных в таблице «Покупатель» (POKUPATEL) представлена на рисунке 7.

Рисунок 7 - Структура данных в таблице «Покупатель» (POKUPATEL)

--Создаем таблицу ПОСТАВКА, в ней ссылаемся на таблицы ПОСТАВЩИК и ТОВАР--

use magazin

create table POSTAVKA

(

COD_POSTAVKI numeric (5) primary key,

DATA_POSTAVKI date,

KOLVO smallint,

COD_POSTAVSHIK_TOVAR numeric(7)

foreign key (COD_POSTAVSHIK_TOVAR) references POSTAVSHIK_TOVAR

ON DELETE CASCADE

)

Структура данных в таблице «Поставка» (POSTAVKA) представлена на рисунке 8.

Рисунок 8 - Структура данных в таблице «Поставка» (POSTAVKA)

--Создаем таблицу ПРОДАЖА, в ней ссылаемся на таблицы ПОКУПАТЕЛЬ и ТОВАР, т.к. товар приобретается покупателем--

use magazin

create table PRODAGA

(

COD_PRODAG numeric (5) primary key,

DATA_PRODAG date,

KOLVO smallint,

COD_TOVAR_POKUPATEL numeric(4)

foreign key (COD_TOVAR_POKUPATEL) references TOVAR_POKUPATEL

ON DELETE CASCADE

)

Структура данных в таблице «Продажа» (PRODAGA) представлена на рисунке 9.

Рисунок 9 - Структура данных в таблице «Продажа» (PRODAGA)

--Создаем таблицу МЕНЕДЖЕР, которая ссылается на таблицу ПРОДАЖА_МЕНЕДЖЕР, т.к. продажа оформляется менеджером--

use magazin

create table MANAGER

(

COD_MANAGER numeric (3) primary key,

FIO_MANAGER varchar(200),

DATAR_MANAGER date,

TELEFON varchar(200)

)

Структура данных в таблице «Менеджер» (MANAGER) представлена на рисунке 10.

Рисунок 10 - Структура данных в таблице «Менеджер» (MANAGER)

--Создаем таблицу ПОСТАВЩИК_ТОВАР--

use magazin

create table POSTAVSHIK_TOVAR

(

COD_POSTAVSHIK_TOVAR numeric(7) primary key,

COD_POSTAVSHIK numeric(5),

COD_TOVARA numeric(7),

foreign key (COD_POSTAVSHIK) references POSTAVSHIK

ON DELETE CASCADE,

FOREIGN KEY (COD_TOVARA) references TOVAR

ON DELETE CASCADE

)

Структура данных в таблице «Поставщик_товар» (POSTAVSHIK_TOVAR) представлена на рисунке 11.

Рисунок 11 - Структура данных в таблице «Поставщик_товар» (POSTAVSHIK_TOVAR)

--Создаем таблицу ТОВАР_ПОКУПАТЕЛЬ--

use magazin

create table TOVAR_POKUPATEL

(

COD_TOVAR_POKUPATEL numeric(4) primary key,

COD_POKUPATEL numeric(4),

COD_TOVARA numeric(7)

foreign key (COD_POKUPATEL) references POKUPATEL

ON DELETE CASCADE,

FOREIGN KEY (COD_TOVARA) references TOVAR

ON DELETE CASCADE

)

--Создаем таблицу ПРОДАЖА_МЕНЕДЖЕР--

use magazin

create table PRODAGA_MANAGER

(

COD_PRODAGA_MANAGER numeric (4) primary key,

COD_PRODAG numeric (5),

COD_MANAGER numeric (3)

foreign key (COD_PRODAG) references PRODAGA

ON DELETE NO ACTION,

FOREIGN KEY (COD_MANAGER) references MANAGER

ON DELETE CASCADE

)

Структура данных в таблице «Продажа_менеджер» (PRODAGA_MANAGER) представлена на рисунке 13.

Рисунок 13 - Структура данных в таблице «Продажа_менеджер» (PRODAGA_MANAGER)

Таблицы созданы. Заполним их. Заполнение таблиц данными позволит в дальнейшем работать с ними: строить запросы и т.д. Правильно выбранные типы данных в структуре данных таблиц позволят верно находить нужную информацию. Заполнение таблиц осуществляется с помощью конструкции INSERT INTO.

Программный код заполнения таблиц:

--Заполнение таблицы ГОРОД--

INSERT INTO dbo.GOROD VALUES (001,'Новосибирск')

INSERT INTO dbo.GOROD VALUES (010,'Москва')

INSERT INTO dbo.GOROD VALUES (011,'Уфа')

INSERT INTO dbo.GOROD VALUES (100,'Санкт-Петербург')

INSERT INTO dbo.GOROD VALUES (101,'Кемерово')

INSERT INTO dbo.GOROD VALUES (110,'Краснодар')

INSERT INTO dbo.GOROD VALUES (111,'Омск')

Данные в таблице «Город» (GOROD) приведены на рисунке 14.

Рисунок 14 - Данные в таблице «Город» (GOROD)

--Заполнение таблицы ПОСТАВЩИК--

INSERT INTO dbo.POSTAVSHIK VALUES (201,'Dior',100,'ул.Ленина 2','2-12-12-15')

INSERT INTO dbo.POSTAVSHIK VALUES (202,'Clinique',111,'ул.Советская 65','3-17-47-01')

INSERT INTO dbo.POSTAVSHIK VALUES (203,'Davidoff',010,'Красный проспект 50','2-30-00-00')

INSERT INTO dbo.POSTAVSHIK VALUES (204,'D&G',110,'Каширское шоссе 48 ','2-15-19-15')

INSERT INTO dbo.POSTAVSHIK VALUES (205,'Kenzo',100,'Комсомольский проспект 232','2-67-48-23')

INSERT INTO dbo.POSTAVSHIK VALUES (206,'Moschino',011,'ул.Тухочевского 17а','2-00-10-01')

INSERT INTO dbo.POSTAVSHIK VALUES (207,'Bvlgari',110,'ул.Селезнева 8','3-20-72-19')

INSERT INTO dbo.POSTAVSHIK VALUES (208,'Chanel',001,'ул.Ядринцевская 7','3-11-19-10')

Данные в таблице «Поставщик» (POSTAVSHIK) приведены на

рисунке 15.

Рисунок 15 - Данные в таблице «Поставщик» (POSTAVSHIK)

--Заполнение таблицы ТОВАР--

INSERT INTO dbo.TOVAR VALUES (200,'Парфюм',3700,117)

INSERT INTO dbo.TOVAR VALUES (300,'Парфюм',3700,6)

INSERT INTO dbo.TOVAR VALUES (400,'Очищение',5500,35)

INSERT INTO dbo.TOVAR VALUES (500,'Бальзам',350,300)

INSERT INTO dbo.TOVAR VALUES (600,'Парфюм',12800,62)

INSERT INTO dbo.TOVAR VALUES (700,'Тушь',930,80)

INSERT INTO dbo.TOVAR VALUES (800,'Тушь',850,95)

Данные в таблице «Товар» (TOVAR) приведены на рисунке 16.

Рисунок 16 - Данные в таблице «Товар» (TOVAR)

--Заполнение таблицы ПОКУПАТЕЛЬ--

INSERT INTO dbo.POKUPATEL VALUES (222,'Иль Де Боте',110, 'ул.Леонова 10','3-61-16-25')

INSERT INTO dbo.POKUPATEL VALUES (333,'Рив Гош',101, 'ул.Попова 17','2-12-56-38')

INSERT INTO dbo.POKUPATEL VALUES (444,'Летуль',001, 'ул.Кирова 5','2-56-32-00')

INSERT INTO dbo.POKUPATEL VALUES (555,'Парфюмика',111, 'ул.Громова 1б','2-55-60-6')

INSERT INTO dbo.POKUPATEL VALUES (666,'Летуль',011, 'ул.Академика Павлова 184','2-10-30-00')

INSERT INTO dbo.POKUPATEL VALUES (777,'Иль Де Боте',101, 'ул.Богомолова 31а','2-55-66-44')

INSERT INTO dbo.POKUPATEL VALUES (888,'Рив Гош',100, 'ул.Ордоникидзе 97','2-62-07-07')

INSERT INTO dbo.POKUPATEL VALUES (999,'Летуаль',011, 'ул.Жаворонок 67','2-15-30-00')

Данные в таблице «Покупатель» (POKUPATEL) приведены на рисунке 17.

Рисунок 17 - Данные в таблице «Покупатель» (POKUPATEL)

--Заполнение таблицы ПОСТАВКА--

INSERT INTO dbo.POSTAVKA VALUES (12,'2012-11-21',12,4001)

INSERT INTO dbo.POSTAVKA VALUES (13,'2013-04-01',30,4002)

INSERT INTO dbo.POSTAVKA VALUES (14,'2013-05-10',2,4003)

INSERT INTO dbo.POSTAVKA VALUES (15,'2012-01-17',18,4004)

INSERT INTO dbo.POSTAVKA VALUES (16,'2012-12-03',20,4005)

INSERT INTO dbo.POSTAVKA VALUES (17,'2013-02-04',3,4006)

INSERT INTO dbo.POSTAVKA VALUES (18,'2013-05-15',6,4007)

Данные в таблице «Поставка» (POSTAVKA) приведены на рисунке 18.

Рисунок 18 - Данные в таблице «Поставка» (POSTAVKA)

--Заполнение таблицы ПРОДАЖА--

INSERT INTO dbo.PRODAGA VALUES (1000,'2012-04-15',7,5001)

INSERT INTO dbo.PRODAGA VALUES (1001,'2013-01-25',34,5002)

INSERT INTO dbo.PRODAGA VALUES (1002,'2013-05-30',53,5003)

INSERT INTO dbo.PRODAGA VALUES (1003,'2013-04-17',4,5004)

INSERT INTO dbo.PRODAGA VALUES (1004,'2012-04-15',107,5005)

INSERT INTO dbo.PRODAGA VALUES (1005,'2012-07-28',21,5006)

INSERT INTO dbo.PRODAGA VALUES (1006,'2013-02-22',18,5007)

INSERT INTO dbo.PRODAGA VALUES (1007,'2012-09-18',90,5008)

Данные в таблице «Продажа» (PRODAGA) приведены на рисунке 19.

Рисунок 19 - Данные в таблице «Продажа» (PRODAGA)

--Заполнение таблицы МЕНЕДЖЕР--

INSERT INTO dbo.MANAGER VALUES (2,'Cкорых Ксения Александровна','1992-12-06',89043706477)

INSERT INTO dbo.MANAGER VALUES (3,'Кезис Анна Сергеевна','1991-03-16',89069067157)

INSERT INTO dbo.MANAGER VALUES (4,'Гаер Виталий Анатольевич','1989-11-30',89538725550)

INSERT INTO dbo.MANAGER VALUES (5,'Гордиец Валентина Игоревна','1982-05-20',89114532404)

INSERT INTO dbo.MANAGER VALUES (6,'Долгов Игнат Валерьевич','1992-06-17',89525432833)

INSERT INTO dbo.MANAGER VALUES (7,'Степанов Валерий Александрович','1988-01-12',89618469798)

INSERT INTO dbo.MANAGER VALUES (8,'Деньгина Светлана Юрьевна','1987-10-02',89529320782)

INSERT INTO dbo.MANAGER VALUES (9,'Разношенская Светлана Александровна','1990-12-27',89236011625)

Данные в таблице «Менеджер» (MANAGER) приведены на рисунке 20.

Рисунок 20 - Данные в таблице «Менеджер» (MANAGER)

--Заполнение таблицы ПОСТАВЩИК_ТОВАР--

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4001,201,200)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4002,202,800)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4003,203,500)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4004,204,600)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4005,205,700)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4006,206,800)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4007,207,300)

INSERT INTO dbo.POSTAVSHIK_TOVAR VALUES (4008,208,400)

Данные в таблице «Поставщик_товар» (POSTAVSHIK_TOVAR) приведены на рисунке 21.

Рисунок 21 - Данные в таблице «Поставщик_товар» (POSTAVSHIK_TOVAR)

--Заполнение таблицы ТОВАР_ПОКУПАТЕЛЬ--

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5001,222,200)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5002, 333,300)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5003,444,400)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5004,555,500)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5005,666,600)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5006,777,700)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5007,888,800)

INSERT INTO dbo.TOVAR_POKUPATEL VALUES (5008,999,400)

Данные в таблице «Товар_Покупатель» (TOVAR_POKUPATEL) приведены на рисунке 22.

Рисунок 22 - Данные в таблице «Товар_Покупатель» (TOVAR_POKUPATEL)

--Заполнение таблицы ПРОДАЖА_МЕНЕДЖЕР--

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6001,1000,2)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6002,1001,3)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6003,1002,4)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6004,1003,5)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6005,1004,6)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6006,1005,7)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6007,1006,8)

INSERT INTO dbo.PRODAGA_MANAGER VALUES (6008,1007,9)

Данные в таблице «Продажа_Менеджер» (PRODAGA_MANAGER) приведены на рисунке 23.

Рисунок 23 - Данные в таблице «Продажа_Менеджер» (PRODAGA_MANAGER)

Создание таблиц для оптового магазина, а так же их заполнение позволили представить данные в наглядной форме и подготовили их для дальнейшей работы.

6. Структура таблиц и данных

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

Создание таблиц позволило представить их общую структуру, структуру данных и данные каждой из таблиц в SQL Server 2008.

Структура таблиц приведена на рисунке 24.

Рисунок 24 - Структура таблиц

Заполнение таблиц данными позволит в дальнейшем работать с ними: строить запросы и т.д. Правильно выбранные типы данных в структуре данных таблиц позволят верно находить нужную информацию.

7. Объекты базы данных

В данной работе в качестве объектов базы данных были взяты: запросы, процедуры, представления, триггеры и курсоры.

7.1 Запросы

Запросы SQL - это метод обращения к содержанию таблиц базы данных для извлечения из них информации согласно заданным критериям запроса пользователя [3, стр.56].

Запрос строится из обязательных операторов

SELECT - оператор который отвечает за атрибуты которые необходимо вывести в результате запроса.

FROM - Оператор отвечающий за таблицы из которых нужно извлечь информацию в результате запроса, очень тесно связан с оператором SELECT. Важно в SELECT писать атрибуты тех таблиц которые в дальнейшем укажете в операторе FROM. Если же нужно создать запрос в котором должны содержаться атрибуты разных таблиц, то нужно использовать оператор INNER JOIN который устанавливает связь между таблицами.

Так же в запросах испльзуется оператор WHERE который используется для формирования условий отбора из строк которые будут выведены в результате запроса [8, стр.33].

Сложные запросы с использованием функций

Вывести минимальную и максимальную цену товара, проданного в 2012 году

Программный код:

use magazin

go

SELECT MIN(CENA)as[min.cena],MAX(CENA)as[max.cena]

FROM TOVAR,TOVAR_POKUPATEL,PRODAGA

WHERE TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL=PRODAGA.COD_TOVAR_POKUPATEL and DATA_PRODAG like '2012-__-__'

Результат выполнения запроса приведен на рисунке 25.

Рисунок 25 - Результат выполнения запроса 7.1.1.1

В результате выполнения запроса сформировалась максимальная и минимальная цены за 2012 год.

Вывести кол-во поставщиков, которые поставляли товар в период с 1 декабря 2012 по 1 марта 2013

Программный код:

use magazin

go

SELECT COUNT(NAIMENOVANIE_POSTAVSHIK)as[Kol-vo postavschikov]

FROM POSTAVSHIK,POSTAVKA, POSTAVSHIK_TOVAR

WHERE POSTAVSHIK.COD_POSTAVSHIK= POSTAVSHIK_TOVAR.COD_POSTAVSHIK and POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR=POSTAVKA.COD_POSTAVSHIK_TOVAR and DATA_POSTAVKI between '01.12.2012' and '1.03.2013'

Результат выполнения запроса приведен на рисунке 26.

Рисунок 26 - Результат выполнения запроса 7.1.1.2

В результате выполнения запроса видно, что условиям, описанным в программном коде, удовлетворяют 2 поставщика.

Вывести количество символов в ФИО менеджера, у которых количество продаж больше 50

Программный код:

use magazin

go

SELECT LEN(FIO_MANAGER)as[Kol-vo simvolov], KOLVO, FIO_MANAGER

FROM MANAGER,PRODAGA,PRODAGA_MANAGER

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA.COD_PRODAG=PRODAGA_MANAGER.COD_PRODAG and KOLVO>50

Результат выполнения запроса приведен на рисунке 27.

Рисунок 27 - Результат выполнения запроса 7.1.1.3

В результате выполнения запроса, выводится количество символов в ФИО менеджера, у которых количество продаж больше 50, таких оказалось трое человек.

На какую сумму было куплено товаров, покупателями из Кемерово

Программный код:

use magazin

go

SELECT SUM(CENA)as[Summa]

FROM TOVAR,GOROD,POKUPATEL,TOVAR_POKUPATEL

WHERE TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and POKUPATEL.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA='Кемерово'

Результат выполнения запроса приведен на рисунке 28.

Рисунок 28 - Результат выполнения запроса 7.1.1.4

В результате выполнения запроса видим, что покупателями из Кемерово было куплено на 4630 рублей.

Количество продаж покупателя Рив Гош

Программный код:

use magazin

go

SELECT COUNT(POKUPATEL.COD_POKUPATEL)as[Kol-vo prodag]

FROM POKUPATEL,PRODAGA,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL=PRODAGA.COD_TOVAR_POKUPATEL and NAIMENOVANIE_POKUPATEL='Рив Гош'

Результат выполнения запроса приведен на рисунке 28.

Рисунок 28 - Результат выполнения запроса 7.1.1.5

В результате выполнения запроса можно увидеть, что количество продаж покупателя Рив Гош равно 3.

Вывести количество покупателей с кодом меньше 500, купивших товар 15 апреля 2012

Программный код:

use magazin

go

SELECT COUNT(POKUPATEL.COD_POKUPATEL)as[Kol-vo prodag]

FROM POKUPATEL,PRODAGA,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL=PRODAGA.COD_TOVAR_POKUPATEL and POKUPATEL.COD_POKUPATEL<500 and DATA_PRODAG='2012-04-15'

Результат выполнения запроса приведен на рисунке 29.

Рисунок 29 - Результат выполнения запроса 7.1.1.6

В результате выполнения запроса видно, что всего двое покупателей с кодом меньше 500, купивших товар 15 апреля 2012

Сколько продаж было совершено менеджером в Уфу

Программный код:

use magazin

go

SELECT COUNT(KOLVO)as[kol-vo prodag v Ufu]

FROM MANAGER,PRODAGA,PRODAGA_MANAGER,GOROD,POKUPATEL, TOVAR_POKUPATEL

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG

and PRODAGA.COD_TOVAR_POKUPATEL=TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL=POKUPATEL.COD_POKUPATEL and POKUPATEL.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA='Уфа'

Результат выполнения запроса приведен на рисунке 30.

Рисунок 30 - Результат выполнения запроса 7.1.1.7

В результате выполнения запроса видим, что в Уфу была совершена всего 1 продажа.

Вывести список товаров, количество которых продано меньше либо равно среднему количеству поступивших товаров

Программный код:

use magazin

go

SELECT distinct NAZVANIE

FROM POSTAVKA,TOVAR,POSTAVSHIK_TOVAR,TOVAR_POKUPATEL, PRODAGA

WHERE PRODAGA.COD_TOVAR_POKUPATEL=TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA and POSTAVKA.COD_POSTAVSHIK_TOVAR= POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA and PRODAGA.KOLVO<=(SELECT AVG(KOLVO)

FROM POSTAVKA)

Результат выполнения запроса приведен на рисунке 31.

Рисунок 31 - Результат выполнения запроса 7.1.1.8

В результате выполнения запроса, у нас образовался список из 2х товаров.

Вывести номера проданных товаров с кодом больше 600

Программный код:

use magazin

go

SELECT 'Товар номер ' + CAST(COD_TOVARA as char), NAZVANIE

FROM TOVAR

WHERE COD_TOVARA>600

Результат выполнения запроса приведен на рисунке 32.

Рисунок 32 - Результат выполнения запроса 7.1.1.9

В результате выполнения запроса видим, что в базе всего 2 проданных товара с кодом больше 600.

Вывести количество дней между датой поставки и сегодняшним днем для товара с кодом 200

Программный код:

use magazin

go

SELECT NAZVANIE, DATEDIFF(day,'2013-04-01',GETDATE())as Days

FROM POSTAVKA,TOVAR,POSTAVSHIK_TOVAR

WHERE POSTAVKA.COD_POSTAVSHIK_TOVAR=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA and TOVAR.COD_TOVARA='200'

Результат выполнения запроса приведен на рисунке 33.

Рисунок 33 - Результат выполнения запроса 7.1.1.10

В результате выполнения запроса получаем 66 дней между датой поставки и сегодняшним днем для товара с кодом 200.

Вывести количество поставщиков из Москвы

Программный код:

use magazin

go

SELECT COUNT(COD_POSTAVSHIK)as[kol-vo postavschikov]

FROM POSTAVSHIK,GOROD

WHERE POSTAVSHIK.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA='Москва'

Результат выполнения запроса приведен на рисунке 34.

Рисунок 34 - Результат выполнения запроса 7.1.1.11

В результате выполнения запроса можно увидеть количество поставщиков из Москвы.

Заменить в дате поставки с тире на точки и вывести поставленный товар

Программный код:

use magazin

go

SELECT

REPLACE (DATA_POSTAVKI,'-', '.')as[Data postavki],NAZVANIE

FROM POSTAVKA,TOVAR,POSTAVSHIK_TOVAR

WHERE POSTAVKA.COD_POSTAVSHIK_TOVAR=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA

Результат выполнения запроса приведен на рисунке 35.

Рисунок 35 - Результат выполнения запроса 7.1.1.12

В результате выполнения запроса видим, тире в дате поставки были заменены на точки.

Вывести таблицу, содержащую наименование поставщика и покупателя

Программный код:

use magazin

go

SELECT NAIMENOVANIE_POKUPATEL as name

FROM POKUPATEL

UNION

SELECT NAIMENOVANIE_POSTAVSHIK as name

FROM POSTAVSHIK

order by name

Результат выполнения запроса приведен на рисунке 46.

Рисунок 46 - Результат выполнения запроса 7.1.1.13

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

Вывести количество символов в названии самого дорогого товара и кто его поставляет

Программный код:

use magazin

go

SELECT LEN(NAZVANIE),NAIMENOVANIE_POSTAVSHIK

FROM TOVAR,POSTAVSHIK,POSTAVSHIK_TOVAR

WHERE CENA in (SELECT MAX(CENA)

FROM TOVAR) and (TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA) and (POSTAVSHIK.COD_POSTAVSHIK=POSTAVSHIK_TOVAR.COD_POSTAVSHIK)

Результат выполнения запроса приведен на рисунке 47.

Рисунок 47 - Результат выполнения запроса 7.1.1.14

Результат выполнения запроса, мы видим количество символов в названии самого дорогого товара и кто его поставляет.

Вывести количество дней простоя товара (от поставки до продажи), между максимальной и минимальной датой

Программный код:

use magazin

go

SELECT distinct DATEDIFF(dd, (SELECT MIN(DATA_POSTAVKI) FROM POSTAVKA), (SELECT MAX(DATA_PRODAG) FROM PRODAGA))

as[prostoi], NAZVANIE

FROM POSTAVKA,TOVAR,POSTAVSHIK_TOVAR,TOVAR_POKUPATEL, PRODAGA

WHERE PRODAGA.COD_TOVAR_POKUPATEL=TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA and POSTAVKA.COD_POSTAVSHIK_TOVAR= POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA

Результат выполнения запроса приведен на рисунке 48.

Рисунок 48 - Результат выполнения запроса 7.1.1.15

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

Запросы на поиск текстовой информации

Вывести ФИО менеджера, который продал Парфюм

Программный код:

SELECT FIO_MANAGER

FROM MANAGER,PRODAGA_MANAGER,TOVAR,TOVAR_POKUPATEL,

PRODAGA

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG

and PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA and NAZVANIE='Парфюм'

Результат выполнения запроса приведен на рисунке 49.

Рисунок 49 - Результат выполнения запроса 7.1.2.1

В результате выполнения запроса, мы видим ФИО менеджера, который продал Парфюм.

Наименование товара, начинающегося на Т

Программный код:

use magazin

go

SELECT distinct NAZVANIE

FROM TOVAR

WHERE NAZVANIE like 'Т%'

Результат выполнения запроса приведен на рисунке 50.

Рисунок 50 - Результат выполнения запроса 7.1.2.2

В результате выполнения запроса видим все наименование товара, начинающегося на Т.

Вывести наименование поставщика, поставляемого товар 17 января 2012

Программный код:

use magazin

go

SELECT NAIMENOVANIE_POSTAVSHIK

FROM POSTAVSHIK,POSTAVKA, POSTAVSHIK_TOVAR

WHERE POSTAVKA.COD_POSTAVSHIK_TOVAR= POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK and DATA_POSTAVKI='17.01.2012'

Результат выполнения запроса приведен на рисунке 51.

Рисунок 51 - Результат выполнения запроса 7.1.2.3

В результате выполнения запроса, мы нашли наименование поставщика, поставляемого товар 17 января 2012.

Вывести ФИО менеджеров, рожденных в декабре

Программный код:

use magazin

go

SELECT FIO_MANAGER

FROM MANAGER

WHERE DATAR_MANAGER like '____-12-__'

Результат выполнения запроса приведен на рисунке 52.

Рисунок 52 - Результат выполнения запроса 7.1.2.4

В результате выполнения запроса видим инициалы всех менеджеров, рожденных в декабре.

Вывести названия городов, где 2ая буква "О"

Программный код:

use magazin

go

SELECT NAIMENOVANIE_GORODA

FROM GOROD

WHERE NAIMENOVANIE_GORODA like '_о%'

Результат выполнения запроса приведен на рисунке 53.

Рисунок 53 - Результат выполнения запроса 7.1.2.5

В результате выполнения запроса видим, два города, в чьих названиях 2ая буква "О".

Вывести всех поставщиков в алфавитном порядке

Программный код:

use magazin

go

SELECT NAIMENOVANIE_POSTAVSHIK

FROM POSTAVSHIK

order by NAIMENOVANIE_POSTAVSHIK asc

Результат выполнения запроса приведен на рисунке 54.

Рисунок 54 - Результат выполнения запроса 7.1.2.6

В результате выполнения запроса видим всех поставщиков в алфавитном порядке.

Вывести название товаров, поставляемых поставщиками, кроме "Dior", "Davidoff" и "Chanel"

Программный код:

use magazin

go

SELECT NAZVANIE

FROM TOVAR,POSTAVSHIK,POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK.COD_POSTAVSHIK=POSTAVSHIK_TOVAR.COD_POSTAVSHIK

and NAZVANIE not in ('Dior', 'Davidoff', 'Chanel')

Результат выполнения запроса приведен на рисунке 55.

Рисунок 55 - Результат выполнения запроса 7.1.2.7

В результате выполнения запроса, мы видим название всех товаров, поставляемых поставщиками, кроме "Dior", "Davidoff" и "Chanel".

Вывести наименование покупателей и товара, купленного в период с 1.06.2012-1.01.2013

Программный код:

use magazin

go

SELECT NAIMENOVANIE_POKUPATEL, NAZVANIE

FROM TOVAR,POKUPATEL,PRODAGA,TOVAR_POKUPATEL

WHERE PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_POKUPATEL=POKUPATEL.COD_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA and DATA_PRODAG between '1.06.2012' and '1.01.2013'

Результат выполнения запроса приведен на рисунке 56.

Рисунок 56 -Результат выполнения запроса 7.1.2.8

В результате выполнения запроса видим, что условиям, описанным в программном коде, удовлетворяют 2 покупателя, купивших товар в период с 1.06.2012-1.01.2013.

Вывести название товара, количество которого было продано больше 10, но меньше 50

Программный код:

use magazin

go

SELECT distinct NAZVANIE

FROM TOVAR, PRODAGA,TOVAR_POKUPATEL

WHERE PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA and PRODAGA.KOLVO between 10 and 50

Результат выполнения запроса приведен на рисунке 57.

Рисунок 57 - Результат выполнения запроса 7.1.2.9

В результате выполнения запроса, мы видим название товаров, количество которых было продано больше 10, но меньше 50.

Вывести информацию о менеджере с фамилией Кезис

Программный код:

use magazin

go

SELECT *

FROM MANAGER

WHERE FIO_MANAGER like 'Кезис%'

Результат выполнения запроса приведен на рисунке 58.

Рисунок 58 - Результат выполнения запроса 7.1.2.10

В результате выполнения запроса, мы получили всю, интересующую нас информацию о менеджере с фамилией Кезис.

Запросы с использованием Group By

Вывести среднюю цену товаров, поставляемых поставщиками 'D&G', 'Chanel' и 'Kenzo'

Программный код:

use magazin

go

SELECT AVG(CENA)as[Sred.Cena], NAIMENOVANIE_POSTAVSHIK

FROM TOVAR, POSTAVSHIK, POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK and NAIMENOVANIE_POSTAVSHIK in('D&G','Chanel','Kenzo')

group by NAIMENOVANIE_POSTAVSHIK

Результат выполнения запроса приведен на рисунке 59.

Рисунок 59 - Результат выполнения запроса 7.1.3.1

В результате выполнения запроса, мы получили среднюю цену товаров, поставляемых поставщиками 'D&G','Chanel' и 'Kenzo'.

Вывести максимальное количество продаж менеджера с кодом 5

Программный код:

use magazin

go

SELECT MAX(KOLVO)as[Max kol-vo prodag], FIO_MANAGER

FROM MANAGER,PRODAGA,PRODAGA_MANAGER

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG and MANAGER.COD_MANAGER='5'

group by FIO_MANAGER

Результат выполнения запроса приведен на рисунке 60.

Рисунок 60 - Результат выполнения запроса 7.1.3.2

Результат запроса, мы получили максимальное количество продаж менеджера с кодом 5, равное четырем.

Вывести минимальную цену на товар, поставляемого из Омска

Программный код:

use magazin

go

SELECT MIN(CENA)as[Minim.cena], NAZVANIE

FROM TOVAR,GOROD,POSTAVSHIK,POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK and POSTAVSHIK.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA='Омск'

group by NAZVANIE

Результат выполнения запроса приведен на рисунке 61.

Рисунок 61 - Результат выполнения запроса 7.1.3.3

Результат запроса выводит минимальную цену на товар, поставляемого из Омска.

Вывести минимальное количество товара, которое купили

Программный код:

use magazin

go

SELECT MIN(TOVAR.KOLVO)as[Min kol-vo tovara],NAZVANIE,NAIMENOVANIE_POKUPATEL

FROM TOVAR,POKUPATEL,PRODAGA,TOVAR_POKUPATEL

WHERE TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and

TOVAR_POKUPATEL.COD_POKUPATEL=POKUPATEL.COD_POKUPATEL and TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL=

PRODAGA.COD_TOVAR_POKUPATEL

group by NAZVANIE,NAIMENOVANIE_POKUPATEL

Результат выполнения запроса приведен на рисунке 62.

Рисунок 62 - Результат выполнения запроса 7.1.3.4

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

Вывести количество покупателей товара «Парфюм»

Программный код:

use magazin

go

SELECT COUNT(NAIMENOVANIE_POKUPATEL)as[Kol-vo pokupateley], NAZVANIE

FROM POKUPATEL,TOVAR,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and NAZVANIE='Парфюм'

group by NAZVANIE

Результат выполнения запроса приведен на рисунке 63.

Рисунок 63 - Результат выполнения запроса 7.1.3.5

В результате выполнения запроса можно увидеть, количество покупателей товара 'Парфюм'.

Вывести максимальный код товара, поставленного из Санкт-Петербурга

Программный код:

use magazin

go

SELECT MAX(TOVAR.COD_TOVARA)as[Max cod],NAZVANIE,NAIMENOVANIE_POSTAVSHIK

FROM TOVAR,GOROD,POSTAVSHIK,POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK

and POSTAVSHIK.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA='Санкт-Петербург'

group by NAZVANIE,NAIMENOVANIE_POSTAVSHIK

Результат выполнения запроса приведен на рисунке 64.



Рисунок 64 - Результат выполнения запроса 7.1.3.6

В результате выполнения запроса, мы видим максимальный код товара, поставленного из Санкт-Петербурга.

Вывести количество поставщиков товара 'Очищение'

Программный код:

use magazin

go

SELECT COUNT(COD_POSTAVSHIK)as[Kol-vo postavschikov], NAZVANIE

FROM TOVAR,POSTAVSHIK_TOVAR, POSTAVSHIK

WHERE POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=

POSTAVSHIK.COD_POSTAVSHIK and NAZVANIE='Очищение'

group by NAZVANIE

Результат выполнения запроса приведен на рисунке 65.

Рисунок 65 - Результат выполнения запроса 7.1.3.7

В результате выполнения запроса можно увидеть, количество поставщиков товара 'Очищение'.

Вывести среднюю цену на товар после 15% скидки и информацию о менеджере, продавшим этот товар

Программный код:

use magazin

go

SELECT AVG (CENA*0.15)as[Sr.cena], FIO_MANAGER

FROM MANAGER,PRODAGA,PRODAGA_MANAGER,TOVAR_POKUPATEL, TOVAR

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG

and PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA

group by FIO_MANAGER

Результат выполнения запроса приведен на рисунке 66.

Рисунок 66 - Результат выполнения запроса 7.1.3.8

В результате запроса определяется средняя цена на товар после 15% скидки и, выводится информация о менеджере, продавшим этот товар.

Посчитать количество букв в ФИО каждого менеджера и вывести количество проданного им товара

Программный код:

use magazin

go

SELECT MAX(LEN(FIO_MANAGER))as[Max dlina],MANAGER.COD_MANAGER,KOLVO

FROM MANAGER,PRODAGA,PRODAGA_MANAGER

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA.COD_PRODAG=PRODAGA_MANAGER.COD_PRODAG

group by MANAGER.COD_MANAGER,KOLVO

Результат выполнения запроса приведен на рисунке 67.

Рисунок 67 - Результат выполнения запроса 7.1.3.9

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

Вывести количество продаж для каждого менеджера

Программный код:

use magazin

go

SELECT COUNT(PRODAGA.COD_PRODAG)as[kol-vo prodag], FIO_MANAGER

FROM MANAGER,PRODAGA,PRODAGA_MANAGER

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA.COD_PRODAG=PRODAGA_MANAGER.COD_PRODAG

group by FIO_MANAGER

Результат выполнения запроса приведен на рисунке 68.

Рисунок 68 - Результат выполнения запроса 7.1.3.10

В результате запроса можно видеть, количество продаж для каждого менеджера.

Вывести количество поставщиков из каждого города

Программный код:

use magazin

go

SELECT COUNT(COD_POSTAVSHIK)as[kol-vo postavschikov], NAIMENOVANIE_GORODA

FROM GOROD,POSTAVSHIK

WHERE GOROD.COD_GOROD=POSTAVSHIK.COD_GOROD

group by NAIMENOVANIE_GORODA

Результат выполнения запроса приведен на рисунке 69.



Рисунок 69 - Результат выполнения запроса 7.1.3.11

В результате запроса мы видим, количество поставщиков из каждого города.

Вывести общую стоимость одинаковых товаров

Программный код:

use magazin

go

SELECT SUM(CENA),NAZVANIE

FROM TOVAR

group by NAZVANIE

Результат выполнения запроса приведен на рисунке 70.

Рисунок 70 - Результат выполнения запроса 7.1.3.12

В результате запроса мы видим общую стоимость одинаковых товаров.

Вывести среднюю цену всех товаров, проданных каждым менеджером

Программный код:

use magazin

go

SELECT AVG(CENA)as[Sred.Cena], FIO_MANAGER

FROM MANAGER,PRODAGA,PRODAGA_MANAGER,TOVAR_POKUPATEL, TOVAR

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG

and PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA

group by FIO_MANAGER

Результат выполнения запроса приведен на рисунке 71.

Рисунок 71 - Результат выполнения запроса 7.1.3.13

Результатом выполнения запроса будет подсчет средних цен всех товаров, проданных каждым менеджером.

Вывести количество поставок каждого поставляемого товара

Программный код:

use magazin

go

SELECT COUNT(POSTAVKA.COD_TOVARA)as[Kol-vo], NAZVANIE, NAIMENOVANIE_POSTAVSHIK

FROM TOVAR,POSTAVKA,POSTAVSHIK

WHERE POSTAVSHIK.COD_POSTAVSHIK=POSTAVKA.COD_POSTAVSHIK and POSTAVKA.COD_TOVARA=TOVAR.COD_TOVARA

group by NAZVANIE,NAIMENOVANIE_POSTAVSHIK

Результат выполнения запроса приведен на рисунке 72.

Рисунок 72 - Результат выполнения запроса 7.1.3.14

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

Вывести название поставщика и минимальную стоимость товара, который он поставляет

Программный код:

use magazin

go

SELECT NAIMENOVANIE_POSTAVSHIK,MIN(CENA)as[Min cena],NAZVANIE

FROM TOVAR, POSTAVSHIK_TOVAR,POSTAVSHIK

WHERE POSTAVSHIK.COD_POSTAVSHIK=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA

group by NAIMENOVANIE_POSTAVSHIK,NAZVANIE

Результат выполнения запроса приведен на рисунке 73.

Рисунок 73 - Результат выполнения запроса 7.1.3.15

В результате запроса, мы нашли минимальную стоимость поставляемого товара, равного 350 рублям.

Запросы с использованием HAVING

Выяснить среднюю цену поставляемых товаров, только тех поставщиков, у которых количество поставляемого товара больше 3

Программный код:

use magazin

go

SELECT AVG(CENA) as [Sr stoimost],NAZVANIE,POSTAVKA.KOLVO

FROM TOVAR,POSTAVKA,POSTAVSHIK,POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and

POSTAVKA.COD_POSTAVSHIK_TOVAR=POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_POSTAVSHIK

group by NAZVANIE,POSTAVKA.KOLVO

HAVING POSTAVKA.KOLVO>3

Результат выполнения запроса приведен на рисунке 74.

Рисунок 74 - Результат выполнения запроса 7.1.4.1

В результате запроса, мы выяснили среднюю стоимость поставляемых товаров, у тех поставщиков, у которых количество поставляемого товара больше 3.

Вывести коды товаров, покупаемых более чем одним покупателем

Программный код:

use magazin

go

SELECT TOVAR.COD_TOVARA

FROM TOVAR,TOVAR_POKUPATEL,POKUPATEL

WHERE TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA

and TOVAR_POKUPATEL.COD_POKUPATEL=POKUPATEL.COD_POKUPATEL

group by TOVAR.COD_TOVARA

HAVING COUNT(*)>1

Результат выполнения запроса приведен на рисунке 75.



Рисунок 75 - Результат выполнения запроса 7.1.4.2

В результате запроса, рассмотрев, все товары, покупаемые более чем одним покупателем, выводим код этих товаров, в нашем случае это код 400.

Какое количество покупателей приобретают товар "Парфюм", стоимость которого меньше средней стоимости всех товаров в магазине

Программный код:

use magazin

go

SELECT COUNT(POKUPATEL.COD_POKUPATEL) as [Kol-vo pokupatelei],NAIMENOVANIE_POKUPATEL

FROM TOVAR,TOVAR_POKUPATEL,POKUPATEL

WHERE TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA

and TOVAR_POKUPATEL.COD_POKUPATEL=POKUPATEL.COD_POKUPATEL and NAZVANIE='Парфюм'

group by NAIMENOVANIE_POKUPATEL,CENA

HAVING CENA<(SELECT AVG(CENA) FROM TOVAR)

Результат выполнения запроса приведен на рисунке 76.

Рисунок 76 - Результат выполнения запроса 7.1.4.3

В результате выполнения запроса, мы получили количество покупателей, приобретающих товар "Парфюм", стоимость которого меньше средней стоимости всех товаров в магазине.

Выяснить количество продаж, осуществленных за 2013 год среди всех продаж, сумма которых больше средней суммы всех продаж и кем было куплено

Программный код:

use magazin

go

SELECT COUNT(KOLVO) as [kol-vo prodag],NAIMENOVANIE_POKUPATEL

FROM PRODAGA,POKUPATEL,TOVAR_POKUPATEL

WHERE PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_POKUPATEL=POKUPATEL.COD_POKUPATEL and DATA_PRODAG like '2013%'

group by NAIMENOVANIE_POKUPATEL,KOLVO

HAVING KOLVO>(SELECT AVG(KOLVO)FROM PRODAGA)

Результат выполнения запроса приведен на рисунке 77.

Рисунок 77 - Результат выполнения запроса 7.1.4.4

В результате запроса, мы выяснили количество продаж, осуществленных за 2013 год среди всех продаж, сумма которых больше средней суммы всех продаж.

Выдать информацию о наименовании поставщиков и максимальной поставки, если общее количество поставок свыше 5

Программный код:

use magazin

go

SELECT NAIMENOVANIE_POSTAVSHIK, MAX(KOLVO)

FROM POSTAVKA,POSTAVSHIK, POSTAVSHIK_TOVAR

WHERE POSTAVKA.COD_POSTAVSHIK_TOVAR=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=

POSTAVSHIK.COD_POSTAVSHIK

group by NAIMENOVANIE_POSTAVSHIK

HAVING COUNT(*)<5

Результат выполнения запроса приведен на рисунке 78.

Рисунок 78 - Результат выполнения запроса 7.1.4.5

Результатом запроса будет информация о наименовании поставщиков и максимальной поставки, если общее количество поставок свыше 5

7.2 Процедуры

Вывести количество продаж в любой город

Программный код:

Создание процедуры:

use magazin

go

create procedure kol_p

(@cod int output,

@kol varchar(50)) as

begin

select @cod=COUNT(PRODAGA.COD_PRODAG)

from PRODAGA, GOROD, POKUPATEL,TOVAR_POKUPATEL

where PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_POKUPATEL=POKUPATEL.COD_POKPATEL and POKUPATEL.COD_GOROD=GOROD.COD_GOROD

and NAIMENOVANIE_GORODA=@kol

end

Вызов процедуры:

go

declare @cod int

execute kol_p @cod output, Уфа

print 'Количество продаж'

print @cod

Результат работы процедуры приведен на рисунке 79.

Рисунок 79 - Результат работы процедуры 7.2.1

В результате выполнения процедуры выводится информация о количестве продаж в Уфу.

Вывести количество дней между датой поставки и сегодняшним днем для товара с любым кодом

Программный код:

Создание процедуры:

use magazin

go

create procedure d_pos

(@dni int output,

@t_cod numeric) as

begin

SELECT @dni=DATEDIFF(day,DATA_POSTAVKI,GETDATE())

FROM POSTAVKA,TOVAR,TOVAR_POKUPATEL,POSTAVSHIK_TOVAR,

PRODAGA

WHERE PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA and POSTAVKA.COD_POSTAVSHIK_TOVAR=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_TOVAR=TOVAR.COD_TOVAR and TOVAR.COD_TOVARA=@t_cod

End

Вызов процедуры:

go

declare @dni int

execute d_pos @dni output,200

print 'Количество дней'

print @dni

Результат работы процедуры приведен на рисунке 80.

мicrosoft таблица запрос символ



Рисунок 80 - Результат работы процедуры 7.2.2

В результате работы запроса выводится информация о количестве дней между датой поставки и сегодняшним днем.

Вывести среднюю цену товаров, поставляемых любым поставщиком

Программный код:

Создание процедуры:

use magazin

go

create procedure sr_p

(@sr money output,

@sc_p varchar(10)) as

begin

SELECT @sr=AVG(CENA)

FROM TOVAR, POSTAVSHIK, POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK

and NAIMENOVANIE_POSTAVSHIK=@sc_p

end

Вызов процедуры:

go

declare @sr money

execute sr_p @sr output ,Dior

print 'Средняя цена товара'

print @sr

Результат работы процедуры приведен на рисунке 81.

Рисунок 81 - Результат работы процедуры 7.2.3

В результате работы процедуры выводится информация о средней цене товара.

Вывести максимальное количество продаж менеджера с любым кодом

Программный код:

Создание процедуры:

use magazin

go

create procedure max_p

(@max int output,

@m_p varchar(10)) as

begin

SELECT @max=MAX(KOLVO)

FROM MANAGER,PRODAGA,PRODAGA_MANAGER

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and

PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG and MANAGER.COD_MANAGER=@m_p

End

Вызов процедуры:

go

declare @max int

execute max_p @max output,7

print 'Максимальное кол-во продаж'

print @max

Результат работы процедуры приведен на рисунке 82.

Рисунок 82 - Результат работы процедуры 7.2.4

В результате работы процедуры мы видим информацию о количествах продаж менеджера.

Вывести минимальную цену на товар, поставляемого из любого города

Программный код:

Создание процедуры:

use magazin

go

create procedure min_c

(@cena money output,

@m_c varchar(8)) as

begin

SELECT @cena=MIN(CENA)

FROM TOVAR,GOROD,POSTAVSHIK,POSTAVSHIK_TOVAR

WHERE TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK

and POSTAVSHIK.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA=@m_c