bai 1:

---tao bang---

create table KHOA (

makhoa nchar(6) not null,

tenkhoa nvarchar(30) not null,

diadiem nvarchar (30),

dienthoai nvarchar(20),

constraint pk_k primary key (makhoa)

)

create table LOP (

malop nchar(6) not null,

tenlop nvarchar(30) not null,

namvao int,

makhoa nchar(6),

constraint pk_k1 primary key(malop)

)

create table SINHVIEN (

masv nchar(10) not null,

hodem nvarchar(30) not null,

ten nvarchar(15) not null,

gioitinh bit,

ngaysinh smalldatetime,

malop nchar(6)

constraint pk_k2 primary key (masv)

)

create table MONHOC(

mamon nchar(6) not null,

tenmon nvarchar(30) not null,

sotinchi int,

constraint pk_k3 primary key (mamon)

)

create table SINHVIEN_MONHOC(

mamon nchar(6) not null,

masv nchar(10) not null,

hocky int not null,

lanthi int not null,

diem decimal(4,2),

constraint pk_k4 primary key(mamon,masv,hocky,lanthi)

)

---tao quan he---

alter table Lop

add constraint fk_LOP

foreign key (makhoa)

references khoa(makhoa)

on delete cascade

on update cascade

alter table SINHVIEN

add constraint fk_SINHVIEN

foreign key (malop)

references khoa(malop)

on delete cascade

on update cascade

alter table SINHVIEN_MONHOC

add constraint fk_SINHVIEN_MONHOC

foreign key (mamon,masv,hocky,lanthi)

references khoa(masv)

on delete cascade

on update cascade

alter table monhoc

add constraint fk_monhoc

foreign key (mamon)

references khoa(mamon)

on delete cascade

on update cascade

--tao rang buoc defaule

alter table sinhvien

add constraint df_gt

default(0) for GT

--rang buoc check:

alter table Lop

add constraint df_namvao

check (namvao>1990)

--them du lieu

insert into Khoa

values ('K01',N'Cong nghe thong tin',N'Tang 2-A5-CS1',42349343)

insert into Khoa

values ('K02',N'Tu nhien',N'Tang 3-A2-CS1','0')

insert into Khoa

values ('K03',N'Ngoai ngu',N'tang 3-A2-Cs1','0')

insert into MonHoc

values ('MH01',N'Tin hoc co so',2)

insert into MonHoc

values ('MH02',N'ung dung CNTT',3)

insert into MonHoc

values ('MH02',N'toan roi rac',3)

insert into Lop

values ('L01',N'SP tin K32',2006,'K01')

insert into Lop

values ('L02',N'SP tin K33',2007,'K01')

insert into Lop

values ('L03',N'CNTT K8',2008,'K01')

insert into Lop

values ('L04',N'CNTT k9',2009,'K01')

insert into Lop

values ('L05',N'CNTT k11',2011,'K01')

insert into Lop

values ('L06',N'SP tin K37',2011,'K01')

insert into Lop

values ('L07',N'SP toan K33',2007,'K02')

insert into Lop

values ('L08',N'Sp anh k34',2008,'K03')

insert into Lop

values ('L09',N'Sp hoa k34',2008,'K02')

insert into SinhVien

values ('SV01','nguyenhuu','hoang','true','3/2/1989 12:00:00 AM','L01')

insert into SinhVien

values ('SV02','tran ngoc','minh','false','3/2/1989 12:00:00 AM','L02')

insert into SinhVien

values ('SV03','nguyen ngoc','ngan','false','2/12/1989 12:00:00 AM','L01')

insert into SinhVien

values ('SV04','hoang van','hung','true','2/2/1990 12:00:00 AM','L04')

insert into SinhVien

values ('SV05','trinh thi','ngoc','false','10/11/1990 12:00:00 AM','L08')

insert into SinhVien

values ('SV06','ha hai','linh','true','2/12/1991 12:00:00 AM','L08')

insert into SinhVien

values ('SV07','hoa ha','hang','false','2/1/1990 12:00:00 AM','L01')

insert into SinhVien

values ('SV08','ha thu','huong','false','2/12/1990 12:00:00 AM','L06')

insert into SinhVien

values ('SV09','nguyen van','linh','true','2/6/1989 12:00:00 AM','L05')

insert into SINHVIEN_MONHOC

values ('MH01',N'SV01',1,1,3.50)

insert into SINHVIEN_MONHOC

values ('MH02',N'SV02',1,1,3.00)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV03',1,1,0.50)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV03',1,2,1.50)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV03',2,1,3.50)

insert into SINHVIEN_MONHOC

