目录
10-127 A4-3在订单表中查找特定国家且平均运费不小于10的信息
10-125 A4-1查找订单表中每位顾客的平均运费
分数 20
全屏浏览题目
切换布局
作者 柯海丰
单位 浙大城市学院
查询订单表(orders)
中每位顾客的平均运费
,结果显示为顾客编号(CustomerID)
和平均运费(avgFreight)
提示:请使用SELECT语句作答。
表结构:
列名 | 数据类型 | 长度 | 主码 | 说明 |
---|---|---|---|---|
OrderID | int | 11 | √ | 订单编号 |
CustomerID | varchar | 5 | 顾客编号 | |
EmployeeID | int | 11 | 员工编号 | |
OrderDate | datetime, | 订购日期 | ||
RequiredDate | datetime, | 预计到达日期 | ||
ShippedDate | datetime, | 发货日期 | ||
ShipVia | int | 11 | 运货商 | |
Freight | decimal | 10,4 | 运费 | |
ShipName | varchar | 40 | 货主姓名 | |
ShipAddress | varchar | 60 | 货主地址 | |
ShipCity | varchar | 15 | 所在城市 | |
ShipRegion | varchar | 15 | 区域 | |
ShipPostalCode | varchar | 10 | 邮政编码 | |
ShipCountry | varchar | 15 | 所在国家 |
表样例
请在这里给出上述表结构对应的表样例。例如
orders
表:
OrderID | CustomerID | EmployeeID | OrderDate | RequiredDate | ShippedDate | ShipVia | Freight | ShipName | ShipAddress | ShipCity | ShipRegion | ShipPostalCode | ShipCountry |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
10248 | VINET | 5 | 1996/7/4 00:00:00 | 1996/8/1 00:00:00 | 1996/7/16 00:00:00 | 3 | 32.3800 | Vins et alcools Chevalier | 59 rue de l-Abbaye | Reims | 51100 | France | |
10249 | TOMSP | 6 | 1996/7/5 00:00:00 | 1996/8/16 00:00:00 | 1996/7/10 00:00:00 | 1 | 11.6100 | Toms Spezialitten | Luisenstr. 48 | Mnster | 44087 | Germany | |
10250 | HANAR | 4 | 1996/7/8 00:00:00 | 1996/8/5 00:00:00 | 1996/7/12 00:00:00 | 2 | 65.8300 | Hanari Carnes | Rua do Pao, 67 | Rio de Janeiro | RJ | 05454-876 | Brazil |
10251 | VICTE | 3 | 1996/7/8 00:00:00 | 1996/8/5 00:00:00 | 1996/7/15 00:00:00 | 1 | 41.3400 | Victuailles en stock | 2, rue du Commerce | Lyon | 69004 | France | |
10252 | SUPRD | 4 | 1996/7/9 00:00:00 | 1996/8/6 00:00:00 | 1996/7/11 00:00:00 | 2 | 51.3000 | Suprmes dlices | Boulevard Tirou, 255 | Charleroi | B-6000 | Belgium | |
10253 | HANAR | 3 | 1996/7/10 00:00:00 | 1996/7/24 00:00:00 | 1996/7/16 00:00:00 | 2 | 58.1700 | Hanari Carnes | Rua do Pao, 67 | Rio de Janeiro | RJ | 05454-876 | Brazil |
10254 | CHOPS | 5 | 1996/7/11 00:00:00 | 1996/8/8 00:00:00 | 1996/7/23 00:00:00 | 2 | 22.9800 | Chop-suey Chinese | Hauptstr. 31 | Bern | 3012 | Switzerland | |
10255 | RICSU | 9 | 1996/7/12 00:00:00 | 1996/8/9 00:00:00 | 1996/7/15 00:00:00 | 3 | 148.3300 | Richter Supermarkt | Starenweg 5 | Genve | 1204 | Switzerland | |
10256 | WELLI | 3 | 1996/7/15 00:00:00 | 1996/8/12 00:00:00 | 1996/7/17 00:00:00 | 2 | 13.9700 | Wellington Importadora | Rua do Mercado, 12 | Resende | SP | 08737-363 | Brazil |
10257 | HILAA | 4 | 1996/7/16 00:00:00 | 1996/8/13 00:00:00 | 1996/7/22 00:00:00 | 3 | 81.9100 | HILARION-Abastos | Carrera 22 con Ave. Carlos Soublette #8-35 | San Cristbal | Tchira | 5022 | Venezuela |
输出样例:
CustomerID | avgFreight |
---|---|
CHOPS | 22.98000000 |
HANAR | 62.00000000 |
HILAA | 81.91000000 |
RICSU | 148.33000000 |
SUPRD | 51.30000000 |
TOMSP | 11.61000000 |
VICTE | 41.34000000 |
VINET | 32.38000000 |
WELLI | 13.97000000 |
select CustomerID,avg(Freight) avgFreight
from orders group by CustomerID
10-126 A4-2统计顾客表中每个国家的顾客数量
分数 20
全屏浏览题目
切换布局
作者 柯海丰
单位 浙大城市学院
查询出顾客表(customers)
中查询每个国家的客户数量,显示为国家(Country)
和客户数量(custCount)
提示:请使用SELECT语句作答。
表结构:
列名 | 数据类型 | 长度 | 主码 | 说明 |
---|---|---|---|---|
CustomerID | varchar | 5 | √ | 顾客编号 |
CompanyName | varchar | 40 | 公司名称 | |
ContactName | varchar | 30 | 联系姓名 | |
ContactTitle | varchar | 30 | 联系头衔 | |
Address | varchar | 60 | 地址 | |
City | varchar | 15 | 城市 | |
Region | varchar | 15 | 区域 | |
PostalCode | varchar | 10 | 邮政编码 | |
Country | varchar | 15 | 国家 | |
Phone | varchar | 24 | 电话 | |
Fax | varchar | 24 | 传真 |
表样例
customers
表:
CustomerID | CompanyName | ContactName | ContactTitle | Address | City | Region | PostalCode | Country | Phone | Fax |
---|---|---|---|---|---|---|---|---|---|---|
ALFKI | Alfreds Futterkiste | Maria Anders | Sales Representative | Obere Str. 57 | Berlin | 12209 | Germany | 030-0074321 | 030-0076545 | |
ANATR | Ana Trujillo Emparedados y helados | Ana Trujillo | Owner | Avda. de la Constitucin 2222 | Mxico D.F. | 05021 | Mexico | (5) 555-4729 | (5) 555-3745 | |
ANTON | Antonio Moreno Taquera | Antonio Moreno | Owner | Mataderos 2312 | Mxico D.F. | 05023 | Mexico | (5) 555-3932 | ||
AROUT | Around the Horn | Thomas Hardy | Sales Representative | 120 Hanover Sq. | London | WA1 1DP | UK | (171) 555-7788 | (171) 555-6750 | |
BERGS | Berglunds snabbkp | Christina Berglund | Order Administrator | Berguvsvgen 8 | Lule | S-958 22 | Sweden | 0921-12 34 65 | 0921-12 34 67 | |
BLAUS | Blauer See Delikatessen | Hanna Moos | Sales Representative | Forsterstr. 57 | Mannheim | 68306 | Germany | 0621-08460 | 0621-08924 | |
BLONP | Blondesddsl pre et fils | Frdrique Citeaux | Marketing Manager | 24, place Klber | Strasbourg | 67000 | France | 88.60.15.31 | 88.60.15.32 | |
BOLID | Blido Comidas preparadas | Martn Sommer | Owner | C/ Araquil, 67 | Madrid | 28023 | Spain | (91) 555 22 82 | (91) 555 91 99 | |
BONAP | Bon app' | Laurence Lebihan | Owner | 12, rue des Bouchers | Marseille | 13008 | France | 91.24.45.40 | 91.24.45.41 | |
BOTTM | Bottom-Dollar Markets | Elizabeth Lincoln | Accounting Manager | 23 Tsawassen Blvd. | Tsawassen | BC | T2F 8M4 | Canada | (604) 555-4729 | (604) 555-3745 |
输出样例:
Country | custCount |
---|---|
Canada | 1 |
France | 2 |
Germany | 2 |
Mexico | 2 |
Spain | 1 |
Sweden | 1 |
UK | 1 |
select Country,count(CustomerID) custCount
from customers group by Country
10-127 A4-3在订单表中查找特定国家且平均运费不小于10的信息
分数 20
全屏浏览题目
切换布局
作者 柯海丰
单位 浙大城市学院
查询订单表(orders)
中查找所在国家(ShipCountry)
为Belgium
,Switzerland
,且平均运费不小于10的的顾客及运费信息,结果显示为顾客编号(CustomerID)
和平均运费(avgFreight)
提示:请使用SELECT语句作答。
表结构:
列名 | 数据类型 | 长度 | 主码 | 说明 |
---|---|---|---|---|
OrderID | int | 11 | √ | 订单编号 |
CustomerID | varchar | 5 | 顾客编号 | |
EmployeeID | int | 11 | 员工编号 | |
OrderDate | datetime, | 订购日期 | ||
RequiredDate | datetime, | 预计到达日期 | ||
ShippedDate | datetime, | 发货日期 | ||
ShipVia | int | 11 | 运货商 | |
Freight | decimal | 10,4 | 运费 | |
ShipName | varchar | 40 | 货主姓名 | |
ShipAddress | varchar | 60 | 货主地址 | |
ShipCity | varchar | 15 | 所在城市 | |
ShipRegion | varchar | 15 | 区域 | |
ShipPostalCode | varchar | 10 | 邮政编码 | |
ShipCountry | varchar | 15 | 所在国家 |
表样例
请在这里给出上述表结构对应的表样例。例如
orders
表:
OrderID | CustomerID | EmployeeID | OrderDate | RequiredDate | ShippedDate | ShipVia | Freight | ShipName | ShipAddress | ShipCity | ShipRegion | ShipPostalCode | ShipCountry |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
10248 | VINET | 5 | 1996/7/4 00:00:00 | 1996/8/1 00:00:00 | 1996/7/16 00:00:00 | 3 | 32.3800 | Vins et alcools Chevalier | 59 rue de l-Abbaye | Reims | 51100 | France | |
10249 | TOMSP | 6 | 1996/7/5 00:00:00 | 1996/8/16 00:00:00 | 1996/7/10 00:00:00 | 1 | 11.6100 | Toms Spezialitten | Luisenstr. 48 | Mnster | 44087 | Germany | |
10250 | HANAR | 4 | 1996/7/8 00:00:00 | 1996/8/5 00:00:00 | 1996/7/12 00:00:00 | 2 | 65.8300 | Hanari Carnes | Rua do Pao, 67 | Rio de Janeiro | RJ | 05454-876 | Brazil |
10251 | VICTE | 3 | 1996/7/8 00:00:00 | 1996/8/5 00:00:00 | 1996/7/15 00:00:00 | 1 | 41.3400 | Victuailles en stock | 2, rue du Commerce | Lyon | 69004 | France | |
10252 | SUPRD | 4 | 1996/7/9 00:00:00 | 1996/8/6 00:00:00 | 1996/7/11 00:00:00 | 2 | 51.3000 | Suprmes dlices | Boulevard Tirou, 255 | Charleroi | B-6000 | Belgium | |
10253 | HANAR | 3 | 1996/7/10 00:00:00 | 1996/7/24 00:00:00 | 1996/7/16 00:00:00 | 2 | 58.1700 | Hanari Carnes | Rua do Pao, 67 | Rio de Janeiro | RJ | 05454-876 | Brazil |
10254 | CHOPS | 5 | 1996/7/11 00:00:00 | 1996/8/8 00:00:00 | 1996/7/23 00:00:00 | 2 | 22.9800 | Chop-suey Chinese | Hauptstr. 31 | Bern | 3012 | Switzerland | |
10255 | RICSU | 9 | 1996/7/12 00:00:00 | 1996/8/9 00:00:00 | 1996/7/15 00:00:00 | 3 | 148.3300 | Richter Supermarkt | Starenweg 5 | Genve | 1204 | Switzerland | |
10256 | WELLI | 3 | 1996/7/15 00:00:00 | 1996/8/12 00:00:00 | 1996/7/17 00:00:00 | 2 | 13.9700 | Wellington Importadora | Rua do Mercado, 12 | Resende | SP | 08737-363 | Brazil |
10257 | HILAA | 4 | 1996/7/16 00:00:00 | 1996/8/13 00:00:00 | 1996/7/22 00:00:00 | 3 | 81.9100 | HILARION-Abastos | Carrera 22 con Ave. Carlos Soublette #8-35 | San Cristbal | Tchira | 5022 | Venezuela |
输出样例:
|CustomerID|avgFreight|
|CHOPS|22.98000000|
|RICSU|148.33000000|
|SUPRD|51.30000000|
select CustomerID,avg(Freight) avgFreight
from orders
where ShipCountry in ('Belgium','Switzerland')
group by CustomerID
having avg(Freight)>=10