当前位置: 代码迷 >> SQL >> SQL查询练习(oracle)[转]
  详细解决方案

SQL查询练习(oracle)[转]

热度:95   发布时间:2016-05-05 12:33:58.0
SQL查询练习题(oracle)[转]
SQL查询练习题(oracle)

博客分类: Oracle数据库
SQL练习题
作者:赵磊
博客:http://elf8848.iteye.com

------------------------------------ 练习题 一  删除重复记录 ---------------------------------------
前提说明:
distinct  只能在查询时过滤,不能完成本题要的删除功能。  例如 select distinct *  from t6

表的结构:
Sql代码 
create table t6( 
       bm char(4), 
       mc varchar2(20) 


表中的数据:

Sql代码 
insert into t6 values(1,'aaaa'); 
insert into t6 values(1,'aaaa'); 
insert into t6 values(2,'bbbb'); 
insert into t6 values(2,'bbbb'); 

要求:
删除重复记录

答案:
方法一,好理解:
 
Sql代码 
delete from t6 where rowid not in (    
select max(t6.rowid) from  t6 group by     
t6.bm,t6.mc); 
--这里用min(rowid)也可以。 

方法二,比前面的复杂一点:

Sql代码 
delete from t6 a where a.rowid!= ( 
select max(rowid) from t6 b where a.bm=b.bm and a.mc=b.mc 



-------------------------------------- 练习题 二  一个查询的练习 ------------------------------------
表的结构:
表1:book表,字段有id(主键),name (书名);
表2:bookEnrol表(图书借出归还登记),字段有id,bookId(外键),dependDate(变更时间),state(1.借出 2.归还)。

创建表的DDL:

Sql代码 
create table book( 
  id int , 
  name varchar2(30), 
  PRIMARY KEY (id) 

   
create table bookEnrol( 
  id int, 
  bookId int, 
  dependDate date, 
  state int, 
  FOREIGN KEY (bookId) REFERENCES book(id) ON DELETE CASCADE 


表中的数据:
book表:
id  name               
1   English
2   Math
3   JAVA

bookEnrol表:
id   bookId   dependDate  state
1      1      2009-01-02    1
2      1      2009-01-12    2
3      2      2009-01-14    1
4      1      2009-01-17    1
5      2      2009-02-14    2
6      2      2009-02-15    1
7      3      2009-02-18    1
8      3      2009-02-19    2

插入数据的SQL语句:

Sql代码 
insert into book values(1,'English'); 
insert into book values(2,'Math'); 
insert into book values(3,'JAVA'); 
 
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); 

要求查询出:  不要使用存储过程

第二个表(bookEnrol)是用来登记的,不管你是借还是还,都要添加一条记录。
请写一个SQL语句,获取到现在状态为已借出的所有图书的相关信息,
ID为3的java书,由于以归还,所以不要查出来。

要求查询结果应为:(被借出的书和被借出的日期)
Id    Name     dependDate
1    English    2009-01-17
2    Math      2009-02-15

答案:
 
Sql代码 
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 
如果大家有更好的方法,可以发出来交流一下。

下面是群友发出来的答案:

副主任的答案

Sql代码 
select a.bid, bo.name, a.adate    
  from (select t.bookid bid, t.state, count(*) acou, max(t.dependdate) adate    
          from bookenrol t    
         group by t.bookid, t.state    
        having t.state = 1) a,    
       (select t.bookid bid, t.state, count(*) bcou, max(t.dependdate) bdate    
          from bookenrol t    
         group by t.bookid, t.state    
        having t.state = 2) b,    
       book bo    
where a.bid = b.bid(+)    
   and bo.id = a.bid    
   and a.acou <> nvl(b.bcou,0); 

副主任小师姝的答案

Sql代码 
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; 


小米的答案

Sql代码 
select a.id,a.name,b.dependdate from book a, bookenrol b,(select max(dependdate) dependdate 
from bookenrol group by bookid) c where a.id(+) =b.bookid and b.dependdate(+)=c.dependdate and b.state=1; 

----------------------------------------- 练习题 三  一个查询的练习 --------------------------------------
表的结构与数据:
表一:各种产品年销售量统计表  sale
  年       产品           销量  
   2005       a             700  
   2005       b             550  
   2005       c             600  
   2006       a             340  
   2006       b             500  
   2007       a             220  
   2007       b             350 


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); 
Sql代码 
insert into t2 values('2007','c',350); 

要求查询出: 
要求得到的结果应为:
  年       产品           销量  
   2005       a           700  
   2006       b           500  
   2007       b           350  
  即:每年销量最多的产品的相关信息。

答案:

Sql代码 
我的: 
select * from t2 a inner join( 
select year_,max(sale) as sl from t2 group by year_) b 
on a.year_=b.year_ and a.sale=b.sl 
 
副主任的: 
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 

-------------------------------------------- 练习题 四  排序问题 -------------------------------------
表的结构与数据:

Sql代码 
create table t4( 
姓名   varchar2(20), 
月积分 varchar2(20), 
总积分 char(3) 

 
insert into t4 values('WhatIsJava','1','99'); 
insert into t4 values('水王','76','981'); 
insert into t4 values('新浪网','65','96'); 
insert into t4 values('牛人','22','9'); 
insert into t4 values('中国队','64','89'); 
insert into t4 values('信息','66','66'); 
insert into t4 values('太阳','53','66'); 
insert into t4 values('中成药','11','33'); 
insert into t4 values('西洋参','257','26'); 
insert into t4 values('大拿','33','23'); 


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

答案:

Sql代码 
我的: 
select * from t4 order by cast(总积分 as int) desc 
小米的: 
select * from t4 order by to_number(总积分) desc; 


---------------------------------------- 练习题 五  查询 --------------------------------------
表的结构与数据:
表A字段如下
month  name income
月份   人员 收入
8      a    1000
9      a    2000
10     a    3000

Sql代码 
create table t5 ( 
month int, 
name varchar2(10), 
income number 

insert into t5 values('08','a',1000); 
insert into t5 values('09','a',2000); 
insert into t5 values('10','a',3000); 
 
这个日期要与你做题的日期 相符,才会有当前月,上一个月,下一个月, 过期请自行修改 


要求查询出:
要求用一个SQL语句(注意是一个)得出所有人(不区分人员)每个月及上月和下月的总收入
要求列表输出为:
        月份       当月收入       上月收入       下月收入
---------- ---------- ---------- ----------
         9       2000       1000       3000

答案:
小程的答案:
Sql代码 
select o.month,sum(o.income) as cur,(select sum(t.income) from t5 t where t.month=(o.month+1) group by t.month) as next, 
(select sum(t.income) from t5 t where t.month=(o.month-1) group by t.month) as last 
from t5 o where o.month=2 group by o.month 
重庆--小彭:
Sql代码 
select month as 月份 ,name as 姓名,sum(income) as 当月工资, 
(select sum(income)  
from t5  
where month = to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))-1) AS 上月工资 , 
(select sum(income)  
from t5  
where month = to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))+1) AS 下月工资  
from t5 where month=substr(to_char(sysdate,'yyyy-mm-dd'),7,1) 
group by month,name 

