use master go if DB_ID DB_for_lab_7 IS NOT NULL drop database DB_for_l

  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
105
106
107
108
109
110
111
112
113
use master
go
if DB_ID (N'DB_for_lab_7') IS NOT NULL
drop database DB_for_lab_7
go
create database DB_for_lab_7
ON (name = Sales_dat, FILENAME = "C:\db\lab7.mdf",
SIZE = 10, MAXSIZE = UNLIMITED, filegrowth = 5% )
LOG ON (name = lab6_log, FILENAME = "C:\db\lab7.ldf",
SIZE = 5MB, MAXSIZE = 25MB, filegrowth = 5MB )
go
use DB_for_lab_7;
go
create table coach (
id int IDENTITY primary key,
name varchar(20)
)
go
alter table coach
add age int check (age >= 18)
go
alter table coach
add salary int default (1000)
go
alter table coach
add date datetime default (getdate())
go
create table team(
id_team int primary key,
name char(20)
)
create table player(
ID int primary key,
name varchar(20),
id_t int,
constraint fk_id_team FOREIGN KEY (id_t) references team (id_team))
go
create view view_1 as select
c.id, c.name, c.age from coach as c
go
create view view_2 as select
t.id_team, t.name, p.Id as id_player, p.name as name_player from
team as t inner join player as p on t.id_team = p.id_t
go
insert into view_1 (name, age)
values ('Gazaev', 60)
go
insert into team
values (1, 'Loko')
insert into team
values (2, 'CSKA')
insert into team
values (3, 'Spartak')
go
insert into player
values (1, 'Abaev', 1)
insert into player
values (2, 'Dzagoev', 3)
insert into player
values (3, 'Glushakov', 2)
go
insert into coach (name, age)
values ('Semin', 64)
go
select * from coach
go
select * from view_1
go
select * from view_2
go
create table student(
id int primary key identity,
name varchar(20),
age int,
male varchar(20))
go
create index student_ind
on student (id, name)
include (age)
go
create view view_3 with schemabinding as select
id, name, age from dbo.student
go
create unique clustered index view_ind
on view_3 (id, name)
go
drop index view_3.view_ind
go
drop index student.student_ind
go