Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
TUAN 6 QUANLYDETAI
CREATE TABLE GIAOVIEN( MAGV NCHAR(10), HOTEN NVARCHAR(30), LUONG INT, PHAI NCHAR(3), NGSINH DATETIME, DIACHI NVARCHAR(50), GVQLCM NCHAR(10), MABM NCHAR(10), PRIMARY KEY(MAGV) ); CREATE TABLE KHOA( MAKHOA NCHAR(10), TENKHOA NVARCHAR(30), NAMTL INT, PHONG NCHAR(10), DIENTHOAI INT, TRUONGKHOA NCHAR(10), NGAYNHAMCHUC DATETIME, PRIMARY KEY (MAKHOA) ); CREATE TABLE BOMON( MABM NCHAR(10), TENBM NVARCHAR(30), PHONG NCHAR(10), DIENTHOAI INT, TRUONGBM NCHAR(10), MAKHOA NCHAR(10), NGAYNHAMCHUC DATETIME, PRIMARY KEY (MABM) ); CREATE TABLE GV_DT( MAGV NCHAR(10), DIENTHOAI INT, PRIMARY KEY(MAGV, DIENTHOAI) ); CREATE TABLE NGUOITHAN( MAGV NCHAR(10), TEN NVARCHAR(30), NGSINH DATETIME, PHAI NCHAR(3), PRIMARY KEY (MAGV, TEN) ); CREATE TABLE CHUDE( MACD NCHAR(10), TENCD NVARCHAR(30), PRIMARY KEY(MACD) ); CREATE TABLE DETAI( MADT NCHAR(10), TENDT NVARCHAR(50), CAPQL NVARCHAR(20), KINHPHI INT, NGAYBD DATETIME, NGAYKT DATETIME, MACD NCHAR(10), GVCNDT NCHAR(10), PRIMARY KEY(MADT) ); CREATE TABLE CONGVIEC( MADT NCHAR(10), SOTT INT, TENCV NVARCHAR(40), NGAYBD DATETIME, NGAYKT DATETIME, PRIMARY KEY(MADT, SOTT) ); CREATE TABLE THAMGIADT( MAGV NCHAR(10), MADT NCHAR(10), SOTT INT, PHUCAP INT, KETQUA NVARCHAR(20), PRIMARY KEY(MAGV, MADT, SOTT) ); ALTER TABLE BOMON ADD CONSTRAINT FK_BOMON_KHOA FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA) ALTER TABLE BOMON ADD CONSTRAINT FK_BOMON_GIAOVIEN FOREIGN KEY (TRUONGBM) REFERENCES GIAOVIEN(MAGV) ALTER TABLE GV_DT ADD CONSTRAINT FK_GV_DT_GIAOVIEN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV) ALTER TABLE NGUOITHAN ADD CONSTRAINT FK_NGUOITHAN_GIAOVIEN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV) ALTER TABLE KHOA ADD CONSTRAINT FK_KHOA_GIAOVIEN FOREIGN KEY (TRUONGKHOA) REFERENCES GIAOVIEN(MAGV) ALTER TABLE THAMGIADT ADD CONSTRAINT FK_THAMGIADT_GIAOVIEN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV) ALTER TABLE THAMGIADT ADD CONSTRAINT FK_THAMGIADT_CONGVIEC FOREIGN KEY (MADT, SOTT) REFERENCES CONGVIEC(MADT, SOTT) ALTER TABLE CONGVIEC ADD CONSTRAINT FK_CONGIVEC_DETAI FOREIGN KEY (MADT) REFERENCES DETAI(MADT) ALTER TABLE DETAI ADD CONSTRAINT FK_DETAI_CHUDE FOREIGN KEY (MACD) REFERENCES CHUDE(MACD) ALTER TABLE DETAI ADD CONSTRAINT FK_DETAI_GIAOVIEN FOREIGN KEY (GVCNDT) REFERENCES GIAOVIEN(MAGV) ALTER TABLE GIAOVIEN ADD CONSTRAINT FK_GIAOVIEN_GIAOVIEN FOREIGN KEY (GVQLCM) REFERENCES GIAOVIEN(MAGV) ALTER TABLE GIAOVIEN ADD CONSTRAINT FK_GIAOVIEN_BOMON FOREIGN KEY (MABM) REFERENCES BOMON(MABM) INSERT INTO GIAOVIEN VALUES ('001', 'NGUYEN HOAI AN', '2000', 'NAM', '2/15/1973', 'TP HCM', NULL, NULL), ('002', 'TRAN TRA HUONG', '2500', 'NU', '6/20/1960', 'TP HCM', NULL, NULL), ('003', 'NGUYEN NGOC ANH','2200','NU','5/11/1975','TP HCM', '002', NULL), ('004', 'TRUONG NAM SON', '2300', 'NAM', '6/20/1959', 'BIEN HOA', NULL, NULL), ('005', 'LY HOANG HA', '2500', 'NAM', '10/23/1954', 'TP HCM', NULL, NULL) INSERT INTO KHOA VALUES ('CNTT', 'CONG NGHE THONG TIN', '1995', 'B11', '0838123456', '002', '2/20/2005'), ('HH', 'HOA HOC', '1980', 'B41', '0838456456', '001', '10/15/2001'), ('SH', 'SINH HOC', '1980', 'B31', '083845454', '004', '10/11/2000'), ('VL', 'VAT LY', '1976', 'B21', '0838223223', '005', '9/18/2003') INSERT INTO BOMON VALUES ('CNTT', 'CONG NGHE TRI THUC', 'B15', '0838126126', NULL, 'CNTT', NULL), ('HHC', 'HOA HUC CO', 'B44', '0838222222', NULL, 'HH', NULL), ('HL', 'HOA LY', 'B42', '0838878787', NULL, 'HH', NULL), ('HPT', 'HOA PHAN TICH', 'B43', '0838777777', '001', 'HH', '10/15/2007'), ('HTTT', 'HE THONG THONG TIN', 'B13', '0838125125', '002', 'CNTT', '9/20/2004') INSERT INTO NGUOITHAN VALUES ('001', 'HUNG', '1/14/1990', 'NAM'), ('001', 'THUY', '12/8/1994', 'NU'), ('003', 'HA', '9/3/1998', 'NU'), ('003', 'THU', '9/3/1998', 'NU') INSERT INTO GV_DT VALUES ('001', '0838912112'), ('001', '0903123123'), ('002', '0913454545'), ('003', '0838121212'), ('003', '0903656565') INSERT INTO CHUDE VALUES ('NCPT', 'NGHIEN CUU PHAT TRIEN'), ('QLGD', 'QUAN LY GIAO DUC'), ('UDCN', 'UNG DUNG CONG NGHE') INSERT INTO DETAI VALUES ('001', 'HTTT QUAN LY CAC TRUONG DAI HOC', 'DHQG', '20', '10/20/2007', '10/20/2008', 'QLGD', '002'), ('002', 'HTTT QUAN LY GIAO VU CHO 1 KHOA', 'TRUONG', '20', '10/12/2000', '10/12/2001', 'QLGD', '002'), ('003', 'NGHIEN CUU CHE TAO SOI NANO PLATIN', 'DHQG', '300', '5/15/2008', '5/15/2010', 'NCPT', '005'), ('004', 'TAO VAT LIEU SINH HOC BANG MANG OI NGUOI', 'NHA NUOC', '100','1/1/2007', '12/31/2009', 'NCPT', '004'), ('005', 'UNG DUNG HOA HOC XANH', 'TRUONG', '200', '10/10/2003', '12/10/2004', 'UDCN', '001') INSERT INTO CONGVIEC VALUES ('001', '1', 'KHOI TAO VA LAP KE HOACH', '10/20/2007', '12/20/2008'), ('001', '2', 'XAC DINH YEU CAU', '12/21/2008', '3/21/2008'), ('001', '3', 'PHAN TICH HE THONG', '3/22/2008', '5/22/2008'), ('001', '4', 'THIET KE HE THONG', '5/23/2008', '6/23/2008'), ('001', '5', 'CAI DAT THU NGHIEM', '6/24/2008', '10/20/2008'), ('002', '1', 'KHOI TAO VA LAP KE HOACH', '5/10/2009', '7/10/2009'), ('002', '2', 'XAC DINH YEU CAU', '7/11/2009', '10/11/2009'), ('002', '3', 'PHAN TICH HE THONG', '10/12/2009', '12/20/2009'), ('002', '4', 'THIET KE HE THONG', '12/21/2009', '3/22/2010'), ('002', '5', 'CAI DAT THU NGHIEM', '3/23/2010', '5/10/2010') INSERT INTO THAMGIADT VALUES ('001', '002', '1', '0', NULL), ('001', '002', '2', '2', NULL), ('002', '001', '4', '2', 'DAT'), ('003', '001', '1', '1', 'DAT'), ('003', '001', '2', '0', 'DAT') UPDATE GIAOVIEN SET MABM='CNTT' WHERE MAGV='001'; UPDATE GIAOVIEN SET MABM='HTTT' WHERE MAGV='002'; UPDATE GIAOVIEN SET MABM='HTTT' WHERE MAGV='003'; UPDATE GIAOVIEN SET MABM='CNTT' WHERE MAGV='004'; UPDATE GIAOVIEN SET MABM='HL' WHERE MAGV='005'; SELECT DISTINCT LUONG FROM GIAOVIEN WHERE LUONG >= ALL ( SELECT LUONG FROM GIAOVIEN) SELECT DISTINCT * FROM GIAOVIEN WHERE LUONG >= ALL ( SELECT LUONG FROM GIAOVIEN) SELECT DISTINCT LUONG FROM GIAOVIEN WHERE MABM = 'HTTT' AND LUONG >= ALL ( SELECT LUONG FROM GIAOVIEN WHERE MABM = 'HTTT') SELECT * FROM GIAOVIEN WHERE MABM = 'HTTT' AND NGSINH <= ALL ( SELECT NGSINH FROM GIAOVIEN WHERE MABM = 'HTTT') SELECT HOTEN FROM (SELECT GV.* FROM GIAOVIEN GV, BOMON BM, KHOA K WHERE GV.MABM = BM.MABM AND BM.MAKHOA = K.MAKHOA AND K.TENKHOA = 'CONG NGHE THONG TIN') AS GV_K_CNTT WHERE NGSINH >= ALL (SELECT NGSINH FROM GIAOVIEN GV, BOMON BM, KHOA K WHERE GV.MABM = BM.MABM AND BM.MAKHOA = K.MAKHOA AND K.TENKHOA = 'CONG NGHE THONG TIN') SELECT TENKHOA, HOTEN FROM KHOA K, GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND BM.MAKHOA = K.MAKHOA AND GV.LUONG >= ALL (SELECT LUONG FROM GIAOVIEN) SELECT GV.* FROM GIAOVIEN GV WHERE GV.LUONG >= ALL (SELECT LUONG FROM GIAOVIEN GV_SS WHERE GV.MABM = GV_SS.MABM) SELECT DT.* FROM DETAI DT WHERE DT.MADT NOT IN (SELECT MADT FROM THAMGIADT TGDT_W, GIAOVIEN GV_W WHERE TGDT_W.MAGV = GV_W.MAGV AND GV_W.HOTEN = 'NGUYEN HOAI AN') SELECT TENDT, HOTEN FROM DETAI DT, GIAOVIEN GV WHERE DT.MADT NOT IN (SELECT MADT FROM THAMGIADT TGDT_W, GIAOVIEN GV_W WHERE TGDT_W.MAGV = GV_W.MAGV AND GV_W.HOTEN = 'NGUYEN HOAI AN') AND DT.GVCNDT = GV.MAGV SELECT HOTEN, MAGV FROM (SELECT GV_F.* FROM GIAOVIEN GV_F, BOMON BM_F, KHOA K_F WHERE GV_F.MABM = BM_F.MABM AND BM_F.MAKHOA = K_F.MAKHOA) AS GV_K_CNTT WHERE MAGV NOT IN (SELECT MAGV FROM THAMGIADT) SELECT HOTEN, MAGV FROM GIAOVIEN GV WHERE MAGV NOT IN (SELECT MAGV FROM THAMGIADT) SELECT GV.* FROM GIAOVIEN GV WHERE LUONG > ALL (SELECT LUONG FROM GIAOVIEN GV_W WHERE GV_W.HOTEN = 'NGUYEN HOAI AN') SELECT GV_TR_BM.* FROM (SELECT GV_FR.* FROM GIAOVIEN GV_FR, BOMON BM_FR WHERE BM_FR.TRUONGBM = GV_FR.MAGV) AS GV_TR_BM WHERE GV_TR_BM.MAGV IN (SELECT MAGV FROM THAMGIADT) SELECT GV.* FROM GIAOVIEN GV WHERE GV.LUONG > ANY (SELECT LUONG FROM GIAOVIEN GV_W, BOMON BM_W, KHOA K_W WHERE GV_W.MABM = BM_W.MABM AND BM_W.TENBM = 'CONG NGHE PHAN MEM') SELECT GV.HOTEN, GV.LUONG FROM GIAOVIEN GV WHERE GV.LUONG > ALL(SELECT DISTINCT GV_W.LUONG FROM GIAOVIEN GV_W, BOMON BM_W WHERE GV_W.MABM = BM_W.MABM AND BM_W.TENBM = 'HE THONG THONG TIN') SELECT TOP 1 K_BM_GV.TENKHOA, K_BM_GV.TONGSOGV FROM (SELECT K.TENKHOA, SUM(TONGSOGV) AS TONGSOGV FROM KHOA K, (SELECT BM1.MAKHOA, BM1.MABM, COUNT(*) AS TONGSOGV FROM BOMON BM1, GIAOVIEN GV1 WHERE BM1.MABM = GV1.MABM GROUP BY BM1.MAKHOA, BM1.MABM ) AS BM_GV WHERE K.MAKHOA = BM_GV.MAKHOA GROUP BY BM_GV.MAKHOA, K.TENKHOA) AS K_BM_GV ORDER BY TONGSOGV DESC SELECT TOP 1 HOTEN FROM (SELECT GV.HOTEN, DT.GVCNDT, COUNT(*) AS TSDT FROM GIAOVIEN GV, DETAI DT WHERE GV.MAGV = DT.GVCNDT GROUP BY GV.HOTEN, DT.GVCNDT) GV_DT ORDER BY GV_DT.TSDT DESC SELECT TOP 1 MABM FROM (SELECT MABM, COUNT(*) AS TSGV FROM GIAOVIEN GROUP BY MABM) BM_GV ORDER BY TSGV DESC SELECT TOP 1 HOTEN, MABM FROM (SELECT GV.HOTEN, GV.MABM, TG.MAGV, COUNT(*) AS TSDT FROM GIAOVIEN GV, THAMGIADT TG WHERE GV.MAGV = TG.MAGV GROUP BY GV.HOTEN, GV.MABM, TG.MAGV) AS GV_TGDT ORDER BY TSDT DESC SELECT TOP 1 HOTEN, MABM FROM (SELECT GV.HOTEN, GV.MABM, TG.MAGV, COUNT(*) AS TSDT FROM GIAOVIEN GV, THAMGIADT TG WHERE GV.MAGV = TG.MAGV AND GV.MABM = 'HTTT' GROUP BY GV.HOTEN, GV.MABM, TG.MAGV) AS GV_TGDT ORDER BY TSDT DESC SELECT TOP 1 HOTEN, MABM FROM (SELECT GV.HOTEN, GV.MABM, COUNT(*) AS TSNT FROM NGUOITHAN NT, GIAOVIEN GV WHERE GV.MAGV = NT.MAGV GROUP BY GV.HOTEN, GV.MABM, GV.MAGV) AS GV_NT ORDER BY TSNT DESC SELECT TOP 1 HOTEN, TSDT FROM (SELECT GV.HOTEN, GV.MAGV, COUNT(*) AS TSDT FROM BOMON BM, GIAOVIEN GV, DETAI DT WHERE BM.TRUONGBM = GV.MAGV AND GV.MAGV = DT.GVCNDT GROUP BY GV.HOTEN, GV.MAGV) AS TBM_DT ORDER BY TSDT DESC
run
|
edit
|
history
|
help
0
Subb7
Sample of Update~Delete with OUTPUT clause (2016 >)
Alquileres
Arif First Query
kirthi
SS my sql server 2014 4/7
non_numeric_table
Window Functions - ROW_NUMBER()
db3
M0413045.sql