SQL SELECT语句和SELECT查询示例（二）

日期： 2009-05-18 作者：SearchSQLServer.com翻译：曾少宁 来源：TechTarget中国 英文

娱乐经纪公司数据库 “List all entertainers and the cities they’re based in, and sort the results by city and name in ascending order.”　 Translation Select city and stage name from the entertainers table and order by city and stage name Clean Up Select city  stage name from &nb……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

娱乐经纪公司数据库

"List all entertainers and the cities they're based in, and sort the results by city and name in ascending order."　

Translation

Select city and stage name from the entertainers table and order by city and stage name

Clean Up

Select city  stage name from  entertainers  order by city  stage name

SQL

SELECT EntCity, EntStageName 
FROM Entertainers 
ORDER BY EntCity ASC, EntStageName ASC

　　CH04_Entertainer_Locations (13 rows)

EntCity

EntStageName

Auburn

Caroline Coie Cuartet

Auburn

Topazz

Bellevue

Jazz Persuasion

Bellevue

Jim Glynn

Bellevue

Susan McLain

Redmond

Carol Peacock Trio

Redmond

JV & the Deep Six

Seattle

Coldwater Cattle Company

Seattle

Country Feeling

Seattle

Julia Schnebly

< < more rows here > >

 

"Give me a unique list of engagement dates. I'm not concerned with how many engagements there are per date."

Translation

Select the distinct start date values from the engagements table

Clean Up

Select  distinct start date  from  engagements 

SQL

SELECT DISTINCT StartDate 
FROM Engagements

　　CH04_Engagement_Dates (64 rows)　

StartDate

2007-09-01

2007-09-10

2007-09-11

2007-09-15

2007-09-17

2007-09-18

2007-09-24

2007-09-29

2007-09-30

2007-10-01

< < more rows here > >

　　教学安排数据库

"Can we view complete class information?"

Translation

Select all columns from the classes table

Clean Up

Select  * from  classes 

SQL

SELECT * 
FROM Classes


CH04_Class_Information (76 rows)　

ClassID

SubjectID

ClassRoomID

Credits

StartTime

Duration

< <other columns> >

1000

11

1231

5

10:00

50

…

1002

12

1619

4

15:30

110

…

1004

13

1627

4

08:00

50

…

1006

13

1627

4

09:00

110

…

1012

14

1627

4

13:00

170

…

1020

15

3404

4

13:00

110

…

1030

16

1231

5

11:00

50

…

1031

16

1231

5

14:00

50

…

1156

37

3443

5

08:00

50

…

1162

37

3443

5

09:00

80

…

< < more rows here > >

 

 

 

 

 

 


"Give me a list of the buildings on campus and the number of floors for each building. Sort the list by building in ascending order."　

Translation

Select building name and number of floors from the buildings table, ordered by building name

Clean Up

Select building name  number of floors from  buildings , order by building name

SQL

SELECT BuildingName, NumberOfFloors
FROM Buildings
ORDER BY BuildingName ASC

　　CH04_Building_List (6 rows)

BuildingName

NumberOfFloors

Arts and Sciences

3

College Center

3

Instructional Building

3

Library

2

PE and Wellness

1

Technology Building

2

 

 

　　保龄球联盟数据库

"Where are we holding our tournaments?"

Translation

Select the distinct tourney location values from the tournaments table

Clean Up

Select  distinct tourney location  from  tournaments

SQL

SELECT DISTINCT TourneyLocation
FROM Tournaments

　　CH04_Tourney_Locations (7 rows)

TourneyLocation

Acapulco Lanes

Bolero Lanes

Imperial Lanes

Red Rooster Lanes

Sports World Lanes

Thunderbird Lanes

Totem Lanes

"Give me a list of all tournament dates and locations. I need the dates in descending order and the locations in alphabetical order."

Translation

Select tourney date and location from the tournaments table and order by tourney date in descending order and location in ascending order

Clean Up

Select tourney date  location from  tournaments order by tourney date  descending  location  ascending

SQL

SELECT TourneyDate, TourneyLocation
FROM Tournaments 
ORDER BY TourneyDate DESC, TourneyLocation ASC

　　CH04_Tourney_Dates (14 rows)

TourneyDate

TourneyLocation

2008-08-15

Totem Lanes

2008-08-08

Imperial Lanes

2008-08-01

Sports World Lanes

2008-07-25

Bolero Lanes

2008-07-18

Thunderbird Lanes

2008-07-11

Red Rooster Lanes

2007-12-04

Acapulco Lanes

2007-11-27

Totem Lanes

2007-11-20

Sports World Lanes

2007-11-13

Imperial Lanes

< < more rows here > >

 

　　食谱数据库

"What types of recipes do we have, and what are the names of the recipes we have for each type? Can you sort the information by type and recipe name?"

Translation

Select recipe class ID and recipe title from the recipes table and order by recipe class ID and recipe title

Clean Up

Select recipe class ID  recipe title from  recipes order by recipeclass ID  recipe title

SQL

SELECT RecipeClassID, RecipeTitle
FROM Recipes
ORDER BY RecipeClassID ASC, RecipeTitle ASC

　　CH04_Recipe_Classes_And_Titles (15 rows)

RecipeClassID

RecipeTitle

1

Fettuccini Alfredo

1

Huachinango Veracruzana 
(Red Snapper, Veracruz style)

1

Irish Stew

1

Pollo Picoso

1

Roast Beef

1

Salmon Filets in Parchment Paper

1

Tourtière 
(French-Canadian Pork Pie)

2

Asparagus

2

Garlic Green Beans

3

Yorkshire Pudding

< < more rows here > >

 

"Show me a list of unique recipe class IDs in the recipes table."

Translation

Select the distinct recipe class ID values from the recipes table

Clean Up

Select  distinct recipe class ID  from  recipes 

SQL

SELECT DISTINCT RecipeClassID 
FROM Recipes

　　CH04_Recipe_Class_Ids(6 rows)　

RecipeClassID

1

2

3

4

5

6

翻译

曾少宁
曾少宁

TechTarget中国特约技术编辑，某高校计算机科学专业教师和网络实验室负责人，曾任职某网络国际厂商，关注数据中心、开发运维、数据库及软件开发技术。有多本关于思科数据中心和虚拟化技术的译著，如《思科绿色数据中心建设与管理》和《基于IP的能源管理》等。

相关推荐