База Данных "Автосалон" MSSQL

2

СОДЕРЖАНИЕ

ПОСТАНОВКА ЗАДАЧИ …………………………………………………………….. 3

ВЫПОЛНЕНИЕ РАБОТЫ …………………………………………………………… 6

ЗАДАНИЕ №1 ………………………………………………………………….. 6

ЗАДАНИЕ №2 ………………………………………………………………….. 7

ЗАДАНИЕ №3 ………………………………………………………………….. 9

ЗАДАНИЕ №4 …………………………………………………………………. 11

ЗАДАНИЕ №5 ………………………………………………………………… 14

ПОСТАНОВКА ЗАДАЧИ

Задание 1.

Создать базу данных,  состоящую из следующих таблиц.

Справочник товаров.

Поля:

Идентификатор товара (целочисленное (4 байта),  автоматическая генерация значения: начальное значение - 10, приращение - 2), первичный ключ

Код товара (тип – текстовый, постоянное кол-во символов, 15 символов), обязательное

Наименование товара (тип – текстовый, переменное кол-во символов, 25 символов), обязательное

Описание (тип текстовый Unicode,  переменное кол-во символов,  1000 символов), не обязательное

Начало продаж (тип – дата и время (4 байта)), обязательное

Реестр покупок

Поля:

Идентификатор покупки (целочисленное (8 байт),  автоматическая генерация значения: начальное значение - 1, приращение - 1)

Дата и время покупки (тип – дата и время (8 байт)), обязательное

Идентификатор купленного товара (тип –целочисленный (4 байта))  - ссылка на справочник товаров, обязательное

Кол-во проданного товара  (тип –целочисленный (2 байта)), обязательное

Цена единицы товара (тип – денежный(4 байта)), обязательное

Скидка (тип – дробное с фиксированной точкой (2 разряда на целую часть, 2 разряда – на десятичную)) – скидка в процентах от суммы покупки, необязательное

Покупатель (тип – текстовый, переменное кол-во символов, 100 символов) – фамилия, имя и отчество покупателя, необязательное

Задание 2.

Заполните базу данных:

Введите 10 товаров

Введите 20-30 покупок. Покупки должны быть за 2007 и 2008 года.  В поле покупатель должны присутствовать  значения в форматах:

Фамилия

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

Фамилия Имя

Фамилия И.О.

Фамилия И.

Задание 3.

Напишите запрос , который выберет из реестра покупок  все покупки, совершенные ранее 01.01.2008. Запрос должен вернуть все поля из реестра покупок.

Напишите запрос, который вернет покупки для определенного кода товара. Запрос должен вернуть следующие поля: Наименование товара, Покупатель, Кол-во проданного товара.

Напишите запрос, который вернёт все покупки для товаров, наименования которых содержат определённую подстроку. Запрос должен вернут следующие поля: Идентификатор покупки, Покупатель, Наименование товара, Сумма покупки (Кол-во товара * Цена ед. товара * (100% - Скидка)). Запрос оформить в виде хранимой процедуры.

Задание 4.

Напишите запрос, который выводит среднюю скидку, максимальную и минимальную цену для определённого id товара.

Напишите запрос,  выводящий суммы покупок и среднюю цену по кодам товара. Товары, которые ни разу не продавались, НЕ ДОЛЖНЫ присутствовать в результате запроса. Запрос оформить в виде хранимой процедуры.

Напишите запрос, выводящий товары, с количеством продаж более определенного значения (параметр хранимой процедуры). Одна продажа – один факт продажи, вне зависимости от кол-ва проданного  товара.  Результат  запроса должен содержать следующие поля:  код товара,  наименование товара,  кол-во продаж, максимальную и минимальную цену товара. Запрос оформить в виде хранимой процедуры.

Напишите запрос, выводящий ВСЕ товары и суммарное кол-во купленных единиц для каждого товара. Результат запроса должен содержать поля: код товара, имя товара и кол-во товара. Запрос оформить в виде хранимой процедуры.

