Đã có bài ass 4

--Tao database QLSV
CREATE database QLSV
go
use QLSV
go
--Tao table SinhVien chua cac thong tin lien quans
create table SinhVien
(
Maso nvarchar(12)primary key,
Hoten nvarchar(30),
Ngaysinh date,
Gioitinh bit,
Diachi nvarchar(30),
Email nvarchar(30),
DiemPC float,
DiemHDJ float,
DiemDW8 float,
DiemRDBMS float
)
go
/* Thay doi thuoc tinh Gioitinh co kieu du lieu tinyint, them thuoc tinh Mota co kieu du lieu nvarchar(300)*/
Alter table SinhVien
Alter column Gioitinh tinyint
Alter table SinhVien
add Mota nvarchar(300)
go
--Them du lieu vao bang SinhVien voi it nhat la 10 sv

insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV01','A','10/10/77','0','thiendang','xaxam_x7@yahoo.com.vn','0','0 ','0','0')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV02','B','7/12/91','1','thiendangxanh','qhai_2009@yahoo.com.vn',' 10','10','10','10')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV03','C','4/10/82','1','thiendangdo','','10','10','10','9')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV04','D','1/1/88','0','thiendangtim','girl91@yahoo.com.vn','1',' 1','1','1')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV05','E','10/1/90','0','thiendangblue','quynhkieu7@yahoo.com.vn', '10','10','10','10')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV06','F','11/10/90','0','thiendanghong','hotgirl@yahoo.com.vn','3' ,'4','5','6')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV07','G','11/11/91','1','thiendangdacam','pro@yahoo.com.vn','8','9 ','9','10')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV08','H','1/3/91','0','thiendanghonghong','','1','4','3','6')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV09','I','20/11/91','1','thiendangnaudo','','7','4','5','9')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV10','K','11/3/91','0','thiendanghonghong','','1','4','3','6')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV11','L','11/3/91','0','thiendanghonghong','','1','4','3','6')
insert into SinhVien(Maso, Hoten ,Ngaysinh , Gioitinh ,Diachi ,Email ,DiemPC ,DiemHDJ ,DiemDW8 ,DiemRDBMS)
values('SV12','N','11/9/91','0','thiendanghonghong','','1','9','9','9')
--Hien thi tat ca sinh vien
Select * from SinhVien go

--Hien thi thong tin cua sinh vien co diem tung mon tren 5
select *from SinhVien
where DiemPC >=5
and DiemHDJ >=5
and DiemDW8 >=5
and DiemRDBMS >=5
go
/* Cho biet cac thong tin ve SV, chi lay thong tin ma so va ho ten va chua co email*/
select Maso, Hoten, Email from SinhVien
where Email=''
-- Hien thi thong tin cac sinh vien co tuoi tren 21
select *from SinhVien
where 2010-YEAR(ngaysinh)>=21
go
--Hien thi top 5 SV co diem HDJ cao nhat
Select Top 5 DiemHDJ From SinhVien

Order by DiemHDJ desc
go
--Hien thi top 3 SV co diem RDBMS cao nhat, neu trung diem thi van hien
select top 3 with ties *from SinhVien
order by DiemRDBMS desc
go
--Hien thi top 30% sinh vien co diem PC cao nhat
select top 30 percent * from SinhVien
order by DiemPC desc
go
--Cap nhat email cua sinh vien, neu khong co email thanh unknow
update SinhVien
set Email='unknown'
where Email=''
go
--Tang 2 diem HDJ cho moi sinh vien
update SinhVien
set DiemHDJ = DiemHDJ+2
go
--Cap nhat ngay inh cua SV
update SinhVien
set Ngaysinh='20/1/1989'
where Maso='SV09'
go
--Tang 20% diem mon PC cho cac ban nu
update SinhVien
set DiemPC=DiemPC+2
where Gioitinh=0
go
--Xoa thong tin nhung SV co nam 1980 tro ve truoc
delete from SinhVien
where YEAR(ngaysinh)<1980
go
--Xoa tat ca Sinhvien co diem mon PC, HDJ, DW8, RDBMS duoi 3
delete from SinhVien
where DiemPC<3
and DiemHDJ<3
and DiemDW8<3
and DiemRDBMS<3
go