Select語句查詢理解練習(pubs)

1、該實驗以SQL Server 2000 系統自帶的 pubs數據庫為例，以一個圖書出版公司為模型。SQL SERVER200 實體關系圖描述s乩 EEBtQr_idfrrd_imner怙itr_rvteifTEtitle L(iititlepublishersIst-OTesg匚 torpid_storHamestorjsldrcsv=itrstateKip.i!E-R圖描述tTir一 Fub-iil一尸近亡 BjivajLCirofa-ltp- ytdalt匚 mtesI pnbdat亡litlijl _ aurlroTB-l Ipper*rnp_ia hiame niiLit InajDS

2、pib.idcEtalecQuinraTk-litanie曲出*亡ES 乞itr statA 旳 co 工 tract? pibidJObEnHx lvl銷售r o y s c h e d (版稅)1< 扣稅書）-titles(<參與編寫有p u b _ i n f o(發(fā)行信息 )1p u b l is h e r s（發(fā)行商)W 雇傭Ma u t h o r s（作者）e m p lo y e e（雇員:)N< 擔任jobs ( 職位 )（1 ）該系統中數據庫基本表如下:Authorsau_idCHECK(au_idLIKE屬性名數據類型含義說明可為空檢查鍵/索引au_i

3、dId作者編號否曰1是主鍵au_lnamevarchar(40)作者姓否au_fnamevarchar(20)作者名否phonechar(12)電話否addressvarchar(40)地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)郵編是曰2是contractBit是否簽約否'0-90-90-9-0-90-9-0-90-90-90-9')2 zip CHECK 約束定義為(zip LIKE '0-90-90-90-90-9')disco unts屬性名數據類型含義說明可為空檢查鍵/索引discountt ype

4、varchar(40)折扣類型否stor_idchar(4)商店編號是夕卜鍵 stores(stor_id)lowqtySmallint數量下限是highqtySmallint數量上限是discountFloat折扣否屬性名數據類型含義說明可為空默認值檢查鍵/索引emp_idEmp id職工編號否是1主鍵fnamevarchar(20)職工名否minitchar(1)是lnamevarchar(30)職工姓否job_idSmallint工作編號否1外鍵 jobs(job_id)job_lvlTinyint否10pub_idchar(4)出版社編號否'9952'外鍵P ublis

5、hers (pu b_id)Hire_dateDatetime工作日期否GETDATE()Empio yeeCHECK約束定義為:(emp_idLIKE'A-ZA-ZA-Z1-90-90-90-90-9FM')OR(emp_idLIKE'A-Z-A-Z1-90-90-90-90-9FM')Jobs屬性名數據類型含義說明可為空檢查鍵/索引job_idSmallint工作編號否主鍵job_descvarchar (50)工作描述否min_lvlTinyint否曰1是max_lvlTinyint否曰2是(1) min_lvl CHECK約束定義為 (min_lvl &

6、gt;= 10)。 max_lvl CHECK約束定義為 (max_lvl <= 250)。pub_info屬性名數據類型含義說明可為空檢查鍵/索引pub_idchar(4)出版社編號否主鍵，外鍵 P ublishers( pub_id)logoImage標志圖是p r_infoText出版信息是Publishers屬性名數據類型含義說明可為空檢查鍵/索引pub_idchar(4)出版社編號否1是1主鍵pub_namevarchar(40)出版社名稱是cityvarchar(20)所在城市是statechar(2)所在州是countryvarchar(30)所在國家是1 pub_id C

