USE MASTER GO IF db_id lab7 IS NOT NULL DROP DATABASE lab7 go CREATE D

 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
USE MASTER;
GO
IF db_id (N'lab7') IS NOT NULL
DROP DATABASE lab7;
go
CREATE DATABASE lab7
ON (NAME = lab7_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab7.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab7_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab7.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab7;
GO
CREATE TABLE table1 (
id int IDENTITY(0, 1) PRIMARY KEY NOT NULL,
field1 char(30),
field2 int);
GO
ALTER TABLE table1 ADD field3 int CHECK (field3 > 0);
GO
ALTER TABLE table1 ADD field4 int DEFAULT (0);
GO
ALTER TABLE table1 ADD field5 datetime DEFAULT (getdate());
GO
CREATE TABLE table2 (
uniq_id uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
field1 int);
GO
CREATE TABLE table3 (
id int PRIMARY KEY,
field1 int);
GO
CREATE SEQUENCE sequence1 START WITH 0 INCREMENT BY 1;
GO
INSERT table3 (id, field1)
VALUES (NEXT VALUE FOR sequence1, 5);
INSERT table3 (id, field1)
VALUES (NEXT VALUE FOR sequence1, 10);
GO
CREATE TABLE country (
countryid int PRIMARY KEY,
namec char(30));
GO
CREATE TABLE ruler (
rulerid int PRIMARY KEY,
name char(30),
ruler_country int FOREIGN KEY REFERENCES country(countryid)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
GO
INSERT country (countryid, namec) VALUES (1, 'Rome');
INSERT country (countryid, namec) VALUES (2, 'Greece');
INSERT country (countryid, namec) VALUES (3, 'Egypt');
INSERT country (countryid, namec) VALUES (4, 'Persia');
INSERT ruler (rulerid, ruler_country, name) VALUES (1, 1, 'Caesar');
INSERT ruler (rulerid, ruler_country, name) VALUES (2, 1, 'Octavian');
INSERT ruler (rulerid, ruler_country, name) VALUES (3, 2, 'Pericles');
INSERT ruler (rulerid, ruler_country, name) VALUES (4, 3, 'Ramses');
INSERT ruler (rulerid, ruler_country, name) VALUES (5, 4, 'Darius');
GO
CREATE VIEW C1L AS
SELECT name, ruler_country FROM ruler WHERE ruler_country = 1;
GO
SELECT * FROM C1L;
GO
CREATE VIEW C_l WITH SCHEMABINDING AS
SELECT a.namec, b.name FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
WITH CHECK OPTION;
GO
SELECT * FROM C_l;
GO
CREATE INDEX ind1 ON ruler (rulerid) INCLUDE (ruler_country, name);
GO
CREATE UNIQUE CLUSTERED INDEX ind2 ON C_l (name);
GO