`

SQL查询练习题(oracle)

阅读更多

作者:赵磊

博客:http://elf8848.iteye.com

 

------------------------------------ 练习题 一  删除重复记录 ---------------------------------------

前提说明:

distinct  只能在查询时过滤,不能完成本题要的删除功能。  例如 select distinct *  from t6

 

表的结构:

create table t6(
       bm char(4),
       mc varchar2(20)
)

 

表中的数据:

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

 

要求:

 删除重复记录

 

答案:

方法一,好理解:

  
delete from t6 where rowid not in (   
select max(t6.rowid) from  t6 group by    
t6.bm,t6.mc);
--这里用min(rowid)也可以。

 

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

 

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:

 
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语句:

 
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

 

答案:

  

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 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);

 

 副主任小师姝的答案

 
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;

 

 

小米的答案

 
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  

 

 
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);

 

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

 

答案:

 
我的:
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

 

-------------------------------------------- 练习题 四  排序问题 -------------------------------------

表的结构与数据:

 
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...),要求按照总积分的数字大小排序。

 

答案:

 

我的:
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

 

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

 

答案:

小程的答案:

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

 重庆--小彭:

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

 

副主任:

 

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 是不会变的

 

 

 

分享到:
评论

相关推荐

    Oracle SQL:经典练习题(附答案)

    1. **基本查询操作**:练习题中包含了查询员工表(emp)和部门表(dept)的基础信息,例如查询所有员工的详细信息。这涉及到SELECT语句的基本用法,包括选择列(如`SELECT * FROM emp`)和指定表(如`FROM emp`)。 ...

    oracle数据库sql练习题(含答案)

    oracle数据库sql练习题(含答案)用于练习增删改查操作。

    Oracle基础查询关联查询练习题.docx

    Oracle 基础查询关联查询练习题 Oracle 基础查询关联查询练习题中涉及到多种查询类型,包括基础查询、关联查询、分组查询等。下面我们将对每个练习题的知识点进行详细解释。 1. 基础查询 基础查询是指从数据库中...

    Sql经典练习题库(附答案)

    #### 五、经典SQL练习题解析 ##### 1. 查询“0001”课程比“C002”课程成绩高的所有学生的学号 ```sql SELECT S# FROM SC WHERE C# = '0001' AND SCORE &gt; ANY (SELECT SCORE FROM SC WHERE C# = '0002'); ``` **...

    oracle经典sql语句练习题和答案

    本资源“oracle经典sql语句练习题和答案”提供了在scott用户下的两个典型表格——emp(员工表)和dept(部门表)的实践操作题目,旨在帮助用户提升SQL技能。 首先,让我们来了解这两个核心表格。`emp`表通常包含...

    Oracle存储过程LP/SQL练习题(含答案)

    Oracle存储过程LP/SQL练习题(含答案) 几个练习题

    sql练习题 自用oracle

    "sql练习题 自用oracle"这个主题意味着我们将在Oracle数据库环境下进行一系列的SQL练习,以加深对SQL语句的理解和应用。Oracle数据库是世界上最广泛使用的数据库系统之一,尤其在企业级应用中占据重要地位。以下将...

    SQL 练习题适合ORACLE MYSQL SQL Server等

    本资源提供了50道SQL练习题,旨在帮助用户提升SQL技能,无论你是初学者还是有一定经验的开发者,都能从中受益。 在ORACLE数据库中,SQL不仅用于数据查询,还支持复杂的数据库操作,如DDL(Data Definition Language...

    oracle sql练习题

    oracle数据库练习题,对新手有帮助,看看没有害处。

    数据库sql语言练习题

    在"sql语句练习题及答案"中,你将找到一系列针对上述知识点的题目,以及相应的解答。通过实践这些题目,你可以巩固对SQL语言的理解,并提升实际操作技能。记得每次完成练习后,对照答案检查自己的理解,以确保对SQL...

    数据库SQL基础练习题与答案(oracle)

    在“数据库SQL基础练习题与答案.txt”文件中,你可以找到涉及以下SQL知识点的习题: 1. **数据查询**:使用SELECT语句从表中检索信息,包括选择特定列、多表联查(JOIN)、使用WHERE子句过滤数据、使用GROUP BY进行...

    Oracle SQL PLSQL练习题

    Oracle SQL 和 PL/SQL 是两种在 Oracle 数据库中进行数据操作和管理的编程语言。这里是一些关于这些语言的关键知识点: ...通过练习题目,你可以巩固这些知识并提升在 Oracle 数据库中的查询能力。

    Oracle的sql语句练习题含答案

    Oracle SQL是用于管理和操作Oracle数据库的强大工具,这些练习题涵盖了SQL查询的基础到进阶技巧。以下是对这些题目涉及的知识点的详细解释: 1. **子查询**:在第一题中,我们看到一个简单的子查询,用于选取部门...

    Oracle的sql语句练习题及参考答案

    在“Oracle的SQL语句练习题及参考答案”中,我们很可能会遇到各种与`SELECT`语句相关的练习,这是SQL中最基础且最重要的部分。 `SELECT`语句用于从数据库中检索数据,其基本语法结构如下: ```sql SELECT column1,...

    Oracle PL/SQL的练习题

    这篇博客“Oracle PL/SQL的练习题”可能包含一系列针对初学者和进阶者的编程挑战,旨在提升对PL/SQL语言的理解和应用能力。虽然没有具体的描述,但我们可以推测这些练习可能涵盖以下几个方面: 1. **变量声明与赋值...

    Oracle SQL/Plus练习题

    通过不断地实践和解决这些练习题,你可以逐步提升你的Oracle SQL/Plus技能,这对于任何涉及Oracle数据库管理的工作都是非常有价值的。记住,熟练掌握SQL语言不仅限于理论知识,更重要的是通过实际操作来加深理解和...

    sql语句强化练习习题及答案

    ### SQL语句强化练习知识点详解 ...以上是根据给定的文件内容整理出来的SQL语句强化练习相关的知识点,覆盖了基本查询、复杂查询以及连接查询等多个方面,有助于深入理解SQL语言的核心功能和使用技巧。

    Oracle SQL练习题及答案_最全最新

    本资料包“Oracle SQL练习题及答案_最全最新”提供了一系列关于Oracle SQL的基础查询语句实例,旨在帮助学习者巩固和提升SQL语法的应用能力。 首先,Oracle SQL的基本查询语句包括SELECT、FROM、WHERE、GROUP BY、...

Global site tag (gtag.js) - Google Analytics