7、HECK約束定義為(p ub_id = '1756'OR ( pub_id = '1622' OR(pu b_id = '0877'OR ( pub_id = '0736'OR (p ub_id = '1389') OR (p ub_id LIKE'990-90-0')。roysched屬性名數據類型含義說明可為空檢查鍵/索引title_idTid書編號否外鍵 titles(title_id)lorangeInt低是hirangeInt高是royaltyInt版權是Sales屬性名數據類型含義說明可

8、為空鍵/索引stor_idchar(4)商店編號否組合主鍵，聚集索引，外鍵stores(stor_id)ord_numvarchar(20)訂單編碼否組合主鍵，聚集索引ord_dateDatetime訂購日期否qtySmallint數量否p aytermsvarchar(12)付款方式否title_idTid書編號否組合主鍵，聚集索引，外鍵titles(title_id)titles屬性名數據類型含義說明可為空檢查鍵/索引title_idTid書編號否主鍵titlevarchar(80)書名否typechar(12)類型否pub_idchar(4)出版社編號是夕卜鍵 Publishers (p

9、ub_id)priceMoney價格是advanceMoney預付款是royaltyInt版稅是Ytd_salesInt年銷售量是notesvarchar(200)簡介是p ubdateDatetime出版日期是Stores屬性名數據類型含義說明可為空檢查鍵/索引stor_idchar(4)商店編號否主鍵stor_namevarchar(40)商店名稱是stor_addressvarchar(40)商店地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)郵編是titleauthor屬性名數據類型含義說明可為空檢查鍵/索引au_idid作者編號否組合

10、主鍵，聚集索引，外鍵authors(au_id)title_idtid書編號否組合主鍵，聚集索引，外鍵titles(title_id)au_ordtinyint是royalt yperint版權百分比是(2 )練習內容目的1 :1.加深對表間關系的理解。2.理解數據庫中數據的查詢方法和應用。3.學會各種查詢的異同及相互之間的轉換方法。內容1 :1.查詢所有作者的作者號、姓名信息2.查詢所有作者的姓名、 作者號信息，并在每個作者的作者號前面顯示字符串“身份證號："，表明顯示的信息是身份證信息3.查詢在CA州的作者姓名和城市4.查詢出版日期在1992.1.1-2000.12.31之間的書

11、名和出版日期（查詢1991年出版的書）5.查詢每個出版社出版的書6.查詢某店銷售某書的數量7.查詢有銷售記錄的所有書信息，包括書的編號、書名、類型和價格查詢已銷售書的信息9.顯示所有的書名（無銷售記錄的書也包括在內）10.查詢已銷售書的信息（書號、書名、作者等）11.目的2 :1.理解數據庫中數據的其他查詢方法和應用;2.學會各種查詢要求的實現。查詢所有出版商業(yè)（bus in ess ）書籍的出版社的名稱內容2 :在實驗1的基礎上，練習查詢語句的使用，包括計算列、求和、最大、最小值、各類 選擇條件、字符匹配、分組和排序， 體會各種查詢的執(zhí)行過程，為簡單綜合應用打下良好的 基礎。1.2.查詢書名

12、以T開頭或者出版社號為 0877，而且價格大于16的書的信息。按照類型的升序和價格的降序（在類型相同時）顯示書的信息（書名、作者、出版社、類型、價格）3.查詢銷售量大于30的書名及銷售數量4.查詢在2002.1.1到2002.10.31間，每本書的銷售總額5.查詢所有作者的所在城市和州名，要求沒有重復信息6.計算多少種書已被訂價7.查詢每本書的書名、作者及它的售書總量計算所有書的平均價格9.查詢價格最高的書的書名、作者及價格目的3 :1.加深對數據庫相關性質的理解;2.各種約束性理解;3.學會數據庫中數據的更新的方法。內容3 :1.參照以上各表給出的主鍵、外鍵設置的設置要求，在自己創(chuàng)建的表中進

13、行相應的設 置。2.向authors表中插入一行作者信息（具體值自定）3.數量超過100的商店增加10%的折扣4.刪除2001.10.3的訂單5.刪除1中所建立的索引6.建立CA州作者所著書的視圖（包括作者號、姓名、所在州、書名、價格、出版日期）7.建立付款方式是現金（cash ）的訂單視圖目的4 :建立CA州的所有商店的視圖1.在查詢分析器中，練習使用IN、比較符、ANY或ALL等操作符進行查詢。2.練習使用EXISTS操作符進行嵌套查詢操作內容4 :1.在Pubs數據庫的titleauthor 和中，用IN謂詞查詢來自CA '州（在authors 表中）的作家的全部作品（title

14、_id ）和作家的代號（au_id）。2.在pubs數據庫中，用比較運算符引出的子查詢找出在名稱為"Algodata.7.Infosystems ”的出版社所在城市中的作者的姓名（au_lname, au_fname）在pubs數據庫中的titles表中，查詢價格大于所有類型（TYPE）為“ bus in ess ”的圖書價格的書名（title ）和價格（price）在pubs數據庫的sales表中查找所有銷售量大于所有圖書平均銷售量avg （qty ）的書的代號（title_id）及銷售量（qty ）。用帶有IN的嵌套查詢，查詢來自城市（city ）%“London

15、”的客戶所訂的訂單信息( customers 禾R orders 表)。用帶有IN的嵌套查詢,國家為“ Germany ”息（包括 Productid使用EXISTS子查詢在查詢 Northwind數據庫中的產品表（Products ）中來自（在供應商表（Suppliers ）表中）的供貨商供應的產品信Product name， categoryid, un it price）。Pubs數據庫titles 表及Publishers表中查詢 New MoonBooks出版社所出版的圖書名稱（title）目的5 :1.分類匯總。內容5 :1.找出Pubs數據庫titles表中計算機類圖書中價格最高

16、的圖書的價格。2.查詢titles表中有幾類圖書。3.按照州進行分類，查找每個州有幾名作者。4.要求按照出版商id進行分類，查找每個出版商的書到目前為止的銷售額總和(ytd_sales )。5.在pubs數據庫的titles表中，找出平均價格大于18美元的書的種類。6.在pubs數據庫的titles表中，找出最高價大于20美元的書的種類。7.找出title_id 和pub_name的對應關系。找出title_id, title 和pub_name 的對應關系。9.查詢每個作者的編號，姓名，所出的書的編號，并對結果排序。10. 從 authors 表中選擇 state,city 列，從 publ

17、isher表中選擇state,city列，并把兩個查詢的結果合并為一個結果集，并對結果集按city列、state列進行排序。11.對上面的查詢語句作修改，保留所有重復的記錄。12.顯示所有來自CA州的作家的全部作品和作家代號。（使用IN，和連接兩種方法）13.查找由位于以字母 B開頭的城市中的任一出版商出版的書名:（使用 exists 和in兩種方法）一、簡單查詢學生選課數據列出全部學生的信息。列出信息系全部學生的學號及姓名。列出所有已被選修的選修課的課號。求c01號課成績大于80分的學生的學號及成績，并按成績由高到低列出。列出非信息系學生的名單。查詢成績在7080分之間的學生選課得分情況列出

18、選修c01號課或C03號課的全體學生的學號和成績。列出所有95級學生的學生成績情況。列出成績?yōu)榭罩担ɑ虿粸榭罩担┑膶W生的學號和課號。10、求出所有學生的總成績。11、列出每個學生的平均成績。12、列出各科的平均成績、最高成績、最低成績和選課人數。SELECT * FROM 學生SELECT學號，姓名FROM學生WHERE專業(yè)='信息系'SELECT DISTINCT 課號 FROM選修課SELECT學號，成績FROM選課WHERE 課號='01 ' AND 成績 >80 ORDER BY 成績 DESC5方法一：SELECT姓名 FROM 學生 WHERE

19、專業(yè) <> '信息系'方法二：SELECT姓名FROM學生WHERE NOT 專業(yè)='信息系'方法三：SELECT姓名FROM 學生 WHERE專業(yè)!='信息系'6 方法一：SELECT * FROM 選課 WHERE 成績 >=70 AND 成績 <=80方法二：SELECT * FROM 選課 WHERE 成績 BETWEEN 70 AND 80不在此范圍內的查詢：（注意寫出和以下語句等價的語句）SELECT * FROM 選課 WHERE 成績 NOT BETWEEN 70 AND 807方法一：SELECT學號，成

20、績FROM選課WHERE課號='c01 ' OR課號='c03方法二：SELECT 學號，成績 FROM 選課 WHERE 課號 IN ( c01 '， c03 ')相反條件查詢：SELECT學號，成績FROM選課WHERE課號NOT IN （ c01 ' ,' c03 '）8 SELECT * FROM 選課 WHERE 學號 LIKE 95%SELECT * FROM 選課 WHERE 學號 LIKE 95.相反條件查詢：SELECT * FROM 選課 WHERE 學號 NOT LIKE 98% '9答案一：SELE

21、CT學號，課號 FROM 選課 WHERE成績IS NULL答案二：SELECT學號，課號 FROM選課 WHERE成績IS NOT NULL10SELECT SUM(成績)AS總成績 FROM 選課11SELECT學號,AVG（成績）AS 平均成績 FROM 選課 GROUP BY 學號12SELECT課號,AVG（成績）AS平均成績，MAX（成績）AS最高分,MIN（成績）AS最低分,COUNT（學號）AS選課人數 FROM 選課 GROUP BY 課號目的4 :2.在查詢分析器中，練習使用IN、比較符、ANY或ALL等操作符進行查詢。3.練習使用EXISTS操作符進行嵌套查詢操作請完成以

22、下習題:14.在Pubs數據庫的titleauthor 和中，用IN謂詞查詢來自CA '州（在authors 表中）的作家的全部作品（title_id ）和作家的代號（au_id）。select title_id,au_idfrom titleauthorwhere au_id in (select au_idfrom authorswhere state='CA')在pubs數據庫中，用比較運算符引出的子查詢找出在名稱為“Algodata Infosystems”的出版社所在城市中的作者的姓名（au_ln ame, au_fname )select au_ln ame

23、, au_fnamefrom authorswhere city= (select cityfrom p ublisherswhere pub_n ame='Algodata In fosystems')在p ubs數據庫中的titles表中，查詢價格大于所有類型（TYPE）為"bus in ess ”的圖書價格的書名（title ）和價格（price）select title ,p ricefrom titleswhere p rice>all (select pricefrom titles where typ e='bus in ess')

24、avg ( qty)在pubs數據庫的sales表中查找所有銷售量大于所有圖書平均銷售量的書的代號（title_id）及銷售量（qty ）。select title_id ,qtyfrom saleswhere qty>all(select avg(qty)from sales用帶有IN的嵌套查詢，查詢來自城市（city ）為"Lon don ”的客戶所訂的訂單信息(customers 禾R orders 表)。select *from orderswhere customerlD in (select customerlDfrom customerswhere city=&#

25、39;l ondon')用帶有IN的嵌套查詢，查詢Northwind數據庫中的產品表（Products ）中來自國家為"Germany ”（在供應商表（Suppliers ）表中）的供貨商供應的產品信息（包括Productid ，Productnamecategoryid, un it price）。SELECT Productid ,P roduct name, categoryid, un it pricefrom Productswhere P roductid in (select supp lierlDfrom Supp lierswhere coun try=&#

26、39;Germa ny')2、提高操作實驗練習使用EXISTS操作符進行嵌套查詢操作。請完成以下習題:使用EXISTS子查詢在 Pubs數據庫titles 表及publishers 表中查詢 New MoonBooks出版社所出版的圖書名稱（title）select titlefrom titles where exists (select *from p ublisherswhere pub_n ame='New Moon Books')T-SQL高級查詢課堂練習及答案-練習1-找出Pubs數據庫titles表中計算機類圖書中價格最高的圖書的價格。USE pubsGO

27、SELECT max( price) FROM titles where type='popu lar_co mp'GO-練習2-查詢titles表中有幾類圖書。USE pubsGOSELECT coun t(disti net type) FROM titlesGO-練習3-按照州進行分類，查找每個州有幾名作者。USE pubsGOSELECT state, coun t(*) FROM authors group by state order by 1GO-練習4-要求按照出版商id進行分類，查找每個出版商的書到目前為止的銷售額總和(ytd_sales )。USE pubs

28、GOSELECT pub_id, sum(ytd_sales) FROM titles group by p ub_id order by 1GO-練習5-在pubs數據庫的titles表中，找出最高價大于 20美元的書的種類。-在pubs數據庫的titles表中，找出平均價格大于 18美元的書的種類。USE pubs平均價格'FROM titlesGOSELECT pub_id,avg( price)'GRO UP BY pub_idHAVING avg( price) > 18GO-練習6USE pubsGOSELECT type,max(price)'平均價

29、格'FROM titlesGRO UP BY typeHAVING max( price) > 20GO-練習7找出title_id和pub_name 的對應關系。Use p ubs goSelect titles.title_id, p ublishers .pub_n ameFrom titles JOIN p ublishersON titles .p ub_id=publishers .p ub_idGo-練習-找出title_id, title 和 pub_name的對應關系。Use p ubs goSelect titles.title_id, titles.title, p ublishers .pub_n ameFrom titles JOIN p ublishersON titles .p ub_id=publishers .p ub_idGo-練習9-查詢每個作者的編號，姓名，所出的書的編號，并對結果排序。Use P ubs goSelect authors.au_id,authors.au_fname + '.' + authors.au _ln ame 'n ame', titleauthor.title idFrom a