高分求一道SQL数据库问题,急!!!(在线等)
设某商业集团关于商品销售商品的数据库中有三个基本表:商店SHOP(S#,SNAME,AREA,MGR_NAME)其属性是商店编号,商店名称,区域名,经理姓名商品GOODS...
设某商业集团关于商品销售商品的数据库中有三个基本表:
商店 SHOP(S#,SNAME,AREA,MGR_NAME)
其属性是商店编号,商店名称,区域名,经理姓名
商品 GOODS(G#,GNAME,PRICE)
其属性是商品编号,商品名称,单价
销售 SALE(S#,G#,QUANTITY)
其属性是商店编号,商品编号,销售数量
按要求写出SQL语句
1、设计合适的属性类型,创建SHOP(商店)表。
2、检索区域名为“EAST”的商品名称和经理名称
3、检索销售“冰箱”的商品的编号和商品名称
4、建一个销售数量大于2000的商品视图,属性为(G#,GNAMES,S#,SNAME,QUANTITY,AREA)。 展开
商店 SHOP(S#,SNAME,AREA,MGR_NAME)
其属性是商店编号,商店名称,区域名,经理姓名
商品 GOODS(G#,GNAME,PRICE)
其属性是商品编号,商品名称,单价
销售 SALE(S#,G#,QUANTITY)
其属性是商店编号,商品编号,销售数量
按要求写出SQL语句
1、设计合适的属性类型,创建SHOP(商店)表。
2、检索区域名为“EAST”的商品名称和经理名称
3、检索销售“冰箱”的商品的编号和商品名称
4、建一个销售数量大于2000的商品视图,属性为(G#,GNAMES,S#,SNAME,QUANTITY,AREA)。 展开
3个回答
展开全部
以下SQL语句在SQL Server 2005 Express中测试通过
第一题:CREATE TABLE SHOP
(
S# bigint NOT NULL,
SName nvarchar(50) NOT NULL,
Area nvarchar(50) NOT NULL,
MGR_NAME nvarchar(50) NOT NULL
) ;
第二题:
SELECT GNAME, MGR_NAME FROM SHOP, GOODS, SALE
WHERE GOODS.G#=SALE.G# and SALE.S#=SHOP.S# and SHOP.AREA="EAST"
第三题:
SELECT G#, GNAME FROM GOODS where GNAME like '冰箱'
第四题
CREATE VIEW 商品视图 AS
SELECT G#,GNAMES, S#, SNAME, QUANTITY, AREA
FROM SHOP, GOODS, SALE
WHERE QUANTITY>2000 and GOODS.G#=SALE.G# and SALE.S#=SHOP.S#
第一题:CREATE TABLE SHOP
(
S# bigint NOT NULL,
SName nvarchar(50) NOT NULL,
Area nvarchar(50) NOT NULL,
MGR_NAME nvarchar(50) NOT NULL
) ;
第二题:
SELECT GNAME, MGR_NAME FROM SHOP, GOODS, SALE
WHERE GOODS.G#=SALE.G# and SALE.S#=SHOP.S# and SHOP.AREA="EAST"
第三题:
SELECT G#, GNAME FROM GOODS where GNAME like '冰箱'
第四题
CREATE VIEW 商品视图 AS
SELECT G#,GNAMES, S#, SNAME, QUANTITY, AREA
FROM SHOP, GOODS, SALE
WHERE QUANTITY>2000 and GOODS.G#=SALE.G# and SALE.S#=SHOP.S#
展开全部
1.
create table shop
(
S# int not null,
SNAME varchar(50) null,
AREA varchar(200) null,
MGR_NAME varchar(10) null,
primary key (S#)
)
2.
select SNAME,MGR_NAME from SHOP where AREA='EAST'
3.
select G#,PRICE from GOODS where GNAME='冰箱'
4
create view shop_goods_sale
as
select b.G#,b..GNAME,c.S#,a.SNAME,c.QUANTITY,a.AREA from SHOP a,GOODS b,SALE c
where a.S#=c.S# and b.G#=c.G# and c.QUANTITY>2000
create table shop
(
S# int not null,
SNAME varchar(50) null,
AREA varchar(200) null,
MGR_NAME varchar(10) null,
primary key (S#)
)
2.
select SNAME,MGR_NAME from SHOP where AREA='EAST'
3.
select G#,PRICE from GOODS where GNAME='冰箱'
4
create view shop_goods_sale
as
select b.G#,b..GNAME,c.S#,a.SNAME,c.QUANTITY,a.AREA from SHOP a,GOODS b,SALE c
where a.S#=c.S# and b.G#=c.G# and c.QUANTITY>2000
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
1.SHOP表的属性的数据类型都可以为varchar(20)就行了的。
2.select GNAME, MGR_NAME
from SHOP, GOODS, SALE
where GOODS.G#=SALE.G# and SALE.S#=SHOP.S#
and SHOP.AREA='EAST';
3.select G#,GNAME
from GOODS
where GNAME='冰箱';
4.create view GOODS_VIEW
as
select GOODS.G#,GOODS.GNAME,SHOP.S#,SHOP.SNAME,SALE.QUANTITY,SHOP.AREA
from SHOP,GOODS,SALE
where SHOP.S#=SALE.S# and GOODS.G#=SALE.G#
and SALE.QUANTITY>2000
2.select GNAME, MGR_NAME
from SHOP, GOODS, SALE
where GOODS.G#=SALE.G# and SALE.S#=SHOP.S#
and SHOP.AREA='EAST';
3.select G#,GNAME
from GOODS
where GNAME='冰箱';
4.create view GOODS_VIEW
as
select GOODS.G#,GOODS.GNAME,SHOP.S#,SHOP.SNAME,SALE.QUANTITY,SHOP.AREA
from SHOP,GOODS,SALE
where SHOP.S#=SALE.S# and GOODS.G#=SALE.G#
and SALE.QUANTITY>2000
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询
广告 您可能关注的内容 |