Bài tập chương 2

Bài tập 1:
--1--
SELECT MACB AS MaCanBo,TENCB AS TenCanBo,MADETAI AS MaDeTai,QUEQUAN AS QueQuan,LUONG
FROM CANBO
WHERE LUONG IS NOT NULL
ORDER BY LUONG DESC;
--2--
SELECT MACB AS MaCanBo,TENCB AS TenCanBo,MADETAI AS MaDeTai,QUEQUAN AS QueQuan,LUONG, ROUND(LUONG/30,2) AS NgayLuong
FROM CANBO
WHERE LUONG IS NOT NULL;
--3--
SELECT *
FROM CANBO
WHERE LUONG >4000000 AND LUONG <8000000;
--4--
SELECT *
FROM CANBO
WHERE TENCB LIKE 'N%' OR TENCB LIKE '%g' OR TENCB LIKE '_u%';
--5--
SELECT *
FROM CANBO
WHERE LUONG < 4000000 AND QUEQUAN NOT IN 'hai Duong';
--6--
SELECT *
FROM NGOAINGU
WHERE TENGOAINGU NOT IN 'Anh';
--7--
SELECT *
FROM DETAI
WHERE MADETAI = 'dt1' OR MADETAI = 'dt3' OR MADETAI ='dt5'
ORDER BY MADETAI;
--8--
SELECT MACB,UPPER(TENCB),LUONG
FROM CANBO;
--9--
SELECT MADETAI,TENDETAI,NGAYDAU,NGAYCUOI,(NGAYCUOI-NGAYDAU) AS KhoangThoiGian
FROM DETAI;
--10--
SELECT MADETAI,TENDETAI,NGAYDAU,NGAYCUOI,(NGAYCUOI+60) AS NgayGiaHan
FROM DETAI;
--11--
SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG,
(CASE
WHEN LUONG >= 5000000 THEN 'A'
WHEN LUONG >= 3000000 THEN 'B'
ELSE 'C'
END
) AS GHICHU
FROM CANBO
WHERE LUONG IS NOT NULL;
--12--
SELECT QUEQUAN,MADETAI,COUNT(MADETAI)AS SLCB
FROM CANBO
GROUP BY QUEQUAN,MADETAI
ORDER BY QUEQUAN,MADETAI;
--13--
SELECT TENCB
FROM CANBO
WHERE LUONG > (SELECT AVG(LUONG) FROM CANBO);
--14--
SELECT TENCB,QUEQUAN,TENDETAI,NGAYDAU,NGAYCUOI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI;
--15--
SELECT TENCB,QUEQUAN,TENDETAI,NGAYDAU,NGAYCUOI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
WHERE NGAYCUOI = to_date('20/11/2013','dd/mm/yyyy');
--16--
SELECT TENCB,QUEQUAN,TENDETAI,NGAYDAU,NGAYCUOI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
WHERE sysdate < NGAYCUOI;
--17--
SELECT COUNT(TENCB) AS SLCB
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
WHERE NGAYCUOI>sysdate;
--18--
SELECT TENCB,QUEQUAN,TENDETAI,NGAYDAU,NGAYCUOI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
WHERE NGAYDAU = (SELECT MAX(NGAYDAU) FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI)
OR NGAYDAU = (SELECT MIN(NGAYDAU) FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI)
ORDER BY NGAYDAU;
--19--
SELECT TENCB,QUEQUAN,TENDETAI,NGAYDAU,NGAYCUOI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
WHERE NGAYCUOI-NGAYDAU=(SELECT MIN(NGAYCUOI-NGAYDAU) FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI);
--20--
SELECT MADETAI,TENDETAI
FROM DETAI
WHERE MADETAI NOT IN (SELECT MADETAI FROM CANBO WHERE MADETAI IS NOT NULL);
--21--
SELECT COUNT(MADETAI) AS SL
FROM DETAI
WHERE NGAYDAU>SYSDATE;
--22--
SELECT TENDETAI,COUNT(MACB) AS SLCB
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
GROUP BY TENDETAI;
--23--
SELECT TENDETAI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
GROUP BY TENDETAI
HAVING COUNT(MACB) = (
SELECT MAX(SL)
FROM (
SELECT TENDETAI,COUNT(MACB) AS SL
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
GROUP BY TENDETAI
)
);
--24--
SELECT TENDETAI
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
GROUP BY TENDETAI
HAVING COUNT(MACB) = (
SELECT MIN(SL)
FROM (
SELECT TENDETAI,COUNT(MACB) AS SL
FROM CANBO JOIN DETAI ON CANBO.MADETAI = DETAI.MADETAI
GROUP BY TENDETAI
)
);
--25--
SELECT MACB,TENCB
FROM CANBO
WHERE MADETAI IS NULL;
--26--
SELECT COUNT(MACB) AS SL
FROM CANBO
WHERE MADETAI IS NULL;
--27--
SELECT TENCB,TENGOAINGU,TRINHDO
FROM CANBO JOIN NGOAINGU ON CANBO.MACB = NGOAINGU.MACB
WHERE TENGOAINGU='Anh' AND TRINHDO='C';
--28--
SELECT TENCB,TENGOAINGU,TRINHDO
FROM CANBO JOIN NGOAINGU ON CANBO.MACB = NGOAINGU.MACB
WHERE (TENGOAINGU='Anh' AND TRINHDO='B') OR (TENGOAINGU='Phap' AND TRINHDO ='A');
--29--
SELECT DISTINCT TENGOAINGU
FROM NGOAINGU
WHERE TENGOAINGU IS NOT NULL;
--30--
SELECT MACB
FROM NGOAINGU
WHERE TENGOAINGU IS NULL;
--31--
SELECT COUNT(MACB) AS SL
FROM NGOAINGU
WHERE TENGOAINGU IS NULL;
--32--
SELECT COUNT(DISTINCT TENGOAINGU) AS SLNN
FROM NGOAINGU
WHERE TENGOAINGU IS NOT NULL;
--33--
SELECT COUNT(MACB) AS SL
FROM NGOAINGU
WHERE TENGOAINGU IS NULL;
--34--
SELECT TENGOAINGU, COUNT(MACB) AS SL
FROM NGOAINGU
WHERE TENGOAINGU IS NOT NULL
GROUP BY TENGOAINGU;
--35--
SELECT TENGOAINGU
FROM NGOAINGU
WHERE TENGOAINGU IS NOT NULL
GROUP BY TENGOAINGU
HAVING COUNT(MACB) = (SELECT MAX(SL) FROM (SELECT TENGOAINGU, COUNT(MACB) AS SL
FROM NGOAINGU
WHERE TENGOAINGU IS NOT NULL
GROUP BY TENGOAINGU)
);
--36--
SELECT TENCB
FROM NGOAINGU JOIN CANBO ON CANBO.MACB = NGOAINGU.MACB
GROUP BY TENCB
HAVING COUNT(TENGOAINGU) = (SELECT MAX(SL) FROM (SELECT MACB, COUNT(TENGOAINGU) AS SL
FROM NGOAINGU
GROUP BY MACB)
);
--37--
UPDATE CANBO
SET LUONG=LUONG+300000
WHERE MADETAI IS NOT NULL;
--38--
UPDATE CANBO
SET LUONG=LUONG-100000
WHERE MADETAI IS NULL;
--39--
UPDATE CANBO
SET LUONG = LUONG + 120000
WHERE MACB IN (SELECT MACB FROM NGOAINGU WHERE TENGOAINGU IS NOT NULL);
--40--
DELETE NGOAINGU
WHERE TENGOAINGU IS NULL;

