资源描述
3. 用 SQL 语句建立第二张习题 5 中 4 个表.代码如下：create table s(sno char(10 primary key,sname char(10,status int,citychar(10。create table p(pno char(10 primary key,pname char(10,colorchar(10,weight int。create table j(jno char(10 primary key,jname char(10,city char(10。create table spj(sno char(10,pno char(10,jno char(10,qty int,primary key(sno,pno,jno,foreign key (sno references s(sno,foreign key(pno references p(pno,foreign key (jno references j(jno。insertinto s(sno,sname,status,cityvalues(s1,精益,20,天津。insertinto s(sno,sname,status,cityvalues(s2,盛锡,10,北京。insertinto s(sno,sname,status,cityvalues(s3,东方红,30,北京。insertinto s(sno,sname,status,cityvalues(s4,丰泰盛,20,天津。insertinto s(sno,sname,status,cityvalues(s5,为民,30,上 海。insertinto p(pno,pname,color,weightvalues(p1,螺母,红,12。insertinto p(pno,pname,color,weightvalues(p2,螺栓，绿,17。insertinto p(pno,pname,color,weightvalues(p3，螺丝刀,蓝,14。insertinto p(pno,pname,color,weightvalues(p4,螺丝刀,红,14。insertinto p(pno,pname,color,weightvalues(p5,凸轮,蓝,40。insertinto p(pno,pname,color,weightvalues(p6,齿轮,红,30。insertinto j(jno,jname,cityvalues(j1,三建,北京。insertinto j(jno,jname,cityvalues(j2,一汽,长春。insertinto j(jno,jname,cityvalues(j3,弹簧厂,天津。insertinto j(jno,jname,cityvalues(j4,造船厂，天津。insertinto j(jno,jname,cityvalues(j5,机车厂,唐山。insertinto j(jno,jname,cityvalues(j6,无线电厂,常州。insertinto j(jno,jname,cityvalues(j7,半导体厂,南京。insertinto spj(sno,pno,jno,qty values(s1,p1,j1,200。insertinto spj(sno,pno,jno,qtyvalues(s1,p1,j3,100。insertinto spj(sno,pno,jno,qty values(s1,p1,j4,700。insertinto spj(sno,pno,jno,qty values(s1,p2,j2,100。insertinto spj(sno,pno,jno,qty values(s2,p3,j1,400。insertinto spj(sno,pno,jno,qtyvalues(s2,p3,j2,200。insertinto spj(sno,pno,jno,qty values(s2,p3,j4,500。insertinto spj(sno,pno,jno,qtyvalues(s2,p3,j5,400in sertinto spj(s no,pno,jno ,qty values(s2,p5,j1,400 in sertinto spj(s no,pno,jno ,qty values(s2,p5,j2,100 in sertinto spj(s no,pno,jno ,qty values(s3,p1,j1,200 in sertinto spj(s no,pno,jno ,qty values(s3,p3,j1,200 in sertinto spj(s no,pno,jno ,qty values(s4,p5,j1,100 in sertinto spj(s no,pno,jno ,qty values(s4,p6,j3,300 in sertinto spj(s no,pno,jno ,qty values(s4,p6,j4,200 in sertinto spj(s no,pno,jno ,qty values(s5,p2,j4,100 in sertinto spj(s no,pno,jno ,qty values(s5,p3,j1,200 in sertinto spj(s no,pno,jno ,qty values(s5,p6,j2,200 in sertinto spj(s no,pno,jno ,qty values(s5,p6,j4,500 commit。运行结果如图：snosnansstatus city红盛益锡方泰民精盛东丰対o o o o o o on-on-津京京津海夭北北夭上SILUpi LULLIA-y131plJl2002siplj31003sipl弹7004siP212WO5s2p3jl4006龍p3122007s2弹500FJ就p3J54009s3p5jl40010s2p5J210011s3pljl20012s3p3jl20013盟p5jlWO1/1赴J oone4. 针对上题中建立地四个表试用 SQL 完成第二章习题 5 中地查询.1）、求供应工程 J1 零件地供应商号码 SNO代码如下：select disti net snofrom spjwhere jno=j1。运行结果如图：Jl三建北京j2一汽怅春J3弹簧厂天津j4造船厂夭津J5机车厂唐山J6无线电厂J?半导体厂南用125pname：color1212 3 3 pppppp母栓纱5 p5丘 p6wight721714144030I I代码如下：select snofrom spjwhere jno=j1 and pno=p1。运行结果如图：sno1si23）、求供应工程 J1 零件为红色地供应商号码 SNO代码如下：select disti net snofrom spj,pwhere spj.p no=p.p no and color=红and jno=j1。运行结果如图：Isno1siu。5）、求至少用了供应商 S1 所提供地全部零件地工程号 JNQ代码如下：select jno from spj where sno=s1。运行结果截图:5.针对习题 3 中四个表试用 SQL 完成以下各项操作:1）、找出所有供应商地姓名和所在城市;代码如下：select sn ame,cityfrom s。运行结果如图：cityw北京 北京 天津 上海2）、找出所有零件地名称、颜色、重量；代码如下：select pn ame,color,weight from p。运行结果如图：3）、找出使用供应商 S1 所供应零件地工程号码;代码如下：select jnofrom spjwhere sno=s1。运行结果如图：pnamecolorwm呂17141440304）、找出工程工程 J2 使用地各种零件地名称及数量;代码如下：select pn ame,qtyfrom spj,pwhere spj.p no=p.p no and jno=j2。运行结果如图：12345红盛益锡方泰民精盛东車为刀刀H H栓蛭丝轮轮甥甥螺螺凸齿1212 3 3 4 4 5 5 6 6红绿蓝红蓝红1pnameqty螺栓10022003凸牠1004齿轮2005）、找出上海厂商供应地所有零件号码;代码如下：select pnofrom spj,swhere spj.sno=s.sno and city=上海。运行结果如图：6）、找出上海产地零件地工程名称；代码如下：select jn amefrom spj,s,jwhere spj.s no=s.s no and spj.j no=j.jno and s.city上海。 运行结果如图：jnajiie1造船厂7三建3一汽1造船厂d
展开阅读全文