ind

/*
Задача 4. База данных фирмы, проводящей аукционы .
Фирма занимается продажей с аукциона антикварных изделий и
произведений искусства. Владельцы вещей, выставляемых на проводимых фирмой
аукционах, юридически являются продавцами. Лица, приобретающие эти вещи,
именуются покупателями. Получив от продавцов партию предметов, фирма решает,
на котором из аукционов выгоднее представить конкретный предмет.
Перед проведением очередного аукциона каждой из выставляемых на нем
вещей присваивается отдельный номер лота. Две вещи,
продаваемые на различных аукционах, могут иметь одинаковые номера лотов.
В книгах фирмы делается запись о каждом аукционе. Там отмечаются дата,
место и время его проведения, а также специфика (например,
выставляются картины, на писанные маслом и не ранее 1900 г.).
Заносятся также сведения о каждом продаваемом предмете:
аукцион, на который он заявлен, номер лота, продавец, отправная цена
и краткое словесное описание. Продавцу разрешается выставлять
любое количество вещей, а покупатель имеет право приобретать
любое количество вещей. Одно и то же лицо или фирма может выступать
и как продавец, и как покупатель. После аукциона служащие фирмы,
проводящей аукционы, записывают фактическую цену, уплаченную
за проданный предмет, и фиксируют данные покупателя.
Создать триггер для проверки того, что вещь в один день не находится
на двух аукционах.
Написать запросы, осуществляющие следующие операции:
1) Вывести список аукционов с указанием отсортированных по величине
 суммарных доходов от продажи.
2) Для указанного интервала дат вывести список проданных на аукционах
 предметов. Для каждого из предметов дать список аукционов,
 где выставлялся этот же предмет.
3) Для указанного интервала дат вывести список продавцов в порядке убывания
 общей суммы, полученной ими от продажи предметов в этот промежуток времени.
4) Для указанного места вывести список аукционов, отсортированных
 по количеству выставленных вещей.
5) Для указанного интервала дат вывести список продавцов, которые принимали участие в аукционах, с указанием для каждого из них списка выставленных предметов.
6) Вывести список покупателей с указанием количества приобретенных предметов в указанный период времени.
*/

USE master
GO

IF  EXISTS (
	SELECT name
		FROM sys.databases
		WHERE name = N'Auction'
)
ALTER DATABASE [Auction] set single_user with rollback immediate
GO

IF  EXISTS (
	SELECT name
		FROM sys.databases
		WHERE name = N'Auction'
)
DROP DATABASE [Auction]
GO

CREATE DATABASE [Auction]
GO

USE [Auction]
GO

IF EXISTS(
  SELECT *
    FROM sys.schemas
   WHERE name = 'Auct'
) DROP SCHEMA Auct
GO

CREATE SCHEMA Auct
GO

if OBJECT_ID('clients', 'u') is not null
	drop table clients
go

if OBJECT_ID('books', 'u') is not null
	drop table books
go

if OBJECT_ID('goods', 'u') is not null
	drop table goods
go

if OBJECT_ID('auct_type', 'u') is not null
	drop table auct_type
go

if OBJECT_ID('lot', 'u') is not null
	drop table lot
go

if OBJECT_ID('buy', 'u') is not null
	drop table buy
go


if OBJECT_ID('correct_data', 't') is not null
	drop trigger correct_data
go


create table clients(
	id int PRIMARY KEY IDENTITY NOT NULL,
	name nvarchar(128)
)
go

insert into clients values
('Фирма 1'),
('Фирма 2'),
('Фирма 3'),
('Фирма 4'),
('Фирма 5')
go

select * from clients
go


create table auct_type(
	id int PRIMARY KEY IDENTITY NOT NULL,
	auct_type_name varchar(128) NOT NULL
)
go

insert into auct_type values
('Специфика 1'),
('Специфика 2'),
('Специфика 3'),
('Специфика 4')
go


create table books(
	id int PRIMARY KEY IDENTITY NOT NULL,
	auct_date date NOT NULL,
	auct_time time NOT NULL,
	auct_place varchar(128) NOT NULL,
	auct_type_id int NOT NULL

	FOREIGN KEY (auct_type_id) REFERENCES auct_type(id)
)
go

insert into books values
('20150101', '12:00:00', 'Место 1', 1),
('20150102', '12:00:00', 'Место 2', 2),
('20150103', '12:00:00', 'Место 3', 4),
('20150104', '12:00:00', 'Место 4', 1),
('20150101', '13:00:00', 'Место 2', 1)
go


select * from clients

create table goods(
	id int PRIMARY KEY IDENTITY NOT NULL,
	name nvarchar(128) NOT NULL,
	seller int NOT NULL

	FOREIGN KEY (seller) REFERENCES clients(id)
)
go

