- 浏览: 8047 次
- 性别:
- 来自: 北京
最新评论
------------------------------------ 练习题 一 删除重复记录 ---------------------------------------
前提说明:
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 是不会变的
前提说明:
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 是不会变的
发表评论
-
mysql limit分页查询优化写法
2015-11-27 09:46 301在mysql中进行分页查询时,一般会使用limit查询,而且通 ... -
Mysql管理命令,备份与恢复,授权远程访问
2015-11-27 09:46 655MySql命令行登录 -------------------- ... -
Orcacle PL/SQL 培训
2015-11-04 09:27 385PL/SQL (procedural language/sq ... -
Oracle 10G sql 培训
2015-11-04 09:25 275本文章通过oracle 10g 11g ... -
Oracle数据库的导出导入
2015-11-04 09:20 302一、背景介绍 9i,10g,11g 支持Export 与 ...
相关推荐
oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具...
sql查询oracle时间段操作,通过oracle数据库快照来实现恢复数据
sqlserver自动生成sql语句工具sqlserver转oracle
SQL SERVER连接oracle数据库几种方法 查询oracle数据库中的表 在master数据库中查看已经存在的链接服务器 要在企业管理器内指定登录帐号
oracle 常用SQL查询,里面有丰富的SQL查询命令
Hibernate SQLQuery 查询Oracle char类型结果为一个字符解决方法 Hibernate SQLQuery 查询Oracle char类型结果为一个字符解决方法 Hibernate SQLQuery 查询Oracle char类型结果为一个字符解决方法 Hibernate SQL...
Sqldbx连接oracle,不用安装client http://www.cnblogs.com/ycdx2001/p/5441405.html
Oracle的经典常用的sql查询,对掌握Oracle以及sql查询都有很大的帮助,欢迎提出意见。
Oracle常用SQL查询语句,Oracle常用SQL查询语句,Oracle常用SQL查询语句
主要给大家介绍了oracle查看执行最慢与查询次数最多的sql语句,文中给出完整的示例代码,相信对大家的学习或者工作具有一定的参考价值,有需要的朋友们下面来一起看看吧。
oracle消耗资源的sql查询语句记录
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition by Stelios Charalambides English | 8 May 2017 | ISBN: 1484224353 | 408 Pages | PDF | 24.39 MB Learn through this practical guide ...
查询ORACLE 系统中当前会话正在执行的有关SQL语句。
oracle常用经典SQL查询,oracle常用经典SQL查询
连接sqlserver数据库后选择相应的数据库-数据表以及目标数据库(目前只有oracle),可以将sqlserver转换成oracle数据,有两种方式,点击转换sql按钮,可以生成sql语句,复制语句到oracle里执行。第二种是连接oracle...
包含网关包配置步骤等等,文件描述解析,安装配置及指引
ORACLE中SQL查询优化技术.pdf ORACLE中SQL查询优化技术.pdf
sql expert 3.2 SQL 语句性能分析的利器,能将你的SQL语句优化出多种方案供你选择! 此版本并不支持ORACLE10G以上,连接的时候会出现初始化失败: Initialization error SQL*Net not properly installed Oracle...
在网上看了很多资料,感觉不是很仔细,很多同学遇到这个问题,就详细说明一下在局域网环境下Navicat连接SQL Server和Oracle 19c的方法,PL/SQL Developer连接Oracle
Welcome to “Beginning SQL for Oracle Database 18C”! Thanks for picking up this book. It’s safe to say you’re interested in learning about Oracle database, as that’s what this book is about. Why ...