10-250 B1-2查询供应商及其供应的产品情况

分数 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