insert into goods values
('Вещь 1', 1),
('Вещь 2', 1),
('Вещь 1', 2),
('Вещь 1', 3),
('Вещь 1', 4),
('Вещь 5', 5)
go


create table lots(
	id int PRIMARY KEY IDENTITY NOT NULL,
	id_good int not null,
	id_lot int not null,
	id_auct int not null,
	start_cost float not null,
	descr_id int not null

	FOREIGN KEY (id_good) REFERENCES goods(id),
	FOREIGN KEY (descr_id) REFERENCES auct_type(id),
	FOREIGN KEY (id_auct) REFERENCES books(id)
)
go


create trigger correct_data ON lots AFTER INSERT AS
begin
	select * from inserted
	select * from lots
	DECLARE @counter int = (
								SELECT count(*)
								FROM inserted
								INNER JOIN books AS IBooks ON IBooks.id = inserted.id_auct
								INNER JOIN lots ON lots.id_good = inserted.id_good
								INNER JOIN books AS CBooks ON CBooks.id = lots.id_auct
								WHERE inserted.id != lots.id and
									inserted.id_good = lots.id_good and
									IBooks.auct_date = CBooks.auct_date and
									IBooks.auct_place != CBooks.auct_place
							)
	IF @counter > 0
	BEGIN
		print ('В этот день товар уже выставлен на другом аукционе');
		ROLLBACK TRANSACTION;
		RETURN
	END
end
go

--плохо для триггера
/*insert into lots values
(1, 1, 1, 1, 1)
go
insert into lots values
(1, 2, 5,  20, 4)
go*/




create table buy(
	fact_cost int not null,
	id_buyer int not null,
	id_good int not null,
	id_auct int not null

	FOREIGN KEY (id_buyer) REFERENCEs clients(id),
	FOREIGN KEY (id_good) REFERENCES goods(id),
	FOREIGN KEY (id_auct) REFERENCES books(id)
)


--Вывести список аукционов с указанием отсортированных по величине суммарных доходов от продажи.
SELECT id as 'Номер аукциона'
FROM books AS CBooks
ORDER BY (
			  SELECT sum(fact_cost)
			  FROM buy
			  WHERE id_auct = CBooks.id
		  )
go


--Для указанного интервала дат вывести список проданных на аукционах предметов. Для каждого из предметов дать список аукционов, где выставлялся этот же предмет.
DECLARE @start	date = '20010101'
DECLARE @end	date = '20151212'
SELECT goods.name	AS	'Проданный предмет',
	   books.id		AS	'Аукцион, на котором продали',
	   (SELECT CBooks.id
	    FROM lots
		INNER JOIN books AS CBooks ON CBooks.id = lots.id_auct
		WHERE lots.id_good = buy.id_good) AS 'Бывшие аукционы'
FROM buy
INNER JOIN goods ON buy.id_good = goods.name
INNER JOIN books ON buy.id_auct = books.id
GO


--Для указанного интервала дат вывести список продавцов в порядке убывания общей суммы, полученной ими от продажи предметов в этот промежуток времени.
DECLARE @start	date = '20010101'
DECLARE @end	date = '20151212'
SELECT clients.name
FROM lots
INNER JOIN goods ON goods.id = lots.id_good
INNER JOIN clients ON clients.id = goods.seller
INNER JOIN buy ON lots.id_good = buy.id_good
ORDER BY (SELECT sum(lots.start_cost) FROM lots) --?????
GO

--Для указанного места вывести список аукционов, отсортированных по количеству выставленных вещей
DECLARE @place varchar(128) = 'Место 1'
SELECT id
FROM books
WHERE auct_place = @place
ORDER BY (SELECT count(*)
		  FROM lots
		  where lots.id_auct = id)
GO

--Для указанного интервала дат вывести список продавцов, которые принимали участие в аукционах, с указанием для каждого из них списка выставленных предметов.
DECLARE @start date = '20010101'
DECLARE @end date = '20160101'
SELECT clients.name AS 'Продавец',
	   goods.name AS 'Выставленный предмет'
FROM lots
INNER JOIN goods ON goods.id = lots.id_good
INNER JOIN clients ON goods.seller = clients.id
INNER JOIN books ON books.id = lots.id_auct
WHERE books.auct_date > @start and books.auct_date < @end
go


--Вывести список покупателей с указанием количества приобретенных предметов в указанный период времени.
DECLARE @start date = '20010101'
DECLARE @end date = '20160101'
SELECT clients.name,
	   (SELECT count(*) FROM buy AS CBuy WHERE CBuy.id_buyer = buy.id_buyer) AS 'Количество приобретенных предметов'
FROM buy
INNER JOIN clients ON buy.id_buyer = clients.id
INNER JOIN books ON books.id = buy.id_auct
WHERE books.auct_date > @start and books.auct_date < @end
go