oracle复杂查询练习题

1.删除重复记录(当表中无主键时)

Sql代码

1. create table TESTTB(
2. bm varchar(4),
3. mc varchar2(20)
4. )
5. insert into TESTTB values(1,'aaaa');
6. insert into TESTTB values(1,'aaaa');
7. insert into TESTTB values(2,'bbbb');
8. insert into TESTTB values(2,'bbbb');
9. /*方案一*/
10. delete from TESTTB where rowid not in
11. (select max(rowid) from TESTTB group by TESTTB.BM,TESTTB.MC)
13. /*方案二*/
14. delete from TESTTB a where a.rowid!= (
15. select max(rowid) from TESTTB b where a.bm=b.bm and a.mc=b.mc
16. )

2.bookEnrol是用来登记的，不管你是借还是还，都要添加一条记录。

请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息，

ID为3的java书，由于以归还，所以不要查出来。要求查询结果应为：(被借出的书和被借出的日期)

Sql代码

1. create table book(
2. id int ,
3. name varchar2(30),
4. PRIMARY KEY (id)
5. )
6. insert into book values(1,'English');
7. insert into book values(2,'Math');
8. insert into book values(3,'JAVA');
10. create table bookEnrol(
11. id int,
12. bookId int,
13. dependDate date,
14. state int,
15. FOREIGN KEY (bookId) REFERENCES book(id) ON DELETE CASCADE
16. )
17. insert into bookEnrol values(1,1,to_date('2009-01-02','yyyy-mm-dd'),1);
18. insert into bookEnrol values(2,1,to_date('2009-01-12','yyyy-mm-dd'),2);
19. insert into bookEnrol values(3,2,to_date('2009-01-14','yyyy-mm-dd'),1);
20. insert into bookEnrol values(4,1,to_date('2009-01-17','yyyy-mm-dd'),1);
21. insert into bookEnrol values(5,2,to_date('2009-02-14','yyyy-mm-dd'),2);
22. insert into bookEnrol values(6,2,to_date('2009-02-15','yyyy-mm-dd'),1);
23. insert into bookEnrol values(7,3,to_date('2009-02-18','yyyy-mm-dd'),1);
24. insert into bookEnrol values(8,3,to_date('2009-02-19','yyyy-mm-dd'),2);
26. /*方案一*/
27. select a.id,a.name,b.dependdate from book a,bookenrol b where
28. a.id=b.bookid
29. and
30. b.dependdate in(select max(dependdate) from bookenrol group by bookid )
31. and b.state=1
33. /*方案二*/
34. select k.id,k.name,a.dependdate
35. from bookenrol a, BOOK k
36. where a.id in (select max(b.id) from bookenrol b group by b.bookid)
37. and a.state = 1
38. and a.bookid = k.id;

3.查询每年销量最多的产品的相关信息

Sql代码

1. create table t2 (
2. year_ varchar2(4),
3. product varchar2(4),
4. sale number
5. )
7. insert into t2 values('2005','a',700);
8. insert into t2 values('2005','b',550);
9. insert into t2 values('2005','c',600);
10. insert into t2 values('2006','a',340);
11. insert into t2 values('2006','b',500);
12. insert into t2 values('2007','a',220);
13. insert into t2 values('2007','b',350);
14. insert into t2 values('2007','c',350);
16. /**方案一*/
17. select a.year_,a.sale,a.product from t2 a inner join(
18. select max(sale) as sl from t2 group by year_) b
19. on a.sale=b.sl order by a.year_
21. /*方案二*/
22. select sa.year_, sa.product, sa.sale
23. from t2 sa,
24. (select t.year_ pye, max(t.sale) maxcout
25. from t2 t
26. group by t.year_) tmp
27. where sa.year_ = tmp.pye
28. and sa.sale = tmp.maxcout

4.排序问题，如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。

Sql代码

1. create table t4(
2. 姓名 varchar2(20),
3. 月积分 varchar2(20),
4. 总积分 char(3)
5. )
7. insert into t4 values('WhatIsJava','1','99');
8. insert into t4 values('水王','76','981');
9. insert into t4 values('新浪网','65','96');
10. insert into t4 values('牛人','22','9');
11. insert into t4 values('中国队','64','89');
12. insert into t4 values('信息','66','66');
13. insert into t4 values('太阳','53','66');
14. insert into t4 values('中成药','11','33');
15. insert into t4 values('西洋参','257','26');
16. insert into t4 values('大拿','33','23');
18. /*方案一*/
19. select * from t4 order by cast(总积分 as int) desc
21. /*方案二*/
22. select * from t4 order by to_number(总积分) desc;

5.得出所有人（不区分人员）每个月及上月和下月的总收入

Sql代码

1. create table t5 ( tmonth int,
2. tname varchar2(10),
3. income number
4. )
5. insert into t5 values('08','a',1000);
6. insert into t5 values('09','a',2000);
7. insert into t5 values('10','a',3000);
9. /*方案一*/
10. select o.tmonth,sum(o.income) as cur,(select sum(t.income) from t5 t where t.tmonth=(o.tmonth+1) group by t.tmonth) as next,
11. (select sum(t.income) from t5 t where t.tmonth=(o.tmonth-1) group by t.tmonth) as last
12. from t5 o where o.tmonth=2 group by o.tmonth
14. /*方案二*/
15. select tmonth as 月份 ,tname as 姓名,sum(income) as 当月工资,
16. (select sum(income)
17. from t5
18. where tmonth = to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))-1) AS 上月工资 ,
19. (select sum(income)
20. from t5
21. where tmonth = to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))+1) AS 下月工资
22. from t5 where tmonth=substr(to_char(sysdate,'yyyy-mm-dd'),7,1)
23. group by tmonth,tname

6.根据现有的学生表，课程表，选课关系表，查询一。没有修过李明老师的课的学生，查询二，既学过a课程，又学过b课程的学生姓名

Sql代码

1. S表 [SNO,SNAME]--学生表
2. C表 [CNO,CNAME,CTEATHER] --课程表
3. SC表 [SNO，CNO，SCGRADE] --选课关系表
5. 查询一：没有修过李明老师的课的学生的姓名
6. select sname from s where not exists
7. (select*from sc,c where sc.cno=c.cno and c.cteather='李明' and sc.sno=s.sno)
9. 查询二：既学过a课程，又学过b课程的学生姓名
10. SELECT S.SNO,S.SNAME
11. FROM S,(
12. SELECT SC.SNO
13. FROM SC,C
14. WHERE SC.CNO=C.CNO
15. AND C.CNAME IN('a','b')
16. GROUP BY SNO
17. )SC WHERE S.SNO=SC.SNO
19. 查询三： 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
20. SELECT S.SNO,S.SNAME,AVG(SC.SCGRADE)
21. FROM S,SC,(
22. SELECT SNO
23. FROM SC
24. WHERE SCGRADE <60
25. GROUP BY SNO
26. HAVING COUNT(DISTINCT CNO)>=2
27. )A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
28. GROUP BY S.SNO,S.SNAME