Разработка автоматизированной информационной системы "Учет продажи мебели"

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

Федеральное государственное автономное образовательное учреждение

высшего профессионального образования

"Сибирский федеральный университет"

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

по дисциплине: "Системы управления базами данных"

на тему: "Разработка автоматизированной информационной системы

"Учет продажи мебели""

Выполнил: студент группы 230ЖЗ

А.В. Апонасенко

Руководитель: М.В. Некрасов

Железногорск - 2013

Содержание

1. Исходные данные - описание предметной области

2. Построение инфологической модели ПО

3. Построение реляционной модели ПО

4. Определение форматов данных и правил целостности реляционной модели

5. Оценка памяти, необходимой для размещения всех таблиц

6. Создание БД по разработанной реляционной модели ПО

7. Первоначальное заполнение БД

8. Реализация функциональных задач

1. Исходные данные - описание предметной области

Фирма занимается продажей мебели и сопутствующих аксессуаров. При этом необходимо решать следующие задачи: производить регистрацию заказов, выполнять расчёт стоимости (при оформлении заказов), вести учёт материальных запасов и хранить информации о поставщиках. Доставка клиентам готовых заказов осуществляется с помощью сторонних фирм. За составление и реализацию заказа отвечает один сотрудник фирмы - агент по продаже. При совершении сделки агент получает 2 % от проданной мебели. У фирмы присутствует склад продукции, при недостаче продукции она закупается у производителя. Клиентам, оформившим заказ на сумму более 50000 рублей, предоставляется скидка в размере 5 %.

О сущностях имеется следующая информация:

· клиент: фамилия, имя, отчество, адрес, должность, домашний телефон;

· заказ: дата заказа, дата исполнения, наименования изделий, общая стоимость заказа;

· изделие: название изделия, описание изделия, цена изделия, количество на складе;

· поставщик мебели: фирма поставщик, представитель поставщика, должность, телефон;

· доставка: вид доставки, фирма доставки, телефон доставки, представитель доставки, стоимость доставки, период доставки.

2. Построение инфологической модели ПО

Убираем связь "многие ко многим".

3. Построение реляционной модели ПО

Дополняем сущности необходимыми атрибутами. Сущности становятся таблицами, атрибуты столбцами. Выделяем "первичные" и "внешние" ключи, обозначаем связи между таблицами.

Реляционная модель отвечает условиям непротиворечивости:

1. Нет повторяющихся первичных ключей в таблице;

2. Все строки таблицы одинаковой структуры;

3. Ссылочная целостность для внешних ключей;

4. Имена всех столбцов различны, а значения - однотипны.

1. Приведение сущностей к 1НФ. Так как все атрибуты простые (атомарные), и среди них нет повторяющихся групп - все сущности находятся в 1НФ.

2. Приведение сущностей к 2НФ. Так как сущности находятся в 1НФ и каждый неключевой атрибут функционально полно зависит от первичного ключа все они находятся в 2НФ.

3. Приведение сущностей к 3НФ. Так как сущности находятся в 2НФ и каждый неключевой атрибут функционально полно зависит только от первичного ключа все они находятся в 3НФ.

4. Приведение сущностей к НФБК. Так как сущности находятся в 3НФ и отсутствуют зависимости ключей от неключевых атрибутов все они находятся в НФБК.

5. Приведение сущностей к 4НФ. Так как сущности находятся в НФБК и отсутствуют независимые многозначные зависимости они находятся в 4НФ. реляционная формат база программное

4. Определение форматов данных и правил целостности реляционной модели

Таблица. Имена столбцов и типы данных таблицы "Адрес"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
Улица	VARCHAR2	20	PRIMARY KEY
Дом	NUMBER	4
Квартира	NUMBER	4
Телефон домашний	VARCHAR2	15
ID_Клиента	NUMBER	4	FOREIGN KEY

Каждый клиент имеет домашний адрес, и он уникален (PRIMARY KEY), связь с таблицей "Клиент" осуществляется посредствам внешнего ключа (FOREIGN KEY).

Таблица. Имена столбцов и типы данных таблицы "Клиент"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
ID_Клиента	NUMBER	4	PRIMARY KEY
Фамилия	VARCHAR2	20	NOT NULL
Имя	VARCHAR2	20	NOT NULL
Отчество	VARCHAR2	20
Должность	VARCHAR2	30