Напишите запрос, выводящий Фамилию самого ценного покупателя (покупателя, заплатившего больше всего за покупки) за определённый период (начало и конец периода передается в виде параметров). Обратите внимание, что в поле покупатель могут быть покупатели в разных форматах. Запрос оформить в виде хранимой процедуры.

Задание 5.

Напишите хранимую процедуру, которая возвращает (в виде выходного параметра) максимальную сумму покупки для определённого кода товара (код товара – входной параметр хранимой процедуры) на определённый месяц (входные параметры: год и месяц), а также историю продаж товара по дням - два столбца: день месяца (должны быть все дни указанного месяца), сумма покупок. Проверьте результат выполнения процедуры вручную.

Напишите хранимую процедуру, которая возвращает для определённого товара на указанный месяц (входные параметры: код товара, год и месяц) кол-во проданного товара нарастающим итогом (т.е. каждая строчка должна содержать кол-во проданного товара с начала месяца до текущего дня включительно). Проверьте результат выполнения процедуры вручную.

ВЫПОЛНЕНИЕ РАБОТЫ

Задание 1.

create database mydb

use mydb

create table dbo.products(

              product_id int not null identity(10,2),

              product_code char(15) not null,

              product_name varchar(25) not null,

              product_desc nvarchar(1000) null,

              product_SoS smalldatetime not null

              )

alter table dbo.products

              add constraint pk_products primary key (product_id)

create table dbo.purchases(

              purchase_id bigint not null identity(1,1),

              purchase_time datetime not null,

              product_id int not null,

              purchase_count smallint not null,

              purchase_cost smallmoney not null,

              purchase_discount decimal(4,2) null,

              purchase_buyer varchar(100) null

)

alter table dbo.purchases

              add constraint fk_purchases foreign key (product_id) references dbo.products(product_id)

Задание 2.

insert dbo.products(product_code, product_name,product_SoS)

              values('PIZZA', 'Пица с грибами', '2005-01-01 00:00:00'),

                              ('TEA', 'Индийский чай', '2005-01-01 00:00:00'),

                              ('BREAD', 'Нарезной батон','2005-01-01 00:00:00'),

                            ('WATER', 'Вода в бутылке','2005-01-01 00:00:00'),

                              ('POTATO', 'Картошка', '2005-01-01 00:00:00'),

                              ('CHOCOLATE', 'Шоколадный батончик','2005-01-01 00:00:00'),

                              ('DOUGHNUTS', 'Пончики', '2005-01-01 00:00:00'),

                              ('SHAMPOO', 'Шампунь', '2005-01-01 00:00:00'),

                              ('JUICE', 'Апельсиновый сок', '2005-01-01 00:00:00'),

                              ('DVD', 'DVD фильм', '2005-01-01 00:00:00')

select * from dbo.products             

