Select語句查詢練習(xí)(pubs)

1、該實(shí)驗(yàn)以SQL Server 2000系統(tǒng)自帶的 pubs 數(shù)據(jù)庫為例，以一個(gè)圖書出版公司為模型。l SQL SERVER200實(shí)體關(guān)系圖描述l E-R圖描述（1）該系統(tǒng)中數(shù)據(jù)庫基本表如下：Authors：屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引au_idId作者編號(hào)否是1主鍵au_lnamevarchar(40)作者姓否au_fnamevarchar(20)作者名否phonechar(12)電話否addressvarchar(40)地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)郵編是是2contractBit是否簽約否1 au_id CHE

2、CK 約束定義為 (au_id LIKE '0-90-90-9-0-90-9-0-90-90-90-9')。2 zip CHECK 約束定義為 (zip LIKE '0-90-90-90-90-9')。=discounts屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引discounttypevarchar(40)折扣類型否stor_idchar(4)商店編號(hào)是外鍵 stores(stor_id)lowqtySmallint數(shù)量下限是highqtySmallint數(shù)量上限是discountFloat折扣否=Employee屬性名數(shù)據(jù)類型含義說明可為空默認(rèn)值檢查鍵/索引e

3、mp_idEmpid職工編號(hào)否是1主鍵fnamevarchar(20)職工名否minitchar(1)是lnamevarchar(30)職工姓否job_idSmallint工作編號(hào)否1外鍵 jobs(job_id)job_lvlTinyint否10pub_idchar(4)出版社編號(hào)否'9952'外鍵publishers(pub_id)Hire_dateDatetime工作日期否GETDATE( )CHECK 約束定義為：(emp_id LIKE 'A-ZA-ZA-Z1-90-90-90-90-9FM') OR (emp_id LIKE 'A-Z-A-Z

4、1-90-90-90-90-9FM')。=Jobs屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引job_idSmallint工作編號(hào)否主鍵job_descvarchar (50)工作描述否min_lvlTinyint否是1max_lvlTinyint否是2(1) min_lvl CHECK 約束定義為 (min_lvl >= 10)。(2) max_lvl CHECK 約束定義為 (max_lvl <= 250)。=pub_info屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引pub_idchar(4)出版社編號(hào)否主鍵，外鍵 publishers(pub_id)logoImage標(biāo)志圖

5、是pr_infoText出版信息是=Publishers屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引pub_idchar(4)出版社編號(hào)否是1主鍵pub_namevarchar(40)出版社名稱是cityvarchar(20)所在城市是statechar(2)所在州是countryvarchar(30)所在國家是1 pub_id CHECK 約束定義為 (pub_id = '1756' OR (pub_id = '1622' OR (pub_id = '0877' OR (pub_id = '0736' OR (pub_id = &#

6、39;1389') OR (pub_id LIKE '990-90-0')。=roysched屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引title_idTid書編號(hào)否外鍵 titles(title_id)lorangeInt低是hirangeInt高是royaltyInt版權(quán)是=Sales屬性名數(shù)據(jù)類型含義說明可為空鍵/索引stor_idchar(4)商店編號(hào)否組合主鍵，聚集索引,外鍵 stores(stor_id)ord_numvarchar(20)訂單編碼否組合主鍵，聚集索引ord_dateDatetime訂購日期否qtySmallint數(shù)量否paytermsvarc

7、har(12)付款方式否title_idTid書編號(hào)否組合主鍵，聚集索引,外鍵 titles(title_id)=titles屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引title_idTid書編號(hào)否主鍵titlevarchar(80)書名否typechar(12)類型否pub_idchar(4)出版社編號(hào)是外鍵 publishers (pub_id)priceMoney價(jià)格是advanceMoney預(yù)付款是royaltyInt版稅是Ytd_salesInt年銷售量是notesvarchar(200)簡介是pubdateDatetime出版日期是=Stores屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索

8、引stor_idchar(4)商店編號(hào)否主鍵stor_namevarchar(40)商店名稱是stor_addressvarchar(40)商店地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)郵編是=titleauthor屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引au_idid作者編號(hào)否組合主鍵，聚集索引,外鍵 authors(au_id)title_idtid書編號(hào)否組合主鍵，聚集索引,外鍵 titles(title_id)au_ordtinyint是royaltyperint版權(quán)百分比是（2）練習(xí)內(nèi)容目的1：1. 加深對(duì)表間關(guān)系的理解。2.

9、理解數(shù)據(jù)庫中數(shù)據(jù)的查詢方法和應(yīng)用。3. 學(xué)會(huì)各種查詢的異同及相互之間的轉(zhuǎn)換方法。內(nèi)容1：1. 查詢所有作者的作者號(hào)、姓名信息2. 查詢所有作者的姓名、作者號(hào)信息，并在每個(gè)作者的作者號(hào)前面顯示字符串“身份證號(hào)：”，表明顯示的信息是身份證信息3. 查詢?cè)贑A州的作者姓名和城市4. 查詢出版日期在1992.1.1-2000.12.31之間的書名和出版日期(查詢1991年出版的書)5. 查詢每個(gè)出版社出版的書6. 查詢某店銷售某書的數(shù)量7. 查詢有銷售記錄的所有書信息，包括書的編號(hào)、書名、類型和價(jià)格8. 查詢已銷售書的信息9. 顯示所有的書名（無銷售記錄的書也包括在內(nèi)）10. 查詢已銷售書的信息（書號(hào)

10、、書名、作者等）11. 查詢所有出版商業(yè)（business）書籍的出版社的名稱目的2：1. 理解數(shù)據(jù)庫中數(shù)據(jù)的其他查詢方法和應(yīng)用；2. 學(xué)會(huì)各種查詢要求的實(shí)現(xiàn)。內(nèi)容2：在實(shí)驗(yàn)1的基礎(chǔ)上，練習(xí)查詢語句的使用，包括計(jì)算列、求和、最大、最小值、各類選擇條件、字符匹配、分組和排序，體會(huì)各種查詢的執(zhí)行過程，為簡單綜合應(yīng)用打下良好的基礎(chǔ)。1. 查詢書名以T開頭或者出版社號(hào)為0877，而且價(jià)格大于16的書的信息。2. 按照類型的升序和價(jià)格的降序（在類型相同時(shí)）顯示書的信息（書名、作者、出版社、類型、價(jià)格）3. 查詢銷售量大于30的書名及銷售數(shù)量4. 查詢?cè)?002.1.1到2002.10.31間，每本書的銷

11、售總額5. 查詢所有作者的所在城市和州名，要求沒有重復(fù)信息6. 計(jì)算多少種書已被訂價(jià)7. 查詢每本書的書名、作者及它的售書總量8. 計(jì)算所有書的平均價(jià)格9. 查詢價(jià)格最高的書的書名、作者及價(jià)格目的3：1. 加深對(duì)數(shù)據(jù)庫相關(guān)性質(zhì)的理解；2. 各種約束性理解；3. 學(xué)會(huì)數(shù)據(jù)庫中數(shù)據(jù)的更新的方法。內(nèi)容3：1. 參照以上各表給出的主鍵、外鍵設(shè)置的設(shè)置要求，在自己創(chuàng)建的表中進(jìn)行相應(yīng)的設(shè)置。2. 向authors表中插入一行作者信息（具體值自定）3. 數(shù)量超過100的商店增加10%的折扣4. 刪除2001.10.3的訂單5. 刪除1中所建立的索引6. 建立CA州作者所著書的視圖（包括作者號(hào)、姓名、所在州、

12、書名、價(jià)格、出版日期）7. 建立付款方式是現(xiàn)金（cash）的訂單視圖8. 建立CA州的所有商店的視圖目的4：1. 在查詢分析器中，練習(xí)使用IN、比較符、ANY或ALL等操作符進(jìn)行查詢。2. 練習(xí)使用EXISTS操作符進(jìn)行嵌套查詢操作內(nèi)容4：1. 在pubs數(shù)據(jù)庫的titleauthor和中，用IN謂詞查詢來自CA州(在authors表中)的作家的全部作品（title_id）和作家的代號(hào)(au_id)。2. 在pubs數(shù)據(jù)庫中，用比較運(yùn)算符引出的子查詢找出在名稱為“Algodata Infosystems”的出版社所在城市中的作者的姓名（au_lname, au_fname）3. 在pubs數(shù)據(jù)

13、庫中的titles表中，查詢價(jià)格大于所有類型(TYPE)為“business”的圖書價(jià)格的書名（title）和價(jià)格(price)4. 在pubs數(shù)據(jù)庫的sales表中查找所有銷售量大于所有圖書平均銷售量avg（qty）的書的代號(hào)(title_id)及銷售量（qty）。5. 用帶有IN的嵌套查詢，查詢來自城市（city）為“London”的客戶所訂的訂單信息（customers和orders表）。6. 用帶有IN的嵌套查詢，查詢Northwind數(shù)據(jù)庫中的產(chǎn)品表（Products）中來自國家為“Germany” （在供應(yīng)商表（Suppliers）表中）的供貨商供應(yīng)的產(chǎn)品信息（包括Producti

14、d，Productname， categoryid, unitprice）。7. 使用EXISTS子查詢?cè)赑ubs數(shù)據(jù)庫titles 表及publishers表中查詢New Moon Books出版社所出版的圖書名稱（title)目的5：1. 分類匯總。內(nèi)容5：1. 找出pubs數(shù)據(jù)庫titles表中計(jì)算機(jī)類圖書中價(jià)格最高的圖書的價(jià)格。2. 查詢titles表中有幾類圖書。3. 按照州進(jìn)行分類，查找每個(gè)州有幾名作者。4. 要求按照出版商id進(jìn)行分類，查找每個(gè)出版商的書到目前為止的銷售額總和（ytd_sales）。5. 在pubs數(shù)據(jù)庫的titles表中，找出平均價(jià)格大于18美元的書的種類。6.

15、 在pubs數(shù)據(jù)庫的titles表中，找出最高價(jià)大于20美元的書的種類。7. 找出title_id和pub_name的對(duì)應(yīng)關(guān)系。8. 找出title_id, title和pub_name的對(duì)應(yīng)關(guān)系。9. 查詢每個(gè)作者的編號(hào)，姓名，所出的書的編號(hào)，并對(duì)結(jié)果排序。10. 從authors表中選擇state,city列，從publisher表中選擇state,city列，并把兩個(gè)查詢的結(jié)果合并為一個(gè)結(jié)果集，并對(duì)結(jié)果集按city列、state列進(jìn)行排序。11. 對(duì)上面的查詢語句作修改，保留所有重復(fù)的記錄。12. 顯示所有來自CA州的作家的全部作品和作家代號(hào)。（使用IN，和連接兩種方法）13. 查找由位

16、于以字母 B 開頭的城市中的任一出版商出版的書名：(使用exists和in兩種方法)一、簡單查詢學(xué)生選課數(shù)據(jù)1、列出全部學(xué)生的信息。2、列出信息系全部學(xué)生的學(xué)號(hào)及姓名。3、列出所有已被選修的選修課的課號(hào)。4、求c01號(hào)課成績大于80分的學(xué)生的學(xué)號(hào)及成績，并按成績由高到低列出。5、列出非信息系學(xué)生的名單。6、查詢成績?cè)?080分之間的學(xué)生選課得分情況7、列出選修c01號(hào)課或c03號(hào)課的全體學(xué)生的學(xué)號(hào)和成績。8、列出所有95級(jí)學(xué)生的學(xué)生成績情況。9、列出成績?yōu)榭罩?或不為空值)的學(xué)生的學(xué)號(hào)和課號(hào)。10、求出所有學(xué)生的總成績。11、列出每個(gè)學(xué)生的平均成績。12、列出各科的平均成績、最高成績、最低成績

