12

use master
go
if exists(select * from sys.databases where name ='mybase4')
    drop database mybase3
go
create database mybase4
go
use mybase4
go
if OBJECT_ID('dbo.f1','function') is not null
    drop function dbo.f1
go
create function dbo.f1(@chislo real)
returns bit
as
  begin
    declare @f bit='false'
    declare @s nvarchar(10)
    set @s=Convert(nvarchar(10),@chislo)
    if(@s like'%.%')
    begin
        if SUBSTRING(@s,1,2) between '00' and '23'
            set @f='true'
        else if SUBSTRING(@s,4,2) between '00' and '59'
            set @f='true'
        else if(@s like '[1-2][0-3].[0-5][0-9]' or @s like '[0-9].[0-5][0-9]')
            set @f='true'
    end
    else
      begin
        if SUBSTRING(@s,1,2) between '00' and '23'
            set @f='true'
      end
    return @f
  end
go
if OBJECT_ID('dbo.f2','function') is not null
    drop function dbo.f2
go
create function dbo.f2(@kod int,@beg real,@end real)
returns bit
as
  begin
    declare @time int
    select @time=dist from Encrip_film where Encrip_film.id=@kod
    declare @f bit='false'
    declare @s nvarchar(10)
    declare @a int=floor(@beg)+floor(@end)+(@beg-floor(@beg))*100+(@end-floor(@end)*100)
    if(@time<=@a)
        set @f='true'
    return @f
  end
go
create table Encrip_ganr
(
 id int identity(1,1) PRimary key not null,
 name_ganr nvarchar(20)
)
create table Encrip_film
(
 id int identity(1,1) PRimary key not null,
 name_film nvarchar(20) not null,
 dist int,
 kod_ganr int not null,
 anotaciya nvarchar(20),
 foreign key (kod_ganr) references Encrip_ganr(id)
)
create table Encrip_chanel
(
 id int identity(1,1) PRimary key not null,
 name_film nvarchar(20)
)
go
create table Main
(
 id int identity(1,1) PRimary key not null,
 kod_film int not Null,
 kod_chanel int not null,
 time_begin real, --check(dbo.f1(time_begin)='true'),
 time_end real, --check(dbo.f1(time_end)='true'),
 foreign key (kod_film) references Encrip_film(id),
 foreign key (kod_chanel) references Encrip_chanel(id))


 --check (dbo.f2(Kod_film,time_begin,time_end)='true')

go
    insert into Encrip_chanel values('1+1')
    insert into Encrip_chanel values('тет')
    insert into Encrip_chanel values('интер')

    insert into Encrip_ganr values('жахи')
    insert into Encrip_ganr values('триллер')
    insert into Encrip_ganr values('боевик')

    insert into Encrip_film values('матрица',180,1,'что-то')
    insert into Encrip_film values('мирный воин',160,2,'что-то')
    insert into Encrip_film values('терминатор',130,1,'что-то')
    select * from Encrip_film
    select * from Encrip_ganr
    select * from Encrip_chanel
    go
    insert into Main (kod_film,kod_chanel,time_begin,time_end) values(1,1,20.01,23.01)
    insert into Main (kod_film,kod_chanel,time_begin,time_end) values(2,2,2,20.01,23.01)
    insert into Main (kod_film,kod_chanel,time_begin,time_end) values(3,3,3,20.01,23.01)
go
select * from Main
go
--select count(*) from Main group by kod_chanel having COUNT(*)<2
--go
--select Encrip_film.name_film,Encrip_film.anotaciya from Main inner join Encrip_film on Main.kod_film=Encrip_film.id
--inner join Encrip_ganr on Encrip_film.kod_ganr=Encrip_ganr.id
--inner join Encrip_chanel on Encrip_chanel.id=Main.kod_chanel where time_begin>=20.00
--go
--select avg(Encrip_film.dist) from Main inner join Encrip_film on Main.kod_film=Encrip_film.id
--inner join Encrip_ganr on Encrip_film.kod_ganr=Encrip_ganr.id
--inner join Encrip_chanel on Encrip_chanel.id=Main.kod_chanel where Encrip_ganr.name_ganr='жахи' group by Encrip_film.id
--go