lab6_BD

GO
USE master
GO
IF EXISTS (SELECT * from sys.databases where name='moedata')
        DROP DATABASE moedata
GO
CREATE DATABASE moedata
GO
ALTER DATABASE moedata SET MULTI_USER
GO
USE moedata
GO
CREATE TABLE Street
(StreetId int NOT NULL identity(1,1)PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
----GO
INSERT INTO Street(Name)
                 VALUES ('Пушкинская')
INSERT INTO Street(Name)
                 VALUES ('Лермонтовская')
INSERT INTO Street(Name)
                 VALUES ('Чуковская')
INSERT INTO Street(Name)
                 VALUES ('Толкиенская')
--GO
----SELECT * FROM Street
----GO
CREATE TABLE Fam
(FamId int NOT NULL identity(1,1) PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
INSERT INTO Fam(Name)
         VALUES ('Яблочных')
INSERT INTO Fam(Name)
         VALUES ('Апельсинов')
INSERT INTO Fam(Name)
         VALUES ('Виноградов')
INSERT INTO Fam(Name)
         VALUES ('Грушев')
INSERT INTO Fam(Name)
         VALUES ('Арбузова')
--GO
----SELECT * FROM Fam
--GO
CREATE TABLE Subscriber
(SubscriberId int NOT NULL identity(1,1) Primary Key,
PhoneNumber nvarchar(9),
FamId int NOT NULL,
Iniz nvarchar(4),
Indeks nvarchar(5),
StreetId int NOT NULL,
HouseNumber nvarchar(4),
FlatNumber int,
Sex nvarchar(1),
FOREIGN KEY (FamID) REFERENCES Fam(FamId),
FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
GO
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('745-34-19',1, 'А.А.','61005',2, '13',3,'М')
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('234-43-53',2, 'Р.В', '61005', 1,'1',45,'М')
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('834-43-53',1, 'Р.В', '61005', 1,'1',45,'М')
go

-----------------------------------------------1
if OBJECT_ID('proc1','procedure') is not null
    drop view proc1
go
create procedure proc1 @fam nvarchar(50), @outp int output
as
    declare @nom int
    select @nom=MAX(Subscriber.HouseNumber) from Subscriber inner join Street
    on Street.StreetId=Subscriber.StreetId where Street.Name=@fam
    set @outp=(select Subscriber.FamId from Subscriber inner join Street
    on Street.StreetId=Subscriber.StreetId where Street.Name=@fam and Subscriber.HouseNumber=@nom)
go

--------------------------------------------------2
if OBJECT_ID('proc2','procedure') is not null
    drop view proc2
go
create procedure proc2 @fam nvarchar(50),@street nvarchar(50),@indeks int,@sex nvarchar(1),@phone nvarchar(9)
as
    if exists(select Fam.Name,Street.name,Subscriber.Indeks,Subscriber.Sex,Subscriber.PhoneNumber
    from Subscriber inner join Fam on Fam.FamId=Subscriber.FamId inner join Street on Street.StreetId=Subscriber.StreetId
    where Fam.Name=@fam and Street.Name=@street and Subscriber.Indeks=@indeks and Subscriber.Sex=@sex and Subscriber.PhoneNumber=@phone)
    begin
        UPDATE Subscriber set PhoneNumber='1a' where FlatNumber=(select max(flatNumber) from Subscriber)
    end
    else
    begin
        declare @kod1 int
        declare @kod2 int
        insert into Fam values(@fam)
        select @kod1=FamId from Fam where Name=@fam
        insert into Street values(@street)
        select @kod2=StreetId from Street where Name=@street
        insert into Subscriber values(@phone,@kod1,null,@indeks,@kod2,null,null,@sex)
    end
go
----------------------------------------------3
if OBJECT_ID('view1','view') is not null
    drop view view1
go
create view view1
as
    select PhoneNumber,Iniz,Indeks,HouseNumber,FlatNumber,Sex from Subscriber
    where PhoneNumber like '%[368]%'
go
select * from view1
go
----------------------------------------------4
if OBJECT_ID('view2','view') is not null
    drop view view2
go
create view view2(f,i,d)
as
    select Street.Name,max(Subscriber.PhoneNumber),count(*) from Subscriber inner join Street on Street.StreetId=Subscriber.StreetId
    group by Street.Name
go
select * from view2
go