Каждый клиент имеет уникальный номер для однозначной его идентификации (PRIMARY KEY), для обращения к клиенту нужно знать имя и фамилию (NOT NULL).

Таблица. Имена столбцов и типы данных таблицы "Заказ"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
№ заказа	NUMBER	4	PRIMARY KEY
Дата оформления	DATE
Дата исполнения	DATE		NOT NULL
Стоимость заказа	NUMBER	6	NOT NULL
ID_Клиента	NUMBER	4	FOREIGN KEY
№ договора доставки	NUMBER	4	FOREIGN KEY

Номер заказа - уникальный номер для однозначной его идентификации (PRIMARY KEY), для возможности исполнения заказа в срок нужно знать дату его исполнения (NOT NULL), для расчета скидки нужно знать стоимость заказа (NOT NULL), для связи с таблицами "Клиент" и "Доставка" существуют соответствующие внешние ключи (FOREIGN KEY).

Таблица. Имена столбцов и типы данных таблицы "Состав заказа"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
№ заказа	NUMBER	4	PRIMARY KEY
Артикул изделия	NUMBER	4
Количество изделий в заказе	NUMBER	2

Столбцы "№ заказа" и "Артикул изделия" являются ключами из-за отношения "многие ко многим" в таблицах "Заказ" и "Изделие".

Таблица. Имена столбцов и типы данных таблицы "Доставка"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
№ договора доставки	NUMBER	4	PRIMARY KEY
Стоимость доставки	NUMBER	6,2
Транспортная компания	VARCHAR2	20	FOREIGN KEY

Номер договора доставки является уникальным номером для однозначной его идентификации (PRIMARY KEY), название транспортной компании является внешним ключом для связи с таблицей Грузоперевозки (FOREIGN KEY).

Таблица. Имена столбцов и типы данных таблицы "Грузоперевозки"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
Транспортная компания	VARCHAR2	20	PRIMARY KEY
Телефон ТК	VARCHAR2	10
ПредставительТК	VARCHAR2	40

Таблица "Грузоперевозки" является базовой и имеет только первичный ключ (PRIMARY KEY).

Таблица. Имена столбцов и типы данных таблицы "Изделие"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
Артикул изделия	NUMBER	4	PRIMARY KEY
Наименование изделия	VARCHAR2	20
Цена изделия	NUMBER	6,2	NOT NULL
Количество на складе	NUMBER	4	NOT NULL
Материал	VARCHAR2	10
Вес, кг	NUMBER	4,2
Габариты	VARCHAR2	10
Наименование поставщика	VARCHAR2	20	FOREIGN KEY

Артикул изделия - уникальный номер для однозначной его идентификации (PRIMARY KEY), для расчета стоимости заказа нужно знать цену изделий (NOT NULL), для того, чтобы вовремя заказать изделия на склад нужно знать их остаток (NOT NULL), для связи с таблицей "Поставщик мебели" существует внешний ключ (FOREIGN KEY).

Таблица. Имена столбцов и типы данных таблицы "Поставщик мебели"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
Наименование поставщика	VARCHAR2	20	PRIMARY KEY
ФИО представителя	VARCHAR2	40
Должность	VARCHAR2	30
Телефон рабочий	VARCHAR2	10

Таблица "Поставщик мебели" является базовой и имеет только первичный ключ (PRIMARY KEY).

Таблица. Имена столбцов и типы данных таблицы "Агент по продаже"

Имя столбца	Тип данных	Максимальная длина	Специальные условия
ФИО агента	VARCHAR2	40	PRIMARY KEY
Зарплата	NUMBER	8,2
№ заказа	NUMBER	4	FOREIGN KEY

ФИО агента являются уникальными и не должны иметь пустого значения для начисления зарплаты (PRIMARY KEY), для связи с таблицей "Заказ" существует внешний ключ (FOREIGN KEY).

5. Оценка памяти, необходимой для размещения всех таблиц

ОРЗ - общий размер заголовка блока;

СП - размер свободной памяти в блоке данных;

D - сумма длин столбцов для средней строки;

РСС - общий размер средней строки;

R - среднее число строк в блоке данных;

ЧБ - число блоков, требуемое для таблицы;

