分数 20

全屏浏览题目

切换布局

作者 柯海丰

单位 浙大城市学院

查找提供产品的各个供应商名称及其供应的产品,显示为:产品编号(ProductID)产品名称(ProductName)供应商编号(SupplierID)公司名称(CompanyName)

提示:请使用SELECT语句作答。

表结构:

products

列名 数据类型 长度 主码 说明
ProductID int 11 产品编号
ProductName varchar 40 产品名称
SupplierID int 11 供应商编号
CategoryID int 11 种类编号
QuantityPerUnit varchar 20 数量
UnitPrice decimal 10,4 单价
UnitsInStock smallint 2 库存数量
UnitsOnOrder smallint 2 订购数量
ReorderLevel smallint 2 再次订购量
Discontinued bit 1 中止

suppliers

列名 数据类型 长度 主码 说明
SupplierID int 11 供应商编号
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 传真
HomePage mediumtext, 主页

表样例

products表:

ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
1 Chai 1 1 10 boxes x 20 bags 18 39 0 10
2 Chang 1 1 24 - 12 oz bottles 19 17 40 25
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10 13 70 25
4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22 53 0 0
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35 0 0 0
6 Grandma's Boysenberry Spread 3 2 12 - 8 oz jars 25 120 0 25
7 Uncle Bob's Organic Dried Pears 3 7 12 - 1 lb pkgs. 30 15 0 10
8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40 6 0 0
9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97 29 0 0
10 Ikura 4 8 12 - 200 ml jars 31 31 0 0

suppliers表:

SupplierID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax HomePage
1 Exotic Liquids Charlotte Cooper Purchasing Manager 49 Gilbert St. London EC1 4SD UK (171) 555-2222
2 New Orleans Cajun Delights Shelley Burke Order Administrator P.O. Box 78934 New Orleans LA 70117 USA (100) 555-4822 #CAJUN.HTM#
3 Grandma Kelly's Homestead Regina Murphy Sales Representative 707 Oxford Rd. Ann Arbor MI 48104 USA (313) 555-5735 (313) 555-3349
4 Tokyo Traders Yoshi Nagase Marketing Manager 9-8 SekimaiMusashino-shi Tokyo 100 Japan (03) 3555-5011
5 Cooperativa de Quesos 'Las Cabras' Antonio del Valle Saavedra Export Administrator Calle del Rosal 4 Oviedo Asturias 33007 Spain (98) 598 76 54
6 Mayumi's Mayumi Ohno Marketing Representative 92 Setsuko Chuo-ku Osaka 545 Japan (06) 431-7877 Mayumi's (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/mayumi.htm#
7 Pavlova, Ltd. Ian Devling Marketing Manager 74 Rose St. Moonie Ponds Melbourne Victoria 3058 Australia (03) 444-2343 (03) 444-6588
8 Specialty Biscuits, Ltd. Peter Wilson Sales Representative 29 King's Way Manchester M14 GSD UK (161) 555-4448
9 PB Knckebrd AB Lars Peterson Sales Agent Kaloadagatan 13 Gteborg S-345 67 Sweden 031-987 65 43 031-987 65 91
10 Refrescos Americanas LTDA Carlos Diaz Marketing Manager Av. das Americanas 12.890 So Paulo 5442 Brazil (11) 555 4640

输出样例:

ProductID ProductName SupplierID CompanyName
3 Aniseed Syrup 1 Exotic Liquids
2 Chang 1 Exotic Liquids
1 Chai 1 Exotic Liquids
4 Chef Anton's Cajun Seasoning 2 New Orleans Cajun Delights
5 Chef Anton's Gumbo Mix 2 New Orleans Cajun Delights
8 Northwoods Cranberry Sauce 3 Grandma Kelly's Homestead
7 Uncle Bob's Organic Dried Pears 3 Grandma Kelly's Homestead
6 Grandma's Boysenberry Spread 3 Grandma Kelly's Homestead
10 Ikura 4 Tokyo Traders
9 Mishi Kobe Niku 4 Tokyo Traders
select products.ProductID,products.ProductName,
products.SupplierID,
suppliers.CompanyName
from products
join suppliers on suppliers.SupplierID=products.SupplierID

 

Logo

有“AI”的1024 = 2048,欢迎大家加入2048 AI社区

更多推荐