values ('MH02',N'SV06',1,1,0.00)

insert into SINHVIEN_MONHOC

values ('MH02',N'SV06',2,2,0.50)

insert into SINHVIEN_MONHOC

values ('MH02',N'SV07',2,1,3.00)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV01',1,1,2.50)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV04',2,1,0.50)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV07',1,1,2.00)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV09',2,1,0.50)

insert into SINHVIEN_MONHOC

values ('MH03',N'SV09',2,2,1.50)

---cap nhat thong tin sinh vien---

update SINHVIEN

set malop ='L02'

where maSV='SV03'

---cap nhat thong tin---

update Khoa

set dienthoai=0438373

where tenkhoa=N'cong nghe thong tin'

---xoa sV---

delete from sinhvien

where masv='SV04'

---xao cac thong tin ve lop va cac sinh vien co ma l04---

delete from SINHVIEN

where malop='L04'

delete from lop

where malop='L04'

bai 2:

---hien thi ma lop,ten lop cua tat ca cac lop---

select*

from Lop,khoa

where Lop.makhoa=Khoa.makhoa

---hien thi thong tin chi tiet cac lop o khoa CNTT---

select*

from Lop,khoa

where Lop.makhoa=Khoa.makhoa and tenkhoa=N'cong nghe thong tin'

---hien thi ho ten cac sinh vien o lop sptin k32---

select hodem,ten

from SINHVIEN,LOP

where Lop.malop=sinhvien.malop and tenlop=N'SP tin K32'

---hien thi ho ten cua sinh vien co ma khoa k01---

select hodem,ten

from Lop,sinhvien

where lop.malop=sinhvien.malop and makhoa='K01'

---hien thi thong tin sinh vien o khoa cntt truong gioi tinh hien thi nam,nu---

select masv,hodem,ten,ngaysinh,

case gioitinh

when 1 then 'Nam'

when 0 then 'Nu'

END

from SinhVien,lop,khoa

where sinhvien.malop=lop.malop and lop.makhoa=khoa.makhoa and tenkhoa=N'cong nghe thong tin'

---hien thi thong tin sinh vien khoa ngoai ngu va tu nhien---

select masv,hodem,ten,ngaysinh,

case gioitinh

when 1 then 'Nam'

when 0 then 'Nu'

END

from SinhVien,lop,khoa

where sinhvien.malop=lop.malop and lop.makhoa=khoa.makhoa and (tenkhoa=N'ngoai ngu' or tenkhoa=N'tu nhien')

---danh sach cac sinh vien vao hoc nam 2007---

select masv,hodem,ten,ngaysinh,

case gioitinh

when 1 then 'Nam'

end

from lop,sinhvien,khoa

where sinhvien.malop=lop.malop and lop.makhoa=khoa.makhoa and tenkhoa=N'cong nghe thong tin' and namvao =2007

---danh sach sinh vien nam co ten la linh---

select hodem,ten,ngaysinh,

case gioitinh

when 1 then 'Nam'

when 0 then 'Nu'

END

from SINHVIEN

where ten like 'linh'

---danh sach sinh vien co ho nguyen---

select hodem,ten,ngaysinh,

case gioitinh

when 1 then 'Nam'

when 0 then 'Nu'

END

from sinhvien

where hodem like 'Nguyen%'

bai 3:

--cho biet tong so sinh vien trong---

select count(masv)

from sinhvien,lop,khoa

where sinhvien.malop=lop.malop and lop.makhoa=khoa.makhoa

---cho biet tong so sinh vien cua khoa k01---

select makhoa,count(masv)

from sinhvien,lop

where sinhvien.malop=lop.malop and makhoa='K01'

group by makhoa

---cho biet tong so luong sinh vien theo tung khoa

select makhoa,count(masv)

from sinhvien,lop

where sinhvien.malop=lop.malop

group by makhoa

---dua ra cac khoa co so luong sinh vien lon hon 3---

select makhoa,count(masv)

from sinhvien,lop

where sinhvien.malop=lop.malop

group by makhoa

having count(masv)>3

---tong so sinh vien nam vao hoc nam 2009 bat dau bang chu H---

select hodem, ten,makhoa,count(masv)

from sinhvien,lop

where sinhvien.malop=lop.malop and namvao =2009 and hodem like 'H%' and gioitinh=1

group by hodem, ten,makhoa

---cho biet tong so luong sinh vien theo tung lop vao nam 2008---

select hodem, ten,makhoa,count(masv)

from sinhvien,lop

where sinhvien.malop=lop.malop and namvao =2008

group by hodem, ten,makhoa