Ирина Эланс

Автор который поможет с любыми образовательными и учебными заданиями

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

СОДЕРЖАНИЕ

ПОСТАНОВКА ЗАДАЧИ …………………………………………………………….. 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

create procedure find_purchases_masked @mask varchar(25)

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+'%'

Результат:

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

create procedure find_purchases

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;

Результат:

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

create procedure selled_products

@count int

select pr.product_code as code,

pr.product_name as NAME,

COUNT(*) as [count],

max(p.purchase_cost)

as max_cost,

min(p.purchase_cost)

as min_cost

from dbo.products as pr

join dbo.purchases as p

on pr.product_id = p.product_id

group by pr.product_code, pr.product_name

having COUNT(*) > @count

Результат:

execute selled_products @count = 2

4) if exists (select * from dbo.sysobjects

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

)

drop procedure number_of_sold

create procedure number_of_sold

select pr.product_code as code,

pr.product_name as NAME,

sum(p.purchase_count) as number

from dbo.products as pr join

dbo.purchases as p

on pr.product_id = p.product_id

group by pr.product_code,pr.product_id, pr.product_name

Результат:

execute number_of_sold

5) if exists (select * from dbo.sysobjects

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

)

drop procedure best_buyer

create procedure best_buyer @b datetime, @e datetime

select top 1

case

when charindex(' ', p.purchase_buyer)=0 then p.purchase_buyer

else substring(p.purchase_buyer,0, charindex(' ', p.purchase_buyer))

end as [Покупатель],

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 Spent]

from dbo.purchases as p

where p.purchase_time >= @b and p.purchase_time <= @e

group by p.purchase_buyer, (case

when charindex(' ', p.purchase_buyer)=0 then p.purchase_buyer

else substring(p.purchase_buyer,0, charindex(' ', p.purchase_buyer))

end)

order by [Total Spent] desc

Результат:

execute best_buyer @b = '2007-20-12', @e = '2008-20-12'

Задание 5.

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

DROP FUNCTION IsYearLeap

CREATE FUNCTION IsYearLeap

(

@year int

)

RETURNS INT

BEGIN

RETURN CASE WHEN ((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0) THEN 1 ELSE 0 END

END

DROP FUNCTION GetMaxDaysInMonth

CREATE FUNCTION GetMaxDaysInMonth

(

@month int,

@year int

)

RETURNS INT

BEGIN

RETURN CASE WHEN @month IS NULL THEN 31

WHEN @month IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN @month IN (4, 6, 9, 11) THEN 30

WHEN @month = 2 THEN CASE WHEN (@year IS NULL) OR (dbo.IsYearLeap(@year) <> 0)

THEN 29 ELSE 28

END

ELSE 0

END

1) create procedure max_purchase @code char(15), @year int, @month int, @maxcost smallmoney output

CREATE TABLE #tempTable

(

p_day INT ,

p_sum SMALLMONEY

);

insert into #tempTable

select DAY(p.purchase_time) as p_day,

(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 p_sum

from dbo.purchases as p join

dbo.products pr on p.product_id = pr.product_id

where pr.product_code = @code and year(p.purchase_time) = @year and month(p.purchase_time) = @month

group by DAY(p.purchase_time)

declare @to int = dbo.GetMaxDaysInMonth(@month, @year)

declare @counter int = 1

while @counter <= @to

begin

if not exists(select * from #tempTable where p_day = @counter)

begin

insert into #tempTable (p_day, p_sum) values (@counter, 0)

end

set @counter = @counter + 1

end

select * from #tempTable order by p_day

select @maxcost = max(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) from dbo.purchases as p

join

dbo.products pr on p.product_id = pr.product_id

where pr.product_code = @code and year(p.purchase_time) = @year and month(p.purchase_time) = @month

group by DAY(p.purchase_time)

Результат:

declare @var smallmoney

execute max_purchase @code = 'PIZZA', @year = 2007, @month = 12 ,@maxcost = @var output

select @var

2) drop procedure purchases_incr

create procedure purchases_incr @code char(15), @year int, @month int

CREATE TABLE #tempTable

(

p_day INT ,

p_count int

);

insert into #tempTable

select DAY(p.purchase_time) as p_day,

sum(p.purchase_count) as p_count

from dbo.purchases as p join

dbo.products pr on p.product_id = pr.product_id

where pr.product_code = @code and year(p.purchase_time) = @year and month(p.purchase_time) = @month

group by DAY(p.purchase_time)

declare @to int = dbo.GetMaxDaysInMonth(@month, @year)

declare @counter int = 1

while @counter <= @to

begin

if not exists(select * from #tempTable where p_day = @counter)

begin

insert into #tempTable (p_day, p_count) values (@counter, 0)

end

set @counter = @counter + 1

end

select p_day, (select SUM(tt.p_count) from #tempTable tt where tt.p_day <= t.p_day) from #tempTable t order by p_day

return 0

Результат: