Lab4_BD

GO
USE master
GO
IF EXISTS (SELECT * from sys.databases where name='mybase')
        DROP DATABASE mybase
GO
CREATE DATABASE mybase
GO
ALTER DATABASE mybase SET SINGLE_USER
GO
USE mybase
GO
CREATE TABLE Street
(StreetId int NOT NULL PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
INSERT INTO Street(StreetId,Name)
                 VALUES (1,'Пушкинская')
INSERT INTO Street(StreetId,Name)
                 VALUES (2,'Лермонтовская')
INSERT INTO Street(StreetId,Name)
                 VALUES (3,'Чуковская')
INSERT INTO Street(StreetId,Name)
                 VALUES (4,'Толкиенская')
GO
--SELECT * FROM Street
--GO
CREATE TABLE Fam
(FamId int NOT NULL PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
INSERT INTO Fam(FamId,Name)
         VALUES (1,'Яблочных')
INSERT INTO Fam(FamId,Name)
         VALUES (2,'Апельсинов')
INSERT INTO Fam(FamId,Name)
         VALUES (3,'Виноградов')
INSERT INTO Fam(FamId,Name)
         VALUES (4,'Грушев')
INSERT INTO Fam(FamId,Name)
         VALUES (5,'Арбузова')
GO
--SELECT * FROM Fam
--GO
CREATE TABLE Subscriber
(SubscriberId int NOT NULL Primary Key,
PhoneNumber nvarchar(9) NOT NULL,
FamId int NOT NULL,
Iniz nvarchar(4) NOT NULL,
Indeks nvarchar(5) NOT NULL,
StreetId int NOT NULL,
HouseNumber int NOT NULL,
FlatNumber int NOT NULL,
Sex nvarchar(1) NOT NULL,
FOREIGN KEY (FamID) REFERENCES Fam(FamId),
FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
GO
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (1,'745-34-19',1, 'А.А.','61005',2, 13,3,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (2,'234-43-53',2, 'Р.В', '61005', 1,1,45,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (3,'654-02-51',3, 'В.И.','61005', 3,11,2,'Ж')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (4,'350-10-20',4, 'Ф.И.', '61005', 4, 7,223,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (5,'787-34-66',1, 'А.С.', '61005', 3,13,89,'Ж')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (6,'787-37-56',2, 'А.С.', '61005', 2,112,13,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (7,'777-34-66',2, 'К.В.', '61005', 1,2,13,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (8,'787-34-12',4, 'М.С.', '61005', 2,112,13,'Ж')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (9,'497-234',4, 'К.М.', '61005', 4,132,13,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (10,'497-234',5, 'К.М.', '61005', 4,132,13,'М')
GO
--SELECT *
--FROM Subscriber
--GO

CREATE TABLE SubscriberCopy
(SubscriberId int NOT NULL Primary Key,
PhoneNumber nvarchar(9) NOT NULL,
FamId int NOT NULL,
Iniz nvarchar(4) NOT NULL,
Indeks nvarchar(5) NOT NULL,
StreetId int NOT NULL,
HouseNumber int NOT NULL,
FlatNumber int NOT NULL,
Sex nvarchar(1) NOT NULL,
FOREIGN KEY (FamID) REFERENCES Fam(FamId),
FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
GO
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (1,'745-34-19',1, 'А.А.','61005',2, 13,3,'М')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (2,'234-43-53',2, 'Р.В', '61005', 1,1,45,'М')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (3,'654-02-51',3, 'В.И.','61005', 3,11,2,'Ж')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (4,'350-10-20',4, 'Ф.И.', '61005', 4, 7,223,'М')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (5,'787-34-66',1, 'А.С.', '61005', 3,13,89,'Ж')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (6,'787-37-56',2, 'А.С.', '61005', 2,112,13,'М')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (7,'777-34-66',2, 'К.В.', '61005', 1,2,13,'М')
INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (8,'787-34-12',4, 'М.С.', '61005', 2,112,13,'Ж')
GO
-------------3
--IF OBJECT_ID('MyDelete1','TRIGGER') IS NOT NULL
--DROP TRIGGER MyDelete1
--GO
--CREATE TRIGGER MyDelete1
--ON Subscriber
--FOR DELETE
--AS
--DECLARE @s nvarchar(9)
--BEGIN
--SELECT @s=PhoneNumber FROM deleted
--declare @i int=0
--declare @c int=0
--WHILE @i<=9
--begin
--  if(substring(@s,@i,1) between '0' and '9' ) SET @c=@c+1
--  SET @i=@i+1
--end
--IF(@c=6)
--  PRINT('Успешно удалено')
--ELSE
--   BEGIN
--  PRINT('Неверное удаление')
--  ROLLBACK TRANSACTION
--   END
--END
--GO
--DELETE Subscriber
--WHERE LEN(PhoneNumber)=7
--GO
--SELECT *
--FROM Subscriber
--GO



--declare @i int=0
--declare @c int=0
--WHILE @i<=9
--begin
--  if(substring(@s,@i,1) between '0' and '9' ) SET @c=@c+1
--  SET @i=@i+1
--end

--------------4
--IF OBJECT_ID('MyInsert1','TRIGGER') IS NOT NULL
--DROP TRIGGER MyInsert1
--GO
--CREATE TRIGGER MyInsert1
--ON Fam
--INSTEAD OF INSERT
--AS
--DECLARE @s nvarchar(25)
--IF NOT EXISTS(SELECT * FROM Inserted
--WHERE Name IN
--(SELECT Name FROM Fam))
--BEGIN
--  INSERT INTO Fam(Name) SELECT
--  Name FROM Inserted
--  print('Subject added')
--END
--ELSE
--  PRINT('Unable to add')
--GO
--INSERT INTO Fam(FamId,Name)
--         VALUES (6,'Яблочных')
--SELECT * FROM Fam
-------------------5
--IF OBJECT_ID('MyDelete2','TRIGGER') IS NOT NULL
--DROP TRIGGER MyDelete2
--GO
--CREATE TRIGGER MyDelete2
--ON Fam
--INSTEAD OF DELETE
--AS
--  DELETE FROM Subscriber WHERE Subscriber.FamId IN (SELECT FamId FROM deleted)
--  DELETE FROM Fam WHERE FamId IN(SELECT FamId FROM deleted)
--GO
--DELETE FROM Fam
--WHERE Name='Арбузова'
--GO
-------------1
--UPDATE TOP(1) SubscriberCopy
--SET HouseNumber=3
--WHERE SubscriberId=3
--GO
-----------2
--SELECT SubscriberId, PhoneNumber, Fam.Name, Iniz,Indeks,Street.Name, HouseNumber, FlatNumber, Sex
--FROM Subscriber,Fam,Street
--WHERE Subscriber.FamId=Fam.FamId and Subscriber.StreetId=Street.StreetId and Fam.Name NOT IN
--(SELECT Fam.Name FROM Fam WHERE Fam.Name like '[АЕЁИОУЭЮЯ]%')
----(SELECT Fam.Name FROM Fam WHERE Fam.Name LIKE('А%') or Fam.Name LIKE ('О%')  or Fam.Name like('И%') or Fam.Name like('Е%') or Fam.Name like('У%') or Fam.Name like('Э%') or Fam.Name like('Я%'))
--UNION SELECT SubscriberId, PhoneNumber, Fam.Name, Iniz,Indeks,Street.Name, HouseNumber, FlatNumber, Sex
--FROM SubscriberCopy,Fam,Street
--WHERE SubscriberCopy.FamId=Fam.FamId and SubscriberCopy.StreetId=Street.StreetId and Fam.Name NOT IN
----SELECT Fam.Name FROM Fam WHERE Fam.Name LIKE('А%') or Fam.Name LIKE ('О%')  or Fam.Name like('И%') or Fam.Name like('Е%') or Fam.Name like('У%') or Fam.Name like('Э%') or Fam.Name like('Я%'))
--(SELECT Fam.Name FROM Fam WHERE Fam.Name like '[АЕЁИОУЭЮЯ]%')
--ORDER BY Street.Name
--GO
----------------------6
--IF OBJECT_ID('UpdeteInsert','TRIGGER') IS NOT NULL
--DROP TRIGGER UpdateInsert
--GO
--CREATE TRIGGER UpdateInsert
--ON Subscriber
--INSTEAD OF UPDATE,INSERT
--AS
--  if not exist(select * from inserted
--  where StreetId in
--  (select * from Subscriber group by StreetId having count(*)>=3))
--      print('Unable to add')
--  else
--      begin
--          insert into Subscriber SELECT
--          * FROM inserted
--          print('Added')
--      end
--GO

--SELECT *
--FROM SubscriberCopy
--GO