java测试题.docx
- 文档编号:6400727
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:10
- 大小:17.70KB
java测试题.docx
《java测试题.docx》由会员分享,可在线阅读,更多相关《java测试题.docx(10页珍藏版)》请在冰豆网上搜索。
java测试题
--题1
deletefromt_userwhereidnotin(selectmax(id)fromt_usergroupbyname);
--题4
deletefromstudentwhereidnotin(selectmin(id)fromstudentgroupbystu_id,uName,course,score);
--题5
createtablestuasselect*fromstudentswhere1=2;
--题6
insertintostuselect*fromstudents;
--题7
selectcourseid,coursename,score,decode(sign(score-60),-1,'fail','pass')mark
fromcssss
--题1
selectp.projectno,min(p.projectname),sum(c.conmoney)fromprojectpleftjoincontractconp.projectno=c.projectno
groupbyp.projectno
--题2
selectc.projectno,sum(pay.paymoney)fromcontractcleftjoinpaymentpayonc.contractno=pay.contractno
groupbyc.projectno
--题3
selectc.projectno,min(p.projectname),sum(pay.paymoney)fromcontractc
leftjoinpaymentpayonc.contractno=pay.contractno
leftjoinprojectponc.projectno=p.projectno
groupbyc.projectno
--不符合要求
selectc.projectno,min(p.projectname),sum(c.conmoney),sum(pay.paymoney)fromcontractc
leftjoinpaymentpayonc.contractno=pay.contractno
leftjoinprojectponc.projectno=p.projectno
groupbyc.projectno
--题4
selectu.pno,u.pn,to_char(n.cm,'999999999999999999999999999.000'),u.pmfrom
(
selectc.projectnopno,min(p.projectname)pn,sum(pay.paymoney)pmfromcontractc
leftjoinpaymentpayonc.contractno=pay.contractno
leftjoinprojectponc.projectno=p.projectno
groupbyc.projectno
)u,
(
selectp.projectnopno,min(p.projectname),sum(c.conmoney)cmfromprojectpleftjoincontractconp.projectno=c.projectno
groupbyp.projectno
)nwhereu.pno=n.pno
一、表USER
id(int)name(varchar2)
1a
2b
3c
4a
5b
6c
要求把name中相同的字段删去,只留下相同字段中id最大那条记录,即:
id(int)name(varchar2)
4a
5b
6c
要求用一条DELETE语句,
deletefromuser1whereidin(selecta.idfromuser1a,user1bwherea.id ; 二、用一条SQL语句查询出每门课都大于80分的学生姓名 namekechengfenshu 张三语文81 张三数学75 李四语文76 李四数学90 王五语文81 王五数学100 王五英语90 selectname,min(fenshu)fromstudentgroupbynamehavingmin(fenshu)>80; 三、一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球对,现在四个球对进行比赛,用一条sql语句显示所有可能的比赛组合.Name=team selectdistinctu1.name,u2.namefromuser1u1,user1u2whereu1.name 四、Student表如下: (删除重复数据) Id(自动编号)(stu_id)学号(uName)姓名(course)课程名称(score)分数 12005001张三数学69 22005002李四数学89 32005001张三数学69 删除除了自动编号不同,其他都相同的学生冗余信息 deletefromstudentwhere(selects.rowidfromstudents,studentsswheres.stu_id=ss.stu_idands.stu_rowid>ss.stu_rowid)=student.rowid; 五、复制表(只复制结构,源表名: student新表名: stu) Createtablestuasselect*fromstudentwhere1=2; 六、表stu1已存在,拷贝表(拷贝数据,源表名: student目标表名: stu1) Insertintostu1asselect*fromstudent; 七、原表: courseidcoursenamescore ------------------------------------- 1java70 2oracle90 3xml40 4jsp30 5servlet80 ------------------------------------- 为了便于阅读,查询此表后的结果显式如下(及格分数为60): courseidcoursenamescoremark --------------------------------------------------- 1java70pass 2oracle90pass 3xml40fail 4jsp30fail 5servlet80pass --------------------------------------------------- 写出此查询语句 问题说明: 有三个表,项目表、合同表、付款表 一个项目可能会有签署多个合同,每个合同会分几次付款。 --下面是建立表的语句 项目表(项目编号,项目名称) 1项目1 2项目2 3项目3 合同表(合同编号,项目编号,合同金额) 111000 211500 322000 付款表(付款编号,合同编号,付款金额) 11100 22200 32800 问题 (一): 设计一个查询,要求返回结果如下: ---------------------------------------------------------- 项目编号项目名称项目所有合同的金额 -------------------------------------------------------- 1项目12500 2项目22000 3项目3NULL selectp.id合同金额,p.pjname合同名称,my合同金额frompjpleftjoin (selectpjid,sum(money)myfrombgbgroupbypjid)bbonbb.pjid=p.id; 问题 (二): 设计一个查询,要求返回结果如下: -------------------------------------- 项目编号项目所有合同已付款金额 ------------------------------------- 11100 2NULL selectpjid项目编号,sum(sum1)已付合同金额frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.idhavingp1.id=t.pjid; -------------------------------------- 问题(三) 设计一个查询,要求返回结果如下: 项目编号项目名称项目所有合同已付款金额 --------------------------------------------------- 1项目11100 2项目2NULL selectpjid项目编号,pjname项目名称,sum(sum1)已付合同金额frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.id,pjnamehavingp1.id=t.pjidorderbypjid; selectpjid项目编号,pjname项目名称,my合同金额,a已付金额from view_summoney4pp,view_summoney3pppwhereppp.pjid=pp.id; select*fromview_summoney; select*fromview_summoney1; select*fromview_summoney2; select*fromview_summoney3; select*fromview_summoney4; ALTERviewview_n_goodsrenamecolumngoodsidtomyid; createorreplaceviewview_summoneyas selectpjid,sum(money)myfrombgbgroupbypjid; createorreplaceviewview_summoney1as selectbgid,sum(paymoney)sum1frompaypgroupbybgid; createorreplaceviewview_summoney2as selectpjid,b.bgid,sum1frombgbleftjoinview_summoney1pay1onb.bgid=pay1.bgid; createorreplaceviewview_summoney3as selectpjid,sum(sum1)afrompjp1,view_summoney2tgroupbypjid,p1.idhavingp1.id=t.pjid; createorreplaceviewview_summoney4as selectp.id,p.pjname,myfrompjp,view_summoneybbwherebb.pjid=p.id; 问题(四): 请您设计一个查询语句,检索的格式如下: ------------------------------------------------------------------------ 项目编号项目名称项目所有合同的金额项目所有合同已付款金额 ------------------------------------------------------------------------ 1项目12500.0001100.000 2项目22000.000NULL selectpjid项目编号,pjname项目名称,my合同金额,nvl(to_char(a),'NULL')已付金额from ( selectp.id,p.pjname,myfrompjp, (selectpjid,sum(money)myfrombgbgroupbypjid)bbwherebb.pjid=p.id )pp, (selectpjid,sum(sum1)afrompjp1, (selectpjid,b.bgid,sum1frombgb leftjoin (selectbgid,sum(paymoney)sum1frompaypgroupbybgid )pay1 on b.bgid=pay1.bgid )tgroupbypjid,p1.idhavingp1.id=t.pjid )ppp whereppp.pjid=pp.id; select*from ( selectp.id,p.pjname,myfrompjp,(selectpjid,sum(money)myfrombgbgroupbypjid)bbwherebb.pjid=p.id)pp, (selectpjid,sum(sum1)frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.idhavingp1.id=t.pjid)pppwhereppp.pjid=pp.id; createtablepj(pjidnumber,pjnamevarchar2(12)); createtablebg(bgidnumber,pjidvarchar2(12),moneynumber); createtablepay(payidnumber,bgidnumber,paymoneynumber); select*frompj; select*frombg; select*frompay; selectp.id,p.pjname,b.sum(money)frombgb,pjpgroupbyb.pjidhavingp.id=b.pjid; selectp.id,p.pjname,afrompjp, (selectpjid,sum(money)afrombgbgroupbypjid)bbwherebb.pjid=p.id; select*from( selectp.id,p.pjname,myfrompjp, (selectpjid,sum(money)myfrombgbgroupbypjid)bbwherebb.pjid=p.id)pp, (selectpjid,sum(sum1)frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.idhavingp1.id=t.pjid)pppwhereppp.pjid=pp.id; selectp.id,p.pjname,afrompjp, (selectpjid,sum(money)afrombgbgroupbypjid)bbwherebb.pjid=p.id; selectpjid,sum(sum1)frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.idhavingp1.id=t.pjid; 问题(四): 请您设计一个查询语句,检索的格式如下: ------------------------------------------------------------------------ 项目编号项目名称项目所有合同的金额项目所有合同已付款金额 ------------------------------------------------------------------------ 1项目12500.0001100.000 2项目22000.000NULL ----------------------------------------------- 项目表(项目编号,项目名称) 1项目1 2项目2 3项目3 合同表(合同编号,项目编号,合同金额) 111000 211500 322000 付款表(付款编号,合同编号,付款金额) 11100 22200 32800 selectpjid项目编号,sum(sum1)已付合同金额frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.idhavingp1.id=t.pjid; selectpjid项目编号,pjname项目名称,sum(sum1)已付合同金额frompjp1,( selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid)tgroupbypjid,p1.id,pjnamehavingp1.id=t.pjidorderbypjid; selectpjid,b.bgid,sum1frombgbleftjoin( selectbgid,sum(paymoney)sum1frompaypgroupbybgid)pay1 onb.bgid=pay1.bgid; select*frompj; selectbgid,sum(paymoney)sum1frompaypgroupbybgid; select*frombg; select*frompay; select*frompj; select*frombg;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- java 测试