oracle复杂查询练习题
1.删除重复记录(当表中无主键时)
www.2cto.com
Sql代码
create table TESTTB(
bm varchar(4),
mc varchar2(20)
)
insert into TESTTB values(1,'aaaa');
insert into TESTTB values(1,'aaaa');
insert into TESTTB values(2,'bbbb');
insert into TESTTB values(2,'bbbb');
/*方案一*/
delete from TESTTB where rowid not in
(select max(rowid) from TESTTB group by TESTTB.BM,TESTTB.MC)
/*方案二*/
delete from TESTTB a where a.rowid!= (
select max(rowid) from TESTTB b where a.bm=b.bm and a.mc=b.mc
)
2.bookEnrol是用来登记的，不管你是借还是还，都要添加一条记录。
请写一个SQL语句，获取到现在状态为已借出的所有
图书的相关信息，
ID为3的java书，由于以归还，所以不要查出来。要求查询结果应为：(被借出的书和被借出的日期)
Sql代码
create table book(
id int ,
name varchar2(30),
PRIMARY KEY (id)
)
insert into book values(1,'English');
insert into book values(2,'Math');
insert into book values(3,'JAVA');
create table bookEnrol(
id int,
bookId int,
dependDate date,
state int,
FOREIGN KEY (bookId) REFERENCES book(id) ON DELETE CASCADE
)
insert into bookEnrol values(1,1,to_date('2009-01-02','yyyy-mm-dd'),1);
insert into bookEnrol values(2,1,to_date('2009-01-12','yyyy-mm-dd'),2);
insert into bookEnrol values(3,2,to_date('2009-01-14','yyyy-mm-dd'),1);
insert into bookEnrol values(4,1,to_date('2009-01-17','yyyy-mm-dd'),1);
insert into bookEnrol values(5,2,to_date('2009-02-14','yyyy-mm-dd'),2);
insert into bookEnrol values(6,2,to_date('2009-02-15','yyyy-mm-dd'),1);
insert into bookEnrol values(7,3,to_date('2009-02-18','yyyy-mm-dd'),1);
insert into bookEnrol values(8,3,to_date('2009-02-19','yyyy-mm-dd'),2);
www.2cto.com
/*方案一*/
select a.id,a.name,b.dependdate from book a,bookenrol b where
a.id=b.bookid
and
b.dependdate in(select max(dependdate) from bookenrol group by bookid )
and b.state=1
/*方案二*/
select k.id,k.name,a.dependdate
from bookenrol a, BOOK k
where a.id in (select max(b.id) from bookenrol b group by b.bookid)
and a.state = 1
and a.bookid = k.id;
3.查询每年销量最多的产品的相关信息
www.2cto.com
Sql代码
create table t2 (
year_ varchar2(4),
product varchar2(4),
sale number
)
insert into t2 values('2005','a',700);
insert into t2 values('2005','b',550);
insert into t2 values('2005','c',600);
insert into t2 values('2006','a',340);
insert into t2 values('2006','b',500);
insert into t2 values('2007','a',220);
insert into t2 values('2007','b',350);
insert into t2 values('2007','c',350);
/**方案一*/
select a.year_,a.sale,a.product from t2 a inner join(
select max(sale) as sl from t2 group by year_) b
on a.sale=b.sl order by a.year_
/*方案二*/
select sa.year_, sa.product, sa.sale
from t2 sa,
(select t.year_ pye, max(t.sale) maxcout
from t2 t
group by t.year_) tmp
where sa.year_ = tmp.pye
and sa.sale = tmp.maxcout
4.排序问题，如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。
www.2cto.com
Sql代码
create table t4(
姓名 varchar2(20),
月积分 varchar2(20),
总积分 char(3)
)
insert int