create database coc4
use coc4
create table candidte(cid varchar(56)primary key,
name varchar(78),
sex varchar(70),
level varchar (67),
bdate int)
create table exam(eid varchar (80)primary key,
examname varchar (45),
categoryid varchar(60))
create table cateqory(catid varchar(77)primary key,
category varchar(45))
create table qlestion (qid varchar (78)primary key,
questiontext varchar (88),
explanatin varchar (78),
examid varchar (67))
create table answer (aid varchar (56)primary key,
aswertaxt varchar (78),
corretanswer varchar(67),
questionid int)
create table examhistory (examid varchar(79)primary key,
datetaken varchar(78),
score varchar(55),
cid varchar(56)foreign key references candidte(cid),
eid varchar(80)foreign key references exam(eid),
catid varchar(77)foreign key references cateqory(catid),
qid varchar(78)foreign key references qlestion(qid),
aid varchar(56)foreign key references answer(aid))
insert into candidte values('101','ayeletestema','m','4',1989-10-06)
insert into exam values('11','dbaleve','202')
insert into cateqory values('202','databaseadminstration')
insert into qlestion values('501','whattpeofke','theprimarykey','1')
insert into answer values ('501','primarykey','true','501')
insert into examhistory
values('e01','dabase','100','101','11','202','501','501')
select*from candidte
update exam set examname='dbaleve4' where eid='11'
create login senait with password='123'
create user maru for login senait
create role db
exec sp_addrolemember @rolename='db',@membername='maru'
grant update,select,insert,delete on exam to maru
revoke update,select,insert,delete on exam to maru