17、和選課人數(shù)。1 SELECT * FROM 學(xué)生2 SELECT 學(xué)號(hào),姓名 FROM 學(xué)生 WHERE 專業(yè)=信息系3 SELECT DISTINCT 課號(hào) FROM 選修課4 SELECT 學(xué)號(hào),成績 FROM 選課 WHERE 課號(hào)=01 AND 成績>80 ORDER BY 成績 DESC5 方法一：SELECT 姓名 FROM 學(xué)生 WHERE 專業(yè)<>信息系方法二：SELECT 姓名 FROM 學(xué)生 WHERE NOT 專業(yè)=信息系方法三：SELECT 姓名 FROM 學(xué)生 WHERE 專業(yè)!=信息系6 方法一：SELECT * FROM 選課 WHERE 成績&

18、gt;=70 AND 成績<=80方法二：SELECT * FROM 選課 WHERE 成績 BETWEEN 70 AND 80不在此范圍內(nèi)的查詢：（注意寫出和以下語句等價(jià)的語句）SELECT * FROM 選課 WHERE 成績 NOT BETWEEN 70 AND 807 方法一：SELECT 學(xué)號(hào),成績 FROM 選課 WHERE 課號(hào)=c01 OR 課號(hào)=c03方法二：SELECT 學(xué)號(hào),成績 FROM 選課 WHERE 課號(hào) IN (c01,c03)相反條件查詢：SELECT 學(xué)號(hào),成績 FROM 選課 WHERE 課號(hào) NOT IN (c01,c03)8 SELECT * F

