wCs7SMQM

1use master;
2go
3
4drop database if exists lab7
5go
6
7create database lab7
8go
9
10use lab7;
11go
12
13
14/* 1 */
15create table Application
16(
17    ID            int identity (1,1) primary key not null,
18    InsuranceID   int unique                     not null,
19    BankChequeEUR int                            not null,
20    Area          char(15)                       not null default 'Schengen'
21);
22go
23
24insert into Application(BankChequeEUR, InsuranceID)
25values (850, 485);
26
27insert into Application(BankChequeEUR, InsuranceID, Area)
28values (1920, 100, 'USA');
29
30insert into Application(BankChequeEUR, InsuranceID)
31values (590, 792);
32go
33
34create view ApplicationView with schemabinding
35as
36select ID, BankChequeEUR, Area
37from dbo.Application;
38go
39
40select *
41from ApplicationView;
42go
43
44
45/* 2 */
46create table VisaCenter
47(
48    VisaCenterName char(15) primary key
49);
50go
51
52create table NewTariff
53(
54    TariffName     char(15) primary key,
55    VisaCenterName char(15)
56        constraint FK_VisaCenter_NewTariff
57            foreign key
58                references VisaCenter (VisaCenterName)
59                on update cascade
60);
61go
62
63insert into VisaCenter (VisaCenterName)
64values ('FirstVC');
65go
66
67insert NewTariff (TariffName, VisaCenterName)
68values ('Tariff', 'FirstVC');
69go
70
71create view VisaCenter_NewTariff
72as
73select TariffName
74from NewTariff as NT
75         join VisaCenter as VC on NT.VisaCenterName = VC.VisaCenterName;
76go
77
78select *
79from VisaCenter_NewTariff;
80go
81
82
83
84/* 3 */
85create unique index IndexApplication on Application (InsuranceID)
86    include (Area);
87
88select *
89from Application
90where Area = 'USA';
91go
92
93
94/* 4 */
95create unique clustered index ApplicationViewIndex on ApplicationView (ID);
96go
97
98select *
99from ApplicationView