csdl_bt1
-------------------------------- //PHAN I //-----------------------------
1
ALTER TABLE HOCVIEN ADD GHICHU VARCHAR (10)
ALTER TABLE HOCVIEN ADD XEPLOAI VARCHAR (10)
ALTER TABLE HOCVIEN ADD DIEMTB NUMERIC (4,2)
2
ALTER TABLE HOCVIEN ADD CONSTRAINT CH_HV CHECK (LEFT (MAHV,3)=MALOP AND RIGHT (MAHV,2) like '[0-9][0-9]')
3
ALTER TABLE HOCVIEN ADD CONSTRAINT CH_HV2 CHECK (GIOITINH ='NAM' OR GIOITINH = 'NU')
ALTER TABLE GIAOVIEN ADD CONSTRAINT CH_GV CHECK (GIOITINH ='NAM' OR GIOITINH = 'NU')
4 /*Luu 2 so le la cua kieu du lieu, chi kiem tra o do con check la rang buoc thi ko can*/
ALTER TABLE KETQUATHI ADD CONSTRAINT CH_KQT CHECK (DIEM BETWEEN 0 AND 10)
5
ALTER TABLE KETQUATHI ADD CONSTRAINT CH_KQT2 CHECK ((KQUA ='DAT' and (DIEM <=10 and DIEM >=5)) or (KQUA='KHONG DAT' and DIEM <5))
6
alter table ketquathi add constraint ch_kqt3 check (LANTHI between 1 and 3)
7
alter table giangday add constraint ch_gd check (HOCKY between 1 and 3)
8
alter table giaovien add constraint ch_gv3 check ( HOCVI in('CN','KS','ThS','TS','Pts'))
9
alter table lop add constraint ch_lop check (left(malop,3)=left(trglop,3))
10
drop trigger GV_truong_khoa
create trigger GV_truong_khoa
on KHOA
for INSERT,UPDATE
As declare
@Khoa_gv varchar(4),
@KHOA varchar(4),
@Trg_khoa char (4),
@hocvi varchar (10),
@thongbao_1 tinyint,
@thongbao_2 tinyint
SELECT
@Khoa_gv = G.MAKHOA,
@KHOA = K.MAKHOA,
@Trg_khoa = K.TRGKHOA,
@hocvi = G.HOCVI
FROM Inserted K Inner Join GIAOVIEN G ON K.TRGKHOA = G.MAGV
if (@KHOA_GV<>@KHOA) set @thongbao_1 = 0
if (@hocvi != 'TS' or @hocvi != 'PTS') set @thongbao_2 =0
if (@thongbao_1 = 0 or @thongbao_2 = 0)
BEGIN
if @thongbao_1 = 0
raiserror('Truong khoa %s khong fai la GV cua KHOA %s!',16,1,@Trg_khoa,@KHOA)
if @thongbao_2 = 0
raiserror ('%s hoc vi ko fai la TS hoac PTS',16,1,@Trg_khoa)
rollback Tran
END
/*
update KHOA
set TRGKHOA = 'GV16'
where MAKHOA ='KTMT'
----
insert into GIAOVIEN
VALUES ('GV16', 'Tran Doan Hung', 'TS', 'GV', 'Nam', '19530311', '20050112', 4.5, 2025000, 'MTT')
delete from GIAOVIEN
where MAGV ='GV16'
*/
select * from GIAOVIEN
11
-- ALTER TABLE HOCVIEN ADD constraint ch_hv3 check (datediff(year,ngsinh,GETDATE())>=18)
drop trigger ngsinh_hocvien
create trigger ngsinh_hocvien
on HOCVIEN
for Insert,Update
as Declare
@ngaysinh smalldatetime,
@namhoc smallint,
@ten_HV varchar(10),
@namsinh smallint
select
@ngaysinh = a.NGSINH,
@namhoc = b.NAM,
@ten_HV = a.TEN,
@namsinh = year (@ngaysinh)
from Inserted a Inner join GIANGDAY b on a.MALOP = b.MALOP
if (@namhoc - @namsinh)<18
begin
raiserror ('Hoc vien %s chua du 18 de dc theo hoc',16,1,@ten_HV)
rollback transaction
end
12
alter table GIANGDAY add constraint ch_gd2 check (datediff(day,TUNGAY,DENNGAY)>0)
13
alter table GIAOVIEN add constraint ch_gv5 check (datediff(year,NGSINH,NGVL)>=22)
14
alter table MONHOC add constraint ch_mh check ((TCLT-TCTH)<=5)
15
create trigger Ngaythi_ngayketthucmon
on KETQUATHI
for INSERT,UPDATE
as
declare
@Ngaythi smalldatetime,
@Ngayketthucmon smalldatetime,
@Lop char (3),
@Mon varchar (10)
Select
@Ngaythi = a.NGTHI,
@Ngayketthucmon = b.DENNGAY,
@Lop = b.MALOP,
@Mon = a.MAMH
from KETQUATHI a inner join GIANGDAY b on a.MAMH =b.MAMH
if (datediff(day,@Ngaythi,@Ngayketthucmon)>0)
begin
raiserror ('Lop %s fai hoc xong mon %s moi dc thi ',16,1,@Lop,@Mon)
rollback transaction
end
****
create trigger tr_del_gd
on giangday
for delete, update
as
declare @gd_mh varchar(10),
@gd_lop char(3),
select @gd_mh=d.mamh,
@gd_lop=d.malop
from deleted d,ketquathi k
where k.mamh=d.mamh
if exists (select * from hocvien h,ketquathi k
where k.mahv=h.mahv and @gd_lop=h.malop)
begin
raiserror('HALT!!!',16,1)
rollback tran
end
select * from GIANGDAY
drop trigger tr_del_gd
/*
delete from giangday where mamh='CSDL' and malop ='K11' and hocky=2
insert into giangday
values ('K11', 'CSDL', 'GV05', 2, 2006, '20060601', '20060715')
*/
16
drop trigger lop_somonhoc_toida
create trigger lop_somonhoc_toida
on GIANGDAY
for INSERT,UPDATE
as Declare
@Lop char(3),
@HOCKY tinyint,
@Namhoc smallint,
@Somon tinyint
Select
@Lop = L.MALOP,
@HOCKY = L.HOCKY,
@Namhoc = L.NAM,
@Somon = count (G.MAMH)
from GIANGDAY G, Inserted L
where L.MALOP = G.MALOP and L.HOCKY = G.HOCKY and L.NAM = G.NAM
group by L.MALOP,L.HOCKY,L.NAM
if (@Somon >3)
begin
raiserror ('Lop %s da hoc 3 MH trong hoc ky %d nam %d vi the ko the hoc them ',16,1,@Lop,@HOCKY,@Namhoc)
rollback transaction
end
select * from GIANGDAY
/* DU LIEU DUNG DE KIEM TRA
INSERT INTO [GIANGDAY] VALUES (N'K11', N'LTHDT', N'GV03', 1, 2006, '20060801', '20060915')
INSERT INTO [GIANGDAY] VALUES (N'K11', N'PTTKTT', N'GV05', 1, 2006, '20060901', '20060915')
delete from giangday where mamh='LTHDT'
delete from giangday where mamh='PTTKTT'
select * from MONHOC
select * from GIANGDAY
select HOCKY,NAM,MALOP,count(MAMH)
from GIANGDAY
group by HOCKY,NAM,MALOP
*/
drop trigger TRIGGER_GIANGDAY
??????????
CREATE TRIGGER TRIGGER_GIANGDAY
ON GIANGDAY
FOR INSERT
AS
IF EXISTS ( SELECT NAM,HOCKY,COUNT (MAMH) FROM INSERTED A ,HOCVIEN HV
where A.HOCKY = HV.HOCKY and A.NAM = HV.NAM and A.MALOP=HV.MALOP
GROUP BY A.NAM, A.HOCKY, A.MALOP
HAVING COUNT ( MAMH )>3)
BEGIN
RAISERROR ('SO MON HOC CUA MOT HOC KY TRONG MOT NAM toi da la' 3',16,1)
ROLLBACK TRAN
END
17
drop trigger lop_sisolop
create trigger lop_sisolop
on HOCVIEN
for INSERT,UPDATE
as Declare
@Lop char(3),
@Siso tinyint,
@So_HV tinyint
select
@Lop = L.MALOP,
@Siso = L.SISO,
@So_HV = count(H.MAHV)
from Inserted I,HOCVIEN H, LOP L
where I.MALOP = L.MALOP
group by L.MALOP,L.SISO
if (@So_HV>@Siso)
begin
raiserror ('Lop %s co tong so HV ko = siso cua Lop',16,1,@Lop)
rollback transaction
end
/* DU LIEU DUNG DE KIEM TRA
INSERT INTO [HOCVIEN](MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)
VALUES ('K1127', 'Ali', 'Baba', '19820227', 'Nam', 'DN', 'K11')
delete from hocvien where ten = 'Baba'
ALTER TABLE HOCVIEN drop constraint ch_HV
update hocvien
set MALOP ='K12'
where MAHV='K1101'
SELECT * FROM HOCVIEN
SELECT * FROM LOP
select MALOP,count (MAHV)
from HOCVIEN
group by MALOP
*/
18
drop trigger TR_DIEUKIEN
create trigger TR_DIEUKIEN
on DIEUKIEN
for INSERT,UPDATE
As declare
@Dk_1 varchar(10),
@Dk_2 varchar(10),
@thongbao_1 tinyint,
@thongbao_2 tinyint
select
@Dk_1 = I.MAMH,
@Dk_2 = I.MAMH_TRUOC
from DIEUKIEN DK , Inserted I
if(@Dk_1 = @Dk_2) set @thongbao_1 =0
if EXISTS
(
select * from DIEUKIEN
where MAMH = @Dk_2 and MAMH_TRUOC = @Dk_1
) set @thongbao_2 = 0
if (@thongbao_1 =0 or @thongbao_2 =0)
begin
if (@thongbao_1 =0)
raiserror ('Trong quan he DIEUKIEN ko dc ton tai 1 bo gia tri ma MAMH = MAMH_TRUOC ',16,1)
if (@thongbao_2 =0)
raiserror ('Trong quan he DIEUKIEN ko dc ton tai 2 bo gia tri dao nguoc nhau',16,1)
rollback tran
end
/*
select * from DIEUKIEN
update DIEUKIEN
set MAMH_TRUOC = 'PTTKHTTT'
where MAMH ='CSDL' and MAMH_TRUOC ='CTDLGT'
*/
19
drop trigger gv_mucluong
create trigger gv_mucluong
on GIAOVIEN
for Insert,Update
as Declare
@hocvi varchar(10),
@hocham varchar(10),
@heso numeric(4,2),
@mucluong money
select
@hocvi = I.HOCVI,
@hocham = I.HOCHAM,
@heso = I.HESO,
@mucluong = I.MUCLUONG
from Inserted I
if EXISTS
(
select * from GIAOVIEN
where hocvi = @hocvi and hocham = @hocham and heso = @heso and mucluong != @mucluong
)
begin
raiserror ('Alibaba va 40 ten cuop dei haha ^^',16,1)
rollback tran
end
/*
Insert Into GIAOVIEN Values ('GV17','Alibaba','ThS','GV','Nam','19711123','20050301',4,1800001,'KHMT')
delete from GIAOVIEN where HOTEN = 'Alibaba'
select * from GIAOVIEN
*/
20**
drop trigger hv_thilai_diemthi
create trigger hv_thilai_diemthi
on KETQUATHI
for Insert, Update
as Declare
@hocvien char(5),
@monhoc varchar(10),
@lanthi tinyint
select
@hocvien = D.MAHV,
@monhoc = D.MAMH,
@lanthi = D.LANTHI
from Inserted D
if EXISTS
(
select * from KETQUATHI
where MAHV =@hocvien and MAMH =@monhoc and lanthi =@lanthi -1 and diem >=5
)
begin
raiserror ('HV %s ko dc phep thi lai vi diem cua lan thi trc do da > 5',16,1,@hocvien)
rollback tran
end
/*
select * from KETQUATHI
insert into KETQUATHI VALUES (N'K1101', N'CSDL', 2, '20060820', 4, 'Khong Dat')
delete from KETQUATHI
where MAHV ='K1101' and MAMH='CSDL' and Lanthi ='2'
update KETQUATHI
set DIEM =8
where MAHV='K1102' and MAMh='CSDL' and LANTHI ='1'
update KETQUATHI
set DIEM =4.00
where MAHV='K1102' and MAMh='CSDL' and LANTHI ='2'
---------
update KETQUATHI
set DIEM =4.00
where MAHV='K1102' and MAMh='CSDL' and LANTHI ='1'
update KETQUATHI
set DIEM =4.25
where MAHV='K1102' and MAMh='CSDL' and LANTHI ='2'
*/
21
drop trigger ngaythi_lanthi
create trigger ngaythi_lanthi
on KETQUATHI
For Insert, Update
as Declare
@mahv char(5) ,
@mamh varchar(10),
@ngaythi smalldatetime,
@lanthi tinyint
select
@mahv = D.MAHV,
@mamh = D.MAMH,
@ngaythi = D.NGTHI,
@lanthi = D.LANTHI
from Inserted D
if Exists
(
select * from KETQUATHI
where mahv =@mahv and mamh =@mamh and lanthi =@lanthi-1 and ngthi >@ngaythi
)
begin
raiserror ('Ko dc phep chinh sua vi lanthi %d mon %s cua %s co ngaythi < lanthi truoc do',16,1,@lanthi,@mamh,@mahv)
rollback tran
end
/*
select * from KETQUATHI
update ketquathi
set ngthi ='20060710'
where MAHV ='k1102' and MAMH='CSDL' and LANTHI='2'
-----
*/
22
^^ GIONG CAU 15 khoi fai La'M ^^
23**
drop trigger thutu_gday_monhoc
create trigger thutu_gday_monhoc
on GIANGDAY
for Insert,Update
as Declare
@mamh_gd varchar(10),
@mamh_tr varchar(10),
@lop char(3)
select
@mamh_gd = I.MAMH,
@mamh_tr = D.MAMH_TRUOC,
@lop = I.MALOP
from Inserted I, DIEUKIEN D
where I.MAMH = D.MAMH
If
(
Not Exists (select * from Giangday where malop =@lop and mamh=@mamh_tr)
And
Exists (select * from DIEUKIEN where mamh=@mamh_gd)
)
begin
raiserror ('Lop %s fai hoc xong monhoc_trc bat buoc',16,1,@lop)
rollback tran
end
/*
update GiangDay
set MAMH ='LTHDT'
where MALOP ='K11' and MAMH ='THDC'
-----
update GiangDay
set MAMH ='THDC'
where MALOP ='K11' and MAMH ='LTHDT'
*/
select * from giangday
select * from dieukien
24
drop trigger GV_gd_monhoc_thuoc_khoa
create trigger GV_gd_monhoc_thuoc_khoa
on GIANGDAY
for Insert, Update
as Declare
@magv char(4),
@makhoa varchar(4),
@khoa varchar(4),
@mamh varchar(10)
select
@magv = I.MAGV,
@makhoa = G.MAKHOA,
@khoa = M.MAKHOA,
@mamh = M.MAMH
from Inserted I,MONHOC M, GIAOVIEN G
where M.MAMH = I.MAMH and G.MAGV = I.MAGV
if (@makhoa <>@khoa)
begin
raiserror ('mon hoc %s ko thuoc khoa %s cua %s phu trach',16,1,@mamh,@makhoa,@magv)
rollback tran
end
/*
update giangday
set magv ='GV16'
where mamh='CSDL' and magv='GV05' and malop='K11'
-----
update giangay
set magv ='GV05'
where mamh='CSDL' and magv='GV16' and malop='K11'
*/
select * from giangday
select * from monhoc
select * from giaovien
----------------------------------/ Phan II /----------------------------------------
1
UPDATE GIAOVIEN
SET HESO = HESO+(0.2)
WHERE MAGV IN (SELECT TRGKHOA FROM KHOA)
2***
SELECT * FROM HOCVIEN
UPDATE HOCVIEN
SET DIEMTB =
(
select avg (diem)
from ketquathi k1
where LANTHI =
(
select MAX(LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
)
group by mahv
having mahv = HOCVIEN.MAHV
)
3
SELECT * FROM HOCVIEN
SELECT * FROM KETQUATHI
UPDATE HOCVIEN
SET GHICHU = 'Cam Thi'
WHERE MAHV IN
(
SELECT MAHV FROM KETQUATHI
WHERE LANTHI ='3' AND DIEM <5
)
/*
UPDATE HOCVIEN
SET GHICHU = 'KO CAM THI ^^'
WHERE MAHV NOT IN
(
SELECT MAHV FROM KETQUATHI
WHERE LANTHI ='3' AND DIEM <5
)
*/
4
Update HOCVIEN
set XEPLOAI =
case diemtb
when (diemtb >=9.00) then 'XS'
when (diemtb >= 8 AND diemtb <9) then 'G'
when (diemtb >=6.5 and diemtb<8) then 'K'
when (diemtb >=5 and diemtb<6.5) then 'TB'
else 'Y'
end
from HOCVIEN
----------------------------------/PHAN III/----------------------------------------
1
SELECT A.MAHV,A.HO +' '+A.TEN AS [HO VA TEN],A.NGSINH,A.MALOP
FROM LOP INNER JOIN HOCVIEN A ON LOP.TRGLOP = A.MAHV
2
SELECT H.MAHV,H.HO +' '+H.TEN AS [HO VA TEN],K.LANTHI,K.DIEM AS [DIEMSO]
FROM HOCVIEN H INNER JOIN KETQUATHI K ON H.MAHV =K.MAHV
WHERE K.MAMH ='CTRR' AND H.MALOP='K12'
ORDER BY H.TEN,H.HO
3
SELECT H.MAHV,H.HO +' '+H.TEN AS [HO VA TEN],K.LANTHI,K.MAMH,K.DIEM AS [DIEMSO]
FROM HOCVIEN H INNER JOIN KETQUATHI K ON H.MAHV =K.MAHV
WHERE K.LANTHI ='1' AND K.KQUA ='DAT'
4
select H.MAHV,H.HO+' '+H.TEN AS [HO TEN],K.DIEM
from HOCVIEN H inner join KETQUATHI K ON.H.MAHV=K.MAHV
WHERE K.MAMH='CTRR' AND H.MALOP='K11' AND K.KQUA='KHONG DAT' AND K.LANTHI='1'
5**
select MAHV, Ho+' '+Ten as [HOTEN]
from HOCVIEN
where MALOP like 'K%' and MAHV in
(
select K.MAHV
from KETQUATHI K
WHERE K.MAMH='CTRR' AND K.KQUA='KHONG DAT'
GROUP BY K.MAHV
HAVING COUNT (k.LANTHI)
=
(select count (k2.lanthi)
from ketquathi k2
where MAMH ='CTRR' and k.MAHV =k2.MAHV
group by MAHV
))
6
select distinct g2.MAMH
from GIAOVIEN g inner join GIANGDAY g2 on g.MAGV=g2.MAGV
where g.HOTEN = 'Tran Tam Thanh' and g2.HOCKY=1 and g2.NAM ='2006'
7
select MAMH,TENMH from MONHOC where MAMH in
(
select distinct g2.MAMH
from LOP g inner join GIANGDAY g2 on g.MAGVCN=g2.MAGV
where g.MALOP = 'K11' and g2.HOCKY=1 and g2.NAM ='2006'
)
8
select MAHV,HO+' '+TEN as [HOTEN] from HOCVIEN
Where MAHV IN
(select TRGLOP from LOP where MALOP in
(
select g2.MALOP
from GIANGDAY g2,GIAOVIEn g
where g2.MAGV = g.MAGV and g.HOTEN = 'Nguyen To Lan'
and g2.MAMH in (select MAMH from MONHOC where TENMH='Co so Du LIeu')
))
9
select MAMH, TENMH
from MONHOC
where MAMH in
(
select MAMH_TRUOC
from DIEUKIEN d, MONHOC m
where d.MAMH = m.MAMH and m.TENMH = 'Co so Du lieu'
)
10
select MAMH, TENMH
from MONHOC
where MAMH in
(
select m.MAMH
from DIEUKIEN d, MONHOC m
where d.MAMH = m.MAMH and d.MAMH_TRUOC in
(select MAMH
from MONHOC
where TENMH = 'Cau truc roi rac'
))
11
select g.MAGV
from GIAOVIEN g, GIANGDAY g2
where g.MAGV = g2.MAGV
and g2.MALOP ='K11' and g2.HOCKY='1' and g2.MAMH ='CTRR'
and g.MAGV in
(
select g.MAGV
from GIAOVIEN g, GIANGDAY g2
where g.MAGV = g2.MAGV
and g2.MALOP ='K12' and g2.HOCKY='1' and g2.MAMH ='CTRR'
)
select * from hocvien
select * from ketquathi
12
select k.mahv,h.ho+' '+h.ten AS [Ho ten]
from ketquathi k,hocvien h
where k.mahv=h.mahv and mamh='CSDL' and KQUA='Khong Dat'
and k.mahv in (
select mahv
from ketquathi
where mamh='CSDL'
group by mahv
having (count(lanthi)=1)
)
select * from KETQUATHI
13
select MAGV from GIAOVIEN
where MAGV not in
(
select MAGV
from GIANGDAY
)
cau 14
select MAGV,HOTEN,MAKHOA
from GIAOVIEN
where MAGV not IN
(
select a.MAGV--,a.MAMH,c.MAKHOA
from GIANGDAY a, GIAOVIEN b , MONHOC c
where a.MAGV = b.MAGV and b.MAKHOA = c.MAKHOA and a.MAMH =c.MAMH
)
cau 15
select * from ketquathi
select * from hocvien
select h.ten ,k.mamh
from HOCVIEN h inner join KETQUATHI k on k.mahv = h.mahv
where (h.MALOP = 'K11' and k.KQUA ='Khong Dat') or
(k.mamh ='CTRR' and h.MALOP = 'K12' and k.LANTHI =2 and k.DIEM =5)
group by h.ten, k.mamh
having count (k.KQUA)=3
/*
select h.ten,k.lanthi
from HOCVIEN h inner join KETQUATHI k on k.mahv = h.mahv
where h.MALOP = 'K12' and k.DIEM =5 and k.mamh ='CTRR'
select h.ten, h.MALOP
from HOCVIEN h inner join KETQUATHI k on k.mahv = h.mahv
where k.mamh ='CTRR' and h.MALOP = 'K12'
INSERT INTO KETQUATHI VALUES ('K1213','CTRR', 2, '20070115', 5, 'Dat')
insert into HOCVIEN (mahv,ho,ten,ngsinh,gioitinh,noisinh,malop)
values ('K1213','Aladin','Cay den than','19860212','Nam','TpHCM','K12')
*/
cau 16
select HOTEN
from GIAOVIEN
where MAGV in
(
select MAGV
from GIANGDAY
where MAMH ='CTRR'
group by HOCKY, NAM, MAGV,MAMH
having count (MALOP) =2
)
17
select h.HO+' '+h.TEN as HOTEN,k1.diem
from ketquathi k1 inner join HOCVIEN h on k1.MAHV = h.MAHV
where MAMH ='CSDL' and LANTHI =
(
select MAX(LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
)
18
select h.HO+' '+h.TEN as HOTEN,k1.diem
from ketquathi k1 inner join HOCVIEN h on k1.MAHV = h.MAHV
where MAMH ='CSDL' and DIEM =
(
select MAX(DIEM)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
)
/*
delete from ketquathi where mahv ='K1213'
delete from hocvien where mahv ='K1213'
INSERT INTO KETQUATHI VALUES ('K1213','CSDL', 2, '20070115', 7, 'Dat')
INSERT INTO KETQUATHI VALUES ('K1213','CSDL', 3, '20070115', 5, 'Dat')
insert into HOCVIEN (mahv,ho,ten,ngsinh,gioitinh,noisinh,malop)
values ('K1213','Aladin','Cay den than','19860212','Nam','TpHCM','K12')
*/
19
select MAKHOA,TENKHOA
from KHOA
where NGTLAP = (select min(NGTLAP) from KHOA)
20
select HOCHAM, count (MAGV) as Tong_so_Giao_Vien
from GIAOVIEN
where HOCHAM ='GS' or HOCHAM ='PGS'
group by HOCHAM
21
select k.MAKHOA, k.TENKHOA, g.HOCVI,count (g.MAGV) as Tong_so_GV
from GIAOVIEN g inner join KHOA k on g.MAKHOA =k.MAKHOA
Group by k.MAKHOA, k.TENKHOA, g.HOCVI
22
select h.HO+' '+h.TEN as HOTEN,k1.MAMH,k1.diem , k1.KQUA into A1
from ketquathi k1 inner join HOCVIEN h on k1.MAHV = h.MAHV
where LANTHI =
(
select MAX(LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
)
order by k1.MAMH
-----
select MAMH,KQUA,count(HOTEN) as Tong_so_SV
from A1
group by MAMH,KQUA
order by MAMH
23
select MAGV,HOTEN
from GIAOVIEN
where MAGV in
(
select L.MAGVCN --,G.MALOP, count (MAMH)
from LOP L inner join GIANGDAY G on L.MALOP = G.MALOP
group by G.MALOP,L.MAGVCN
having count (MAMH) >=1
)
24
Select HO+' '+TEN as HOTEN
from HOCVIEN
where MAHV in
(Select TRGLOP from LOP where SISO = (SELECT MAX(SISO) from LOP ))
25
drop table a2
select MAHV, MAMH, count (LANTHI) as tong_so_LT into A2
from KETQUATHI K inner join Lop L on K.MAHV = L.TRGLOP
where KQUA = 'Khong Dat'
group by MAHV, MAMH
having count (LANTHI) =
(
select count (LANTHI)
from KETQUATHI k2
where k.MAHV = k2.MAHV
group by MAHV, MAMH
)
-----
Select HO+' '+TEN as HOTEN
from HOCVIEN
where MAHV IN
(
select MAHV
from A2
group by MAHV
having count (MAMH) <='3'
)
26
select MAHV, Ho+' '+Ten as [Ho Ten]
from HOCVIEN
where MAHV in
(
select MAHV --, count (MAMH) as [So mon dat diem 9_10]
from KETQUATHI
where DIEM between 9 and 10
group by MAHV
having count (MAMH) >= ALL
(
select count (MAMH) as [So mon dat diem 9_10]
from KETQUATHI
where DIEM between 9 and 10
group by MAHV
))
27
select h.malop,k.mahv as [Ma Hoc vien],h.ho +' '+h.ten as [HoTen],count(k.mamh) as [So mon dat diem 9-10]
from Ketquathi k,Hocvien h
where k.mahv=h.mahv and diem between 9 and 10
group by h.malop,k.mahv,h.ho +' '+h.ten
having count(k.mamh) >= All
(
select count(k2.mamh)
from ketquathi k2,hocvien h2
where k2.mahv=h2.mahv and diem between 9 and 10 and
h.malop=h2.malop
group by h2.malop,k2.mahv,h2.ho +' '+h2.ten
)
28
select HOCKY,NAM, MAGV, count (MAMH)as Tong_so_mon_day, count (MALOP)as Tong_so_lop_day
from GIANGDAY
group by HOCKY,NAM, MAGV
29
select MAGV, count (MAMH) as [Tong so mon Day]
from GIANGDAY
group by HOCKY,NAM,MAGV
having count (MAMH) >= All
(
select count (MAMH)
from GIANGDAY
group by HOCKY,NAM,MAGV
)
30
select MAMH,TENMH
from MONHOC
where MAMH IN
(
select MAMH
from KETQUATHI
where LANTHI ='1' and KQUA='Khong Dat'
group by MAMH
having count (MAHV) >= All
(
select count (MAHV)
from KETQUATHI
where LANTHI ='1' and KQUA='Khong Dat'
group by MAMH
))
31
select MAHV, count (MAMH) as So_mon_Dat --into A7
from KETQUATHI k1
where LANTHI ='1' and KQUA ='Dat'
group by MAHV
having count (MAMH) =
(
select count (distinct MAMH)
from KETQUATHI k2
where k2.MAHV = k1.MAHV
group by MAHV
)
-----
select MAHV,HO+' '+TEN as HOTEN
from HOCVIEN
where MAHV IN
(
select MAHV
from A7
where So_mon_dat = (select MAX (So_mon_dat)from A7)
)
32
select MAHV, count (MAMH) as So_mon_Dat --into A8
from KETQUATHI k1
where LANTHI =
(
select MAX (LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
)
and KQUA ='Dat'
group by MAHV
having count (MAMH) =
(
select count (distinct MAMH)
from KETQUATHI k3
where k3.MAHV = k1.MAHV
group by MAHV
)
-----
select MAHV,HO+' '+TEN as HOTEN
from HOCVIEN
where MAHV IN
(
select MAHV
from A8
where So_mon_dat = (select MAX (So_mon_dat)from A8)
)
35
select MAMH, MAX(DIEM)as Max_Diem --into A9
from KETQUATHI k1
where LANTHI =
(
select MAX(LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH = k2.MAMH
group by MAHV,MAMH
)
group by MAMH
-----
select k1.MAMH,k1.MAHV, k1.DIEM
from KETQUATHI k1,A9
where LANTHI =
(
select MAX (LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
) and k1.MAMH = A9.MAMH and k1.DIEM = A9.Max_Diem
order by k1.MAMH
33
select MAHV, count (MAMH) as So_mon_Dat into A10
from KETQUATHI
where LANTHI ='1' and KQUA ='Dat'
group by MAHV
having count (MAMH) =
(
select count (MAMH)
from MONHOC
)
-----
select MAHV,HO+' '+TEN as HOTEN
from HOCVIEN
where MAHV IN
(
select MAHV
from A10
where So_mon_dat = (select MAX (So_mon_dat)from A10)
)
34
select MAHV, count (MAMH) as So_mon_Dat -- into A11
from KETQUATHI k1
where LANTHI =
(
select MAX (LANTHI)
from KETQUATHI k2
where k1.MAHV =k2.MAHV and k1.MAMH =k2.MAMH
group by MAHV,MAMH
)
and KQUA ='Dat'
group by MAHV
having count (MAMH) =
(
select count (MAMH)
from MONHOC
)
-----
select MAHV,HO+' '+TEN as HOTEN
from HOCVIEN
where MAHV IN
(
select MAHV
from A11
where So_mon_dat = (select MAX (So_mon_dat)from A11)
)
Bạn đang đọc truyện trên: Truyen2U.Com