oracle复杂查询练习题

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