副主任:

Sql代码 
drop table t5 
create table t5 (    
month date,    
name varchar2(10),    
income number    
)    
insert into t5 values(to_date('2010-08-01','yyyy-MM-dd'),'a',1000);    
insert into t5 values(to_date('2010-09-01','yyyy-MM-dd'),'a',2000);    
insert into t5 values(to_date('2010-10-01','yyyy-MM-dd'),'a',3000); 
 
select sum(to_number(substr(to_char(sysdate,'yyyy-MM-dd'),6,2)))/count(*), 
       sum(decode(month, to_date(to_char(add_months(trunc(sysdate),-1),'yyyy-MM'),'yyyy-MM'), income, 0)) 上月, 
       sum(decode(month, to_date(to_char(add_months(trunc(sysdate),0),'yyyy-MM'),'yyyy-MM'), income, 0)) 当月, 
       sum(decode(month, to_date(to_char(add_months(trunc(sysdate),1),'yyyy-MM'),'yyyy-MM'), income, 0)) 下月 
  from t5 
group by name; 




----------------------------------------------- 练习题 其它 ----------------------------------------
rowid 和 rownum 的区别
rowid 用于定位数据表中某条数据的位置,是唯一的、也不会改变
rownum 表示查询某条记录在整个结果集中的位置,
同一条记录查询条件不同对应的 rownum 是不同的而 rowid 是不会变的

  相关解决方案