Lab2_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
CREATE FUNCTION dbo.f1(@o nvarchar(1))
returns nvarchar(10)
as
    begin
        declare @x nvarchar(10)
        if @o='М'
          begin
            set @x='мужской'
          end
        else
          begin
            set @x='женский'
          end
        return @x
    end
go
------------------1
SELECT SubscriberId as 'Код',PhoneNumber as'Телефон',FamId as'Код фамилии',Iniz as'Инциалы',Indeks as'Индекс',
StreetId as'код улицы', HouseNumber as'Номер дома', FlatNumber as'Номер квартиры', dbo.f1(Sex) as'Пол'
FROM Subscriber

------------------2
--SELECT DISTINCT HouseNumber,FlatNumber,Sex FROM Subscriber
-------------------3
--SELECT Fam.Name FROM Subscriber,Fam
--INNER JOIN Fam
--ON Fam.FamId=Subscriber.FamId
--WHERE Sex='М'
-------------------4
--select Fam.Name from Subscriber inner join Fam on Subscriber.FamId=Fam.id
--where substring(PhoneNumber,3,1)=[3,7,8]
--and not substring(PhoneNumber,6,1)=[6,3,7] and substring(PhoneNumber,8,1) between 4 and 9
---------------------5
--SELECT * FROM Subscriber
--INNER JOIN Street
--ON Street.StreetId=Subscriber.StreetId
--WHERE Street.Name Like '%Героев%'
-----------------------------6
--SELECT * FROM Subscriber
--WHERE FlatNumber IS NULL
----------------------------7
--select Fam.Name from Subscriber inner join Fam on Subscriber.FamId=Fam.id
--order by Subscriber.HouseNumber desc,Subscriber.FlatNumber asc
-------------------------------------8
--select Fam.Name from Subscriber inner join Fam on Subscriber.FamId=Fam.id
--order by ABS(FlatNumber-13) DESC
--------------------------------9
--SELECT COUNT(*) FROM Fam
--WHERE Fam.Name LIKE '%ов'
-----------------------10
--SELECT MIN(FlatNumber),StreeId FROM Subscriber
--ORDER BY StreetId
--WHERE Sex='Ж'




-------------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
--IF(LEN(@s)=7)
--  PRINT('Успешно удалено')
--ELSE
--   BEGIN
--  PRINT('Неверное удаление')
--  ROLLBACK TRANSACTION
--   END
--END
--GO
--DELETE Subscriber
--WHERE LEN(PhoneNumber)=7
--GO
--SELECT *
--FROM SubscriberCopy
--GO
--------------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(9)
--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
-------------------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('А%') 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 ('О%'))
--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