Bài giải_CenterManagerment Database

--Câu 1:
Create database CenterManagerment
--Câu 2:
create table PHONGHOC(
ma_phonghoc varchar(15) primary key,
tenphong varchar(50) not null,
soluong int not null)

create table LOPHOC(
ma_lophoc varchar(15) primary key,
tenlop varchar(50) not null,
phonghoc varchar(15) not null foreign key references PHONGHOC
)
create table SINHVIEN (
mssv varchar(15) primary key ,
hoten varchar(50) not null ,
ngaysinh datetime not null ,
lophoc varchar(15) not null foreign key references LOPHOC)
--Câu 2:
--a
select sinhvien.mssv, sinhvien.hoten,lophoc.tenlop, phonghoc.tenphong
from sinhvien, lophoc, phonghoc
where sinhvien.lophoc=lophoc.ma_lophoc and lophoc.phonghoc=phonghoc.ma_phonghoc
and sinhvien.hoten like 'NGUYEN%'
--b
select * from phonghoc where soluong>100
--c
insert into phonghoc values('LT04', 'Ly thuyet 04', 60)
--d
delete from sinhvien where mssv like '%2003%'
--e
update sinhvien set ngaysinh='15/02/1980' where mssv='CP200607-G01'

--Câu 3
declare phonghoc_cursor cursor scroll
for select * from phonghoc where soluong>=48
open phonghoc_cursor
fetch first from phonghoc_cursor
while @@fetch_status=0
begin
fetch next from phonghoc_cursor
end
close phonghoc_cursor
deallocate phonghoc_cursor