Lab_3_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,'Грушев')
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,'Ж')

GO
SELECT *
FROM Subscriber
GO
---------1
--SELECT *
--FROM Subscriber
--WHERE StreetId IN
--(SELECT StreetId
--FROM Street
--WHERE Name='Пушкинская')
--GO
---------2
--SELECT Name FROM Fam
--WHERE Fam.FamId in
--(SELECT Subscriber.FamId FROM Subscriber
--WHERE (Subscriber.HouseNumber = 1 and Subscriber.FamId in
--(SELECT Subscriber.FamId FROM Subscriber WHERE Subscriber.HouseNumber = 2)))
--GO
---------3
--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
--GO
-------4
--SELECT SubscriberId, PhoneNumber, Iniz,Indeks, HouseNumber, FlatNumber, Sex, Street.Name, Fam.Name
--FROM Subscriber
--INNER JOIN Street
--ON Subscriber.StreetId=Street.StreetId
--INNER JOIN Fam
--ON Subscriber.FamId=Fam.FamId
--WHERE PhoneNumber NOT IN(SELECT PhoneNumber FROM Subscriber WHERE PhoneNumber LIKE '7%')
--ORDER BY Fam.Name
--GO
-------5
--SELECT SubscriberId, PhoneNumber, Iniz,Indeks, HouseNumber, FlatNumber, Sex, Street.Name, Fam.Name
--FROM Subscriber
--INNER JOIN Street
--ON Subscriber.StreetId=Street.StreetId
--INNER JOIN Fam
--ON Subscriber.FamId=Fam.FamId
--GO
---------6
--SELECT MIN(FlatNumber),Subscriber.StreetId
--FROM Subscriber
--WHERE Subscriber.Sex='Ж'
--GROUP BY StreetId
--GO
---------7
--SELECT Name
--FROM Fam
--WHERE FamId=
--(SELECT FamId
--FROM Subscriber
--WHERE Subscriber.HouseNumber=13
--GROUP BY FamId
--HAVING COUNT(FamId)>2)
--GO
--------8
--SELECT Name
--FROM Street
--WHERE StreetId=
--(SELECT TOP 1 StreetId --, COUNT(phonenumber) as 'colnumber'
--FROM Subscriber
--WHERE PhoneNumber like ('7%')
--GROUP BY Subscriber.StreetId
--ORDER BY COUNT(PhoneNumber) desc)
--GO
--------9
--DECLARE @a int
--SELECT @a=Count(PhoneNumber)
--FROM Subscriber
--WHERE (StreetId=1 and PhoneNumber IN
--(SELECT PhoneNumber
--FROM Subscriber
--WHERE PhoneNumber LIKE '777%'))
--IF @a>0 PRINT('True')
--ELSE PRINT ('False')
--GO