数据库问题求助

系和学生 为 一 对 多关系；

学生和课程 为 多 对 多关系；

E-R图现在没法画，因为我没有软件，5555

(1)求供应红色零件的供应商名字；

select ssname from s ,p ,sp where

ssno = spsno and ppno = sppno

and pcolor = '红色';

(2)求北京供应商的号码、名字和状况(Status)；

select sno,sname,status from s where scity = '北京'

(3)求零件P2的总供应量 (SP表的qty字段是数量吗)

select count(spqty) from p,sp where ppname = 'P2' and ppno = sppno

(4) 把零件P2的重量加5，颜色改为**

update p set Weight = Weight+5 ,color ='**' where ppname = 'P2'

视图是由若干基本表或其他视图构成的表的定义

用户可以用SQL语句对视图和基本表进行查询等 *** 作。在用户角度来看，视图和基本表是一样的，没有区别，都是关系(表格)

在SQL中，视图是外模式一级数据结构的基本单位。它是从一个或几个基本表中导出的表，是从现有基本表中抽取若干子集组成用户的“专用表”。这种构造方式必须使用SQL中的SELECT语句来实现。在定义一个视图时，只是把其定义存放在系统的数据中，而并不直接存储视图对应的数据，直到用户使用视图时才去求得对应的数据

用MySQL测试通过,尽量写标准SQL,如果是其他数据库,应该可以通过或稍加修改即可通过

13

select t2sname from sp t1

left join s as t2 on t1sno=t2sno

left join p t3 on t1pno=t3pno

where t3color='red'

group by t2sname

(1) select SNO '供应商号' from SPJ where JNO = 'J1'

(2) select JJNAME from

SPJ inner join P on SPJSNO = PPNO inner join J on SPJJNO = JJNO

where JCITY = '上海' and PCOLOR = '红色'

(3) select PPNAME,COUNT(SPJQTY) from

SPJ inner join P on SPJSNO = PPNO inner join J on SPJJNO = JJNO

where JJNO = 'J2'

group by PPNAME

(4) select JJNO from

SPJ inner join P on SPJSNO = PPNO and PCOLOR = '红色' inner join J on SPJJNO = JJNO inner join S on SPJSNO = SSNO

where SCITY <> '天津'

(5) select PPNO,COUNT(SPJQTY) 'count' from

SPJ inner join P on SPJSNO = PPNO inner join J on SPJJNO = JJNO

group by PPNO

order by count desc

(6) update P set PCOLOR ='蓝色' where Color = '红色'

(7)

create view view_s

as

select from

SPJ inner join P on SPJSNO = PPNO inner join J on SPJJNO = JJNO inner join S on SPJSNO = SSNO

where SSNO = 'S1'

(8) delete from SPJ where JNO ='J1'

不知对不对，仅供参考~

1)

select ename,addr

from D

where dname='Research'

2)

select Pp#,Pd#,Dmgre#,Eaddr

from E,D,P

where Ppname='Stafford'

and Pd#=Ed#

and Dd#=Pd#

3)

select ename

from E

where Eename='5'

4)

select Pp#

from E,P

where Eename='Smith'

and Ed#=Pd#

5)

select Eename

from E,Depend

where Ee#=Depende#

group by Depende#

having count(name)>=2

6)

select Eename

from E,Depend

where Ese# is not null

and Dependname is not null

and Ee#=Depende#

7)

select Eename

from E,Depend

where Dependname is null

and Ee#=Depend=e#

8)

select ename,salary

from E

where salary in

(select max(salary) from E)

以上就是关于数据库问题求助全部的内容，包括:数据库问题求助、在sql数据库里的视图是什么意思，怎么理解、SQL问题： 供应商：S(SNO，SNAME，CITY，STATUS) 零件：P(PNO，PNAME，WEIGHT，COLOR，CITY)等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！