--Bai tap 2--
--1--
--Bang Lylich--
CREATE TABLE LYLICH
(
MASV NUMBER
CONSTRAINT MASV_PK PRIMARY KEY,
TENSV VARCHAR2(50) NOT NULL,
NGAYSINH DATE NOT NULL,
QUEQUAN VARCHAR2(70) NOT NULL,
GIOITINH NVARCHAR2(10) NOT NULL,
DIENTHOAI NVARCHAR2(20),
NGAYNHAPTRUONG DATE NOT NULL,
NGAYRATRUONG DATE
);
ALTER TABLE LYLICH ADD CONSTRAINT CHECK_NS CHECK (NGAYSINH>=TO_DATE('1/1/1990','DD/MM/YYYY') AND NGAYSINH<=TO_DATE('31/12/1993','DD/MM/YYYY'));
ALTER TABLE LYLICH ADD CONSTRAINT CHECK_GT CHECK (GIOITINH IN ('NAM','NU'));
ALTER TABLE LYLICH ADD CONSTRAINT CHECK_SDT CHECK (REGEXP_LIKE(DIENTHOAI,'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'));
ALTER TABLE LYLICH ADD CONSTRAINT CHECK_NNT CHECK ((NGAYNHAPTRUONG)>=TO_DATE('01/01/2010','DD/MM/YYYY'));
ALTER TABLE LYLICH ADD CONSTRAINT CHECK_NRT CHECK (NGAYRATRUONG>NGAYNHAPTRUONG);
--HocTap--
CREATE TABLE HOCTAP
(
MASV NUMBER CONSTRAINT MASVIEN_PK PRIMARY KEY,
MALOP VARCHAR2(20) NOT NULL,
DIEMTB1 NUMBER NOT NULL,
DIEMTB2 NUMBER NOT NULL,
DIEMTB3 NUMBER,
DIEMTB4 NUMBER
);
ALTER TABLE HOCTAP ADD CONSTRAINT CHECH_DTB1 CHECK (DIEMTB1>=0 AND DIEMTB1<=9.99);
ALTER TABLE HOCTAP ADD CONSTRAINT CHECH_DTB2 CHECK (DIEMTB2>=0 AND DIEMTB2<=9.99);
ALTER TABLE HOCTAP ADD CONSTRAINT CHECH_DTB3 CHECK (DIEMTB3>=0 AND DIEMTB3<=9.99);
ALTER TABLE HOCTAP ADD CONSTRAINT CHECH_DTB4 CHECK (DIEMTB4>=0 AND DIEMTB4<=9.99);
--2--
INSERT INTO LYLICH(MASV,TENSV,NGAYSINH,QUEQUAN,GIOITINH,DIENTHOAI,NGAYNHAPTRUONG,NGAYRATRUONG)
VALUES ('144040098','Thach Quang Thai',to_date('09/10/1993','dd/mm/yyyy'),'Ha Noi','NAM','01652545558',to_date('14/9/2011','dd/mm/yyyy'),'');
INSERT INTO LYLICH(MASV,tensv,NGAYSINH,QUEQUAN,GIOITINH,DIENTHOAI,NGAYNHAPTRUONG,NGAYRATRUONG)
VALUES ('144040008','Thach Quang Thang',to_date('14/3/1992','dd/mm/yyyy'),'Ha Nam','NU','0151555551',to_date('13/9/2011','dd/mm/yyyy'),to_date('14/9/2014','dd/mm/yyyy'));
INSERT INTO LYLICH(MASV,TENSV,NGAYSINH,QUEQUAN,GIOITINH,DIENTHOAI,NGAYNHAPTRUONG,NGAYRATRUONG)
VALUES ('144040198','Thach Quang Hung',to_date('14/9/1990','dd/mm/yYyy'),'Quang Ninh','NAM','01651545554',to_date('14/6/2011','dd/mm/yyyy'),'');
INSERT INTO LYLICH(MASV,TENSV,NGAYSINH,QUEQUAN,GIOITINH,DIENTHOAI,NGAYNHAPTRUONG,NGAYRATRUONG)
VALUES ('144040398','Thach Quang Cuong',to_date('11/4/1992','dd/mm/yyyY'),'Hai Duong','NU','0165545553',to_date('14/2/2011','dd/mm/yyyy'),to_date('14/6/2015','dd/mm/yyyy'));
INSERT INTO LYLICH(MASV,TENSV,NGAYSINH,QUEQUAN,GIOITINH,DIENTHOAI,NGAYNHAPTRUONG,NGAYRATRUONG)
VALUES ('144040698','Thach Quang Tung',to_date('4/9/1991','dd/mm/yyyy'),'Hai Phong','NAM','0165545551',to_date('14/1/2011','dd/mm/yyyy'),to_date('11/3/2016','dd/mm/yyyy'));
--SELECT * FROM LYLICH;
INSERT INTO HOCTAP(MASV,MALOP,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4)
VALUES ('144040098','K14HTTTA','8.21','7.92','','');
INSERT INTO HOCTAP(MASV,MALOP,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4)
VALUES ('144040008','K14HTTTA','5.21','8.92','6.72','7.64');
INSERT INTO HOCTAP(MASV,MALOP,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4)
VALUES ('144040198','K14HTTTB','8.21','7.92','6.12','');
INSERT INTO HOCTAP(MASV,MALOP,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4)
VALUES ('144040398','K14HTTTA','8.11','7.92','5.18','6.28');
INSERT INTO HOCTAP(MASV,MALOP,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4)
VALUES ('144040698','K14HTTTB','8.91','6.92','7.15','4.71');
--SELECT * FROM HOCTAP;
--3--
SELECT TENSV,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV;
--4--
SELECT TENSV,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB,
(CASE
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=8 THEN 'GIOI'
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=6.5 THEN 'KHA'
ELSE 'YEU'
END
) AS XEPLOAI
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV;
--5--
SELECT QUEQUAN,COUNT(MASV) AS SL
FROM LYLICH
GROUP BY QUEQUAN;
--6--
SELECT COUNT(MASV) AS SL
FROM LYLICH
WHERE NGAYRATRUONG IS NOT NULL;
--7--
SELECT COUNT(MASV) AS SL
FROM LYLICH
WHERE NGAYRATRUONG IS NULL;
--8--
SELECT TENSV,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV
WHERE ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)
=(SELECT MAX(DTB) FROM (
SELECT TENSV,DIEMTB1,DIEMTB2,DIEMTB3,DIEMTB4,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV)
);
--9--
SELECT TENSV,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB,
(CASE
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=8 THEN 'GIOI'
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=6.5 THEN 'KHA'
ELSE 'YEU'
END
) AS XEPLOAI
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV
WHERE NGAYRATRUONG IS NOT NULL AND NGAYRATRUONG-NGAYNHAPTRUONG>4*365;
--10--
SELECT MALOP,COUNT(MASV) AS SLSV, MAX(ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)) AS DTB
FROM HOCTAP
GROUP BY MALOP;
--11--
SELECT TENSV,MALOP,DIEMTB1,DIEMTB2
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV
WHERE DIEMTB3 IS NULL AND DIEMTB4 IS NULL;
--12--
SELECT COUNT(MASV) AS SLSV
FROM HOCTAP
WHERE DIEMTB3 IS NULL AND DIEMTB4 IS NULL AND DIEMTB2 IS NULL;
--13--
SELECT *
FROM (SELECT LYLICH.MASV,TENSV,LYLICH.QUEQUAN,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB,
(CASE
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=8 THEN 'GIOI'
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=6.5 THEN 'KHA'
ELSE 'YEU'
END
) AS XEPLOAI
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV)
WHERE XEPLOAI = 'GIOI' AND QUEQUAN = 'Ha Noi';
--14--
SELECT COUNT(MASV) AS SLSV
FROM (SELECT LYLICH.MASV,TENSV,LYLICH.QUEQUAN,ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2) AS DTB,
(CASE
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=8 THEN 'GIOI'
WHEN ROUND((DECODE(DIEMTB1,NULL,0,DIEMTB1)+DECODE(DIEMTB2,NULL,0,DIEMTB2)+DECODE(DIEMTB3,NULL,0,DIEMTB3)+DECODE(DIEMTB4,NULL,0,DIEMTB4))/(4-DECODE(DIEMTB1,NULL,1,0)-DECODE(DIEMTB2,NULL,1,0)-DECODE(DIEMTB3,NULL,1,0)-DECODE(DIEMTB4,NULL,1,0)),2)>=6.5 THEN 'KHA'
ELSE 'YEU'
END
) AS XEPLOAI
FROM LYLICH JOIN HOCTAP ON LYLICH.MASV = HOCTAP.MASV
WHERE DIEMTB4 IS NOT NULL)
WHERE XEPLOAI ='YEU';