КБ - количество байт, требуемое для таблицы.

1. Таблица "Адрес"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 15 + 3 + 3 + 10 + 3 = 34 Байт

· РСС = 3 + 5*1 + 0*3 + 34 = 42 Байта

R = (1767,2-2R) / 42

R = 1767,2 / 44 = 40,16 Строк/блок

· ЧБ = 50 / 40,16 = 1,24 = 2 Блока

· КБ = 2 * 2048 = 4096 Байт.

2. Таблица "Клиент"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 3 + 15 + 15 + 15 + 20 = 68 Байт

· РСС = 3 + 5*1 + 0*3 + 68 = 76 Байт

R = (1767,2-2R) / 76

R = 1767,2 / 78 = 22,65 Строк/блок

· ЧБ = 50 / 22,65 = 2,2 = 3 Блока

· КБ = 3 * 2048 = 6144 Байт

3. Таблица "Заказ"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 3 + 7 + 7 + 3 + 3 + 3 = 26 Байт

· РСС = 3 + 6*1 + 0*3 + 26 = 35 Байт

R = (1767,2-2R) / 35

R = 1767,2 / 37 = 47,7 Строк/блок

· ЧБ = 50 / 47,7 = 1,04 = 2 Блока

· КБ = 2 * 2048 = 4096 Байт.

4. Таблица "Состав заказа"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 3 + 3 + 2 = 8 Байт

· РСС = 3 + 3*1 + 0*3 + 8 = 14 Байт

R = (1767,2-2R) / 14

R = 1767,2 / 16 = 110,45 Строк/блок

· ЧБ = 150 / 110,45 = 1,35= 2 Блока

· КБ = 2 * 2048 = 4096 Байт.

5. Таблица "Доставка"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 3 + 3 + 15 = 21 Байт

· РСС = 3 + 3*1 + 0*3 + 21 = 27 Байт

R = (1767,2-2R) / 27

R = 1767,2 / 29 = 60,93 Строк/блок

· ЧБ = 50 / 60,93 = 0,82 = 1 Блок

· КБ = 1 * 2048 = 2048 Байт.

6. Таблица "Грузоперевозки"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 15 + 7 + 30 = 52 Байт

· РСС = 3 + 3*1 + 0*3 + 52 = 58 Байт

R = (1767,2-2R) / 58

R = 1767,2 / 60 = 29,45 Строк/блок

· ЧБ = 30 / 29,45 = 1,01 = 2 Блока

· КБ = 2 * 2048 = 4096 Байт.

7. Таблица "Изделие"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 3 + 15 + 3 +3 + 7 + 1 + 7 + 15 = 54 Байта

· РСС = 3 + 8*1 + 0*3 + 54 = 65 Байт

R = (1767,2-2R) / 65

R = 1767,2 / 67 = 26,3 Строк/блок

· ЧБ = 50 / 26,3 = 1,9 = 2 Блока

· КБ = 2 * 2048 = 4096 Байт.

8. Таблица "Поставщик мебели"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 15 + 30 + 25 + 7 = 77 Байта

· РСС = 3 + 4*1 + 0*3 + 77 = 84 Байт

R = (1767,2-2R) / 84

R = 1767,2 / 86 = 20,54 Строк/блок

· ЧБ = 30 / 20,54 = 1,46 = 2 Блока

· КБ = 2 * 2048 = 4096 Байт.

9. Таблица "Агент по продаже"

ОРЗ = 80 + (4 + 2R) Байт

СП = 1767,2-2R Байт

· D = 30 + 3 + 3 = 36 Байта

· РСС = 3 + 3*1 + 0*3 + 36 = 42 Байт

R = (1767,2-2R) / 42

R = 1767,2 / 44 = 40,16 Строк/блок

· ЧБ = 10 / 40,16 = 0,24 = 1 Блок

· КБ = 1 * 2048 = 2048 Байт.

Общее количество байт для размещения всех таблиц составляет: 34816 Байта.

6. Создание БД по разработанной реляционной модели ПО

1. Создание таблицы "Адрес":