19、ROM 選課 WHERE 學(xué)號(hào) LIKE 95%SELECT * FROM 選課 WHERE 學(xué)號(hào) LIKE 95_ _ _ _相反條件查詢：SELECT * FROM 選課 WHERE 學(xué)號(hào) NOT LIKE 98%9 答案一：SELECT 學(xué)號(hào),課號(hào) FROM 選課 WHERE 成績 IS NULL答案二：SELECT 學(xué)號(hào),課號(hào) FROM 選課 WHERE 成績 IS NOT NULL10 SELECT SUM(成績) AS 總成績 FROM 選課11 SELECT 學(xué)號(hào),AVG(成績) AS 平均成績 FROM 選課 GROUP BY 學(xué)號(hào)12 SELECT 課號(hào),AVG(成績) AS

20、 平均成績,MAX(成績) AS 最高分,MIN(成績) AS 最低分,COUNT(學(xué)號(hào)) AS 選課人數(shù) FROM 選課 GROUP BY 課號(hào)目的4：2. 在查詢分析器中，練習(xí)使用IN、比較符、ANY或ALL等操作符進(jìn)行查詢。3. 練習(xí)使用EXISTS操作符進(jìn)行嵌套查詢操作請(qǐng)完成以下習(xí)題：14. 在pubs數(shù)據(jù)庫的titleauthor和中，用IN謂詞查詢來自CA州(在authors表中)的作家的全部作品（title_id）和作家的代號(hào)(au_id)。select title_id,au_idfrom titleauthorwhere au_id in (select au_id from

21、 authors where state='CA') 在pubs數(shù)據(jù)庫中，用比較運(yùn)算符引出的子查詢找出在名稱為“Algodata Infosystems”的出版社所在城市中的作者的姓名（au_lname, au_fname）select au_lname, au_fnamefrom authors where city= (select city from publishers where pub_name='Algodata Infosystems')在pubs數(shù)據(jù)庫中的titles表中，查詢價(jià)格大于所有類型(TYPE)為“business”的圖書價(jià)格的書名（

