USE master;
GO

IF DB_ID ('SecondDB') IS NOT NULL
	DROP DATABASE SecondDB;
GO

CREATE DATABASE SecondDB
 ON (NAME = SecondDB_Primary, FILENAME = '/home/lab6/seconddb.mdf', SIZE = 5, MAXSIZE = 100, FILEGROWTH = 5%)
    LOG ON (NAME = SecondDB_Log, FILENAME = '/home/lab6/secondlog.ldf', SIZE = 5, MAXSIZE = 25, FILEGROWTH = 5)
GO

USE SecondDB
GO

--Создать таблицу с автоинкрементным первичным ключом

--уникальность свойства IDENTITY гарантирована только в рамках таблицы, в которой оно использовано;
--таблица может содержать только один столбец со свойством IDENTITY;
CREATE SCHEMA FirstSchema
	CREATE TABLE FirstTable(
    id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    phone CHAR(11) NULL,
    email VARCHAR(50) NOT NULL,
    date DATETIME NOT NULL,
		age INT NOT NULL,
		city VARCHAR(30)
)
GO
/*
--возвращает последнее значение идентификатора, созданного для таблицы
--значение идентификатора может относиться к любому сеансу и любой области
SELECT IDENT_CURRENT('FirstTable') AS [IDENT_CURRENT]
--возвращает последнее значение идентификатора, вставленного
--в любую таблицу в текущем сеансе по всем областям действий
SELECT @@IDENTITY AS [IDENTITY]
--возвращает последнее значение идентификатора, вставленного
--в любую таблицу в текущем сеансе в той же области
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO

--Функция IDENT_CURRENT не ограничена областью действия и сеансом, но ограничена указанной таблицей;
--Функция @@IDENTITY ограничена текущим сеансом;
--Функция SCOPE_IDENTITY ограничена текущим сеансом и областью действия.
--Под областью подразумевается хранимая процедура, триггер или пакет

INSERT INTO FirstSchema.FirstTable VALUES ('8900000000', 'db@yandex.ru', GETDATE(), 20, 'Berlin')
INSERT INTO FirstSchema.FirstTable VALUES ('8900000001', 'db1@yandex.ru', GETDATE(), 36, 'Rome')
GO

SELECT IDENT_CURRENT('FirstTable') AS [IDENT_CURRENT]
SELECT @@IDENTITY AS [IDENTITY]
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO

DROP TABLE FirstSchema.FirstTable
SELECT IDENT_CURRENT('FirstTable') AS [IDENT_CURRENT]
SELECT @@IDENTITY AS [IDENTITY]
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
*/

--Добавить поля, для которых используются ограничения (CHECK), значения по умолчанию (DEFAULT),
ALTER TABLE FirstSchema.FirstTable ADD CONSTRAINT CK_date CHECK (date > CONVERT(DATETIME, '01-09-2010', 105))
ALTER TABLE FirstSchema.FirstTable ADD CONSTRAINT CK_Person CHECK (age >= 18 AND City ='Moscow');
ALTER TABLE FirstSchema.FirstTable ADD CONSTRAINT DFLT_date DEFAULT GETDATE() FOR date;
GO

INSERT INTO FirstSchema.FirstTable (phone, email, age, city) VALUES (89992001010, 'e@mail.ru', 18, 'Moscow')
INSERT INTO FirstSchema.FirstTable VALUES (89992001011, 'e@mail.ru', CONVERT(DATETIME, '01-09-2017', 105), 18, 'Moscow')
INSERT INTO FirstSchema.FirstTable VALUES (89992001018, 'p@mail.ru', CONVERT(DATETIME, '01-09-2017', 105), 20, 'Moscow')
INSERT INTO FirstSchema.FirstTable VALUES (89992001015, 'u@mail.ru', CONVERT(DATETIME, '01-09-2014', 105), 23, 'Moscow')
INSERT INTO FirstSchema.FirstTable VALUES (89992001017, 'o@mail.ru', CONVERT(DATETIME, '01-09-2015', 105), 22, 'Moscow')
--INSERT INTO FirstSchema.FirstTable VALUES (89992001012, 'r@mail.ru', CONVERT(DATETIME, '01-09-2009', 105), 30, 'Bolshevo')
--INSERT INTO FirstSchema.FirstTable VALUES (89992001013, 't@mail.ru', CONVERT(DATETIME, '01-09-2015', 105), 42, 'Mytischi')
--INSERT INTO FirstSchema.FirstTable VALUES (89992001014, 'y@mail.ru', CONVERT(DATETIME, '01-09-2014', 105), 17, 'Shelkovo')
--INSERT INTO FirstSchema.FirstTable VALUES (89992001016, 'i@mail.ru', CONVERT(DATETIME, '01-09-2012', 105), 50, 'Himki')
GO

