taenycp_8990- Senior Member
- Giới tính :
Bài gửi : 37
Tổng Điểm : 87
Điểm Thưởng : 4
Sinh Nhật : 05/06/1990 Bị Dụ Dỗ : 11/09/2009
Tuổi : 34
Nơi Xuất Phát : Củ Chuối
by taenycp_8990 3/12/2009, 15:15
- Code:
select * from dean
select * from PHONGBAN
--32
SELECT MAPB
FROM DEAN
WHERE MAPB NOT IN (
SELECT MAPB
FROM DEAN
WHERE DDIEM_DA <> 'HA NOI'
)
--28
SELECT NV.MANV, PC.MADA, DDIEM_DA
FROM NHANVIEN NV, PHANCONG PC, DEAN DA
WHERE NV.MANV=PC.MANV AND
PC.MADA=DA.MADA AND
DDIEM_DA NOT IN (
SELECT DIADIEM
FROM DIADIEMPB DDPB
WHERE DDPB.MAPB=NV.MAPB
)
--34
SELECT TENPB, 'TENTP'=TP.TENNV, 'SLNU'=COUNT(NV.MANV)
FROM NHANVIEN NV, PHONGBAN PB, NHANVIEN TP
WHERE NV.PHAI='NU' AND
NV.MAPB=PB.MAPB AND
PB.TRUONGPHONG=TP.MANV
GROUP BY PB.MAPB, TENPB, TP.TENNV
HAVING COUNT(NV.MANV) >= ALL(
SELECT COUNT(MANV)
FROM NHANVIEN
WHERE PHAI='NU'
GROUP BY MAPB
)
--36
SELECT TENDA, TENNV, THOIGIAN
FROM NHANVIEN NV, PHANCONG PC, DEAN DA
WHERE NV.MANV=PC.MANV AND
PC.MADA=DA.MADA AND
THOIGIAN = (
SELECT MAX(THOIGIAN)
FROM PHANCONG
WHERE MADA=PC.MADA
)
SELECT * FROM DEAN
SELECT MANV, 'SLDA'=COUNT(MADA)
FROM PHANCONG
GROUP BY MANV
HAVING COUNT(MADA) = (
SELECT COUNT(MADA)
FROM DEAN
)
SELECT *
FROM NHANVIEN A
WHERE NOT EXISTS (
SELECT *
FROM DEAN C
WHERE MAPB='P1' AND
MADA NOT IN (
SELECT MADA
FROM PHANCONG B
WHERE B.MANV=A.MANV
)
)
--[url=http://cntt.1forum.biz/] [b] ALL
--CHO BIET CAC NHAN VIEN LAM VIEC ALL DE AN CUA PHONG P1
SP_TABLES
SELECT * FROM GIANGDAY
SELECT * FROM KETQUA
CREATE VIEW VKQHT AS
SELECT MSSV, MAMH, 'DLN'=MAX(DIEM)
FROM KETQUA KQ, GIANGDAY GD
WHERE KQ.MAKHOAHOC=GD.MAKHOAHOC
GROUP BY MSSV, MAMH
ORDER BY MSSV
SELECT SV.MSSV, TEN, 'SLM'=COUNT(MAMH), 'DTB'=AVG(DLN)
FROM VKQHT V, SINHVIEN SV
WHERE V.MSSV=SV.MSSV
GROUP BY SV.MSSV, TEN
--37
SELECT MSSV
FROM VKQHT
WHERE MAMH='CSDL' AND
MSSV IN (
SELECT MSSV
FROM VKQHT
WHERE MAMH='CTDL'
)
SELECT SV.MSSV, TEN, MAMH, DLN
FROM SINHVIEN SV, VKQHT V
WHERE SV.MSSV=V.MSSV AND
(SV.MSSV IN (
SELECT MSSV
FROM VKQHT
WHERE (MAMH='CSDL' OR MAMH='CTDL')
GROUP BY MSSV
HAVING COUNT(MAMH)=2
) OR
SV.MSSV IN (
SELECT MSSV
FROM VKQHT
WHERE (MAMH='CSDL' OR MAMH='CTDL') AND
DLN >= 8
))
ORDER BY SV.MSSV
SELECT * FROM SINHVIEN
ALTER TABLE SINHVIEN DROP COLUMN SLMON, DTB, XL
ALTER TABLE SINHVIEN ADD SLMON TINYINT, DTB FLOAT, XL NVARCHAR(20)
UPDATE SINHVIEN
SET SLMON = (SELECT COUNT(MAMH) FROM VKQHT WHERE MSSV=SINHVIEN.MSSV),
DTB = (SELECT AVG(DLN) FROM VKQHT WHERE MSSV=SINHVIEN.MSSV)
UPDATE SINHVIEN
SET XL = CASE
WHEN DTB<5 THEN N'YẾU'
WHEN DTB<6.5 THEN N'TB'
WHEN DTB<8 THEN N'KHÁ'
WHEN DTB<9 THEN N'GIỎI'
ELSE N'XUẤT SẮC'
END
DELETE FROM KHOA
WHERE MAKHOA NOT IN (SELECT MAKHOA FROM SINHVIEN)
CREATE VIEW VD5 AS
SELECT MSSV, 'DTB'=AVG(DLN)
INTO TAM
FROM VKQHT V
GROUP BY MSSV
HAVING AVG(DLN)<5
DELETE FROM SINHVIEN WHERE MSSV IN (
SELECT MSSV FROM VD5)
DELETE FROM KETQUA WHERE MSSV IN (
SELECT MSSV FROM VD5)
SELECT * FROM KETQUA ORDER BY MSSV
-- CHINH SUA KHOA NGOAI
ALTER TABLE KETQUA ADD CONSTRAINT FK_KQ_SV FOREIGN KEY (MSSV)
REFERENCES SINHVIEN(MSSV) ON DELETE CASCADE
SP_FKEYS SINHVIEN
-- TAM THOI DISABLE KHOA NGOAI
ALTER TABLE KETQUA NOCHECK CONSTRAINT PK_KQ_SV
GO
UPDATE KETQUA SET MSSV='SV010' WHERE MSSV='SV004'
UPDATE SINHVIEN SET MSSV='SV010' WHERE MSSV='SV004'
GO
ALTER TABLE KETQUA CHECK CONSTRAINT PK_KQ_SV
[/b][/url]
