7

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
USE master;
GO
IF db_id (N'Bank_lab7') IS NOT NULL
DROP DATABASE Bank_lab7;
go
CREATE DATABASE Bank_lab7
ON (NAME = Bank_dat_lab7, FILENAME = 'C:\data\Bankdatlab7.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = Bank_log_lab7, FILENAME = 'C:\data\Bankloglab7.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE Bank_lab7;
GO
CREATE TABLE Human (idofperson int IDENTITY(0, 1) PRIMARY KEY,
firstname char(50),
secondname char(50),
location char(150),
phone int);
GO
ALTER TABLE Human ADD age int CHECK (age > 0);
GO
ALTER TABLE Human ADD email char(25) DEFAULT ('н/д');
GO
ALTER TABLE Human ADD dateofbirth datetime DEFAULT (getdate());
GO
CREATE TABLE Employee (uniqid uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
post char(50),
passnumber int,
experience int);
GO
CREATE TABLE Client (clientid int PRIMARY KEY,
accountnumber int);
GO
CREATE SEQUENCE ForClient START WITH 0 INCREMENT BY 1;
GO
INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 1);
INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 2);
GO
/*
SELECT * FROM Client;
GO
*/
CREATE TABLE Operation (idoper int PRIMARY KEY,
dt datetime);
GO
CREATE TABLE Credit (ido int FOREIGN KEY REFERENCES Operation(idoper)
ON DELETE SET NULL
ON UPDATE CASCADE,
sumofmoney money,
procent decimal);
GO
INSERT Operation (idoper, dt) VALUES (1, '20131201');
INSERT Operation (idoper, dt) VALUES (2, '20131222');
INSERT Operation (idoper, dt) VALUES (3, '20140111');
INSERT Operation (idoper, dt) VALUES (4, '20140112');
INSERT Credit(ido, sumofmoney, procent) VALUES (1, $100000, 10.5);
INSERT Credit(ido, sumofmoney, procent) VALUES (1, $25000, 5.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (2, $1000000, 15.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (3, $1000, 5.0);
GO
-- view 1
CREATE VIEW CCO AS
SELECT ido, sumofmoney FROM Credit WHERE ido = 1;
GO
SELECT * FROM CCO;
GO
-- view 2
CREATE VIEW OC WITH SCHEMABINDING AS
SELECT a.idoper, a.dt, b.sumofmoney FROM
dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = 1
WITH CHECK OPTION;
GO
SELECT * FROM OC;
GO
-- index 1
CREATE UNIQUE CLUSTERED INDEX idOC ON OC (sumofmoney);
GO
-- index 2
CREATE INDEX idOC1 ON Credit (ido) INCLUDE (sumofmoney);
GO