Oracle复杂查询练习题

Oracle复杂查询练习题

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

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) 
12.  
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书，由于以归还，所以不要查出来。要求查询结果应为：(被借出的书和被借出的日期)

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');   
9.  
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);   
25.  
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   
32.  
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;