實驗2 數(shù)據(jù)查詢

1、實驗D02: 數(shù)據(jù)查詢一、實驗名稱數(shù)據(jù)查詢二、實驗目的1. 加深對表間關(guān)系的理解。2. 理解數(shù)據(jù)庫中數(shù)據(jù)的查詢方法和應用。3. 學會各種查詢的異同及相互之間的轉(zhuǎn)換方法。4. 理解數(shù)據(jù)庫中數(shù)據(jù)的其他查詢方法和應用。Authors：屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引au_idId作者編號否是1主鍵au_lnamevarchar(40)作者姓否au_fnamevarchar(20)作者名否phonechar(12)電話否addressvarchar(40)地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)郵編是是2contractBit是否簽約否

2、=discounts屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引discounttypevarchar(40)折扣類型否stor_idchar(4)商店編號是外鍵 stores(stor_id)lowqtySmallint數(shù)量下限是highqtySmallint數(shù)量上限是discountFloat折扣否=Employee屬性名數(shù)據(jù)類型含義說明可為空默認值檢查鍵/索引emp_idEmpid職工編號否是1主鍵fnamevarchar(20)職工名否minitchar(1)是lnamevarchar(30)職工姓否job_idSmallint工作編號否1外鍵 jobs(job_id)job_lvlTin

3、yint否10pub_idchar(4)出版社編號否'9952'外鍵publishers(pub_id)Hire_dateDatetime工作日期否GETDATE( )=Jobs屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引job_idSmallint工作編號否主鍵job_descvarchar (50)工作描述否min_lvlTinyint否是1max_lvlTinyint否是2=pub_info屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引pub_idchar(4)出版社編號否主鍵，外鍵 publishers(pub_id)logoImage標志圖是pr_infoText出版信息是=P

4、ublishers屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引pub_idchar(4)出版社編號否是1主鍵pub_namevarchar(40)出版社名稱是cityvarchar(20)所在城市是statechar(2)所在州是countryvarchar(30)所在國家是=roysched屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引title_idTid書編號否外鍵 titles(title_id)lorangeInt低是hirangeInt高是royaltyInt版權(quán)是=Sales屬性名數(shù)據(jù)類型含義說明可為空鍵/索引stor_idchar(4)商店編號否組合主鍵，聚集索引,外鍵 stores(s

5、tor_id)ord_numvarchar(20)訂單編碼否組合主鍵，聚集索引ord_dateDatetime訂購日期否qtySmallint數(shù)量否paytermsvarchar(12)付款方式否title_idTid書編號否組合主鍵，聚集索引,外鍵 titles(title_id)=titles屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引title_idTid書編號否主鍵titlevarchar(80)書名否typechar(12)類型否pub_idchar(4)出版社編號是外鍵 publishers (pub_id)priceMoney價格是advanceMoney預付款是royaltyInt

6、版稅是Ytd_salesInt年銷售量是notesvarchar(200)簡介是pubdateDatetime出版日期是=Stores屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引stor_idchar(4)商店編號否主鍵stor_namevarchar(40)商店名稱是stor_addressvarchar(40)商店地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)郵編是=titleauthor屬性名數(shù)據(jù)類型含義說明可為空檢查鍵/索引au_idid作者編號否組合主鍵，聚集索引,外鍵 authors(au_id)title_idtid書編號否組合主鍵

7、，聚集索引,外鍵 titles(title_id)au_ordtinyint是royaltyperint版權(quán)百分比是五、實驗內(nèi)容(一) Select1. 查詢所有作者的姓名、作者號信息，并在每個作者的作者號前面顯示字符串“身份證號：”，表明顯示的信息是身份證信息代碼：select '身份證號:'+au_id,au_fname,au_lnamefrom authors結(jié)果： au_fname au_lname- - -身份證號:409-56-7008 Abraham Bennet身份證號:648-92-1872 Reginald Blotchet-Halls身份證號:238-95

8、-7766 Cheryl Carson身份證號:722-51-5454 Michel DeFrance身份證號:712-45-1867 Innes del Castillo身份證號:427-17-2319 Ann Dull身份證號:213-46-8915 Marjorie Green身份證號:527-72-3246 Morningstar Greene身份證號:472-27-2349 Burt Gringlesby身份證號:846-92-7186 Sheryl Hunter身份證號:756-30-7391 Livia Karsen身份證號:486-29-1786 Charlene Locksl

9、ey身份證號:724-80-9391 Stearns MacFeather身份證號:893-72-1158 Heather McBadden身份證號:267-41-2394 Michael O'Leary身份證號:807-91-6654 Sylvia Panteley身份證號:998-72-3567 Albert Ringer身份證號:899-46-2035 Anne Ringer身份證號:341-22-1782 Meander Smith身份證號:274-80-9391 Dean Straight身份證號:724-08-9931 Dirk Stringer身份證號:172-32-11