CREATE TABLE Адрес (

Улица VARCHAR2 (20),

Дом NUMBER (4),

Квартира NUMBER (4),

Телефон_домашний VARCHAR (15),

ID_Клиента NUMBER (4),

CONSTRAINT pk_улица_дом_квартира PRIMARY KEY (Улица, Дом, Квартира),

CONSTRAINT fk_id_клиента FOREIGN KEY (ID_Клиента) REFERENCES Клиент (ID_Клиента));

2. Создание таблицы "Клиент":

CREATE TABLE Клиент (

ID_Клиента NUMBER (4),

Фамилия VARCHAR2 (20) NOT NULL,

Имя VARCHAR2 (20) NOT NULL,

Отчество VARCHAR2 (20),

Должность VARCHAR2 (30),

CONSTRAINT pk_id_клиента PRIMARY KEY (ID_Клиента));

3. Создание таблицы "Заказ":

CREATE TABLE Заказ (

Ном_заказа NUMBER (4),

Дата_оформления DATE,

Дата_исполнения DATE NOT NULL,

Стоимость_заказа NUMBER (6) NOT NULL,

ID_Клиента NUMBER (4),

Ном_договора_доставки NUMBER (4),

CONSTRAINT pk_заказ PRIMARY KEY (Ном_заказа),

CONSTRAINT fk_клиент FOREIGN KEY (ID_Клиента) REFERENCES Клиент (ID_Клиента) ON DELETE CASCADE,

CONSTRAINT fk_договор FOREIGN KEY (Ном_договора_доставки) REFERENCES Доставка (Ном_договора_доставки) ON DELETE CASCADE);

4. Создание таблицы "Состав заказа":

CREATE TABLE Состав_заказа (

Ном_заказа NUMBER (4),

Артикул_изделия NUMBER (4),

Количество_изделий_в_заказе NUMBER (2),

CONSTRAINT pk_заказ_артикул PRIMARY KEY (Ном_заказа, Артикул_изделия),);

5. Создание таблицы "Доставка":

CREATE TABLE Доставка (

Ном_договора_доставки NUMBER (4),

Стоимость_доставки NUMBER (6,2),

Транспортная_компания VARCHAR2 (20),

CONSTRAINT pk_договор PRIMARY KEY (Ном_договора_доставки),

CONSTRAINT fk_тк FOREIGN KEY (Транспортная_компания) REFERENCES Грузоперевозки (Транспортная_компания));

6. Создание таблицы "Грузоперевозки":

CREATE TABLE Грузоперевозки (

Транспортная_компания VARCHAR2 (20),

Телефон_ТК VARCHAR2 (10),

Представитель_ТК VARCHAR2 (40),

CONSTRAINT pk_тк PRIMARY KEY (Транспортная_компания));

7. Создание таблицы "Изделие":

CREATE TABLE Изделие (

Артикул_изделия NUMBER (4),

Наименование_изделия VARCHAR2 (20),

Цена_изделия NUMBER (6,2) NOT NULL,

Количество_на_складе NUMBER (4) NOT NULL,

Материал VARCHAR2 (10),

Вес_кг NUMBER (4,2),

Габариты VARCHAR2 (10),

Наименование_поставщика VARCHAR2 (20),

CONSTRAINT pk_артикул PRIMARY KEY (Артикул_изделия),

CONSTRAINT fk_поставщик FOREIGN KEY (Наименование_поставщика) REFERENCES Поставщик_мебели (Наименование_поставщика));

8. Создание таблицы "Поставщик мебели":

CREATE TABLE Поставщик_мебели (

Наименование_поставщика VARCHAR2 (20),

ФИО_представителя VARCHAR2 (40),

Должность VARCHAR2 (30),

Телефон_рабочий VARCHAR2 (10),

CONSTRAINT pk_поставщик PRIMARY KEY (Наименование_поставщика));

9. Создание таблицы "Агент по продаже":

CREATE TABLE Агент_по_продаже (

ФИО_агента VARCHAR2 (40),

Зарплата NUMBER (8,2),

Ном_заказа NUMBER (4),

CONSTRAINT pk_агент PRIMARY KEY (ФИО_агента),

CONSTRAINT fk_заказ FOREIGN KEY (Ном_заказа) REFERENCES Заказ (Ном_заказа));

7. Первоначальное заполнение базы данных

1. Заполнение таблицы "Адрес":

INSERT INTO Адрес

VALUES ('Ленинградский пр.', 27, 141, '231-22-15', 77);

