LabaSqlGraph

--Создание схем как на паре
create schema [Nodes]
go
create schema [Edges]
go


--Создание графовых таблиц с человеческими названиями
create table Nodes.SprayPaints
(
    Id    int primary key,
    Name  nvarchar(35) not null,
    Color char(1)      not null
) as Node

create table Nodes.Squares
(
    Id   int primary key,
    Name nvarchar(35) not null
) as Node

create table Edges.Paints
(
    Datetime datetime not null,
    Volume   tinyint  not null
) as Edge


--Заполение таблиц
insert into Nodes.SprayPaints(Id, Name, Color)
select *
from dbo.utV

insert into Nodes.Squares(Id, Name)
select *
from dbo.utQ


insert into Edges.Paints($from_id, $to_id, Volume, Datetime)
select SprayPaints.$node_id as from_id,
    Squares.$node_id as to_id,
    utB.B_VOL as Volume,
    utB.B_DATETIME as Datetime
from Nodes.SprayPaints
    join dbo.utB on SprayPaints.Id = utB.B_V_ID
    join Nodes.Squares on utB.B_Q_ID = Squares.Id


--Запросы
--1) Найти квадраты, которые окрашивались красной краской.
--Вывести идентификатор квадрата и объем красной краски.
select Squares.Id,
       sum(Paints.Volume) as RedVolume
from Nodes.SprayPaints,
     Edges.Paints,
     Nodes.Squares
where match(SprayPaints - (Paints) -> Squares)
  and SprayPaints.Color = 'R'
group by Squares.Id


--2) Найти квадраты, которые окрашивались как красной, так и синей краской.
--Вывести: название квадрата.
select Squares.Name
from Nodes.SprayPaints,
     Edges.Paints,
     Nodes.Squares
where match(SprayPaints - (Paints) -> Squares) and SprayPaints.Color in ('R', 'B')
group by Squares.Id, Squares.Name
having count(distinct SprayPaints.Color) = 2

--3) Найти квадраты, которые окрашивались всеми тремя цветами.
select Squares.Id
from Nodes.SprayPaints,
     Edges.Paints,
     Nodes.Squares
where match(SprayPaints - (Paints) -> Squares)
group by Squares.Id
having count(distinct SprayPaints.Color) = 3

--4) Найти баллончики, которыми окрашивали более одного квадрата.
select SprayPaints.Id
from Nodes.SprayPaints,
     Edges.Paints,
     Nodes.Squares
where match(SprayPaints - (Paints) -> Squares)
group by SprayPaints.Id
having count(distinct Squares.Id) > 1

--Свои запросы
--1) Найти баллончик(и) которым(ими) окрашивали наибольшое количество квадратов и вывести это количество
select top 1 with ties SprayPaints.Id, count(distinct Squares.Id) SquaresCount
from Nodes.SprayPaints,
     Edges.Paints,
     Nodes.Squares
where match(SprayPaints - (Paints) -> Squares)
group by SprayPaints.Id
order by SquaresCount desc


--2) Найти черные(не покрашенные) квадраты
select Squares.Id
from Nodes.Squares
except
select Squares.Id
from Nodes.SprayPaints,
     Edges.Paints,
     Nodes.Squares
where match(SprayPaints - (Paints) -> Squares)
group by Squares.Id

--3) Найти белые квадраты
with RedVolumes as (
        select Squares.Id,
               sum(Paints.Volume) as RedVolume
        from Nodes.SprayPaints,
             Edges.Paints,
             Nodes.Squares
        where match(SprayPaints - (Paints) -> Squares)
          and SprayPaints.Color = 'R'
        group by Squares.Id
    ),
    BlueVolumes as (
        select Squares.Id,
                   sum(Paints.Volume) as BlueVolume
            from Nodes.SprayPaints,
                 Edges.Paints,
                 Nodes.Squares
            where match(SprayPaints - (Paints) -> Squares)
              and SprayPaints.Color = 'B'
            group by Squares.Id
    ),
    GreenVolumes as (
        select Squares.Id,
                   sum(Paints.Volume) as GreenVolume
            from Nodes.SprayPaints,
                 Edges.Paints,
                 Nodes.Squares
            where match(SprayPaints - (Paints) -> Squares)
              and SprayPaints.Color = 'G'
            group by Squares.Id
    )
select RedVolumes.Id,
       BlueVolume,
       RedVolume,
       GreenVolume
from RedVolumes
join BlueVolumes on BlueVolumes.Id = RedVolumes.Id
join GreenVolumes on GreenVolumes.Id = RedVolumes.Id
where BlueVolume = 255 and RedVolume = 255 and GreenVolume = 255