-
Notifications
You must be signed in to change notification settings - Fork 0
/
project.sql
63 lines (48 loc) · 1.79 KB
/
project.sql
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
create database hosteldb
use hosteldb
create table logintbl(id int ,name varchar(50), password varchar(50) not null)
create table studenttbl (
sid int not null primary key identity(1,1),
sfname varchar(50) not null,
slname varchar(50) not null,
sreg varchar(50) not null,
sdep varchar(50) not null,
scity varchar(50) not null,
sstate varchar(50) not null,
sphone varchar(50) not null,
rid int foreign key references roomtbl(rid)
)
alter table studenttbl
add images varbinary(max)
sp_help studenttbl
create table floortbl(
fid int not null primary key identity(1,1),fname varchar(50) not null,fcap int not null
)
create table roomtbl(
rid int not null primary key identity(1,1),rcap int not null,fid int null foreign key references floortbl(fid)
)
create table teamtbl (
tid int not null,
ttype varchar(50) not null primary key,
)
create table stafftbl(
stid int not null primary key identity(1,1),
stfname varchar(50) not null,
stlname varchar(50) not null,
stsalary varchar(50) not null,
stcity varchar(50) not null,
ststate varchar(50) not null,
stemail varchar(50) not null,
stphone varchar(50) not null,
ttype varchar(50) foreign key references teamtbl(ttype)
)
alter table stafftbl
add stimage image
sp_help stafftbl
insert into teamtbl(tid,ttype) values(6,'Security Guard')
insert into stafftbl(stfname,stlname,stsalary,stcity,ststate,stemail,stphone,ttype) values
('fa','df','1000','dfsdf','sgf','asfj@gmail.com','43234224','Security Guard')
insert into logintbl (id,name,password) values(1,'admin','admin123')
insert into floortbl(fname,fcap) values('Basement',5)
,('First',6),('Second',5),('Third',9);
insert into roomtbl(rcap,fid) values(3,4),(2,4),(3,4),(2,4),(3,4),(3,4),(3,4),(3,4),(3,4),(2,3)