INSERT INTO Адрес

VALUES ('Ленинградский пр.', 31, 17, '244-52-55', 78);

INSERT INTO Адрес

VALUES ('Ленина ул.', 19, 8, '225-92-75', 79);

INSERT INTO Адрес (Улица, Дом, Квартира, ID_Клиента)

VALUES ('Ленинградский пр.', 28, 7, 333);

2. Заполнение таблицы "Клиент":

INSERT INTO Клиент

VALUES (77, 'Иванов', 'Иван', 'Иванович', 'Инженер');

INSERT INTO Клиент

VALUES (78, 'Рыбаков', 'Андрей', 'Валентинович', 'Охранник');

INSERT INTO Клиент

VALUES (79, 'Баринов', 'Федор', 'Кириллович', 'Менеджер');

INSERT INTO Клиент

VALUES (333, 'Абакумов', 'Федор', 'Кириллович', 'Слесарь');

3. Заполнение таблицы "Грузоперевозки":

INSERT INTO Грузоперевозки

VALUES ('Альянс', 'Говоров', '233-86-88');

INSERT INTO Грузоперевозки

VALUES ('Автотрейдинг', 'Кирилов', '222-66-55');

INSERT INTO Грузоперевозки

VALUES ('Грузовозов', 'Сомова', '238-73-37');

4. Заполнение таблицы "Изделие":

INSERT INTO Изделие

VALUES (8, 'Прихожая', 9500, 5, 'МДФ', 50, '50*200*185', 'Кардинал');

INSERT INTO Изделие

VALUES (9, 'Кресло', 8000, 9, 'Металл', 20, '70*70*100', 'Балтика');

INSERT INTO Изделие

VALUES (10, 'Диван', 9000, 8, 'Кожа', 60, '70*200*100', 'Феонит');

INSERT INTO Изделие

VALUES (11, 'Кух. гарнитур', 9000, 20, 'МДФ', 50, '90*200*200', 'Кардинал');

5. Заполнение таблицы "Поставщик мебели":

INSERT INTO Поставщик_мебели

VALUES ('Балтика', 'Александров', 'Менеджер', '254-17-15');

INSERT INTO Поставщик_мебели

VALUES ('Кардинал', 'Морозова', 'Менеджер', '255-36-98');

INSERT INTO Поставщик_мебели

VALUES ('Феонит', 'Карпов', 'Менеджер', '268-85-47');

6. Заполнение таблицы "Агент по продаже"INSERT INTO Агент_по_продаже:

VALUES ('Иванов', 0, 1);

INSERT INTO Агент_по_продаже

VALUES ('Петров', 0, 2);

INSERT INTO Агент_по_продаже

VALUES ('Сидоров', 0, 3);

INSERT INTO Агент_по_продаже

VALUES ('Сидоров', 0, 3);

8. Реализация функциональных задач

1. Оформление заказа №1:

INSERT INTO Доставка

VALUES (1, 500, 'Альянс');

INSERT INTO Заказ

VALUES (1, '03.05.2013', '11.05.2013', 9000, 77, 1);

INSERT INTO Состав_заказа

VALUES (1, 8, 1);

INSERT INTO Состав_заказа

VALUES (1, 9, 1);

2. Оформление заказа №2:

INSERT INTO Доставка

VALUES (2, 200, 'Автотрейдинг');

INSERT INTO Заказ

VALUES (2, '03.05.2013', '05.05.2013', 9000, 77, 2);

INSERT INTO Состав_заказа

VALUES (2, 9, 2);

INSERT INTO Состав_заказа

VALUES (2, 10, 2);

3. Оформление заказа №3:

INSERT INTO Доставка

VALUES (3, 200, 'Автотрейдинг');

INSERT INTO Заказ

VALUES (3, '17.12.2012', '18.12.2012', 24000, 337, 3);

INSERT INTO Состав_заказа

VALUES (3, 8, 1);

INSERT INTO Состав_заказа

VALUES (3, 9, 1);

INSERT INTO Состав_заказа

VALUES (3, 10, 1);



4. Отмена заказа №2:

DELETE FROM Заказ

WHERE Ном_заказа = 2;

3. Изменить фамилию, имя, отчество, домашний телефон клиента "Рыбаков":

UPDATE Клиент

