目录

 

一、查询

1、学生表成绩查询

2、查询客户费用记录

3、年月转季度练习

4、行列转换练习(case when/union all)

5、组合分析函数练习

6、union all 练习

7、同比环比的练习（位移函数）

8、组合分析函数练习

---

一、查询

1、学生表成绩查询

 

建表插入数据：

create table SCORE_fengzi
(
  cname   VARCHAR2(10),
  sciense VARCHAR2(10),
  score   NUMBER(5)
)

这句话可以用plsql编辑表数据：

SELECT  * FROM SCORE_fengzi FOR UPDATE;

1）用一条SQL查出所有学科分数都大于80分的学生姓名。(找每个人最低一门功课的成绩大于80即可）

--这个是查询每门大于80的数据得出的
select cname,count(1) from score_fengzi 
where score > 80
group by cname
having count(1) > 1

select cname from score_fengzi
group by cname
having min(score) > 80

2）用一条SQL查出每门学科都高于该学科平均分的学生姓名。（先查询学科平均分，再用left join 连表查询）

select cname from score_fengzi s1
left join 
(select sciense, avg(score) avs  from score_fengzi
group by sciense) s2
on s1.sciense = s2.sciense 
where s1.score > s2.avs
group by s1.cname
having count(1) = (select count(distinct(sciense)) from score_fengzi)

3）用一条SQL查出如下的结果：

select cname as "姓名",
sum(case when sciense = '语文' then score end ) as "语文",
sum(case when sciense = '数学' then score end ) as "数学"
from score_fengzi
group by cname
order by cname

2、查询客户费用记录

1）有一个电话客户费用记录的表
TB_CHARGE(SERV_ID,ACCT_ITEM,CHARGE, REC_DATE),四个字段分别是用户ID，账目项，金额和消费日期，示例数据如表1所示，请用SQL语句做成如表2所示的结果。

参考建表为T：

select serv_ID,
sum(case when ACCT_ITEM = '市内通话费' then charge else 0 end) as "市内通话费",
sum(case when ACCT_ITEM = '省内长途费' then charge else 0 end) as "省内通话费",
sum(case when ACCT_ITEM = '国内长途费' then charge else 0 end) as "国内通话费"
from T
group by serv_ID

3、年月转季度练习

1）左表Sales表是每个月的销量总额，用SQL表达出：统计每个季度的销量总额，如右表所示：

select substr(months,1,4)||'Q'||to_char(to_date(months,'yyyymm'),'Q') as season, sum(sell) as sell from sales
group by substr(months,1,4)||'Q'||to_char(to_date(months,'yyyymm'),'Q')

注释：

• 用拼接的方法先得出season
• SUBSTR(X,START,N) 字符截取函数 把字符X从START开始截取N位，如果N省掉,默认截取到最后一位
• 数据类型转换函数

TO_NUMBER()

to_char()

to_date()

4、行列转换练习(case when/union all)

1）case when/union all .练习

• 一开始只会横着的表：

select 
sum(case when age < 10 then cnt end ) as "[0,10)",
sum(case when age >= 10 and age <20 then cnt end ) as "[10,20)",
sum(case when age >=20 and age <30 then cnt end ) as "[20,30)",  
sum(case when age >=30 then cnt end ) as "30以上"
from test_fengzi

 

 

• 竖着的表用union all 连接

select '[0,10)' as age, sum(cnt) as cnt from test_fengzi where age between 0 and 9
union all
select '[10,20)' as age, sum(cnt) as cnt from test_fengzi where age between 10 and 19
union all
select '[20,30)' as age, sum(cnt) as cnt from test_fengzi where age between 20 and 29
union all
select '30以上' as age, sum(cnt) as cnt from test_fengzi where age>=30

2）case when 练习

select cus_id,sum(case when  acc_type = 'Checking' then acc_no end) checkingaccount,
sum(case when  acc_type = 'saving' then acc_no end) savingaccount
from accounts
group by cus_id

5、组合分析函数练习

select date_id,daliy_out,sum(daliy_out)over(order by daliy_out desc)  from pro 
group by date_id,daliy_out
order by date_id

6、union all 练习

主键 地区 邮编 楼栋 教室
1 广州 510000 7 118

转换为

Name value
主键 1
地区 广州
邮编 510000
楼栋 7
教室 118

select 'primaryy' name,to_char(primaryy) value from wx
union all
select 'district' name,to_char(district) value from wx
union all
select 'postcode' name,to_char(postcode) value from wx
union all
select 'address' name,to_char(address) value from wx
union all
select 'class' name,to_char(class) value from wx

7、同比环比的练习（位移函数）

1）现有一张销量表SALE02，数据如左表所示。要求写出SQL语句，完成建一个视图V_SALE，求销量的去年同比和上月环比，如右表所示：

• 同比是指与上一统计年度的同期进行对比；
• 环比是指与上一统计周期进行对比。

select months,sell,lag(sell,12)over(order by months) lastyear,
lag(sell,1)over(order by months) lastmonth from sell02

• 求同比和环比

select s.months,s.sell,s.sell/a.lastyear  tt, s.sell/a.lastmonth hh from sell02 s,
(select months,sell,lag(sell,12)over(order by months) lastyear,
lag(sell,1)over(order by months) lastmonth from sell02)a
where s.months = a.months

select s.months,s.sell,round(s.sell/a.lastyear*100 )|| '%' tt, s.sell/a.lastmonth hh from sell02 s,
(select months,sell,lag(sell,12)over(order by months) lastyear,
lag(sell,1)over(order by months) lastmonth from sell02)a
where s.months = a.months

• 同比、环比用百分号显示，但是我目前没找到更好的办法让有数据的字段显示%，目前做成的了没有数据的也有了%，后期再查资料看吧。

8、组合分析函数练习

1）求EMP表中每个部门工资高于部门平均工资超过2人的员工数量占整个部门人数的百分比（题目意思是例如部门A总人数为10，超过部门平均工资的人数是6，则6/10）

1. 先分别计算部门的平均工资和部门总人数
2. 再计算超过部门平均工资有两个人以上的部门人数

--1、先分别计算部门的平均工资和部门总人数

select e.* ,AVG(SAL)OVER(PARTITION BY DEPTNO) avgsal,
 count(1) over( partition by deptno ) tt from emp e

 

--再计算超过部门平均工资有两个人以上的部门人数

select b.deptno, sum(case when b.sal > a.avgsal then 1 else 0 end)/a.tt ss from emp b,
(select e.* ,AVG(SAL)OVER(PARTITION BY DEPTNO) avgsal,
 count(1) over( partition by deptno ) tt from emp e) a
where b.empno = a.empno
and a.tt > 2
group by b.deptno,a.tt

9、有球队a、b、c、d四个队，两两组队比赛的情况有多少种

 

建表：

create table department_fengzi (dname varchar2(5));


select * from department_fengzi;

insert into department_fengzi values ('a');
insert into department_fengzi values ('b');
insert into department_fengzi values ('c');
insert into department_fengzi values ('d');

select s.d1||s.d2 from
(select a.dname d1,b.dname d2 from department_fengzi a,department_fengzi b
where a.dname < b.dname) s