10、76 Johnson White身份證號:672-71-3249 Akiko Yokomoto(23 行受影響)2. 查詢在CA州的作者姓名和城市 代碼：select au_lname,au_fname,cityfrom authorswhere state='CA' 結(jié)果： au_lname au_fname city- - -White Johnson Menlo ParkGreen Marjorie OaklandCarson Cheryl BerkeleyO'Leary Michael San JoseStraight Dean OaklandBennet Ab

11、raham BerkeleyDull Ann Palo AltoGringlesby Burt CoveloLocksley Charlene San FranciscoYokomoto Akiko Walnut CreekStringer Dirk OaklandMacFeather Stearns OaklandKarsen Livia OaklandHunter Sheryl Palo AltoMcBadden Heather Vacaville(15 行受影響)3. 查詢書名以T開頭或者出版社號為0877，而且價格大于16的書的信息。代碼：select *from titleswher

12、e title like 'T%'or pub_id like '0877'and price>16GO結(jié)果：BU1032The Busy Executive's Database Guidebusiness 138919.995000.00104095An overview of available database systems with emphasis on common business applications. Illustrated.1991-06-12 00:00:00.000MC2222Silicon Valley Gastr

13、onomic Treatsmod_cook 087719.990.00122032Favorite recipes for quick, easy, and elegant meals.1991-06-09 00:00:00.000MC3021The Gourmet Microwavemod_cook 08772.9915000.002422246Traditional French gourmet recipes adapted for modern microwave cooking.1991-06-18 00:00:00.000MC3026The Psychology of Comput

14、er CookingUNDECIDED 0877NULLNULLNULLNULLNULL2015-09-28 08:24:55.863PS1372Computer Phobic AND Non-Phobic Individuals: Behavior Variationspsychology 087721.597000.0010375A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't.1991-

15、10-21 00:00:00.000TC3218Onions, Leeks, and Garlic: Cooking Secrets of the Mediterraneantrad_cook 087720.957000.0010375Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.1991-10-21 00:00:00.0004. 查詢銷售量大于30的書名及銷售數(shù)量代碼： select title ,Ytd_salesfrom titleswhere

16、Ytd_sales>30結(jié)果：The Busy Executive's Database Guide4095Cooking with Computers: Surreptitious Balance Sheets3876You Can Combat Computer Stress!18722Straight Talk About Computers4095Silicon Valley Gastronomic Treats2032The Gourmet Microwave22246But Is It User Friendly?8780Secrets of Silicon Vall

17、ey4095Computer Phobic AND Non-Phobic Individuals: Behavior Variations375Is Anger the Enemy?2045Life Without Fear111Prolonged Data Deprivation: Four Case Studies4072Emotional Security: A New Algorithm3336Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean375Fifty Years in Buckingham Palac

18、e Kitchens15096Sushi, Anyone?40955. 查詢訂單數(shù)大于3的各商店編碼及訂單數(shù)量。代碼： select stor_id,qtyfrom Saleswhere qty>3結(jié)果：638057066507066757067107067407067207067207131207131257131207131257131157131257896157896107896358042158042108042258042306. 查詢在1994.1.1到1994.10.31間，每本書的銷售總額代碼：select Sales.title_id,銷售總額=qty*pricefr

19、om titles ,Sales where sales.title_id=titles.title_id andord_date between'1994-1-1' and '1994-10-31'結(jié)果：BU103299.95PS209132.85PS2091821.25PS2091109.50PS2091219.00MC302174.75MC302144.85BU1032199.907. 查詢所有作者的所在城市和州名，要求沒有重復信息代碼：select distinct state,cityfrom authors結(jié)果：CABerkeleyCACoveloC

20、AMenlo ParkCAOaklandCAPalo AltoCASan FranciscoCASan JoseCAVacavilleCAWalnut CreekINGaryKSLawrenceMDRockvilleMIAnn ArborORCorvallisTNNashvilleUTSalt Lake City8. 查詢每本書的書名、作者及它的售書總量代碼：select title,name=au_fname+''+au_lname+'',SUM(distinct ytd_Sales)from authors ,titles,titleauthorwhere

21、authors.au_id=titleauthor.au_id and titleauthor.title_id =titles.title_idgroup by au_fname,au_lname,title結(jié)果：titlename(無列名)The Busy Executive's Database GuideAbrahamBennet4095Sushi, Anyone?AkikoYokomoto4095Is Anger the Enemy?AlbertRinger2045Life Without FearAlbertRinger111Secrets of Silicon Valle

