code CSDL QL.BOngDA create database QLBONGDA USE QLBongDa create table CAUTHU ( MACT numeric identity not null, HOTEN nvarchar(100) not null, VITRI nvarchar(20) not null, NGAYSINH datetime , DIACHI nvarchar(200) , MACLB varchar(5) not null, MAQG varchar(5) not null, SO int not null constraint pk1 primary key (MACT) ) create table QUOCGIA ( MAQG varchar(5) not null, TENQG nvarchar(60) not null constraint pk2 primary key (MAQG) ) create table CAULACBO ( MACLB varchar(5) not null, TENCLB nvarchar(100) not null, MASAN varchar(5) not null, MATINH varchar(5) not null constraint pk3 primary key (MACLB) ) create table TINH ( MATINH varchar(5) not null, TENTINH nvarchar(100) not null, constraint pk4 primary key (MATINH) ) create table SANVD ( MASAN varchar (5) not null, TENSAN nvarchar(100) not null, DIACHI nvarchar(200) constraint pk5 primary key (MASAN) ) create table HUANLUYENVIEN ( MAHLV varchar(5) not null, TENHLV nvarchar (100) not null, NGAYSINH datetime, DIACHI nvarchar(200), DIENTHOAI nvarchar(20), MAQG varchar(5) not null constraint pk6 primary key (MAHLV) ) create table HLV_CLB ( MAHLV varchar(5) not null, MACLB varchar(5) not null, VAITRO nvarchar(100) not null, constraint pk7 primary key (MAHLV,MACLB) ) create table TRANDAU ( MATRAN numeric identity not null, NAM int not null, VONG int not null, NGAYTD datetime not null, MACLB1 varchar(5) not null, MACLB2 varchar(5) not null, MASAN varchar(5) not null, KETQUA varchar(5) not null constraint pk8 primary key (MATRAN) ) create table BANGXH ( MACLB varchar(5) not null, NAM int not null, VONG int not null, SOTRAN int not null, THANG int not null, HOA int not null, THUA int not null, HIEUSO varchar(5) not null, DIEM int not null, HANG int not null constraint pk9 primary key (MACLB,NAM,VONG) ) alter table HUANLUYENVIEN add constraint fk1 foreign key (MAQG) references QUOCGIA alter table HLV_CLB add constraint fk2 foreign key (MAHLV) references HUANLUYENVIEN alter table HLV_CLB add constraint fk3 foreign key (MACLB) references CAULACBO alter table CAUTHU add constraint fk4 foreign key (MAQG) references QUOCGIA alter table CAUTHU add constraint fk5 foreign key (MACLB) references CAULACBO alter table CAULACBO add constraint fk6 foreign key (MATINH) references TINH alter table TRANDAU add constraint fk7 foreign key (MASAN) references SANVD alter table CAULACBO add constraint fk8 foreign key (MASAN) references SANVD alter table TRANDAU add constraint fk9 foreign key (MACLB1) references CAULACBO alter table TRANDAU add constraint fk10 foreign key (MACLB2) references CAULACBO alter table BANGXH add constraint fk11 foreign key (MACLB) references CAULACBO Insert CAUTHU values (NNguyễn Vũ Phong,NTiền Vệ,02/20/1990,NULL,BBD,VN,17) Insert CAUTHU values (NNguyễn Công Vinh,NTiền Đạo,03/10/1992,NULL,HAGL,VN,9) Insert CAUTHU values (NTrần Tấn Tài,NTiền Vệ,11/12/1989,NULL,BBD,VN,8) Insert CAUTHU values (NPhan Hồng Sơn,NThủ Môn,06/10/1991,NULL,HAGL,VN,1) Insert CAUTHU values (NRonaldo,NTiền Vệ,12/12/1989,NULL,SDN,BRA,7) Insert CAUTHU values (Robinho,NTiền Vệ,10/12/1989,NULL,SDN,BRA,8) Insert CAUTHU values (Vidic,NHậu Vệ,10/15/1987,NULL,HAGL,ANH,3) Insert CAUTHU values (NTrần Văn Santos,NThủ Môn,10/21/1990,NULL,BBD,BRA,1) Insert CAUTHU values (NNguyễn Trường Sơn,NHậu Vệ,8/26/1993,NULL,BBD,VN,4) Insert QUOCGIA values (VN,NViệt Nam) Insert QUOCGIA values (ANH,NANH QUỐC) Insert QUOCGIA values (TBN,NTây Ban Nha) Insert QUOCGIA values (BDN,NBồ Đào Nha) Insert QUOCGIA values (BRA,NBra-xin) Insert QUOCGIA values (ITA,NÝ) Insert QUOCGIA values (THA,NThái Lan) Insert CAULACBO values (BBD,NBECAMEX BÌNH DƯƠNG,GD,BD) Insert CAULACBO values (HAGL,NHOÀNG ANH GIA LAI,PL,GL) Insert CAULACBO values (SDN,NSHB ĐÀ NẴNG,CL,DN) Insert CAULACBO values (KKH,NKHATOCO KHÁNH HÒA,NT,KH) Insert CAULACBO values (TPY,NTHÉP PHÚ YÊN,TH,PY) Insert CAULACBO values (GDT,NGẠCH ĐỒNG TÂM LONG AN,LA,LA) INSERT TINH VALUES (BD,NBình Dương) INSERT TINH VALUES (GL,NGia Lai) INSERT TINH VALUES (DN,NĐà Nẵng) INSERT TINH VALUES (KH,NKhánh Hòa) INSERT TINH VALUES (PY,NPhú Yên) INSERT TINH VALUES (LA,NLong An) insert SANVD values (GD,NGò Đậu,N123 QL1, TX Thủ Dầu Một , Bình Dương) insert SANVD values (PL,NPleiku,N22 Hồ Tùng Mậu, Thống Nhất, Thị xã Pleiku, Gia Lai) insert SANVD values (CL,NChi Lăng,N127 Võ Văn Tần, Đà Nẵng) insert SANVD values (NT,NNha Trang,N128 Phan Chu Chinh , Nha Trang , Khánh Hòa) insert SANVD values (TH,NTuy Hòa,N57 Trường Chinh, Tuy Hòa,Phú Yên) insert SANVD values (LA,NLong An,N102 Hùng Vương, Tp Tân An, Long An) insert HUANLUYENVIEN values (HLV01,Vital,10/15/1955,NULL,0918011075,BDN) insert HUANLUYENVIEN values (HLV02,NLê Huỳnh Đức,05/20/1972,NULL,01223456789,VN) insert HUANLUYENVIEN values (HLV03,Kiatisuk,12/11/1970,NULL,01990123456,THA) insert HUANLUYENVIEN values (HLV04,NHoành Anh Tuấn,06/10/1970,null,0989112233,VN) insert HUANLUYENVIEN values (HLV05,NTrần Công Minh,07/07/1973,NULL,0909099990,VN) insert HUANLUYENVIEN values (HLV06,NTrần Văn Phúc,03/02/1965,NULL,01650101234,VN) insert HLV_CLB values (HLV01,BBD,NHLV Chính) insert HLV_CLB values (HLV02,SDN,NHLV Chính) insert HLV_CLB values (HLV03,HAGL,NHLV Chính) insert HLV_CLB values (HLV04,KKH,NHLV Chính) insert HLV_CLB values (HLV05,GDT,NHLV Chính) insert HLV_CLB values (HLV06,BBD,NHLV thủ môn) insert TRANDAU values (2009,1,2/7/2009,BBD,SDN,GD,3-0) insert TRANDAU values (2009,1,2/7/2009,KKH,GDT,NT,1-1) insert TRANDAU values (2009,2,2/16/2009,SDN,KKH,CL,2-2) insert TRANDAU values (2009,2,2/16/2009,TPY,BBD,TH,5-0) insert TRANDAU values (2009,3,3/1/2009,TPY,GDT,TH,0-2) insert TRANDAU values (2009,3,3/1/2009,KKH,BBD,NT,0-1) insert TRANDAU values (2009,4,3/7/2009,KKH,TPY,NT,1-0) insert TRANDAU values (2009,4,3/7/2009,BBD,GDT,GD,2-2) insert BANGXH values (BBD,2009,1,1,1,0,0,3-0,3,1) insert BANGXH values (KKH,2009,1,1,0,1,0,1-1,1,2) insert BANGXH values (GDT,2009,1,1,1,0,0,3-0,1,3) insert BANGXH values (TPY,2009,1,0,0,0,0,0-0,0,4) insert BANGXH values (SDN,2009,1,1,0,0,1,0-3,0,5) insert BANGXH values (TPY,2009,2,1,1,0,0,5-0,3,1) insert BANGXH values (BBD,2009,2,2,1,0,1,3-5,3,2) insert BANGXH values (KKH,2009,2,2,0,2,0,3-3,2,3) insert BANGXH values (GDT,2009,2,1,0,1,0,1-1,1,4) insert BANGXH values (SDN,2009,2,2,1,1,0,2-5,1,5) insert BANGXH values (BBD,2009,3,3,2,0,1,4-5,6,1) insert BANGXH values (GDT,2009,3,2,1,1,0,3-1,4,2) insert BANGXH values (TPY,2009,3,2,1,0,1,5-2,3,3) insert BANGXH values (KKH,2009,3,3,0,2,1,3-4,2,4) insert BANGXH values (SDN,2009,3,2,1,1,0,2-5,1,5) insert BANGXH values (BBD,2009,4,4,2,1,1,6-7,7,1) insert BANGXH values (GDT,2009,4,3,1,2,0,5-1,5,2) insert BANGXH values (KKH,2009,4,4,1,2,1,4-4,5,3) insert BANGXH values (TPY,2009,4,3,1,0,2,5-3,3,4) insert BANGXH values (SDN,2009,4,2,1,1,0,2-5,1,5) USE QLBONGDA --a. --1. SELECT MACT,HOTEN,VITRI,NGAYSINH,DIACHI FROM CAUTHU --2. SELECT * FROM CAUTHU WHERE SO=7 AND VITRI = NTiền vệ --3. SELECT TENHLV,NGAYSINH,DIACHI,DIENTHOAI FROM HUANLUYENVIEN --4. SELECT * FROM CAUTHU WHERE MACLB = (SELECT MACLB FROM CAULACBO WHERE TENCLB = NBecamex Bình Dương) and MAQG = (SELECT MAQG FROM QUOCGIA WHERE TENQG=NViệt Nam) --5. SELECT * FROM CAUTHU WHERE MACLB = (SELECT MACLB FROM CAULACBO WHERE TENCLB = NSHB Đà Nẵng) and MAQG = (SELECT MAQG FROM QUOCGIA WHERE TENQG=NBra-xin) --6. SELECT * FROM CAUTHU WHERE MACLB = (SELECT MACLB FROM CAULACBO WHERE MASAN = (SELECT MASAN FROM SANVD WHERE TENSAN = Long An)) --7. SELECT MATRAN,NGAYTD,(SELECT TENCLB FROM CAULACBO WHERE MACLB1=CAULACBO.MACLB) AS TENCLB1,(SELECT TENCLB FROM CAULACBO WHERE MACLB2=CAULACBO.MACLB) AS TENCLB2,KETQUA FROM TRANDAU,SANVD WHERE TRANDAU.MASAN=SANVD.MASAN AND VONG = 2 AND NAM =2009 --8. SELECT * FROM HUANLUYENVIEN WHERE MAQG = (SELECT MAQG FROM QUOCGIA WHERE TENQG=NViệt Nam) --9. SELECT TOP(3)TENCLB,(SELECT TOP(3) DIEM) AS Điểm FROM CAULACBO,BANGXH WHERE VONG=3 AND CAULACBO.MACLB=BANGXH.MACLB ORDER BY DIEM DESC --10 SELECT TENHLV,NGAYSINH,DIACHI,VAITRO,TENCLB FROM HUANLUYENVIEN A,HLV_CLB B,CAULACBO C WHERE (A.MAHLV=B.MAHLV AND B.MACLB=C.MACLB) AND MATINH = (SELECT MATINH FROM TINH WHERE TENTINH=NBình Dương) --b. --1. SELECT COUNT(CAUTHU.MACLB) AS Số Lượng,CAULACBO.MACLB FROM CAUTHU,CAULACBO WHERE CAUTHU.MACLB=CAULACBO.MACLB GROUP BY CAULACBO.MACLB --2. SELECT COUNT(CAUTHU.MACT) AS Số Lượng, CAULACBO.MACLB FROM CAUTHU,CAULACBO WHERE CAUTHU.MACLB=CAULACBO.MACLB AND MAQG = VN GROUP BY CAULACBO.MACLB --3. SELECT COUNT(CAUTHU.MACLB) AS Số Lượng,CAULACBO.MACLB FROM CAUTHU,CAULACBO WHERE (CAUTHU.MACLB=CAULACBO.MACLB) and MAQG !=VN GROUP BY CAULACBO.MACLB --4. SELECT COUNT(A.MACT) as Số Lượng,TENTINH FROM CAUTHU A , CAULACBO B ,TINH C WHERE (A.MACLB=B.MACLB AND B.MATINH=C.MATINH) AND VITRI = NTiền Đạo GROUP BY B.MACLB,TENTINH --5. SELECT TENCLB FROM BANGXH , CAULACBO WHERE BANGXH.MACLB=CAULACBO.MACLB AND DIEM = (SELECT MAX(DIEM) FROM BANGXH WHERE VONG = 3) AND VONG = 3 --c. --1. SELECT * FROM HUANLUYENVIEN A , HLV_CLB B , CAULACBO C WHERE (A.MAHLV=B.MAHLV AND B.MACLB=C.MACLB) AND DIENTHOAI IS NULL --2. SELECT MAHLV FROM HUANLUYENVIEN WHERE MAHLV NOT IN (SELECT MAHLV FROM HLV_CLB) AND MAQG = (SELECT MAQG FROM QUOCGIA WHERE TENQG=NViệt Nam) --3. SELECT * FROM CAUTHU WHERE MACLB IN (SELECT MACLB FROM BANGXH WHERE DIEM > 6 OR DIEM ♥) --4. SELECT NGAYTD,TENSAN,(SELECT TENCLB FROM CAULACBO WHERE MACLB1=CAULACBO.MACLB) AS TENCLB1,(SELECT TENCLB FROM CAULACBO WHERE MACLB2=CAULACBO.MACLB) AS TENCLB2,KETQUA FROM TRANDAU,SANVD WHERE (MACLB1 = (SELECT BANGXH.MACLB FROM BANGXH , CAULACBO WHERE BANGXH.MACLB=CAULACBO.MACLB AND DIEM = (SELECT MAX(DIEM) FROM BANGXH WHERE VONG = 3) AND VONG = 3) OR MACLB2 = (SELECT BANGXH.MACLB FROM BANGXH , CAULACBO WHERE BANGXH.MACLB=CAULACBO.MACLB AND DIEM = (SELECT MAX(DIEM) FROM BANGXH WHERE VONG = 3) AND VONG = 3) ) AND SANVD.MASAN=TRANDAU.MASAN SELECT * FROM SANVD SELECT * FROM CAULACBO SELECT * FROM CAUTHU SELECT * FROM BANGXH order by vong SELECT * FROM TRANDAU SELECT * FROM HUANLUYENVIEN SELECT * FROM hlv_clb
Posted on: Tue, 25 Mar 2014 17:29:16 +0000