Cơ bản về sql server 2000

. h. Taïc giaí Haìn Ngoüc Cáøm coï nhæîng truyãûn naìo âaî âæåüc âàng trãn baïo / taûp chê? Cho biãút tæûa cuía chuïng. i. Cho biãút säú læåüng baìi baïo maî taûp chê Kiãún thæïc ngaìy nay âaî âàng trãn táút caí caïc säú baïo. j. Cho biãút thãø loaûi cuía caïc baìi baïo do caïc taïc giaí coï hoü Lã saïng taïc. k. Tçm tæûa caïc baìi thå âaî âæåüc âàng trãn caïc baïo/taûp chê trong tuáön lãù tæì 23/4/95 âãún 30/4/95. l. Tçm tãn caïc taïc giaí âæåüc âàng baìi trãn baïo Cæûu Chiãún Binh. m. Tçm tãn nhæîng baïo/taûp chê coï âang xaî luáûn. In ra tãn caïc baìi xaî luáûn âoï vaì caïc säú baïo/taûp chê tæång æïng. Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 19 BAÌI 2 I. MUÛC ÂÊCH Luyãûn táûp cáu lãûnh SELECT läöng nhau âãø traí låìi cho caïc cáu hoíi cáön sæí duûng pheïp giao hoàûc pheïp træì. Ngoaìi ra, coìn luyãûn táûp khaí nàng váûn duûng caïc haìm kãút táûp max, min, sum, count, avg. II. LYÏ THUYÃÚT Cáu lãûnh SQL sæí duûng trong baìi naìy: SELECT FROM WHERE ( SELECT FROM [WHERE ] ) [AND | OR ( SELECT FROM [WHERE ] ) [ ... ] ] * Điều kiện chứa select con có các dạng sau đây: Dạng 1. (): ĐK đúng khi giá trị của cột so sánh đúng với giá trị trả về từ select con. Dạng 2. ALL (): ĐK đúng khi giá trị của cột so sánh đúng với tất cả các giá trị trả về từ select con. Dạng 3. ANY|SOME (): ĐK đúng khi giá trị của cột so sánh đúng với bất kỳ một giá trị nào trả về từ select con. Dạng 4. [NOT] IN (): ĐK đúng khi giá trị của cột nằm trong tập hợp các giá trị trả về của select con. Dạng 5. [NOT] EXISTS (): Đk đúng khi kết quả trả về của select con khác rỗng. Các dạng này phải được lựa chọn sử dụng hợp lệ tùy theo kết quả trả về của select con như sau: Select con trả về: Một cột Nhiều cột Một dòng Dạng 1 Nhiều dòng Dạng 2, 3, 4 Dạng 5 Læu yï: - Cáu lãûnh SELECT trong SQL Server 2000 cho pheïp läöng âãún täúi âa laì 32 mæïc. - Mãûnh âãö HAVING coï thãø chæïa SELECT con. Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 20 III. BAÌI TÁÛP COÏ HÆÅÏNG DÁÙN: 1. CSDL: Quaín lyï cäng trçnh (CSDL1) Baíng 1. KTRUCSU(HOTEN_KTS, NAMS_KTS, PHAI, NOI_TN, DCHI_LL_KT) Baíng 2. CHUTHAU(TEN_THAU, TEL, DCHI_THAU) Baíng 3. CHUNHAN(TEN_CHU, DCHI_CHU) Baíng 4. CONGNHAN(HOTEN_CN, NAMS_CN, NAM_VAO_N, CH_MON) Baíng 5. CGTRINH(STT_CTR, TEN_CTR, DCHI_CTR, TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, NGAY_BD) Baíng 6. THAMGIA(HOTEN_CN, STT_CTR, NGAY_TGIA, SO_NGAY) Baíng 7. THIETKE(HOTEN_KTS, STT_CTR, THU_LAO) Tham khaío trang 12 âãø xem diãùn giaíi cho tæìng baíng. 2. Yãu cáöu 1: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau: a. Cho biãút hoü tãn caïc kiãún truïc sæ væìa thiãút kãú caïc cäng trçnh do Phoìng Dëch vuû Såí Xáy dæûng thi cäng, væìa thiãút kãú caïc cäng trçnh do chuí tháöu Lã Vàn Sån thi cäng. Phán têch cáu hoíi: Coï thãø minh hoüa cáu hoíi naìy bàòng hçnh aính giao nhau cuía hai táûp sau: Hoü tãn caïc KTS coï thiãút kãú ctr do chuí tháöu ‘Le Van Son’ thi cäng Hoü tãn caïc KTS coï thiãút kãú ctr do chuí tháöu ‘Phoìng dëch vuû so xd’ thi cäng Vuìng giao nhau giæîa hai táûp chênh laì vuìng kãút quaí cáön tçm Váûy ta phaíi tçm hai táûp håüp naìy bàòng hai cáu lãûnh SELECT vaì giao chuïng laûi våïi nhau. Tuy nhiãn, vç pheïp giao khäng âæåüc häø tråü træûc tiãúp nãn ta sæí duûng daûng cáu SELECT läöng nhau trong âoï sæí duûng tæì khoïa IN âãø kãút näúi select cha vaì select con våïi nhau (tæïc laì daûng âiãöu kiãûn chæïa select con säú 4 âaî âãö cáûp åí trãn). Læu yï: Caïch nháûn biãút daûng cáu hoíi sæí duûng pheïp GIAO âãø traí låìi: Yãu cáöu tçm mäüt væìa thæûc hiãûn naìy hay såí hæîu mäüt naìy, laûi væìa thæûc hiãûn khaïc hay såí hæîu mäüt <tênh cháút> khaïc Cáu truy váún: Select distinct HOTEN_KTS From THIETKE a, CGTRINH b Where a.STT_CTR = b.STT_CTR and TEN_THAU = 'phong dich vu so xd' Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 21 and HOTEN_KTS IN (Select HOTEN_KTS From THIETKE a, CGTRINH b Where a.STT_CTR = b.STT_CTR and TEN_THAU = 'le van son') Læu yï: Trong cáu SELECT con daûng naìy (duìng tæì khoïa IN) ta chè âæåüc select mäüt cäüt duy nháút (khäng âæåüc select nhiãöu cäüt), vaì cäüt select phaíi laì cäüt ta âàût trong âiãöu kiãûn chæïa select con. Trong vê duû trãn âoï laì cäüt hoten_kts. Thàõc màõc: Select cha va select con cuía cáu truy váún trãn coï thãø âäøi chäù cho nhau âæåüc khäng? b. Cho biãút tãn cäng trçnh coï kinh phê cao nháút. Phán têch cáu hoíi: Vç ta chæa biãút kinh phê cao nháút laì bao nhiãu nãn ta phaíi duìng mäüt select con kãút håüp våïi haìm max âãø tçm ra con säú naìy. Vç chè coï duy nháút mäüt con säú låïn nháút, nãn ta coï thãø sæí duûng daûng âiãöu kiãûn chæïa select con säú 1. Cáu truy váún: Select TEN_CTR From CGTRINH Where KINH_PHI = (Select max(KINH_PHI) From CGTRINH) Læu yï: Âäúi våïi caïc cáu hoíi daûng cáu hoíi: Tçm såí hæîu mäüt . nháút. Ta âãöu phaíi sæí duûng select con trong cáu truy váún. c. Cho biãút hoü tãn caïc cäng nhán coï tham gia caïc cäng trçnh åí Cáön Thå, nhæng khäng coï tham gia cäng trçnh åí Vénh Long. Phán têch cáu hoíi: Nhæ ta biãút CSDL cuía ta chè læu træî nhæîng thäng tin coï hay âaî xaíy ra. Vç váûy âãø traí låìi âæåüc cáy hoíi naìy, ta chè coï thãø láúy táûp håüp nhæîng cäng nhán coï tham gia åí Cáön Thå træì âi táûp håüp nhæîng cäng nhán coï tham gia åí Vénh Long, nhæ minh hoüa trong trong hçnh sau: Hoü tãn Cäng Nhán coï tham gia caïc cäng trçnh åí ‘Cáön Thå’ Hoü tãn Cäng Nhán coï tham gia caïc cäng trçnh åí ‘Vénh Long’ Vuìng bãn traïi chênh laì vuìng kãút quaí cáön tçm Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 22 Váûy træåïc hãút ta phaíi tçm hai táûp håüp naìy bàòng hai cáu lãûnh SELECT vaì sau âoï træì táûp thæï nháút cho táûp thæï hai. Tuy nhiãn, vç pheïp træì khäng âæåüc häø tråü træûc tiãúp nãn ta sæí duûng daûng cáu SELECT läöng nhau trong âoï sæí duûng tæì khoïa NOT IN âãø kãút näúi select cha vaì select con våïi nhau (tæïc laì daûng âiãöu kiãûn chæïa select con säú 4 âaî âãö cáûp åí trãn). Læu yï: Caïch nháûn biãút daûng cáu hoíi sæí duûng pheïp TRÆÌ âãø traí låìi: Yãu cáöu tçm mäüt coï thæûc hiãûn naìy hay såí hæîu mäüt naìy, nhæng khäng (hoàûc chæa) thæûc hiãûn khaïc hay såí hæîu mäüt khaïc Cáu truy váún: Select distinct HOTEN_CN From THAMGIA a, CGTRINH b Where a.STT_CTR = b.STT_CTR and TINH_THANH = 'Can Tho' and HOTEN_CN NOT IN (Select HOTEN_CN From THAMGIA a, CGTRINH b Where a.STT_CTR = b.STT_CTR and TINH_THANH = 'Vinh Long') Thàõc màõc: Select cha va select con cuía cáu truy váún trãn coï thãø âäøi chäù cho nhau âæåüc khäng? d. Cho biãút tãn cuía caïc chuí tháöu âaî thi cäng caïc cäng trçnh coï kinh phê låïn hån táút caí caïc cäng trçnh do chuí tháöu Phoìng Dëch vuû Såí Xáy dæûng thi cäng. Phán têch cáu hoíi: Træåïc hãút ta phaíi duìng mäüt select âãø tçm táút caí caïc kinh phê cuía caïc cäng trçnh do chuí tháöu Phoìng dëch vuû såí xáy dæûng thi cäng. Sau âoï mäüt select thæï 2 âãø tçm caïc chuí tháöu thi cäng caïc cäng trçnh låïn hån táút caí caïc kinh phê naìy. Ta tháúy hai cáu select naìy coï thãø läöng nhau select thæï nháút seî laì select con vç noï âæåüc thæûc hiãûn træåïc, coìn select thæï 2 seî laì select cha, vaì select cha seî duìng âiãöu kiãûn chæïa select con daûng thæï 2 (coï tæì khoïa ALL). Cáu truy váún: Select TEN_THAU From CGTRINH Where KINH_PHI > ALL (Select KINH_PHI From CGTRINH Where TEN_THAU = 'Phong dich vu so xd') Thàõc màõc: Cáu naìy ta coï thãø viãút laûi bàòng caïch sæí duûng daûng âiãöu kiãûn chæïa select con säú 1 âæåüc khäng? Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 23 e. Cho biãút hoü tãn caïc kiãún truïc sæ coï thuì lao thiãút kãú mäüt cäng trçnh naìo âoï dæåïi giaï trë trung bçnh thuì lao thiãút kãú cho mäüt cäng trçnh. Phán têch cáu hoíi: Træåïc hãút ta phaíi duìng mäüt select âãø tçm giaï trë trung bçnh thuì lao thiãút kãú cuía mäüt cäng trçnh. Sau âoï duìng mäüt select thæï 2 âãø tçm caïc kiãún truïc sæ coï thuì lao thiãút kãú mäüt cäng trçnh naìo âoï nhoí hån con säú tçm âæåüc. Ta tháúy hai cáu select naìy coï thãø läöng nhau: select thæï nháút seî laì select con vç noï âæåüc thæûc hiãûn træåïc, coìn select thæï 2 seî laì select cha, vaì select cha seî duìng âiãöu kiãûn chæïa select con daûng thæï 1. Cáu truy váún: Select HOTEN_KTS From THIETKE a, CGTRINH b Where a.STT_CTR = b.STT_CTR and THU_LAO < (Select Avg(THU_LAO) From THIETKE) f. Tçm tãn vaì âëa chè nhæîng tháöu âaî truïng tháöu cäng trçnh coï kinh phê tháúp nháút. Phán têch cáu hoíi: Vç ta chæa biãút kinh phê tháúp nháút laì bao nhiãu nãn ta phaíi duìng mäüt select con kãút håüp våïi haìm min âãø tçm ra con säú naìy. Vç chè coï duy nháút mäüt con säú nhoí nháút, nãn ta coï thãø sæí duûng daûng âiãöu kiãûn chæïa select con säú 1. Cáu truy váún: Select TEN_THAU, DCHI_THAU From CHU_THAU a, CGTRINH b Where a.TEN_THAU = b.TEN_THAU And KINH_PHI = (Select min(KINH_PHI) From CGTRINH) Thàõc màõc: - Daûng cáu hoíi naìy giäúng cáu hoíi naìo trong baìi thæûc haình naìy? - Cáu naìy coï thãø sæí duûng daûng âiãöu kiãûn chæïa select con säú 3 hay khäng? g. Tçm hoü tãn vaì chuyãn män cuía caïc cäng nhán tham gia caïc cäng trçnh do kiãún truïc sæ ‘Le Thanh Tung’ thiãút kãú. Phán têch cáu hoíi: Træåïc hãút ta phaíi duìng mäüt select âãø tçm säú thæï tæû caïc cäng trçnh do kiãún truïc sæ Lã Thanh Tuìng thiãút kãú. Sau âoï duìng mäüt select thæï 2 âãø tçm caïc cäng nhán âaî tham gia vaìo caïc cäng trçnh tçm âæåüc. Ta tháúy hai cáu select naìy coï thãø läöng nhau: select thæï nháút seî laì select con vç noï âæåüc thæûc hiãûn træåïc, coìn select thæï 2 seî laì select cha, vaì select cha seî duìng âiãöu kiãûn chæïa select con daûng thæï 4. Cáu truy váún: Select a.HOTEN_CN, CH_MON From CONGNHAN a, THAMGIA b, CGTRINH c Where a.HOTEN_CN = b.HOTEN_CN and b.STT_CTR = c.STT_CTR Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 24 and b.STT_CTR IN (Select STT_CTR From THIETKE Where HOTEN_KTS = 'Le Thanh Tung') h. Tçm caïc càûp tãn cuía chuí tháöu coï truïng tháöu caïc cäng trçnh taûi cuìng mäüt thaình phäú. Phán têch cáu hoíi: Vç mäùi doìng trong baíng CGTRINH chè cho biãút chuí tháöu naìo thi cäng cäng trçnh naìo, nãn âãø tçm âæåüc nhæîng càûp chuí tháöu truïng tháöu caïc cäng trçnh taûi cuìng thaình phäú, thç ta phaíi måí baíng CGTRINH hai láön våïi hai bê danh khaïc nhau, kãút näúi chuïng bàòng caïc âiãöu kiãûn khaïc tãn tháöu nhæng cuìng tènh thaình: a.TEN_THAU b.TEN_THAU (1) a.TINH_THANH = b.TINH_THANH Tuy nhiãn, våïi âiãöu kiãûn kãút näúi nhæ váûy, ta seî nháûn âæåüc hai doìng cho mäùi càûp våïi thæï tæû tãn tháöu ngæåüc nhau; chàòng haûn: (Le Van Son, Cty xd so 6) vaì (Cty xd so 6, Le Van Son). Âãø khàõc phuûc tçnh traûng naìy, ta sæía laûi âiãöu kiãûn kãút näúi (1) bàòng: a.TEN_THAU < b.TEN_THAU Cáu truy váún: Select Distinct c1.TEN_THAU, c2.TEN_THAU, c1.TINH_THANH From CGTRINH c1, CGTRINH c2 Where c1.TEN_THAU < c2.TEN_THAU and c1.TINH_THANH = c2.TINH_THANH i. Tçm caïc càûp tãn cuía caïc cäng nhán coï laìm viãûc chung våïi nhau trong êt nháút laì hai cäng trçnh. Phán têch cáu hoíi: Træåïc hãút ta phaíi duìng mäüt select âãø tçm caïc càûp hoü tãn cäng nhán coï tham gia cuìng cäng trçnh. Sau âoï duìng mäüt select thæï hai nhoïm tæìng càûp laûi vaì âãúm säú cäng trçnh hoü tham gia chung våïi nhau, choün ra nhæîng càûp coï säú cäng trçnh tham gia chung låïn hån hoàûc bàòng 2. Hai select naìy khäng thãø läöng nhau, vç select thæï hai nhoïm dæî liãûu trãn kãút quaí cuía select thæï nháút. Vç váûy bàõt buäüc ta phaíi duìng 2 select. Select thæï nháút seî âæa kãút quaí vaìo baíng #TAM bàòng mãûnh âãö INTO Læu yï: Caïc baíng coï tãn bàõt âáöu bàòng dáúu # seî âæåüc læu vaìo CSDL tempdb. Âáy laì CSDL nhaïp, vç váûy, sau khi ta âoïng kãút näúi våïi SQL Server thç caïc baíng naìy seî bë xoïa. Cáu select thæï nháút hoaìn toaìn tæång tæû nhæ cáu h åí trãn. Vç mäùi doìng trong baíng THAMGIA chè cho biãút cäng nhán naìo tham gia cäng trçnh naìo, nãn âãø tçm âæåüc nhæîng càûp cäng nhán tham gia cuìng cäng trçnh, thç ta phaíi måí baíng THAMGIA hai láön våïi hai bê danh khaïc nhau, kãút näúi chuïng bàòng caïc âiãöu kiãûn: a.HOTEN_CN b.HOTEN_CN (1) a.STT_CTR = b.STT_CTR Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 25 Tuy nhiãn, våïi âiãöu kiãûn kãút näúi nhæ váûy, ta seî nháûn âæåüc hai doìng cho mäùi càûp våïi thæï tæû hoü tãn ngæåüc nhau; chàòng haûn: (Nguyen Van A, Tran Thi B) vaì (Tran Thi B, Nguyen Van A). Âãø khàõc phuûc tçnh traûng naìy, ta sæía laûi âiãöu kiãûn kãút näúi (1) bàòng: a.HOTEN_CN < b.HOTEN_CN Cáu truy váún: Select t1.HOTEN_CN As cn1, t2.HOTEN_CN As cn2, t1.STT_CTR Into #CAU_2i From THAMGIA t1, THAMGIA t2 Where t1.HOTEN_CN < t2.HOTEN_CN and t1.STT_CTR = t2.STT_CTR Order by cn1, cn2 Læu yï: Màûc duì hai cäüt láúy tæì hai baíng våïi bê danh khaïc nhau nhæng coï tãn giäúng nhau, nãn ta phaíi âàût laûi tãn cäüt bàòng tæì khoïa AS thç måïi coï thãø læu laûi vaìo baíng måïi. Select cn1, cn2 From #CAU_2i Group by cn1, cn2 Having count(STT_CTR) >=2 IV. BAÌI TÁÛP TÆÛ LAÌM: 1. CSDL: Quaín lyï mua baïn haìng hoïa (CSDL3) Baíng 1. HANGHOA(MA_HANG,TEN_HG) Mäùi haìng hoïa âæåüc gaïn mäüt maî haìng riãng biãût âãø dãù phán biãût, vaì coï mäüt tãn haìng. Baíng 2. DAILY(STT_DL, TEN_DL, DCHI_DL) Mäùi âaûi lyï âæåüc gaïn mäüt säú thæï tæû phán, coï mäüt tãn âaûi lyï vaì mäüt âëa chè liãn laûc. Baíng 3. MUA(MA_HANG, STT_DL, NGAY_MUA, SOLG_MUA, TRIGIA_MUA) Khi mäüt âaûi lyï mua mäüt màût haìng naìo âoï (MA_HANG), vaìo mäüt ngaìy naìo âoï (NGAY_MUA), seî âæåüc ghi nháûn laûi säú læåüng mua vaì trë giaï mua täøng cäüng. Baíng 4. BAN(MA_HANG, STT_DL, NGAY_BAN, SOLG_BAN, TRIGIA_BAN) Khi mäüt âaûi lyï baïn mäüt màût haìng naìo âoï (MA_HANG), vaìo mäüt ngaìy naìo âoï (NGAY_BAN), seî âæåüc ghi nháûn laûi säú læåüng baïn vaì trë giaï baïn täøng cäüng. 2. Yãu cáöu 1: Tçm sæû thäng thæång giæîa caïc baíng trong CSDL3 3. Yãu cáöu 2: Måí caïc baíng dæî liãûu âãø xem kiãøu dæî liãûu cuía tæìng træåìng vaì quan saït dæî liãûu cuía tæìng baíng. Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 26 4. Yãu cáöu 3: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau: a. Tçm tãn nhæîng âaûi lyï væìa coï baïn coca cola væìa coï baïn pepsi cola. b. Tçm tãn nhæîng màût haìng âæåüc mua nhæng chæa âæåüc baïn. c. Tçm tãn vaì âëa chè nhæîng âaûi lyï coï mua cuìng màût haìng våïi Vaûn Låüi mua. d. Cho biãút tãn caïc màût haìng âæåüc êt nháút laì hai âaûi lyï mua vaìo våïi säú læåüng trãn 50. e. Tçm täøng trë giaï mua Coca Cola åí âaûi lyï Tán Hiãûp Hæng. f. Tçm täøng trë giaï baïn Coca Cola åí âaûi lyï Tán Hiãûp Hæng. g. Tçm tãn nhæîng màût haìng âæåüc baïn åí âaûi lyï Tán Hiãûp Hæng vaìo caí hai ngaìy 15/12/94 vaì ngaìy 31/12/94. h. Coï bao nhiãu màût haìng âæåüc mua vaìo nhæng chæa âæåüc baïn ra åí âaûi lyï Tán Hiãûp Hæng trong thaïng 12 nàm 1994. i. Tçm tãn caïc màût haìng coï mua vaì baïn trong cuìng mäüt ngaìy åí cuìng mäüt âaûi lyï. j. Tçm tãn vaì âëa chè cuía caïc âaûi lyï vaì nhæîng màût haìng coï säú læåüng mua vaì baïn bàòng nhau trong cuìng mäüt ngaìy. k. Tçm tãn âaûi lyï âaî mua coca cola våïi säú læåüng nhiãöu hån táút caí caïc âaûi lyï khaïc. Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 27 BAÌI 3 I. MUÛC ÂÊCH: Luyãûn táûp cáu lãûnh SELECT coï GROUP BY, HAVING coï kãút håüp våïi caïc daûng SELECT âaî thæûc táûp åí caïc baìi træåïc. II. LYÏ THUYÃÚT: Cáu lãûnh SQL sæí duûng trong baìi naìy: SELECT , )> FROM [ WHERE ] GROUP BY [, [,...]] HAVING III. BAÌI TÁÛP COÏ HÆÅÏNG DÁÙN 1. CSDL: Quaín lyï Cäng Trçnh (CSDL 1) 1. KTRUCSU(HOTEN_KTS, NAMS_KTS, PHAI, NOI_TN, DCHI_LL_KT) 2. CHUTHAU(TEN_THAU, TEL, DCHI_THAU) 3. CHUNHAN(TEN_CHU, DCHI_CHU) 4. CONGNHAN(HOTEN_CN, NAMS_CN, NAM_VAO_N, CH_MON) 5. CGTRINH(STT_CTR, TEN_CTR, DCHI_CTR, TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, NGAY_BD) 6. THAMGIA(HOTEN_CN, STT_CTR, NGAY_TGIA, SO_NGAY) 7. THIETKE(HOTEN_KTS, STT_CTR, THU_LAO) Tham khaío trang 12 âãø xem diãùn giaíi cho tæìng baíng. 2. Yãu cáöu 1: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau: a. Tçm täøng kinh phê cuía táút caí caïc cäng trçnh theo tæìng chuí tháöu. Phán têch cáu hoíi: Vç mäùi doìng dæî liãûu trong baíng CGTRINH chè cho biãút kinh phê cuía tæìng cäng trçnh, nãn muäún biãút täøng kinh phê theo tæìng chuí tháöu thç ta phaíi nhoïm caïc doìng dæî liãûu theo cäüt TEN_THAU, sau âoï duìng haìm sum âãø cäüng kinh phê cho mäùi nhoïm. Cáu truy váún: Select TEN_THAU, sum(KINH_PHI) As TongKP From CGTRINH Group by TEN_THAU Thàõc màõc: Nãúu trong mãûnh âãö Select ta thãm cäüt TEN_CTR thç coï âæåüc khäng?Taûi sao? Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 28 b. Cho biãút hoü tãn caïc kiãún truïc sæ coï täøng thuì lao thiãút kãú caïc cäng trçnh låïn hån 25 triãûu. Phán têch cáu hoíi: Vç mäùi doìng dæî liãûu trong baíng THIETKE chè cho biãút thuì lao thiãút kãú cuía mäüt kiãún truïc sæ cho mäüt cäng trçnh naìo âoï, nãn muäún biãút täøng thuì lao thiãút kãú caïc cäng trçnh cuía tæìng kiãún truïc sæ, thç ta phaíi nhoïm caïc doìng dæî liãûu theo cäüt HOTEN_KTS, sau âoï duìng haìm sum âãø cäüng thuì lao cho mäùi nhoïm. Sau cuìng, duìng mãûnh âãö HAVING âãø choün laûi caïc nhoïm (hay caïc kiãún truïc sæ) coï täøng thuì lao > 25. Cáu truy váún: Select HOTEN_KTS, sum(THU_LAO) As TongTL From THIETKE Group by HOTEN_KTS Having sum(THU_LAO) > 25 c. Cho biãút säú læåüng caïc kiãún truïc sæ coï täøng thuì lao thiãút kãú caïc cäng trçnh låïn hån 25 triãûu. Phán têch cáu hoíi: Cáu hoíi naìy tæång tæû nhæ cáu b, nhæng thay vç liãût kã hoü tãn caïc kiãún truïc sæ thoía âiãöu kiãûn, thç ta chè cáön cho biãút coï täøng cäüng bao nhiãu kiãún truïc sæ thoía âiãöu kiãûn nhæ váûy. Vç váûy, træåïc hãút ta phaíi tçm danh saïch caïc kiãún truïc sæ thoía âiãöu kiãûn træåïc räöi måïi âãúm trãn danh saïch tçm âæåüc. Hay noïi khaïc hån, ta phaíi thæûc hiãûn 2 bæåïc bàòng 2 lãûnh select khaïc nhau: Cáu 1. giäúng hãût cáu b, nhæng kãút quaí xuáút ra seî âæåüc læu vaìo baíng #CAU_3C Cáu 2. Âãúm säú doìng trãn táûp tin #CAU_3C Cáu Truy Váún Select HOTEN_KTS, sum(THU_LAO) As TongTL Into #CAU_3C From THIETKE Group by HOTEN_KTS Having sum(THU_LAO) > 25 Select count(*) As Solg_KTS From #CAU_3C Thàõc màõc: ÅÍ bæåïc 2 ta coï cáön phaíi âãúm phán biãût khäng? d. Tçm täøng säú cäng nhán âaî tham gia cuía mäùi cäng trçnh. Phán têch cáu hoíi: Vç mäùi doìng dæî liãûu trong baíng THAMGIA chè cho cäng nhán naìo tham gia cäng trçnh naìo, nãn muäún biãút täøng säú cäng nhán âaî tham gia theo tæìng cäng trçnh, thç ta phaíi nhoïm Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 29 caïc doìng dæî liãûu theo cäüt STT_CTR, sau âoï duìng haìm count âãø âãúm säú doìng (mäùi doìng laì mäüt cäng nhán) trong mäùi nhoïm âãø coï âæåüc täøng säú cäng nhán tham gia. Cáu truy váún: Select STT_CTR, count(*) As TongSoCN From THAMGIA Group by STT_CTR Thàõc màõc: Trong mãûnh âãö Select cuía cáu naìy coï thãø thãm cäüt HOTEN_CN khäng? e. Tçm tãn vaì âëa chè cäng trçnh coï täøng säú cäng nhán tham gia nhiãöu nháút. Phán têch cáu hoíi: Cáu hoíi naìy tæång tæû nhæ cáu d, nhæng thay vç cho biãút täøng säú cäng nhán tham gia cuía táút caí caïc cäng trçnh, thç åí âáy ta chè yãu cáöu kãút xuáút ra cäng trçnh naìo coï täøng säú cäng nhán tham gia nhiãöu nháút. Vç váûy, træåïc hãút ta phaíi thæûc hiãûn cáu d træåïc âæa kãút quaí vaìo baíng #CAU_3E, tæì baíng #CAU_3E ta måïi coï thãø choün ra cäng trçnh coï täøng säú cäng nhán tham gia nhiãöu nháút âæåüc. Váûy, ta phaíi thæûc hiãûn 2 bæåïc bàòng 2 lãûnh select khaïc nhau: Cáu 1. giäúng hãût cáu d, nhæng kãút quaí xuáút ra seî âæåüc læu vaìo baíng #CAU_3E Cáu 2. Choün ra doìng coï TongSoCN cao nháút tæì táûp tin #CAU_3E, âäöng thåìi kãút näúi våïi baíng CGTRINH âãø láúy thäng tin vãö tãn vaì âëa chè cäng trçnh. Cáu Truy Váún: Select STT_CTR, count(*) As TongSoCN Into #CAU_3E From THAMGIA Group by STT_CTR Select TEN_CTR, DIACHI_CTR, TongSoCN From #CAU_3E a, CGTRINH b Where a.STT_CTR = b.STT_CTR And TongSoCN = (Select Max(TongSoCN) From #CAU_3E) f. Cho biãút tãn caïc thaình phäú vaì kinh phê trung bçnh cho mäùi cäng trçnh cuía tæìng thaình phäú tæång æïng. Phán têch cáu hoíi: Vç mäùi doìng dæî liãûu trong baíng CGTRINH chè cho biãút kinh phê cuía mäùi cäng trçnh vaì cho biãút cäng trçnh âoï naìo thuäüc tènh thaình naìo, nãn muäún biãút kinh phê trung bçnh cho mäùi cäng trçnh cuía tæìng thaình phäú, thç ta phaíi nhoïm caïc doìng dæî liãûu theo cäüt TINH_THANH, sau âoï duìng haìm avg âãø tênh kinh phê trung bçnh cho mäùi nhoïm (tæïc laì mäùi thaình phäú). Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 30 Cáu truy váún: Select TINH_THANH, Avg(KINH_PHI) As KinhPhiTB From CGTRINH Group By TINH_THANH g. Cho biãút hoü tãn caïc cäng nhán coï täøng säú ngaìy tham gia vaìo caïc cäng trçnh låïn hån täøng säú ngaìy tham gia cuía cäng nhán Nguyen Hong Van. Phán têch cáu hoíi: Âãø traí låìi âæåüc cáu hoíi naìy træåïc hãút ta phaíi biãút täøng säú ngaìy tham gia caïc cäng trçnh cuía mäùi cäng nhán. Sau âoï måïi choün ra nhæîng cäng nhán coï täøng säú ngaìy naìy låïn hån täøng säú ngaìy cuía cäng nhán Nguyãùn Häöng Ván. Vç váûy, ta phaíi thæûc hiãûn hai bæåïc naìy bàòng 2 lãûnh Select khaïc nhau: Cáu 1. Nhoïm dæî liãûu trong baíng THAMGIA theo HOTEN_CN, vaì tênh täøng säú ngaìy tham gia. Læu kãút quaí vaìo baíng #CAU_3G Cáu 2. Choün caïc doìng trãn baíng #CAU_3G thoía âiãöu kiãûn. Cáu truy váún: Select HOTEN_CN, Sum(SO_NGAY) As TongSoNgay Into #CAU_3G From THAMGIA Group By HOTEN_CN Select HOTEN_CN, TongSoNgay From #CAU_3G Where TongSoNgay > (Select TongSoNgay From #CAU_3G Where HOTEN_CN = 'Nguyen Hong Van') h. Cho biãút täøng säú cäng trçnh maì mäùi chuí tháöu âaî thi cäng taûi mäùi thaình phäú. Phán têch cáu hoíi: Vç mäùi doìng dæî liãûu trong baíng CGTRINH chè cho cäng trçnh naìo thuäüc tènh thaình naìo vaì do chuí tháöu naìo thi cäng, nãn muäún biãút täøng säú cäng trçnh maì mäùi chuí tháöu âaî thi cäng taûi mäùi tènh thaình, thç ta phaíi nhoïm caïc doìng dæî liãûu theo 2 cäüt TEN_THAU vaì TINH_THANH, sau âoï duìng haìm count âãø âãúm säú doìng chênh laì säú cäng trçnh cuía mäùi nhoïm. Cáu Truy Váún: Select TEN_THAU, TINH_THANH, Count (STT_CTR) As TongSoCTR From CGTRINH Group By TEN_THAU, TINH_THANH i. Cho biãút hoü tãn cäng nhán coï tham gia åí táút caí caïc cäng trçnh. Phán têch cáu hoíi: Vç mäùi doìng dæî liãûu trong baíng THAMGIA chè cho cäng nhán naìo tham gia cäng trçnh naìo, nãn muäún biãút cäng nhán naìo coï tham gia hãút táút caí caïc cäng trçnh træåïc hãút ta âãúm xem mäùi cäng nhán âaî tham gia täøng cäüng bao nhiãu cäng trçnh, âæa vaìo baíng #CAU_3i. Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 31 Sau âoï, choün tæì baíng #CAU_3i nhæîng cäng nhán coï täøng säú cäng trçnh tham gia bàòng våïi täøng säú cäng trçnh ta coï, bàòng caïch âãúm säú doìng trong baíng CGTRINH. Cáu Truy Váún: Select HOTEN_CN, Count (STT_CTR) As TongSoCtrTG Into #CAU_3i From THAMGIA Group by HOTEN_CN Select HOTEN_CN From #CAU_3i Where TongSoCtrTG = (Select Count(*) From CGTRINH) IV. BAÌI TÁÛP TÆÛ LAÌM 1. CSDL: Quaín lyï mua baïn haìng hoïa (CSDL3) 1. HANGHOA(MA_HANG,TEN_HG) 2. DAILY(STT_DL, TEN_DL, DCHI_DL) 3. MUA(MA_HANG, STT_DL, NGAY_MUA, SOLG_MUA, TRIGIA_MUA) 4. BAN(MA_HANG, STT_DL, NGAY_BAN, SOLG_BAN, TRIGIA_BAN) Tham khaío trang 25 âãø xem diãùn giaíi cho tæìng baíng. 2. Yãu cáöu 1: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau: a. Tçm âån giaï mua trung bçnh cuía bia Saìi goìn tênh trãn táút caí caïc âaûi lyï. b. Tçm âån giaï mua trung bçnh cuía bia Saìi goìn tênh trãn trãn tæìng âaûi lyïï c. Hiãøn thë säú læåüng täön kho cuía tæìng màût haìng theo tæìng âaûi lyï. d. Tçm tãn, âëa chè cuía âaûi lyï coï täøng giaï trë mua trong mäüt ngaìy låïn hån 700000. e. Våïi mäùi âaûi lyï, haîy cho biãút ngaìy naìo coï säú læåüng màût haìng baïn ra laì êt nháút. f. Tçm tãn vaì âëa chè cuía nhæîng âaûi lyï coï täøng trë giaï baïn cao nháút. g. Tçm täøng soï tiãön maì mäùi âaûi lyï âaî chi âãø mua haìng theo tæìng thaïng. h. Tçm tãn vaì täøng säú læåüng baïn ra cuía caïc màût haìng âaî baïn âæåüc våïi säú læåüng nhiãöu nháút cuía tæìng âaûi lyï. i. Cho biãút täøng säú moïn haìng maì mäùi âaûi lyï kinh doanh (mua vaì baïn). j. Tçm tãn âaûi lyï âaî mua vaìo màût haìng bia Heineken våïi âån giaï mua laì cao nháút. Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 32 BAÌI 4 I. MUÛC ÂÊCH: Luyãûn táûp cáu lãûnh SELECT coï sæí duûng caïc haìm haìm xæí lyï chuäùi, haìm xæí lyï ngaìy thaïng, haìm chuyãøn kiãøu, ...; kãút håüp våïi caïc daûng cáu SELECT cuía caïc baìi træåïc. II. BAÌI TÁÛP COÏ HÆÅÏNG DÁÙN: 1. CSDL: Quaín lyï giaíng daûy (CSDL5) Baíng 1. CHUCDANH(MACD, TENCD, GIOCHUAN, TIEN_1TIET) Mäùi chæïc danh coï mäüt maî chæïc danh phán biãût, mäüt tãn chæïc danh, coï mäüt säú læåüng giåì chuáøn tæång æïng, vaì säú tiãön mäüt tiãút traí theo chæïc danh âoï. Baíng 2. GIAOVIEN(MAGV, HOTEN, PHAI, MACD) Mäùi giaïo viãn coï mäüt maî giaïo viãn phán biãût, mäüt hoü tãn, phaïi vaì mäüt maî chæïc danh tæång æïng. Baíng 3. GD_0506(MAGV, MONDAY, LOPDAY, SOTIET, SISO) Thäng tin vãö viãûc giaíng daûy cuía giaïo viãn trong nàm hoüc 05-06 bao gäöm maî giaïo viãn, tãn män daûy, tãn låïp daûy, säú tiãút daûy, sè säú låïp. Baíng 4. TAMUNG(MAGV, NGAY_TA, SOTIEN) Mäùi giaïo viãn coï thãø taûm æïng tiãön. Khi taûm æïng, thäng tin vãö ngaìy taûm æïng vaì säú tiãön taûm æïng seî âæåüc ghi nháûn laûi. 2. Yãu cáöu 1: Tçm sæû thäng thæång giæîa caïc baíng trong CSDL 3. Yãu cáöu 2: Måí caïc baíng dæî liãûu âãø xem kiãøu dæî liãûu cuía tæìng træåìng vaì quan saït dæî liãûu cuía tæìng baíng 4. Yãu cáöu 3: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau: a. In ra danh saïch maî giaïo viãn, hoü tãn caïc caïn bäü cuìng våïi danh xæng laì ‘Tháöy’ nãúu caïn bäü laì nam, vaì ‘Cä’ nãúu caïn bäü laì næî. Phán têch cáu hoíi: Ta tháúy danh xæng Tháöy hay Cä seî phuû thuäüc vaìo cäüt PHAI trong baíng GIAOVIEN. Ta seî duìng cáúu truïc CASE âån giaín âãø kiãøm tra cäüt PHAI vaì traí vãö thäng tin phuì håüp. Tham khaío cuï phaïp cuía CASE åí trang 8. Cáu Truy Váún: Select MAGV, Case PHAI When 0 Then 'Thay ' + HOTEN When 1 Then 'Co ' + HOTEN End As HT From GIAOVIEN Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 33 b. In ra danh saïch gäöm 2 cäüt: Cäüt ’Hoü giaïo viãn’ vaì cäüt ’Chæî Loït+Tãn giaïo viãn’ Phán têch cáu hoíi: Cäüt HOTEN trong baíng GIAOVIEN bao gäöm caí hoü vaì tãn. Âãø taïch hoü vaì tãn ra thaình 2 cäüt thç ta duìng caïc haìm left âãø láúy hoü, haìm right âãø láúy ra tãn. Âãø taïch hoü ra, ta cáön biãút chiãöu daìi cuía hoü, hay noïi khaïc hån laì vë trê khoaíng tràõng âáöu tiãn trong chuäùi HOTEN. Ta seî sæí duûng haìm CharIndex âãø tçm vë trê naìy. Coìn tãn ta seî láúy tæì bãn phaíi sang mäüt säú læåüng kê tæû bàòng chiãöu daìi hoü tãn træì âi chiãöu daìi hoü. Tham khaío cuï phaïp cuía caïc haìm naìy åí trang 5. Cáu Truy Váún: Select Left(HOTEN, CharIndex(' ', HOTEN)) As Ho_Gv, Right (HOTEN, Len(HOTEN) - CharIndex(' ', HOTEN)) As Ten_GV From GIAOVIEN c. Cho biãút danh saïch hoü tãn caïc giaïo viãn chè daûy cho caïc låïp âiãûn tæí. Phán têch cáu hoíi: Tæì chè’trong cáu hoíi cho ta biãút âáy laì daûng cáu hoíi sæí duûng pheïp Træì hai táûp håüp. Táûp håüp thæï nháút (T1) laì táûp håüp caïc giaïo viãn coï daûy cho låïp Âiãûn tæí, táûp håüp thæï hai (T2) laì táûp håüp caïc giaïo viãn coï daûy caïc låïp gheïp Âiãûn tæí vaì Tin Hoüc hoàûc caïc låïp khaïc (nhæ Lyï, Chãú biãún,...). Láúy T1 træì cho T2. Coï nghéa laì T1 laì select cha, coìn T2 laì select con. Âiãöu kiãûn chæïa select con sæí duûng tæì khoïa NOT IN. Cáu Truy Váún: Select distinct a.MAGV, HOTEN From GD_0506 a, GIAOVIEN b Where a.MAGV = b.MAGV And LOPDAY Like '%DT%' And a.MAGV Not In (Select MAGV From GD_0506 Where LOPDAY Like '%TH%' or LOPDAY not like '%DT%') d. Tçm caïc giaïo viãn coï chæïc danh laì Giaíng Viãn âaî daûy cho låïp DT20. (læu yï: låïp DT-TH20 laì låïp gheïp cuía DT20 vaì TH20). Cáu Truy Váún: Select HOTEN, LOPDAY From GIAOVIEN a, GD_0506 b, CHUCDANH c Where a.MAGV = b.MAGV And a.MACD = c.MACD And TENCD = 'Giang Vien' And LOPDAY Like '%DT%20%' Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 34 e. Tçm hoü tãn caïc giaïo viãn coï tãn loït laì Vàn, vaì coï taûm æïng tiãön êt nháút laì 2 láön. Phán têch cáu hoíi: Vç mäùi doìng cuía baíng taûm æïng chè cho biãút giaïo viãn naìo taûm æïng ngaìy naìo, våïi säú tiãön bao nhiãu thäi, nãn muäún biãút giaïo viãn naìo taûm æïng êt nháút hai láön thç ta phaíi nhoïm dæî liãûu theo cäüt MAGV, sau âoï âãúm säú doìng cuía mäùi nhoïm vaì chè choün nhoïm coï säú doìng låïn hån 1. Cáu Truy Váún: Select HOTEN, Count(NGAY_TA) As SoLanTA From GIAOVIEN a, TAMUNG b Where a.MAGV = b.MAGV And HOTEN Like ‘% Van %’ Group By b.MA_GV Having count(NGAY_TA) > 1 f. Cho biãút hoü tãn caïc giaïo viãn âaî taûm æïng trong thaïng 1 nàm 2006? Cáu Truy Váún: Select HOTEN, NGAY_TA From GIAOVIEN a, TAMUNG b Where a.MAGV = b.MAGV And DatePart(month, NGAY_TA) = 1 And DatePart(year, NGAY_TA) = 2006 g. Cho biãút hoü tãn caïc giaïo viãn âaî taûm æïng trong 3 thaïng gáön âáy? Cáu Truy Váún: Select HOTEN, NGAY_TA From GIAOVIEN a, TAMUNG b Where a.MAGV = b.MAGV And DateDiff(month, NGAY_TA, GETDATE()) <= 3 h. Cho biãút täøng säú tiãút (âaî qui chuáøn) maì giaïo viãn coï tãn Nam âaî giaíng daûy. Biãút ràòng: + Säú tiãút qui chuáøn = SOTIET * HESO; trong âoï HESO âæåüc tênh nhæ sau: Nãúu sé säú låïp <= 80 thç HESO =1; Nãúu 80 < sé säú låïp <= 120 thç HESO =1.2; Nãúu 120 < sé säú låïp <= 150 thç HESO =1.4; Ngæåüc laûi, sé säú låïp > 150 thç HESO =1.5 Phán têch cáu hoíi: ÅÍ âáy ta phaíi duìng cáúu truïc CASE daûng tçm kiãúm âãø tênh säú tiãút qui chuáøn. Tham khaío trang 8 Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 35 Cáu truy váún: Select Sum( Case When SISO <= 80 Then SOTIET When SISO <= 120 Then SOTIET * 1.2 When SISO <= 150 Then SOTIET * 1.4 Else SOTIET * 1.5 END) From GIAOVIEN a, GD_0506 b Where a.MAGV = b.MAGV And HOTEN Like '% Nam' i. Tçm hoü tãn caïc giaïo viãn coï daûy væåüt giåì chuáøn Phán têch cáu hoíi: Tæång tæû nhæ cáu h, ta cuîng phaíi tênh säú tiãút qui chuáøn cuía tæìng giaïo viãn sau âoï choün ra nhæîng giaïo viãn coï täøng säú tiãút qui chuáøn låïn hån giåì chuáøn theo chæïc danh qui âënh. Cáu truy váún. -- Tênh täøng säú säú tiãút qui chuáøn cho tæìng giaïo viãn, âæa vaìo baíng taûm #CAU_4i Select MAGV, Sum( Case When SISO <= 80 Then SOTIET When SISO <= 120 Then SOTIET * 1.2 When SISO <= 150 Then SOTIET * 1.4 Else SOTIET * 1.5 END) As TongSTQC Into #CAU_4i From GD_0506 b Group By MAGV -- Hiãøn thë hoü tãn caïc giaïo viãn coï täøng säú tiãút daûy væåüt giåì chuáøn. Select HOTEN, TongSTQC, GIOCHUAN From #CAU_4i a, GIAOVIEN b, CHUCDANH c Where a.MAGV = b.MAGV And b.MACD = c.MACD And ToïngTQC > GIOCHUAN j. Haîy tênh säú tiãön thæûc lénh cuía táút caí caïc caïn bäü trong træåìng. Biãút ràòng: + Caïn bäü chè âæåüc laînh tiãön giaíng daûy khi âaî daûy væåüt giåì chuáøn (nghéa laì säú tiãút trong giåì chuáøn khäng âæåüc tênh tiãön). + Säú tiãön giaíng daûy thæûc tãú cho mäüt giaïo viãn laì: Säú tiãút væåüt giåì qui chuáøn * TIEN_1TIET. + Thæûc lénh cuía caïn bäü âæåüc tênh theo cäng thæïc nhæ sau: Nãúu caïn bäü coï daûy væåüt giåì vaì coï taûm æïng thç: Thæûc lénh = säú tiãön giaíng daûy - täøng säú tiãön taûm æïng. Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 36 Nãúu caïn bäü coï daûy væåüt giåì maì khäng coï taûm æïng thç: Thæûc lénh = säú tiãön giaíng daûy Nãúu caïn bäü khäng coï daûy væåüt giåì maì coï taûm æïng thç: Thæûc lénh = - täøng säú tiãön taûm æïng. Nãúu caïn bäü khäng coï daûy væåüt giåì vaì khäng coï taûm æïng thç: Thæûc lénh = 0. Phán têch cáu hoíi : Tæång tæû nhæ cáu i, ta cuîng tênh täøng säú giåì daûy chuáøn cuía mäùi giaïo viãn, âæa vaìo baíng taûm #CAU_4j_1. Tênh täøng säú tiãön taûm æïng cuía mäùi caïn bäü, âæa vaìo baíng taûm #CAU_4j_2. Kãút näúi hai baíng naìy âãø tênh säú tiãön thæûc lénh. Tuy nhiãn, nãúu kãút näúi tæû nhiãn bçnh thæåìng, thç nhæîng ngæåìi coï màût åí mäüt trong hai baíng seî biãún máút (nghéa laì nhæîng ngæåìi chè coï daûy hoàûc chè coï taûm æïng), âáy laì âiãöu ta khäng muäún, hoàûc coï nhæîng ngæåìi khäng coï daûy cuîng khäng hãö taûm æïng cuîng phaíi xuáút ra kãút quaí. Vç váûy, åí âáy ta phaíi sæí duûng kãút näúi måí räüng. Læu yï : SQL Server 2000 häø tråü kãút näúi måí räüng vãö mäüt phêa bàòng caïch duìng tæì khoïa left / right outer join. Cáu hoíi truy váún : Select MAGV, Sum( Case When SISO <= 80 Then SOTIET When SISO <= 120 Then SOTIET * 1.2 When SISO <= 150 Then SOTIET * 1.4 Else SOTIET * 1.5 END) As TongSTQC Into #CAU_4j_1 From GD_0506 b Group By MAGV Select MAGV, Sum(SOTIEN) As TongTA Into #CAU_4j_2 From TAMUNG Group By MAGV Select a.MAGV, CASE When (TongSTQC > GIOCHUAN) And TongTA is null Then TIEN_1TIET * (TongSTQC - GIOCHUAN) When (TongSTQC > GIOCHUAN) And TongTA is not null Then (TIEN_1TIET * (TongSTQC - GIOCHUAN) ) - TongTA When (TongSTQC <= GIOCHUAN) And TongTA is null Then 0 Else -TongTA End As ThucLinh From #CAu_4j_1 a right outer join GIAOVIEN b on a.MAGV = b.MAGV join CHUCDANH c on b.MACD = c.MACD Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 37 left outer join #CAU_4j_2 d on d.MAGV = a.MAGV III. BAÌI TÁÛP TÆÛ LAÌM 1. CSDL: Quaín lyï cáu laûc bäü (CSDL4) Baíng 1. CLB(MA_CLB, TEN_CLB, DCHI_CLB) Mäùi cáu laûc bäü coï mäüt maî cáu laûc bäü phán biãût, mäüt tãn vaì mäüt âëa chè cáu laûc bäü. Baíng 2. DOI (MA_DOI, LT, PHAI, MA_CLB) Mäùi âäüi coï mäüt maî âäüi phán biãût, thuäüc vãö mäüt læïa tuäøi, mäüt phaïi naìo âoï vaì thuäüc cáu laûc bäü. Baíng 3. NHA_TD (MA_NHATD, DCHI_NHATD, TG_SOSAN) Mäùi nhaì thi âáúu coï mäüt maî nhaì thi âáúu phán biãût, mäüt âëa chè vaì täøng säú sán noï coï. Baíng 4. TGIAN_TD (LUATUOI, TGIAN_TD) Thåìi gian thi âáúu trong mäüt tráûn âáúu âæåüc qui âënh theo læïa tuäøi cuía âäüi thi âáúu. Baíng 5. TRANDAU (MA_TD, NGAY_TD, GIO_TD, MA_NHATD) Mäùi tráûn âáúu coï mäüt maî tráûn âáúu phán biãût, thi âáúu vaìo ngaìy, giåì naìo âoï vaì diãùn ra taûi mäüt nhaì thi. Baíng 6. THAMGIA(MA_TD, MA_DOI, MA_CLB) Khi mäüt âäüi cuía mäüt cáu laûc bäü tham gia thi âáúu trong mäüt tráûn âáúu naìo âoï. Thç thäng tin seî âæåüc læu laûi trong baíng naìy. Baíng 7. VDV (MA_VDV, TEN_VDV, DCHI_VDV, PHAI, LUATUOI, MA_DOI, MA_CLB) Mäùi ván âäüng viãn coï mäüt maî váûn âäüng viãn phán biãût, mäüt tãn, mäüt âëa chè, thuäüc mäüt phaïi, mäüt læïa tuäøi naìo âoï, âäöng thåìi phaíi cho biãút váûn âäüng viãn âoï chåi cho âäüi naìo cuía cáu laûc bäü naìo. 2. Yãu cáöu 1: a. Tçm tãn táút caí caïc váûn âäüng viãn coï tham gia thi âáúu trong thaïng 1 nàm 1990. b. Cho biãút thäng tin vãö caïc tráûn âáúu gäöm: maî tráûn âáúu, âëa chè nhaì thi âáúu nåi diãùn ra tráûn âáúu, ngaìy thi âáúu, thåìi âiãøm bàõt âáöu vaì kãút thuïc tráûn âáúu. c. Tçm caïc càûp maî tráûn âáúu kãút thuïc vaìo cuìng ngaìy giåì. d. Tçm tãn caïc váûn âäüng viãn coï thi âáúu taûi nhaì thi âáúu säú 27 trong ngaìy 900616, tæì 10h saïng âãún 4h30 chiãöu. e. Tçm âëa chè nhaì thi âáúu, nåi maì váûn âäüng viãn Tuáún âang thi âáúu vaìo 3h chiãöu ngaìy 901206. f. Tçm danh saïch caïc tráûn âáúu bàõt âáöu hoàûc kãút thuïc trong khoaíng thåìi gian tæì 12h âãún 16h diãùn ra trãn mäüt sán cuía nhaì thi âáúu säú 49 trong ngaìy 900805. Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 38 g. Tçm tãn caïc váûn âäüng viãn coï tham gia thi âáúu trong caïc thaïng êt ra laì nhæ váûn âäüng viãn Tuáún. h. Tçm säú læåüng tráûn âáúu âæåüc täø chæïc theo tæìng nàm cuía tæìng nhaì thi âáúu. i. Tçm danh saïch caïc cáu laûc bäü coï âäüi thi âáúu taûi nhaì thi âáúu säú 01 vaìo caïc ngaìy trong thaïng 1 nàm 90. j. Tçm danh saïch caïc âäüi coï váûn âäüng viãn coï tãn bàõt âáöu bàòng kyï tæû H, vaì coï thi âáúu trong thaïng 8 nàm 90. Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL - Trang 39 BAÌI 5 I. MUÛC ÂÊCH: Baìi táûp täøng håüp II. CSDL: QUAÍN LYÏ DÆÛ AÏN (CSDL6) Baíng 1. NHA_CCAP(MA_NCC, TEN_NCC, VON, TH_PHO) Baíng 2. HANG_HOA(MA_HANG, TEN_HANG, MAU, TR_LUONG) Baíng 3. DU_AN(MA_DU_AN, TEN_DU_AN, TH_PHO) Baíng 4. CUNG_UNG(MA_NCC, MA_HANG, MA_DU_AN, SO_LUONG) III. CÁU HOÍI: 1. Tçm thäng tin âáöy âuí gäöm maî, tãn vaì thaình phäú cuía mäùi dæû aïn åí TP HCM. 2. Tçm maî vaì tãn caïc màût haìng âæåüc cung æïng cho dæû aïn Tán an. 3. Tçm tãn vaì väún cuía caïc nhaì cung cáúp âaî cung æïng màût haìng Sån Baûch Tuyãút cho dæû aïn Tán Phuï. Cäüt väún cáön ghi thãm âån vë tiãön tãû âi keìm. 4. Tçm tãn caïc dæû aïn do nhaì cung cáúp S1 cung æïng. 5. Tçm tãn nhaì cung cáúp âaî cung æïng caïc màût haìng maìu âoí cho dæû aïn J1. 6. Tçm tãn cuía caïc màût haìng maì khäng coï màût haìng naìo coï troüng læåüng nhoí hån. 7. Tçm tãn nhaì cung cáúp âaî cung æïng cho caí hai dæû aïn J1 vaì J2. 8. Hiãøn thë säú màût haìng maì mäùi nhaì cung cáúp âaî cung æïng cho caïc dæû aïn. 9. Tçm säú læåüng trung bçnh caïc màût haìng maî mäùi nhaì cung cáúp coï thãø cung æïng. 10. Våïi mäùi màût haìng, haîy hiãøn thë säú læåüng cung æïng maì mäùi nhaì cung cáúp âaî cung æïng cho caïc dæû aïn. 11. Âäúi våïi mäùi màût haìng cung æïng cho mäüt dæû aïn, tçm maî haìng, tãn dæû aïn vaì täøng säú læåüng haìng tæång æïng. 12. Âäúi våïi mäùi màût haìng cung æïng cho mäüt dæû aïn, tçm tãn haìng, tãn dæû aïn vaì täøng säú læåüng haìng tæång æïng. 13. Tçm tãn nhaì cung cáúp coï väún âáöu tæ cao nháút. 14. Tçm tãn caïc nhaì cung cáúp âaî cung æïng cho mäüt dæû aïn báút kyì åí thaình phäú Häö Chê Minh. 15. Tçm maî cuía caïc nhaì cung cáúp âaî cung æïng cho êt nháút hai dæû aïn coï cuìng thaình phäú våïi nhaì cung cáúp âoï. 16. Tçm tãn caïc dæû aïn khäng âæåüc cung æïng báút cæï màût haìng maìu âoí naìo båíi mäüt haîng åí thaình phäú Häö Chê Minh. 17. Tçm maî cuía caïc nhaì cung cáúp cung æïng êt nháút laì mäüt màût haìng, caïc màût haìng naìy âæåüc cung æïng êt nháút båíi mäüt nhaì cung cáúp, nhaì cung cáúp naìy cung cáúp êt nháút laì mäüt màût haìng maìu âoí. 18. Tçm maî cuía caïc dæû aïn âang sæí duûng êt nháút laì mäüt màût haìng maì S1 coï khaí nàng cung æïng. Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL - Trang 40 19. Tçm tãn dæû aïn âaî âæåüc cung æïng màût haìng Xi Màng Hoaìng Thaûch våïi säú læåüng låïn nháút. 20. Tçm maî caïc dæû aïn âæåüc cung æïng hoaìn toaìn båíi haîng S2. 21. Tçm maî caïc dæû aïn chè sæí duûng nhæîng màût haìng maì haîng S1 coï khaí nàng cung æïng. 22. Tçm täøng säú læåüng cuía mäùi màût haìng maì mäùi dæû aïn âaî âæåüc cung cáúp. 23. Tçm täøng säú dæû aïn âæåüc haîng S3 cung æïng. 24. Tçm täøng säú dæû aïn maì mäùi nhaì cung cáúp âaî cung æïng. 25. Tçm täøng säú læåüng màût haìng P1 do haîng S1 cung æïng. 26. Tçm trung bçnh säú læåüng cho màût haìng P1 âæåüc cung æïng cho mäüt dæû aïn. 27. Tçm trung bçnh säú læåüng cho màût haìng P1 âæåüc cung æïng båíi mäüt haîng. 28. Tçm trung bçnh säú læåüng cho màût haìng P1 âæåüc cung æïng cho mäüt dæû aïn båíi mäüt haîng. 29. Tçm tãn nhaì cung cáúp âaî cung æïng cho êt nháút laì 3 dæû aïn. 30. Tçm säú læåüng caïc nhaì cung cáúp âaî cung æïng cho caïc dæû aïn taûi êt nháút laì 3 thaình phäú. 31. Tim säú læåüng trung bçnh caïc bao xi màng (báút kãø hiãûu gç, maìu gç) maî mäùi dæû aïn âaî tiãu thuû. 32. Tçm tãn dæû aïn âaî âæåüc cung æïng màût haìng sån (báút kãø hiãûu gç, maìu gç) våïi täøng säú læåüng cao nháút. 33. Tçm maî cuía nhæîng haîng naìo cung æïng cuìng mäüt màût haìng cho táút caí caïc dæû aïn. 34. Tçm maî caïc dæû aïn âæåüc haîng S1 cung æïng cho táút caí caïc màût haìng maì haîng S1 coï khaí nàng cung æïng. 35. Tçm maî caïc dæû aïn âæåüc cung æïng båíi táút caí nhæîng haîng naìo cung æïng màût haìng maìu âoí. 36. Tçm maî caïc màût haìng âæåüc cung æïng cho táút caí caïc dæû aïn åí TP HCM. Khoa CNTT & TT - ÂHCT Giaïo Trçnh Thæûc Haình CSDL MUÛC LUÛC PHÁÖN 1. CÅ BAÍN VÃÖ SQL Server 2000........................................................................... 1 I. GIÅÏI THIÃÛU SÅ LÆÅÜC VÃÖ DËCH VUÛ SQL QUERY ANALYZER........................... 1 1. Måí chæång trçnh Query Analyzer:.............................................................................. 1 2. Cå såí dæî liãûu trong SQL Server:................................................................................. 2 3. Choün hay måí CSDL laìm viãûc:.................................................................................... 2 4. Caïc kiãøu dæî liãûu thäng duûng trong SQL:.................................................................... 2 5. Quan saït CSDL bàòng Object Browser:....................................................................... 3 6. Soaûn thaío vaì thæûc thi cáu truy váún:............................................................................. 4 7. Mäüt säú haìm thäng duûng trong SQL Server: ............................................................... 5 II. Caïc läùi thæåìng gàûp trong SQL.................................................................................... 9 1. Tãn cäüt khäng tçm tháúy trong baíng:............................................................................ 9 2. Tãn baíng khäng tçm tháúy: ........................................................................................... 9 3. Läùi cuï phaïp: ................................................................................................................ 9 4. Tãn cäüt coï màût åí nhiãöu baíng....................................................................................... 9 5. Pheïp so saïnh khäng tæång thêch kiãøu ......................................................................... 9 III. HÆÅÏNG DÁÙN THÆÛC HAÌNH.................................................................................. 10 1. Måí Query Analyzer âàng nháûp vaìo SQL Server trãn maïy chuí................................ 10 2. Quan saït CSDL: ........................................................................................................ 10 3. Måí CSDL muäún truy váún: ........................................................................................ 10 4. Soaûn thaío vaì thæûc hiãûn cáu truy váún. ........................................................................ 10 5. Quan saït kãút quaí vaì kiãøm chæïng. .............................................................................. 10 6. Læu laûi caïc cáu truy váún vaìo táûp tin *.sql. ................................................................ 10 IV. CAÏCH LÆU vaì XOÏA BAÍNG TAÛM: ........................................................................ 10 V. CHEÏP CSDL:............................................................................................................ 11 1. Sao læu laûi CSDL:..................................................................................................... 11 2. Phuûc häöi CSDL tæì táûp tin âaî sao læu: ....................................................................... 11 PHÁÖN 2. BAÌI TÁÛP THÆÛC HAÌNH ................................................................................... 12 I. Muûc âêch....................................................................................................................... 12 II. LYÏ THUYÃÚt ............................................................................................................. 12 III. Baìi táûp coï hæåïng dáùn: ............................................................................................... 12 1. CSDL: Quaín lyï cäng trçnh (CSDL1) ........................................................................ 12 2. Yãu cáöu 1: Tçm sæû thäng thæång giæîa caïc baíng trong CSDL:.................................. 13 3. Yãu cáöu 2: Måí caïc baíng dæî liãûu âãø xem kiãøu dæî liãûu cuía tæìng træåìng vaì quan saït dæî liãûu cuía tæìng baíng............................................................................................................ 13 4. Yãu cáöu 3: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:.................................. 14 IV. Baìi táûp tæû laìm:........................................................................................................... 17 1. CSDL: Quaín lyï baïo chê (CSDL2)............................................................................. 17 2. Yãu cáöu 1: Tçm sæû thäng thæång giæîa caïc baíng trong CSDL2................................. 17 Tráön Ngán Bçnh Giaïo Trçnh Thæûc Haình CSDL 3. Yãu cáöu 2: Måí caïc baíng dæî liãûu âãø xem kiãøu dæî liãûu cuía tæìng træåìng vaì quan saït dæî liãûu cuía tæìng baíng............................................................................................................18 4. Yãu cáöu 3: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:..................................18 I. Muûc âêch .......................................................................................................................19 II. LYÏ THUYÃÚt..............................................................................................................19 III. Baìi táûp coï hæåïng dáùn: ...............................................................................................20 1. CSDL: Quaín lyï cäng trçnh (CSDL1) ........................................................................20 2. Yãu cáöu 1: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:..................................20 IV. Baìi táûp TÆÛ LAÌM:......................................................................................................25 1. CSDL: Quaín lyï mua baïn haìng hoïa (CSDL3) ..........................................................25 2. Yãu cáöu 1: Tçm sæû thäng thæång giæîa caïc baíng trong CSDL3.................................25 3. Yãu cáöu 2: Måí caïc baíng dæî liãûu âãø xem kiãøu dæî liãûu cuía tæìng træåìng vaì quan saït dæî liãûu cuía tæìng baíng............................................................................................................25 4. Yãu cáöu 3: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:..................................26 I. Muûc âêch: ......................................................................................................................27 II. Lyï Thuyãút:.................................................................................................................27 III. Baìi táûp coï hæåïng dáùn ................................................................................................27 1. CSDL: Quaín lyï Cäng Trçnh (CSDL 1).....................................................................27 2. Yãu cáöu 1: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:..................................27 IV. BAÌi Táûp tæû laìm..........................................................................................................31 1. CSDL: Quaín lyï mua baïn haìng hoïa (CSDL3) ...........................................................31 2. Yãu cáöu 1: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:..................................31 I. Muûc âêch: ......................................................................................................................32 II. Baìi táûp coï hæåïng dáùn: ...............................................................................................32 1. CSDL: Quaín lyï giaíng daûy (CSDL5).........................................................................32 2. Yãu cáöu 1: Tçm sæû thäng thæång giæîa caïc baíng trong CSDL...................................32 3. Yãu cáöu 2: Måí caïc baíng dæî liãûu âãø xem kiãøu dæî liãûu cuía tæìng træåìng vaì quan saït dæî liãûu cuía tæìng baíng............................................................................................................32 4. Yãu cáöu 3: Haîy viãút cáu lãûnh SQL âãø traí låìi caïc cáu hoíi sau:..................................32 III. baìi táûp tæû laìm.............................................................................................................37 1. CSDL: Quaín lyï cáu laûc bäü (CSDL4) .......................................................................37 2. Yãu cáöu 1: .................................................................................................................37 I. MUÛc âêch: .....................................................................................................................39 II. CSDL: QUAÍN Lyï DÆÛ aïn (CSDL6).........................................................................39 III. CÁU HOÍI: .................................................................................................................39 TAÌI LIÃÛU THAM KHAÍO [1] Giaïo Trçnh CSQL - Âinh Khàõc Quyãön. Khoa Cäng Nghãû Thäng Tin, 2005 [2] SQL Tutorial: [3] SQL Server 2000 Book Online