22、yAnnDull4095Is Anger the Enemy?AnneRinger2045The Gourmet MicrowaveAnneRinger22246Sushi, Anyone?BurtGringlesby4095Emotional Security: A New AlgorithmCharleneLocksley3336Net EtiquetteCharleneLocksleyNULLBut Is It User Friendly?CherylCarson8780Straight Talk About ComputersDeanStraight4095Silicon Valley

23、 Gastronomic TreatsInnesdel Castillo2032Prolonged Data Deprivation: Four Case StudiesJohnsonWhite4072Computer Phobic AND Non-Phobic Individuals: Behavior VariationsLiviaKarsen375The Busy Executive's Database GuideMarjorieGreen4095You Can Combat Computer Stress!MarjorieGreen18722Cooking with Comp

24、uters: Surreptitious Balance SheetsMichaelO'Leary3876Sushi, Anyone?MichaelO'Leary4095The Gourmet MicrowaveMichelDeFrance22246Fifty Years in Buckingham Palace KitchensReginaldBlotchet-Halls15096Secrets of Silicon ValleySherylHunter4095Computer Phobic AND Non-Phobic Individuals: Behavior Varia

25、tionsStearnsMacFeather375Cooking with Computers: Surreptitious Balance SheetsStearnsMacFeather3876Onions, Leeks, and Garlic: Cooking Secrets of the MediterraneanSylviaPanteley3759. 查詢價格最高的書的書名、作者及價格 代碼： select title,au_fname+au_lname name,pricefrom titles,authors,titleauthorwhere price=(select max(d

26、istinct price)from titles)and authors.au_id=titleauthor.au_id andtitles.title_id=titleauthor.title_id結(jié)果：titlenamepriceBut Is It User Friendly?CherylCarson22.9510. 查詢每個店銷售的各書的數(shù)量 代碼： select stor_name,title,qtyfrom stores,titles,saleswhere stores.stor_id=sales.stor_id and titles.title_id=sales.title_id

27、 結(jié)果： stor_nametitleqtyEric the Read BooksThe Busy Executive's Database Guide5Eric the Read BooksIs Anger the Enemy?3Barnum'sSecrets of Silicon Valley50Barnum'sIs Anger the Enemy?75News & BrewsIs Anger the Enemy?10News & BrewsOnions, Leeks, and Garlic: Cooking Secrets of the Medit

28、erranean40News & BrewsFifty Years in Buckingham Palace Kitchens20News & BrewsSushi, Anyone?20Doc-U-Mat: Quality Laundry and BooksIs Anger the Enemy?20Doc-U-Mat: Quality Laundry and BooksThe Gourmet Microwave25Doc-U-Mat: Quality Laundry and BooksComputer Phobic AND Non-Phobic Individuals: Beh

29、avior Variations20Doc-U-Mat: Quality Laundry and BooksLife Without Fear25Doc-U-Mat: Quality Laundry and BooksProlonged Data Deprivation: Four Case Studies15Doc-U-Mat: Quality Laundry and BooksEmotional Security: A New Algorithm25Fricative BookshopStraight Talk About Computers15Fricative BookshopSili

30、con Valley Gastronomic Treats10Fricative BookshopYou Can Combat Computer Stress!35BookbeatThe Gourmet Microwave15BookbeatThe Busy Executive's Database Guide10BookbeatCooking with Computers: Surreptitious Balance Sheets25BookbeatBut Is It User Friendly?3011. 查詢有銷售記錄的所有書信息，包括書的編號、書名、類型和價格代碼：select

31、 title,type,price,titles.title_idfrom titles,saleswhere sales.title_id=titles.title_id and qty>0order by title結(jié)果：titletypepricetitle_idBut Is It User Friendly?popular_comp22.95PC1035Computer Phobic AND Non-Phobic Individuals: Behavior Variationspsychology 21.59PS1372Cooking with Computers: Surrep

32、titious Balance Sheetsbusiness 11.95BU1111Emotional Security: A New Algorithmpsychology 7.99PS7777Fifty Years in Buckingham Palace Kitchenstrad_cook 11.95TC4203Is Anger the Enemy?psychology 10.95PS2091Is Anger the Enemy?psychology 10.95PS2091Is Anger the Enemy?psychology 10.95PS2091Is Anger the Enem

33、y?psychology 10.95PS2091Life Without Fearpsychology 7.00PS2106Onions, Leeks, and Garlic: Cooking Secrets of the Mediterraneantrad_cook 20.95TC3218Prolonged Data Deprivation: Four Case Studiespsychology 19.99PS3333Secrets of Silicon Valleypopular_comp20.00PC8888Silicon Valley Gastronomic Treatsmod_co

