自来水的建表语句
--建立价格区间表
create table t_pricetable
(
id number primary key,
price number(10,2),
ownertypeid number,
minnum number,
maxnum number
);
--业主类型
create table t_ownertype
(
id number primary key,
name varchar2(30)
);
--业主表
create table t_owners
(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);
--区域表
create table t_area
(
id number,
name varchar2(30)
);
--收费员表
create table t_operator
(
id number,
name varchar2(30)
);
--地址表
create table t_address
(
id number primary key,
name varchar2(100),
areaid number,
operatorid number
);
--账务表--
create table t_account
(
id number primary key,
owneruuid number,
ownertype number,
areaid number,
year char(4),
month char(2),
num0 number,
num1 number,
usenum number,
meteruser number,
meterdate date,
money number(10,2),
isfee char(1),
feedate date,
feeuser number
);
create sequence seq_account;
--业主类型
insert into t_ownertype values(1,'居民');
insert into t_ownertype values(2,'行政事业单位');
insert into t_ownertype values(3,'商业');
--地址信息--
insert into t_address values( 1,'明兴花园',1,1);
insert into t_address values( 2,'鑫源秋墅',1,1);
insert into t_address values( 3,'华龙苑南里小区',2,2);
insert into t_address values( 4,'河畔花园',2,2);
insert into t_address values( 5,'霍营',2,2);
insert into t_address values( 6,'回龙观东大街',3,2);
insert into t_address values( 7,'西二旗',3,2);
--业主信息
insert into t_owners values(1,'范冰',1,'1-1','30406',to_date('2015-04-12','yyyy-MM-dd'),1 );
insert into t_owners values(2,'王强',1,'1-2','30407',to_date('2015-02-14','yyyy-MM-dd'),1 );
insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('2015-03-18','yyyy-MM-dd'),1 );
insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('2015-06-15','yyyy-MM-dd'),1 );
insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('2013-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('2014-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(7,'周健',3,'2-5','30433',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('2016-10-11','yyyy-MM-dd'),2 );
insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );
--操作员
insert into t_operator values(1,'马小云');
insert into t_operator values(2,'李翠花');
--地区--
insert into t_area values(1,'海淀');
insert into t_area values(2,'昌平');
insert into t_area values(3,'西城');
insert into t_area values(4,'东城');
insert into t_area values(5,'朝阳');
insert into t_area values(6,'玄武');
--价格表--
insert into t_pricetable values(1,2.45,1,0,5);
insert into t_pricetable values(2,3.45,1,5,10);
insert into t_pricetable values(3,4.45,1,10,null);
insert into t_pricetable values(4,3.87,2,0,5);
insert into t_pricetable values(5,4.87,2,5,10);
insert into t_pricetable values(6,5.87,2,10,null);
insert into t_pricetable values(7,4.36,3,0,5);
insert into t_pricetable values(8,5.36,3,5,10);
insert into t_pricetable values(9,6.36,3,10,null);
--账务表--
insert into t_account values( seq_account.nextval,1,1,1,'2012','01',30203,50123,0,1,sysdate,34.51,'1',to_date('2012-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','02',50123,60303,0,1,sysdate,23.43,'1',to_date('2012-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','03',60303,74111,0,1,sysdate,45.34,'1',to_date('2012-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','04',74111,77012,0,1,sysdate,52.54,'1',to_date('2012-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','05',77012,79031,0,1,sysdate,54.66,'1',to_date('2012-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','06',79031,80201,0,1,sysdate,76.45,'1',to_date('2012-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','07',80201,88331,0,1,sysdate,65.65,'1',to_date('2012-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','08',88331,89123,0,1,sysdate,55.67,'1',to_date('2012-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','09',89123,90122,0,1,sysdate,112.54,'1',to_date('2012-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','10',90122,93911,0,1,sysdate,76.21,'1',to_date('2012-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','11',93911,95012,0,1,sysdate,76.25,'1',to_date('2012-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','12',95012,99081,0,1,sysdate,44.51,'1',to_date('2013-01-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','01',30334,50433,0,1,sysdate,34.51,'1',to_date('2013-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','02',50433,60765,0,1,sysdate,23.43,'1',to_date('2013-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','03',60765,74155,0,1,sysdate,45.34,'1',to_date('2013-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','04',74155,77099,0,1,sysdate,52.54,'1',to_date('2013-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','05',77099,79076,0,1,sysdate,54.66,'1',to_date('2013-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','06',79076,80287,0,1,sysdate,76.45,'1',to_date('2013-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','07',80287,88432,0,1,sysdate,65.65,'1',to_date('2013-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','08',88432,89765,0,1,sysdate,55.67,'1',to_date('2013-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','09',89765,90567,0,1,sysdate,112.54,'1',to_date('2013-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','10',90567,93932,0,1,sysdate,76.21,'1',to_date('2013-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','11',93932,95076,0,1,sysdate,76.25,'1',to_date('2013-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,100,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-01','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,101,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2015-01-01','yyyy-MM-dd'),2 );
update t_account set usenum=num1-num0;
update t_account set money=usenum*2.45;
commit;
一、单表查询
1.1简单条件查询
1.精确查询
select * from T_OWNERS where watermeter='30408' 
2.模糊查询
select * from t_owners where name like '%刘%'
3. and 运算符
select * from t_owners where name like '%刘%' and housenumber like '%5%'
4. or 运算符
select * from t_owners where name like '%刘%' or housenumber like '%5%'查询结果：
5. and 与 or 运算符混合使用
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3查询结果：
6. 范围查询
select * from T_ACCOUNT where usenum>=10000 and usenum<=20000select * from T_ACCOUNT where usenum between 10000 and 20000查询结果：
7. 空值查询
select * from T_PRICETABLE t where maxnum is null
select * from T_PRICETABLE t where maxnum is not null
1.2去掉重复记录
select distinct addressid from T_OWNERS查询结果：
1.3排序查询
1.升序排序--默认
select * from T_ACCOUNT order by usenum
2.降序排序
select * from T_ACCOUNT order by usenum desc
1.4基于伪列的查询
伪列：假的列。它是每一个行数据的物理地址。它的地址返回的就是一长串。你用oracle你可以不用建主键，主键的作用就是唯一标识，oracle的rowid相当于唯一标识。
rowid的查询效率比主键查询效率高？？？
rowid本身是物理地址，所谓物理地址他就是可以直接定位，主键查询的实质就是通过索引查询rowid，它要经过查找的过程才能找到他。所以rowid比主键查询更快
1. ROWID
select rowID,t.* from T_AREA tselect rowID,t.* from T_AREA t where ROWID='AAAM1uAAGAAAAD8AAC';
2 .ROWNUM
ROWNUM是每一行的行号。他就是一个序号，他就是结果集的序号，他就会给结果集自动添加序号。
select rownum,t.* from T_OWNERTYPE t
1.5聚合统计
ORACLE 的聚合统计是通过分组函数来实现的，与 MYSQL 一致。
1. 聚合函数
select sum(usenum) from t_account where year='2012'
select avg(usenum) from T_ACCOUNT where year='2012'
select max(usenum) from T_ACCOUNT where year='2012'
select min(usenum) from T_ACCOUNT where year='2012'
select count(*) from T_OWNERS t where ownertypeid=1
2. 分组聚合 Group by---select后一定是分组聚合的条件或者是聚合函数
select areaid,sum(money) from t_account group by areaid
3. 分组后条件查询 having
select areaid,sum(money) from t_account group by areaid having sum(money)>169000
二、连接查询
2.1多表内连接查询
（1）需求：查询显示业主编号，业主名称，业主类型名称，如下图：
select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id
select o.id 业主编号,o.name 业主名称,ad.name 地址, ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad
where o.ownertypeid=ot.id and o.addressid=ad.id
select o.id 业主编号,o.name 业主名称,ar.name 区域, ad.name 地 址, ot.name 业主类型
select ow.id 业主编号,ow.name 业主名称,ad.name 地址, ar.name 所属区域,op.name 收费员, ot.name 业主类型
from T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad , T_AREA ar,T_OPERATOR op
where ow.ownertypeid=ot.id and ow.addressid=ad.id
and ad.areaid=ar.id and ad.operatorid=op.id2.2左外连接查询--2种写法
SELECT ow.id,ow.name,ac.year ,ac.month,ac.money
FROM T_OWNERS ow left join T_ACCOUNT ac
on ow.id=ac.owneruuidSELECT ow.id,ow.name,ac.year ,ac.month,ac.money
FROM T_OWNERS ow,T_ACCOUNT ac
WHERE ow.id=ac.owneruuid(+)2.3右外连接查询
select ow.id,ow.name,ac.year,ac.month,ac.money
from T_OWNERS ow right join T_ACCOUNT ac
on ow.id=ac.owneruuidselect ow.id,ow.name,ac.year,ac.month,ac.money
from T_OWNERS ow , T_ACCOUNT ac where ow.id(+) =ac.owneruuid三、子查询
3.1where 子句中的子查询--2种
1. 单行子查询---用大于或者等于的符号
- 只返回一条记录
- 单行操作符
select * from T_ACCOUNT
where year='2012' and month='01' and usenum>
( select avg(usenum) from T_ACCOUNT where year='2012' and month='01' )
2. 多行子查询
- 返回了多条记录
- 多行操作符
select * from T_OWNERS where addressid in ( 1,3,4 )
（2）需求：查询地址含有“花园”的业主的信息
select * from T_OWNERS
where addressid in
( select id from t_address where name like '%花园%' )
select * from T_OWNERS
where addressid not in
( select id from t_address where name like '%花园%' )
3.2from 子句中的子查询---多行子查询
select * from
(select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id)
where 业主类型='居民'
3.3select 子句中的子查询--单行字查询
select id,name,
(select name from t_address where id=addressid) addressname
from t_owners
select id,name,
( select name from t_address where id=addressid )
addressname,
( select (select name from t_area where id=areaid ) from t_address where id=addressid ) adrename
from t_owners;
四、分页查询
4.1简单分页---2层嵌套
select rownum,t.* from T_ACCOUNT t where rownum<=10
select rownum,t.* from T_ACCOUNT t where rownum>10 and rownum<=20select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20)
where r>10我们可以用rownum先产生值，然后再去做筛选。就是做一个子查询的结果。
4.2基于排序的分页---3层嵌套
select * from
(select rownum r,t.* from T_ACCOUNT t
where rownum<=20 order by usenum desc)
where r>10先排序在生成。
select rownum r,t.* from T_ACCOUNT t
where rownum<=20 order by usenum desc
select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10
五、单行函数--5种
函数就是你给他一个值，他返回一个值。单行函数就是你给他一个值，它经过运算之后，他在返回给你一个值。聚合函数/分组函数它是统计的是一列信息，最后给你一个汇总的数。
5.1字符函数
select length('ABCD') from dual;
select substr('ABCD',2,2) from dual;
select concat('ABC','D') from dual;
select 'ABC'||'D' from dual;
5.2数值函数
select round(100.567) from dual
select round(100.567,2) from dual
select trunc(100.567) from dual
select trunc(100.567,2) from dual
select mod(10,3) from dual
5.3日期函数
select sysdate from dual
select add_months(sysdate,2) from dual
select last_day(sysdate) from dual
select TRUNC(sysdate) from dual
select TRUNC(sysdate,'yyyy') from dual
select TRUNC(sysdate,'mm') from dual
其他的：
5.4转换函数
select TO_CHAR(1024) from dual
select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual
select to_number('100') from dual查询结果：
5.5其它函数
select NVL(NULL,0) from dual
select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
from T_PRICETABLE where OWNERTYPEID=1查询结果：
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
【功能】根据条件返回相应值
select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位 ',3,'商业') as 类型
from T_OWNERS查询结果：
select name ,(case ownertypeid
when 1 then '居民'
when 2 then '行政事业单位'
when 3 then '商业'
else '其它'
end )
from T_OWNERS查询结果：
select name,(case
when ownertypeid= 1 then '居民'
when ownertypeid= 2 then '行政事业'
when ownertypeid= 3 then '商业'
end )
rom T_OWNERS六、行列转换
需求：按月份统计 2012 年各个地区的水费，如下图
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month='01' then money else 0 end) 一月,
sum( case when month='02' then money else 0 end) 二月,
sum( case when month='03' then money else 0 end) 三月,
sum( case when month='04' then money else 0 end) 四月,
sum( case when month='05' then money else 0 end) 五月,
sum( case when month='06' then money else 0 end) 六月,
sum( case when month='07' then money else 0 end) 七月,
sum( case when month='08' then money else 0 end) 八月,
sum( case when month='09' then money else 0 end) 九月,
sum( case when month='10' then money else 0 end) 十月,
sum( case when month='11' then money else 0 end) 十一月,
sum( case when month='12' then money else 0 end) 十二月
from T_ACCOUNT where year='2012' group by areaid
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>='01' and month<='03' then money else 0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else 0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else 0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else 0 end) 第四季度
from T_ACCOUNT where year='2012' group by areaid查询结果：
七、分析函数---3种
select rank() over(order by usenum desc ),usenum from T_ACCOUNTselect dense_rank() over(order by usenum desc ),usenum from T_ACCOUNTselect row_number() over(order by usenum desc ),usenum from T_ACCOUNT结果：
select * from
(select row_number() over(order by usenum desc ) rownumber,usenum from T_ACCOUNT)
where rownumber>10
八、集合运算
8.1什么是集合运算
8.2并集运算
UNION ALL 不去掉重复记录
select * from t_owners where id<=7
union all
select * from t_owners where id>=5
select * from t_owners where id<=7
union
select * from t_owners where id>=5结果：
8.3交集运算
select * from t_owners where id<=7
intersect
select * from t_owners where id>=5结果：
8.4差集运算
差集就是减法，就是吧第一个结果中出现的第二个结果的数据减出去。
select * from t_owners where id<=7
minus
select * from t_owners where id>=5
select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT结果:
九、综合案例
为《自来水收费系统》开发统计模块相关的功能
1.收费日报单（总）
统计某日的收费，按区域分组汇总，效果如下：
语句：
| select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm-dd')='2012-05-14' group by areaid |
2.收费日报单（收费员）
统计某收费员某日的收费，按区域分组汇总，效果如下：
语句：
| select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm-dd')='2012-05-14' and feeuser=2 group by areaid |
3.收费月报表（总）
统计某年某月的收费记录，按区域分组汇总
语句:
| select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm')='2012-05' group by areaid |
4.收费月报表（收费员）
统计某收费员某年某月的收费记录，按区域分组汇总
语句：
| select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm')='2012-05' and feeuser=2 group by areaid |
5.收费年报表（分区域统计）
统计某年收费情况，按区域分组汇总，效果如下：
语句：
| select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy')='2012' group by areaid |
6.收费年报表（分月份统计）
统计某年收费情况，按月份分组汇总，效果如下
语句：
| select to_char(feedate,'mm') 月份,sum(usenum)/1000 使用吨数,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy')='2013' GROUP BY to_char(feedate,'mm') ORDER BY to_char(feedate,'mm') |
7.收费年报表（分月份统计）
统计某年收费情况，按月份分组汇总，效果如下
语句:
| select '用水量(吨)' 统计项, sum (case when to_char(feedate,'mm')='01' then usenum else 0 end )/1000 一月, sum (case when to_char(feedate,'mm')='02' then usenum else 0 end )/1000 二月, sum (case when to_char(feedate,'mm')='03' then usenum else 0 end )/1000 三月, sum (case when to_char(feedate,'mm')='04' then usenum else 0 end )/1000 四月, sum (case when to_char(feedate,'mm')='05' then usenum else 0 end )/1000 五月, sum (case when to_char(feedate,'mm')='06' then usenum else 0 end )/1000 六月, sum (case when to_char(feedate,'mm')='07' then usenum else 0 end )/1000 七月, sum (case when to_char(feedate,'mm')='08' then usenum else 0 end )/1000 八月, sum (case when to_char(feedate,'mm')='09' then usenum else 0 end )/1000 九月, sum (case when to_char(feedate,'mm')='10' then usenum else 0 end )/1000 十月, sum (case when to_char(feedate,'mm')='11' then usenum else 0 end )/1000 十一月, sum (case when to_char(feedate,'mm')='12' then usenum else 0 end )/1000 十二月 from T_ACCOUNT where to_char(feedate,'yyyy')='2013' UNION ALL select '金额(元)' 统计项, sum (case when to_char(feedate,'mm')='01' then money else 0 end ) 一月, sum (case when to_char(feedate,'mm')='02' then money else 0 end ) 二月, sum (case when to_char(feedate,'mm')='03' then money else 0 end ) 三月, sum (case when to_char(feedate,'mm')='04' then money else 0 end ) 四月, sum (case when to_char(feedate,'mm')='05' then money else 0 end ) 五月, sum (case when to_char(feedate,'mm')='06' then money else 0 end ) 六月, sum (case when to_char(feedate,'mm')='07' then money else 0 end ) 七月, sum (case when to_char(feedate,'mm')='08' then money else 0 end ) 八月, sum (case when to_char(feedate,'mm')='09' then money else 0 end ) 九月, sum (case when to_char(feedate,'mm')='10' then money else 0 end ) 十月, sum (case when to_char(feedate,'mm')='11' then money else 0 end ) 十一月, sum (case when to_char(feedate,'mm')='12' then money else 0 end ) 十二月 from T_ACCOUNT where to_char(feedate,'yyyy')='2013' |
8.统计用水量，收费金额（分类型统计）
根据业主类型分别统计每种居民的用水量（整数，四舍五入）及收费金额 ，如果该类型在台账表中无数据也需要列出值为0的记录 ， 效果如下：
语句：
| select ow.name, nvl( round(sum(usenum)/1000),0) "用水量(吨)" , nvl( sum(money),0) 金额 from T_OWNERTYPE ow ,T_ACCOUNT ac where ow.id=ac.ownertype(+) group by ow.name |
分析：这里所用到的知识点包括左外连接、sum()、分组group by 、round() 和nvl()
9.统计每个区域的业主户数，并列出合计
语句：
| select ar.name 区域,count(ow.id) 业主户数 from T_AREA ar ,T_OWNERS ow,T_ADDRESS ad where ad.id=ow.addressid and ad.areaid=ar.id group by ar.name union all select '合计',count(1) from T_OWNERS |
- 统计每个区域的业主户数，如果该区域没有业主户数也要列出0
如图:
语句：
| select ar.name 区域,count(owad.id) 业主户数 from T_AREA ar , ( select ow.id,ow.name,ad.areaid from T_OWNERS ow,T_ADDRESS ad where ow.addressid=ad.id ) owad where ar.id=owad.areaid(+) group by ar.name |
十、练习
1.表结构分析
1.请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
select ename,sal from emp where job='CLERK' or job='MANAGER';
2.请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。
select ename,deptno,sal,job from emp where deptno between 10 and 30;
3.请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。
select ename,sal,job from emp where ename like 'J%';
4.请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
select ename,job,sal from emp where sal<=2000 order by sal desc;
5.请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。
select ename,sal,emp.deptno,dname,loc from emp,dept
where emp.deptno=dept.deptno and job=’CLERK’;
6.在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。
select ename,job,sal from emp where sal>(select sal from emp where ename=’JONES’);
7.列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。
select ename,job,deptno from emp where deptno not in (select deptno from dept);
8.查找工资在1000～3000之间的雇员所在部门的所有人员信息
select * from emp where deptno in (select distinct deptno from emp where sal between 1000 and 3000);
9.雇员中谁的工资最高。
select ename from emp where sal=(select max(sal) from emp);
select ename from (select * from emp order by sal desc) where rownum<=1;
10.查询所有雇员的姓名、SAL与COMM之和。
select ename,sal+nvl(comm,0) “sal-and-comm” from emp;
11.查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate<=to_date(‘1981-07-01’,’yyyy-mm-dd’);
12.查询各部门中81年1月1日以后来的员工数
select deptno,count(*) from emp where hiredate>=to_date(‘1981-01-01’,’yyyy-mm-dd’) group by deptno;
13.查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资
select ename,sal from emp where (job=’MANAGER’ or job=’SALES’) and deptno in (select deptno from dept where loc=’CHICAGO’);
14.查询列出来公司就职时间超过24年的员工名单
select ename from emp where hiredate<=add_months(sysdate,-288);
15.查询于81年来公司所有员工的总收入（SAL和COMM）
select sum(sal+nvl(comm,0)) from emp where to_char(hiredate,’yyyy’)=’1981’;
16.查询显示每个雇员加入公司的准确时间，按××××年××月××日 时分秒显示。
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
17.查询公司中按年份月份统计各地的录用职工数量
select to_char(hiredate,'yyyy-mm'),loc,count(*) from emp,dept
where emp.deptno=dept.deptno group by to_char(hiredate,'yyyy-mm'),loc;
18.查询列出各部门的部门名和部门经理名字
select dname,ename from emp,dept where emp.deptno=dept.deptno and job=’MANAGER’;
19.查询部门平均工资最高的部门名称和最低的部门名称
select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) ) where rownum<=1)
union all select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) desc ) where rownum<=1);
20.查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名
select ename,dname
from (select ename,deptno from
(select ename,deptno from emp where hiredate>(select hiredate from emp where empno=7521) order by hiredate ) where rownum<=1) e,dept
where e.deptno=dept.deptno
3352
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
