csdl_bt2
CREATE DATABASE QLGV
USE QLGV
CREATE TABLE KHOA
( MAKHOA VARCHAR(4) PRIMARY KEY,
TENKHOA VARCHAR (40),
NGTLAP SMALLDATETIME,
TRGKHOA CHAR (4)
CREATE TABLE MONHOC
MAMH VARCHAR (10) PRIMARY KEY NOT NULL,
TENMH VARCHAR (40),
TCLT TINYINT,
TCTH TINYINT,
MAKHOA VARCHAR (4),
CREATE TABLE DIEUKIEN
MAMH VARCHAR (10) NOT NULL,
MAMH_TRUOC VARCHAR (40)NOT NULL
CONSTRAINT PK_DK PRIMARY KEY(MAMH,MAMH_TRUOC)
CREATE TABLE LOP
MALOP CHAR (3) PRIMARY KEY,
TENLOP VARCHAR (40),
TRGLOP CHAR (5),
SISO TINYINT,
MAGVCN CHAR (4)
CREATE TABLE KETQUATHI
MAHV CHAR (5) NOT NULL,
MAMH VARCHAR (10)NOT NULL,
LANTHI TINYINT NOT NULL,
NGTHI SMALLDATETIME,
DIEM NUMERIC (4,2),
KQUA VARCHAR (10)
CONSTRAINT PRI_KEY PRIMARY KEY (MAHV,MAMH,LANTHI)
ALTER TABLE HOCVIEN ADD CONSTRAINT FRO_KY FOREIGN KEY (MALOP) REFERENCES LOP (MALOP)
ALTER TABLE MONHOC ADD CONSTRAINT FRO_KY_MH FOREIGN KEY (MAKHOA)REFERENCES KHOA (MAKHOA)
-------------------------------- //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
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)
raiserror ('Lop %s fai hoc xong mon %s moi dc thi ',16,1,@Lop,@Mon)
rollback transaction
end
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)
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)
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)
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
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
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
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)
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)
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: AzTruyen.Top