34、ok 19.99MC2222Straight Talk About Computersbusiness 19.99BU7832Sushi, Anyone?trad_cook 14.99TC7777The Busy Executive's Database Guidebusiness 19.99BU1032The Busy Executive's Database Guidebusiness 19.99BU1032The Gourmet Microwavemod_cook 2.99MC3021The Gourmet Microwavemod_cook 2.99MC3021You

35、Can Combat Computer Stress!business 2.99BU207512. 顯示所有的書名（無銷售記錄的書也包括在內(nèi)） 代碼：select titlefrom titles結(jié)果：But Is It User Friendly?Computer Phobic AND Non-Phobic Individuals: Behavior VariationsCooking with Computers: Surreptitious Balance SheetsEmotional Security: A New AlgorithmFifty Years in Buckingham

36、 Palace KitchensIs Anger the Enemy?Life Without FearNet EtiquetteOnions, Leeks, and Garlic: Cooking Secrets of the MediterraneanProlonged Data Deprivation: Four Case StudiesSecrets of Silicon ValleySilicon Valley Gastronomic TreatsStraight Talk About ComputersSushi, Anyone?The Busy Executive's D

37、atabase GuideThe Gourmet MicrowaveThe Psychology of Computer CookingYou Can Combat Computer Stress!13. 查詢已銷售書的信息（書號、書名、作者名、銷售的商店名等）代碼：Select distinct sales.title_id,title,name=au_fname+''+au_lname ,stor_namefrom authors,titles,sales,stores,titleauthorwhere titles.title_id=sales.title_id and

38、stores.stor_id=sales.stor_id and titleauthor.au_id=authors.au_id and sales.title_id=titleauthor.title_id and qty>0結(jié)果： title_idtitlenamestor_nameBU1032The Busy Executive's Database GuideAbrahamBennetBookbeatBU1032The Busy Executive's Database GuideAbrahamBennetEric the Read BooksBU1032The

39、Busy Executive's Database GuideMarjorieGreenBookbeatBU1032The Busy Executive's Database GuideMarjorieGreenEric the Read BooksBU1111Cooking with Computers: Surreptitious Balance SheetsMichaelO'LearyBookbeatBU1111Cooking with Computers: Surreptitious Balance SheetsStearnsMacFeatherBookbeat

40、BU2075You Can Combat Computer Stress!MarjorieGreenFricative BookshopBU7832Straight Talk About ComputersDeanStraightFricative BookshopMC2222Silicon Valley Gastronomic TreatsInnesdel CastilloFricative BookshopMC3021The Gourmet MicrowaveAnneRingerBookbeatMC3021The Gourmet MicrowaveAnneRingerDoc-U-Mat:

41、Quality Laundry and BooksMC3021The Gourmet MicrowaveMichelDeFranceBookbeatMC3021The Gourmet MicrowaveMichelDeFranceDoc-U-Mat: Quality Laundry and BooksPC1035But Is It User Friendly?CherylCarsonBookbeatPC8888Secrets of Silicon ValleyAnnDullBarnum'sPC8888Secrets of Silicon ValleySherylHunterBarnum

42、'sPS1372Computer Phobic AND Non-Phobic Individuals: Behavior VariationsLiviaKarsenDoc-U-Mat: Quality Laundry and BooksPS1372Computer Phobic AND Non-Phobic Individuals: Behavior VariationsStearnsMacFeatherDoc-U-Mat: Quality Laundry and BooksPS2091Is Anger the Enemy?AlbertRingerBarnum'sPS2091I

43、s Anger the Enemy?AlbertRingerDoc-U-Mat: Quality Laundry and BooksPS2091Is Anger the Enemy?AlbertRingerEric the Read BooksPS2091Is Anger the Enemy?AlbertRingerNews & BrewsPS2091Is Anger the Enemy?AnneRingerBarnum'sPS2091Is Anger the Enemy?AnneRingerDoc-U-Mat: Quality Laundry and BooksPS2091I

44、s Anger the Enemy?AnneRingerEric the Read BooksPS2091Is Anger the Enemy?AnneRingerNews & BrewsPS2106Life Without FearAlbertRingerDoc-U-Mat: Quality Laundry and BooksPS3333Prolonged Data Deprivation: Four Case StudiesJohnsonWhiteDoc-U-Mat: Quality Laundry and BooksPS7777Emotional Security: A New AlgorithmCharleneLocksleyDoc-U-Mat: Quality Laundry and BooksTC3218Onions, Leeks, and Garlic: Cooking Secrets of the MediterraneanSylviaPanteleyNews & BrewsTC4203Fifty Years in Buckingham Palace KitchensReginaldBlotchet-HallsNews & BrewsTC7777Sushi, Anyone?AkikoYokomotoNews & BrewsTC