22、title）和價(jià)格(price)select title,pricefrom titleswhere price>all (select price from titles where type='business')在pubs數(shù)據(jù)庫的sales表中查找所有銷售量大于所有圖書平均銷售量avg（qty）的書的代號(hào)(title_id)及銷售量（qty）。 select title_id ,qtyfrom saleswhere qty>all(select avg(qty)from sales)用帶有IN的嵌套查詢，查詢來自城市（city）為“London”的客戶所訂的訂

23、單信息（customers和orders表）。select *from orderswhere customerID in (select customerIDfrom customerswhere city='london')用帶有IN的嵌套查詢，查詢Northwind數(shù)據(jù)庫中的產(chǎn)品表（Products）中來自國家為“Germany” （在供應(yīng)商表（Suppliers）表中）的供貨商供應(yīng)的產(chǎn)品信息（包括Productid，Productname， categoryid, unitprice）。SELECT Productid,Productname, categoryid,u

24、nitpricefrom Productswhere Productid in(select supplierIDfrom Supplierswhere country='Germany')2、提高操作實(shí)驗(yàn)練習(xí)使用EXISTS操作符進(jìn)行嵌套查詢操作。請(qǐng)完成以下習(xí)題：使用EXISTS子查詢?cè)赑ubs數(shù)據(jù)庫titles 表及publishers表中查詢New Moon Books出版社所出版的圖書名稱（title)select title from titles where exists (select * from publishers where pub_name='N

25、ew Moon Books')T-SQL高級(jí)查詢課堂練習(xí)及答案-練習(xí)1-找出pubs數(shù)據(jù)庫titles表中計(jì)算機(jī)類圖書中價(jià)格最高的圖書的價(jià)格。USE pubsGOSELECT max(price) FROM titleswhere type='popular_comp'GO-練習(xí)2-查詢titles表中有幾類圖書。USE pubsGOSELECT count(distinct type) FROM titlesGO-練習(xí)3-按照州進(jìn)行分類，查找每個(gè)州有幾名作者。USE pubsGOSELECT state, count(*) FROM authorsgroup by s

26、tateorder by 1GO-練習(xí)4-要求按照出版商id進(jìn)行分類，查找每個(gè)出版商的書到目前為止的銷售額總和（ytd_sales）。USE pubsGOSELECT pub_id, sum(ytd_sales) FROM titlesgroup by pub_idorder by 1GO-練習(xí)5-在pubs數(shù)據(jù)庫的titles表中，找出平均價(jià)格大于18美元的書的種類。USE pubsGOSELECT pub_id,avg(price) '平均價(jià)格' FROM titlesGROUP BY pub_idHAVING avg(price) > 18GO-練習(xí)6-在pubs數(shù)

27、據(jù)庫的titles表中，找出最高價(jià)大于20美元的書的種類。USE pubsGOSELECT type,max(price) '平均價(jià)格' FROM titlesGROUP BY typeHAVING max(price) > 20GO-練習(xí)7-找出title_id和pub_name的對(duì)應(yīng)關(guān)系。Use pubsgoSelect titles.title_id, publishers.pub_nameFrom titles JOIN publishersON titles.pub_id=publishers.pub_idGo-練習(xí)8-找出title_id, title和pub

28、_name的對(duì)應(yīng)關(guān)系。Use pubsgoSelect titles.title_id, titles.title, publishers.pub_nameFrom titles JOIN publishersON titles.pub_id=publishers.pub_idGo-練習(xí)9-查詢每個(gè)作者的編號(hào)，姓名，所出的書的編號(hào)，并對(duì)結(jié)果排序。Use pubsgoSelect authors.au_id, authors.au_fname + '.' + authors.au_lname 'name', titleauthor.title_idFrom authors JOIN titleauthorON authors.au_id=titlea