SET Фамилия = 'Раков',

Имя = 'Петр',

Отчество = 'Евлампиевич'

WHERE ID_Клиента = 78;

UPDATE Адрес

SET Телефон_домашний = '777-25-47'

WHERE ID_Клиента = 78;

4. Выбрать заказы с периодом доставки меньше 2-х дней и стоимостью ниже 300 рублей:

SELECT DISTINCT Ном_заказа, Дата_оформления, Дата_исполнения, Стоимость_доставки

FROM Заказ, Доставка

WHERE Дата_исполнения - Дата_оформления < 2 AND Стоимость_доставки < 300;

5. Добавить в заказ 3 изделия от поставщиков "Балтика", "Кардинал", "Феонит":

INSERT INTO Состав_заказа

VALUES (1, 10, 1);

UPDATE Состав_заказа

SET Количество_изделий_в_заказе = Количество_изделий_в_заказе + 1

WHERE Ном_заказа = 1;

6. Выдать первых пять заказчиков с наибольшей общей стоимостью заказов за последний месяц:

SELECT ID_Клиента, Стоимость_заказа

FROM (SELECT ID_Клиента, Стоимость_заказа

FROM Заказ

ORDER BY Стоимость_заказа DESC)

WHERE ROWNUM <= 5;

7. Добавить поставщика мебели:

INSERT INTO Поставщик_мебели

VALUES ('Изумруд', 'Попов', 'Менеджер', '261-44-33');

8. Уменьшить количество изделий "Кухонный гарнитур" на складе на 15 %:

UPDATE Изделие

SET Количество_на_складе = Количество_на_складе - (Количество_на_складе / 100 * 15)

WHERE Артикул_изделия = 11;

9. Уволить агента, оформившего наименьшее количество заказов:

DELETE

FROM Агент_по_продаже

WHERE ФИО_агента =

(SELECT Фамилия_агента

FROM Заказ

GROUP BY Фамилия_агента

HAVING (COUNT(*)) =

(SELECT MIN(COUNT(*))

FROM Заказ

GROUP BY Фамилия_агента));

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

SELECT Заказ. Ном_заказа, Дата_оформления, ФИО_агента

FROM Заказ, Агент_по_продаже

WHERE ФИО_агента LIKE 'Ба %';

11. Определить размер заработной платы агента "Русинова":

SELECT SUM (Стоимость_заказа * 0.02)

FROM Заказ, Агент_по_продаже

WHERE ФИО_агента = 'Русинов';

12. Выдать информацию об агенте, совершившем наибольшее количество сделок в "Январе" месяце текущего года:

SELECT Фамилия_агента, count(*)

FROM Заказ

WHERE Дата_оформления BETWEEN TO_DATE('01.05.2013','DD/MM/YYYY') AND TO_DATE('31.05.2013','DD/MM/YYYY')

GROUP BY Фамилия_агента

HAVING COUNT(*) =

(SELECT MAX(COUNT(*))

FROM Заказ

WHERE Дата_оформления BETWEEN TO_DATE('01.05.2013','DD/MM/YYYY') AND TO_DATE('31.05.2013','DD/MM/YYYY')

GROUP BY Фамилия_агента);

13. Выдать общую стоимость заказа с учётом скидки (если она применима к данному заказу) и стоимостью доставки:

SELECT Ном_заказа, Стоимость_заказа - Стоимость_заказа*Скидка + Стоимость_доставки

FROM Заказ, Доставка

WHERE Заказ. Ном_договора_доставки = Доставка. Ном_договора_доставки;

14. Подсчитать общий объём продаж фирмы за период с 1 января текущего года по текущую дату:

SELECT SUM (Стоимость_заказа)

FROM Заказ

WHERE Дата_оформления >= TO_DATE('01.01.2013','DD/MM/YYYY') AND

Дата_оформления <= TO_DATE(sysdate);

15. Подсчитать среднемесячный объем продаж фирмы за период с 1 января текущего года по текущую дату:

SELECT AVG(SUM(Стоимость_заказа - Стоимость_заказа*Скидка))

FROM Заказ

WHERE Дата_оформления >= TO_DATE('01.01.2013','DD/MM/YYYY') AND

Дата_оформления <= TO_DATE(sysdate)

