目录
一、查询
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 e