--Создать таблицу с первичным ключом на основе глобального уникального идентификатора

--уникальный для всей базы данных или всех баз данных во всех компьютерных сетях
CREATE TABLE FirstSchema.UniqueTable (
	workID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
	workName VARCHAR(25) NULL
)
GO

INSERT INTO FirstSchema.UniqueTable (workName) VALUES ('Nature');
INSERT INTO FirstSchema.UniqueTable VALUES (NEWID(), 'Sea');

--Создать таблицу с первичным ключом на основе последовательности

--Sequence – генерирует последовательность чисел так же как и identity.
--последовательность не зависит от какой-либо конкретной таблицы
--В системах баз данных последовательности применяют для автоматической генерации PK

CREATE TABLE FirstSchema.SecondTable
(
	customerID INT NOT NULL PRIMARY KEY,
	customerName VARCHAR(25) NOT NULL,
	numberOfWorks INT NULL
)
GO

CREATE TABLE FirstSchema.ThirdTable
(
	customerID1 INT NOT NULL PRIMARY KEY,
	customerName1 VARCHAR(25) NOT NULL,
	numberOfWorks1 INT NULL
)
GO

--сквозная нумерация
CREATE SEQUENCE FirstSequence
START WITH 1
INCREMENT BY 1;
GO

INSERT FirstSchema.SecondTable (customerID, customerName, numberOfWorks)
		VALUES (NEXT VALUE FOR FirstSequence, 'Ivanov', 2)
INSERT FirstSchema.ThirdTable (customerID1, customerName1, numberOfWorks1)
		VALUES (NEXT VALUE FOR FirstSequence, 'Sidorov', 8)
INSERT FirstSchema.SecondTable (customerID, customerName, numberOfWorks)
		VALUES (NEXT VALUE FOR FirstSequence, 'Popov', 1)
GO

--Создать две связанные таблицы
CREATE TABLE FirstSchema.TableWithPK
(
	purchaseID INT PRIMARY KEY IDENTITY NOT NULL,
	quantity INT NOT NULL,
	seller VARCHAR(25) NOT NULL
)
GO

INSERT INTO FirstSchema.TableWithPK VALUES (5, 'Ivan');
INSERT INTO FirstSchema.TableWithPK VALUES (10, 'Petr');
INSERT INTO FirstSchema.TableWithPK VALUES (3, 'Ivan');
INSERT INTO FirstSchema.TableWithPK VALUES (3, 'Ivan');
INSERT INTO FirstSchema.TableWithPK VALUES (9, 'Anna');
INSERT INTO FirstSchema.TableWithPK VALUES (6, 'Petr');

CREATE TABLE FirstSchema.TableWithFK(
  productID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
  productName VARCHAR(30) NOT NULL,
	manufacturer VARCHAR(30) NOT NULL,
  purchaseID INT NULL,
		CONSTRAINT configs FOREIGN KEY (purchaseID) REFERENCES FirstSchema.TableWithPK (purchaseID)
	  --выдает ошибку
		--ON DELETE NO ACTION
		--проставлет null
    ON DELETE SET NULL
    --ON DELETE SET DEFAULT
		--удаляет строку
    --ON DELETE CASCADE
)
GO

INSERT INTO FirstSchema.TableWithFK VALUES ('Product1', 'Russia', 1);
INSERT INTO FirstSchema.TableWithFK VALUES ('Product2', 'China', 2);
INSERT INTO FirstSchema.TableWithFK VALUES ('Product2', 'Russia', 3);
INSERT INTO FirstSchema.TableWithFK VALUES ('Product3', 'Italy', 4);
GO

DELETE FirstSchema.TableWithPK WHERE purchaseID = 4
GO

--lab7
CREATE VIEW FirstSchema.FirstView WITH SCHEMABINDING AS
	SELECT o.id, o.phone, o.email, o.date, o.age, o.city
	FROM FirstSchema.FirstTable o
	WHERE age > 19
	WITH CHECK OPTION
GO

CREATE VIEW FirstSchema.SecondView AS
	SELECT a.purchaseID, a.quantity, a.seller, b.manufacturer, b.productID, b.productName
	FROM FirstSchema.TableWithPK a RIGHT JOIN FirstSchema.TableWithFK b
		ON a.purchaseID = b.purchaseID
	WITH CHECK OPTION
GO

CREATE INDEX FirstIndex ON FirstSchema.FirstTable (email, phone)
	INCLUDE (age)
	GO

CREATE UNIQUE CLUSTERED INDEX SecondIndex ON FirstSchema.FirstView (age)
	GO

SELECT * FROM FirstSchema.FirstView