insert dbo.purchases(purchase_time, product_id, purchase_count, purchase_cost, purchase_discount,purchase_buyer )

              values('2007-12-12 12:30:00', 12, 1, 50.00, 3.50 ,'Дунаев А.'),

                              ('2007-13-12 8:30:00', 16, 1, 30.50, NULL,'Поташев Д. А.'),

                              ('2007-14-12 21:20:00', 10, 2, 49.75, NULL,'Анисимов'),

                              ('2007-15-12 12:15:00', 18, 1, 60.40, 2, 'Анисимов Алексей'),

                              ('2007-16-12 18:45:00', 14, 1, 21.50, 1,'Поташев Дмитрий Александрович'),

                              ('2007-17-12 8:45:00', 16, 1, 30.50, NULL,'Поташев Дмитрий'),

                              ('2007-18-12 19:11:00', 18, 2, 60.40, 3,'Разина А.'),

                              ('2007-19-12 20:00:00', 26, 2, 14.88, NULL,NULL),

                              ('2007-20-12 20:00:00', 26, 8, 14.88, NULL,NULL),

                              ('2007-21-12 8:00:00', 16, 1, 30.50, 2,'Поташев Д.'),

                              ('2007-22-12 14:15:00', 12, 1, 50.00, NULL,'Дунаев А. Н.'),

                              ('2007-23-12 16:45:00', 12, 2, 50.00, 3.75,'Дунаев Александр'),

                              ('2007-24-12 20:00:00', 10, 2, 49.75, NULL,'Разина Анастасия'),

                              ('2007-25-12 14:59:00', 10, 2, 49.75, NULL,'Поташев Д.'),

                              ('2007-26-12 12:31:00', 16, 1, 30.20, 7.25,'Дунаев А. Н.'),

                              ('2007-27-12 12:31:00', 18, 3, 60.32, 3.50,'Путинцев А.'),

                              ('2007-30-12 16:00:00', 10, 20, 49.75, 5,'Поташев Д. А.'),

                              ('2007-30-12 16:00:00', 14, 2, 21.50, 5,'Поташев Д. А.'),

                              ('2007-30-12 16:00:00', 12, 4, 50.00, 5,'Поташев Д. А.'),

                              ('2007-30-12 16:00:00', 18, 4, 60.40, 5,'Поташев Д. А.'),

                              ('2007-30-12 16:00:00', 16, 2, 30.50, 5,'Поташев Д. А.'),

                              ('2008-02-01 7:22:00', 22, 1, 115.50, NULL,'Поташев Д. А.'),

                              ('2008-03-01 20:00:00', 26, 8, 14.88, NULL,'Поташев Д. А.'),

                              ('2008-04-01 12:30:00', 24, 1, 32.17, NULL,'Поташев Д. А.'),

                              ('2008-05-01 12:30:00', 28, 1, 99.99, NULL,'Дунаев А. Н.')

Результат:

Задание 3.

1)  select * from dbo.purchases

              where purchase_time < '2008-01-01'

Результат:

2) select pr.product_name, p.purchase_buyer, p.purchase_count

              from dbo.products as pr

                            join dbo.purchases as p

                            on pr.product_id = p.product_id

              where pr.product_code = 'PIZZA'

Результат:

3) if exists (select * from dbo.sysobjects

                                                                      where id = object_id(N'find_purchases_masked')  and objectproperty(id, N'IsProcedure') = 1

               )

   drop procedure find_purchases_masked

go

create procedure find_purchases_masked @mask varchar(25)

as

              select pr.product_id, p.purchase_buyer, pr.product_name,

                            case when p.purchase_discount is null then p.purchase_count * p.purchase_cost

                                                                                                                                                          else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)

                            end as [Total cost]

                            from dbo.products as pr

                                          join dbo.purchases as p

                                          on pr.product_id = p.product_id

                            where pr.product_name like '%'+@mask+'%'

go

Результат:

execute find_purchases_masked @mask = 'ин'
 

Задание 4.

1) select SUM(p.purchase_discount) / COUNT(*) as Average_discount,

                 MIN(p.purchase_cost)

                               as min_cost,

                            max(p.purchase_cost)

                                          as max_cost

                            from dbo.purchases as p

                            where product_id = 18

Результат:

2) if exists (select * from dbo.sysobjects

                                                                      where id = object_id(N'find_purchases')  and objectproperty(id, N'IsProcedure') = 1

               )

   drop procedure find_purchases

go

create procedure find_purchases

as

              select pr.product_code as code,

              sum(case when p.purchase_discount is null then p.purchase_count * p.purchase_cost

                                                                                                                                                          else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)

                                          end) as [Total Cost],

                                          avg(p.purchase_cost) as Avg_cost

                                          from dbo.purchases as p join

                                                        dbo.products as pr on pr.product_id = p.product_id group by pr.product_code;

go

Результат:

execute find_purchases

3) if exists (select * from dbo.sysobjects

                                                                      where id = object_id(N'selled_products')  and objectproperty(id, N'IsProcedure') = 1

               )

   drop procedure selled_products

go

create procedure selled_products

              @count int

as

select pr.product_code as code,

                            pr.product_name as NAME,