БД 13 лаба

 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
USE MASTER;
GO
IF db_id (N'lab131') IS NOT NULL
DROP DATABASE lab131;
GO
CREATE DATABASE lab131
ON (NAME = lab131_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab131.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = llab131_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab131.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
IF db_id (N'lab132') IS NOT NULL
DROP DATABASE lab132;
GO
CREATE DATABASE lab132
ON (NAME = lab132_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab132.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab132_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab132.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab131;
GO
CREATE TABLE table1 (
ID INTEGER PRIMARY KEY CHECK (ID < 1000),
ColumnA INTEGER,
ColumnB INTEGER)
GO
USE lab132;
GO
CREATE TABLE table1 (
ID INTEGER PRIMARY KEY CHECK (ID >= 1000),
ColumnA INTEGER,
ColumnB INTEGER)
GO
USE MASTER;
GO
CREATE VIEW view1
AS
SELECT * FROM lab131.dbo.table1 UNION ALL SELECT * FROM lab132.dbo.table1;
GO
INSERT view1 (ID, ColumnA, ColumnB) VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (1000, 4, 5);
GO
DELETE view1 WHERE ID = 2;
GO
UPDATE view1 SET ID = 1001 WHERE ID = 3;
GO
SELECT * FROM view1;
GO
SELECT * FROM lab131.dbo.table1;
GO
SELECT * FROM lab132.dbo.table1;
GO
DROP VIEW view1;
DROP TABLE lab131.dbo.table1;
DROP TABLE lab132.dbo.table1;
DROP DATABASE lab131;
DROP DATAbASE lab132;
GO