Автор: Пользователь скрыл имя, 23 Марта 2012 в 19:03, курсовая работа
Задание 1.
Создать базу данных, состоящую из следующих таблиц.
Справочник товаров.
ПОСТАНОВКА ЗАДАЧИ …………………………………………………………….. 3
ВЫПОЛНЕНИЕ РАБОТЫ …………………………………………………………… 6
ЗАДАНИЕ №1 ………………………………………………………………….. 6
ЗАДАНИЕ №2 ………………………………………………………………….. 7
ЗАДАНИЕ №3 ………………………………………………………………….. 9
ЗАДАНИЕ №4 …………………………………………………………………. 11
ЗАДАНИЕ №5 ………………………………………………………………… 14
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
go
Результат:
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
go
create procedure number_of_sold
as
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
go
Результат:
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
go
create procedure best_buyer @b datetime, @e datetime
as
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
go
Результат:
execute best_buyer @b = '2007-20-12', @e = '2008-20-12'
Задание 5.
Для выполнения этого задания дополнительно были реализованы функции определяющий число дней в месяце, а так же является ли год високосным.
DROP FUNCTION IsYearLeap
GO
CREATE FUNCTION IsYearLeap
(
@year int
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN ((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0) THEN 1 ELSE 0 END
END
GO
DROP FUNCTION GetMaxDaysInMonth
GO
CREATE FUNCTION GetMaxDaysInMonth
(
@month int,
@year int
)
RETURNS INT
AS
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
END
GO
1) create procedure max_purchase @code char(15), @year int, @month int, @maxcost smallmoney output
as
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)
go
Результат:
declare @var smallmoney
execute max_purchase @code = 'PIZZA', @year = 2007, @month = 12 ,@maxcost = @var output
select @var
2) drop procedure purchases_incr
go
create procedure purchases_incr @code char(15), @year int, @month int
as
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
go
Результат: