1.删除重复记录(当表中无主键时)
- createtableTESTTB(
- bmvarchar(4),
- mcvarchar2(20)
- )
- insertintoTESTTBvalues(1,'aaaa');
- insertintoTESTTBvalues(1,'aaaa');
- insertintoTESTTBvalues(2,'bbbb');
- insertintoTESTTBvalues(2,'bbbb');
- /*方案一*/
- deletefromTESTTBwhererowidnotin
- (selectmax(rowid)fromTESTTBgroupbyTESTTB.BM,TESTTB.MC)
- /*方案二*/
- deletefromTESTTBawherea.rowid!=(
- selectmax(rowid)fromTESTTBbwherea.bm=b.bmanda.mc=b.mc
- )
2.bookEnrol是用来登记的,不管你是借还是还,都要添加一条记录。
请写一个SQL语句,获取到现在状态为已借出的所有图书的相关信息,
ID为3的java书,由于以归还,所以不要查出来。要求查询结果应为:(被借出的书和被借出的日期)
- createtablebook(
- idint,
- namevarchar2(30),
- PRIMARYKEY(id)
- )
- insertintobookvalues(1,'English');
- insertintobookvalues(2,'Math');
- insertintobookvalues(3,'JAVA');
- createtablebookEnrol(
- idint,
- bookIdint,
- dependDatedate,
- stateint,
- FOREIGNKEY(bookId)REFERENCESbook(id)ONDELETECASCADE
- )
- insertintobookEnrolvalues(1,1,to_date('2009-01-02','yyyy-mm-dd'),1);
- insertintobookEnrolvalues(2,1,to_date('2009-01-12','yyyy-mm-dd'),2);
- insertintobookEnrolvalues(3,2,to_date('2009-01-14','yyyy-mm-dd'),1);
- insertintobookEnrolvalues(4,1,to_date('2009-01-17','yyyy-mm-dd'),1);
- insertintobookEnrolvalues(5,2,to_date('2009-02-14','yyyy-mm-dd'),2);
- insertintobookEnrolvalues(6,2,to_date('2009-02-15','yyyy-mm-dd'),1);
- insertintobookEnrolvalues(7,3,to_date('2009-02-18','yyyy-mm-dd'),1);
- insertintobookEnrolvalues(8,3,to_date('2009-02-19','yyyy-mm-dd'),2);
- /*方案一*/
- selecta.id,a.name,b.dependdatefrombooka,bookenrolbwhere
- a.id=b.bookid
- and
- b.dependdatein(selectmax(dependdate)frombookenrolgroupbybookid)
- andb.state=1
- /*方案二*/
- selectk.id,k.name,a.dependdate
- frombookenrola,BOOKk
- wherea.idin(selectmax(b.id)frombookenrolbgroupbyb.bookid)
- anda.state=1
- anda.bookid=k.id;
3.查询每年销量最多的产品的相关信息
- createtablet2(
- year_varchar2(4),
- productvarchar2(4),
- salenumber
- )
- insertintot2values('2005','a',700);
- insertintot2values('2005','b',550);
- insertintot2values('2005','c',600);
- insertintot2values('2006','a',340);
- insertintot2values('2006','b',500);
- insertintot2values('2007','a',220);
- insertintot2values('2007','b',350);
- insertintot2values('2007','c',350);
- /**方案一*/
- selecta.year_,a.sale,a.productfromt2ainnerjoin(
- selectmax(sale)asslfromt2groupbyyear_)b
- ona.sale=b.slorderbya.year_
- /*方案二*/
- selectsa.year_,sa.product,sa.sale
- fromt2sa,
- (selectt.year_pye,max(t.sale)maxcout
- fromt2t
- groupbyt.year_)tmp
- wheresa.year_=tmp.pye
- andsa.sale=tmp.maxcout
4.排序问题,如果用总积分做降序排序..因为总积分是字符型,所以排出来是这样子(9,8,7,6,5...),要求按照总积分的数字大小排序。
- createtablet4(
- 姓名varchar2(20),
- 月积分varchar2(20),
- 总积分char(3)
- )
- insertintot4values('WhatIsJava','1','99');
- insertintot4values('水王','76','981');
- insertintot4values('新浪网','65','96');
- insertintot4values('牛人','22','9');
- insertintot4values('中国队','64','89');
- insertintot4values('信息','66','66');
- insertintot4values('太阳','53','66');
- insertintot4values('中成药','11','33');
- insertintot4values('西洋参','257','26');
- insertintot4values('大拿','33','23');
- /*方案一*/
- select*fromt4orderbycast(总积分asint)desc
- /*方案二*/
- select*fromt4orderbyto_number(总积分)desc;
5.得出所有人(不区分人员)每个月及上月和下月的总收入
- createtablet5(tmonthint,
- tnamevarchar2(10),
- incomenumber
- )
- insertintot5values('08','a',1000);
- insertintot5values('09','a',2000);
- insertintot5values('10','a',3000);
- /*方案一*/
- selecto.tmonth,sum(o.income)ascur,(selectsum(t.income)fromt5twheret.tmonth=(o.tmonth+1)groupbyt.tmonth)asnext,
- (selectsum(t.income)fromt5twheret.tmonth=(o.tmonth-1)groupbyt.tmonth)aslast
- fromt5owhereo.tmonth=2groupbyo.tmonth
- /*方案二*/
- selecttmonthas月份,tnameas姓名,sum(income)as当月工资,
- (selectsum(income)
- fromt5
- wheretmonth=to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))-1)AS上月工资,
- (selectsum(income)
- fromt5
- wheretmonth=to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))+1)AS下月工资
- fromt5wheretmonth=substr(to_char(sysdate,'yyyy-mm-dd'),7,1)
- groupbytmonth,tname
6.根据现有的学生表,课程表,选课关系表,查询一。没有修过李明老师的课的学生,查询二,既学过a课程,又学过b课程的学生姓名
- S表[SNO,SNAME]--学生表
- C表[CNO,CNAME,CTEATHER]--课程表
- SC表[SNO,CNO,SCGRADE]--选课关系表
- 查询一:没有修过李明老师的课的学生的姓名
- selectsnamefromswherenotexists
- (select*fromsc,cwheresc.cno=c.cnoandc.cteather='李明'andsc.sno=s.sno)
- 查询二:既学过a课程,又学过b课程的学生姓名
- SELECTS.SNO,S.SNAME
- FROMS,(
- SELECTSC.SNO
- FROMSC,C
- WHERESC.CNO=C.CNO
- ANDC.CNAMEIN('a','b')
- GROUPBYSNO
- )SCWHERES.SNO=SC.SNO
- 查询三:列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
- SELECTS.SNO,S.SNAME,AVG(SC.SCGRADE)
- FROMS,SC,(
- SELECTSNO
- FROMSC
- WHERESCGRADE<60
- GROUPBYSNO
- HAVINGCOUNT(DISTINCTCNO)>=2
- )AWHERES.SNO=A.SNOANDSC.SNO=A.SNO
- GROUPBYS.SNO,S.SNAME