GROUP BY TRUNC(TO_DATE(Дата_оформления),'MONTH');

16. Вывести самого "активного" покупателя (совершившего наибольшее число заказов):

SELECT Фамилия, Имя, Отчество, COUNT(*)

FROM Клиент, Заказ

WHERE Клиент.ID_Клиента = Заказ.ID_Клиента

GROUP BY Фамилия, Имя, Отчество

HAVING COUNT(*) =

(SELECT MAX(COUNT(*))

FROM Заказ

GROUP BY ID_клиента);

17. Вывести информацию о покупателях, заказы которых превысили 20000 рублей:

SELECT Фамилия, Имя, Отчество, SUM (Стоимость_заказа)

FROM Клиент, Заказ

WHERE Клиент.ID_Клиента = Заказ.ID_Клиента

GROUP BY Фамилия, Имя, Отчество

HAVING SUM (Стоимость_заказа) > 20000;



18. Составить список адресов доставки транспортной компании на конкретную дату.

SELECT DISTINCT Транспортная_компания, Улица, Дом, Квартира

FROM Адрес, Заказ, Доставка

WHERE Адрес. ID_Клиента = Заказ. ID_Клиента AND Заказ. Ном_договора_доставки = Доставка. Ном_договора_доставки AND Дата_исполнения = TO_DATE ('11.05.2013','DD/MM/YYYY');

19. Выдать список заказов, совершенных клиентом "Петровым" за период с "Января" по "Декабрь" прошлого года:

SELECT Ном_заказа

FROM Заказ, Клиент

WHERE Фамилия = 'Петров' AND

Дата_оформления >= TO_DATE('01.01.2012','DD/MM/YYYY') AND

Дата_оформления < TO_DATE('01.12.2012','DD/MM/YYYY');

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

SELECT ФИО_агента

FROM Заказ, Агент_по_продаже

WHERE Заказ. Фамилия_агента = Агент_по_продаже. ФИО_агента AND TRUNC(TO_DATE(Дата_оформления),'Month') = TO_DATE('01.05.2013','DD/MM/YYYY')

GROUP BY ФИО_Агента

HAVING SUM(Стоимость_заказа)*

(SELECT Count(*)

FROM Агент_по_продаже) >

(SELECT SUM(Стоимость_заказа)*0.02/(12)

FROM Заказ

WHERE TRUNC(TO_DATE(Дата_оформления),'YEAR') = TO_DATE('01.01.2013','DD/MM/YYYY'));

21. Выдать список заказов, доставленных по конкретному адресу с суммой менее 15000 рублей:

SELECT DISTINCT Ном_заказа

FROM Заказ, Клиент, Адрес

WHERE Улица = 'Ленинградский пр.' AND Дом = 27 AND Квартира = 141 AND Стоимость_заказа < 15000;

22. Выдать список клиентов, не имеющих домашних телефонов, проживающих на улице "Ленинградский проспект":

SELECT Фамилия, Имя, Отчество, Улица, Телефон_домашний

FROM Клиент, Адрес

WHERE Клиент.ID_Клиента = Адрес. ID_Клиента AND Улица LIKE 'Ленинградский %' AND Телефон_домашний IS NULL;

23. Вывести ФИО клиентов, оформивших меньшее количество заказов, чем "Иванов":

SELECT Фамилия, Имя, Отчество, COUNT(*)

FROM Клиент, Заказ

WHERE Клиент.ID_Клиента = Заказ.ID_Клиента

GROUP BY Фамилия, Имя, Отчество

HAVING COUNT(*) <

(SELECT COUNT(*)

FROM Клиент, Заказ

WHERE Клиент.ID_Клиента = Заказ.ID_Клиента and Фамилия = 'Иванов');

24. По всем неисполненным заказам вывести подробную информацию о фирмах поставщиках:

SELECT Транспортная_компания

FROM Заказ, Доставка

WHERE Дата_исполнения is null;



25. Вычислить среднюю стоимость заказов "Иванова", "Семенова":

SELECT Фамилия, AVG (Стоимость_заказа)

FROM Заказ, Клиент WHERE Клиент.ID_Клиента = Заказ.ID_Клиента

AND Фамилия in ('Иванов', 'Семенов')

GROUP BY Фамилия;

Размещено на Allbest.ru

...