一些oracle题目

转过来的oracle题目，有用。

一:
oracle查询表中15—20条记录
(select * from AAA where rownum < 20) minus (select * from AAA where rownum < 15);

SQL MINUS 前面数据集减后面数据集

create table thedate (riqi varchar2(10),shengfu varchar2(10));

insert into thedate values('2005-05-09','胜');
insert into thedate values('2005-05-09','胜');
insert into thedate values('2005-05-09','负');
insert into thedate values('2005-05-09','胜');
insert into thedate values('2005-05-09','胜');
insert into thedate values('2005-05-10','胜');
insert into thedate values('2005-05-10','负');
insert into thedate values('2005-05-10','负');

根据日期统计胜负数量：
select riqi,sum(case when shengfu = '胜' then 1 else 0 end ) as sheng,
            sum(case when shengfu = '负' then 1 else 0 end) as fu
from thedate group by riqi;

二:
已知一个表的结构为：
姓名  科目  成绩
张三  语文  20
张三  数学  30
张三  英语  50
李四  语文  70
李四  数学  60
李四  英语  90
怎样通过select语句把他变成以下结构：
姓名  语文  数学  英语
张三  20    30    50
李四  70    60    90

create table student (姓名 varchar2(10),科目 varchar2(10),成绩 int);
insert into student values('张三','语文',20);
insert into student values('张三','数学',30);
insert into student values('张三','英语',50);
insert into student values('李四','语文',70);
insert into student values('李四','数学',60);
insert into student values('李四','英语',90);
drop table student;

select decode(姓名,null,'总和',姓名) "姓名",
sum(decode(科目,'语文',成绩)) "语文",
sum(decode(科目,'数学',成绩)) "数学",
sum(decode(科目,'英语',成绩)) "英语",
sum(成绩)  "总成绩"
from student  group by 姓名;

select decode(姓名,null,'总和',姓名) "姓名",
sum(case when 科目 = '语文' then 成绩 end) "语文",
sum(case when 科目 = '数学' then 成绩 end) "数学",
sum(case when 科目 = '英语' then 成绩 end) "英语",
sum(成绩)  "总成绩"
from student  group by 姓名;

decode相当于case when then else

 

三:
SQL问答题
SELECT * FROM TABLE
和
SELECT * FROM TABLE
WHERE NAME LIKE '%%' AND ADDR LIKE '%%'
AND (1_ADDR LIKE '%%' OR 2_ADDR LIKE '%%'
OR 3_ADDR LIKE '%%' OR 4_ADDR LIKE '%%' )
的检索结果为何不同?

null的关系

 

四：
一、
教师号　　星期号　是否有课
　１　　　　２　　　有
　１　　　　３　　　有
　２　　　　１　　　有
　３　　　　２　　　有
　１　　　　２　　　有
写一条sql语句让你变为这样的表
教师号　星期一　星期二　星期三
　１　　　　　　　２　　　１　
　２　　　１　　　
　３　　　　　　　１
各星期下的数字表示：对应的教师在星期几已经排的课数

create table teacher(教师号 int,星期号 int,是否有课 varchar2(10));
insert into teacher values(1,2,'有');
insert into teacher values(1,3,'有');
insert into teacher values(2,1,'有');
insert into teacher values(3,2,'有');
insert into teacher values(1,2,'有');
select * from teacher;
drop table teacher;

select 教师号,
       sum(case when 星期号 = 1 then 1 else 0 end) as 星期一,
       sum(case when 星期号 = 2 then 1 else 0 end) as 星期二,
       sum(case when 星期号 = 3 then 1 else 0 end) as 星期三
from teacher group by 教师号;

 

 

五：
书表(books)
book_id,book_name,creatdate,Lastmodifydate,decription
001,三个人的世界,2005-02-02,2005-07-07,NULL
作者表(authors)
A_id,A_name
01,王纷
02,李尚
03,泰和
部门表(depts)
d_id,d_name
001,编辑一部
002,编辑二部
003,编辑三部
书和作者关联表(bookmap)
book_id,A_id
001,01
001,02
001,03
部门和作者关联表(depmap)
d_id,a_id
001,01
002,02
003,03
找出每个部门的所写的总书两,比如,一本书有3个人写,如果三个人在不同的部门,则每个部门的总数量就是1.最后结果如下:
部门,书量
编辑一部,1
编辑二部,1
编辑三部,1

 

六：
两个表情况
表名：wu_plan
ID     plan     model      corp_code    plannum    prixis
1      00001    exx22        nokia       2000        0
2      00002    lc001        sony        3000        0

表名：wu_bom
ID    plan      pact         amount
1     00001     aa1           300
2     00001     aa2           200
3     00002     bb1           500
4     00002     bb2           800
5     00002     bb3           400

查询这两个表中plan唯一，每一个plan中，amount最少的，plannum大于prixis的记录
结果是:
ID     plan     model      corp_code    plannum    prixis    pact  amount
1      00001    exx22        nokia       2000        0      aa2     200
2      00002    lc001        sony        3000        0      bb3     400

create table wu_bom(ID int,plan varchar2(10),pact varchar2(10),amount int);
insert into wu_bom values(1,'0001','aa1',300);
insert into wu_bom values(2,'0001','aa2',200);
insert into wu_bom values(3,'0002','bb1',500);
insert into wu_bom values(4,'0002','bb2',800);
insert into wu_bom values(5,'0002','bb3',400);
select * from wu_bom;
drop table wu_bom;

select plan,min(amount) from wu_bom group by plan;

七：
表1结构如下：
部门 条码 品名 销售额 销售数量 销售日期

表2结构如下
课别 部门

要求：先按部门排序，再按销售额、销售数量排序检索出某个课别每个部门一个时期内的商品销售额的前三名，
如查询01课别2007年4月15日到2007年4月22日每个部门一个周内的商品销售额合计的前三名

DDL是数据定义语言，如drop，alter，truncate等都是DDL
insert，update，delete，merge等都是DML
在oracle里DML是可以rollback的，而DDL是不可以的
DDl语句结束一个transaction

LIKE '%SA/_%'ESCAPE'/';

SELECT LPAD(17000,10,'*') FROM dual;

inner join,full outer join,left join,right jion
内部连接 inner join 两表都满足的组合
full outer 全连 两表相同的组合在一起，A表有，B表没有的数据（显示为null）,同样B表有A表没有的显示为(null)
A表 left join  B表 左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null
A表 right join B表 右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null

数据类型？

mod取余数 round是四舍五入 trunc是截取 TRUNC(5.75, -1)=0  ROUND(5.75, -1)=10
对date类型操作的函数可以是count min max
where 中不能使用聚集函数 having中可以
SQL的执行顺序为：先where 再group 再having 再select 后order

创建视图的select语句里面不能包含 union 和order by

八：
create tavle aaa(
id int,
sname varchar2(10),
smoney int,
sprovince varchar2(10));

insert into aaa values(1,'zhnagsan',2098,'A');
insert into aaa values(2,'lisi',3000,'B');
insert into aaa values(3,'wangwu',6789,'C');
insert into aaa values(4,'liumazi',4587,'C');
insert into aaa values(5,'dongjiu',3298,'B');
insert into aaa values(6,'shiga',4567,'A');
select * from aaa;
drop table aaa;

id: 合同id sname：姓名 smoney：业绩 sprovince：地区
1：显示出业绩大于同一地区平均值的合同id 姓名 地区 业绩
select aaa.* from
aaa join (select avg(smoney) as thesum,sprovince from aaa group by sprovince) bbb
on (aaa.sprovince=bbb.sprovince) where aaa.smoney>thesum;

2：把同一地区的 平均业绩 地区 插入到新表中（新表只包含两个字段：平均业绩 地区）
inset into bbb(thesum,sprovince)
select avg(smoney),sprovince from aaa group by sprovince;

九：
create table aaa(
id int,
region varchar2(10),
saler varchar2(10),
money int);

insert into aaa values(1,'北京','杨健',100);
insert into aaa values(2,'上海','张三',200);
insert into aaa values(3,'杭州','李四',500);
insert into aaa values(4,'上海','王五',200);
insert into aaa values(5,'上海','赵六',400);
insert into aaa values(6,'北京','田七',300);
insert into aaa values(7,'北京','武八',200);
insert into aaa values(8,'杭州','陈九',100);
select * from aaa;
drop table aaa;

查询每个区域有多少个销售人员并按区域倒叙排序
select region,count(saler) from aaa group by region desc;

查询所有相同区域中合同金额最少的saler
select aaa.* from aaa a where a.money = (select min(money) from aaa b where a.region=b.region);

查询表中合同金额小于所在区域平均合同金额的合同id
select * from aaa a where a.money < (select avg(money) from aaa b where a.region = b.region group by region);

十：
create table aaa(
product varchar2(10),
colour varchar2(10),
thenumber int);

insert into aaa values('产品1','红色',123);
insert into aaa values('产品2','蓝色',126);
insert into aaa values('产品2','蓝色',103);
insert into aaa values('产品2','红色',100);
insert into aaa values('产品1','红色',89);
insert into aaa values('产品1','红色',223);
insert into aaa values('产品3','红色',156);
insert into aaa values('品3','蓝色',223);
产
按产品分类，仅列出各商品中红色多于蓝色的商品名称及差额

select t1.product,t2.sum_lan-t1.sum_hong as theplus from
(select product,sum(THENUMBER) as sum_hong from aaa where colour = '红色' group by product) t1 join
(select product,sum(THENUMBER) as sum_lan from aaa where colour = '蓝色' group by product) t2
on t1.product = t2.product
where t1.sum_hong<sum_lan;

十一：测试null

create table aaa(id int,value int);
insert into aaa values(1,100);
insert into aaa values(2,98);
insert into aaa values(3,null);
insert into aaa values(4,114);
insert into aaa values(5,90);
insert into aaa values(6,45);

select count(value) from aaa;
COUNT(VALUE)
------------
           5

select id,count(value) from aaa group by id;
                                     ID COUNT(VALUE)
--------------------------------------- ------------
                                      6            1
                                      5            1
                                      1            1
                                      2            1
                                      3            0         --对应着0
                                      4            1

select sum(value) from aaa;
SUM(VALUE)
----------
       447  --把null当做0计算

select nvl(value,100) from aaa where id = 3;
NVL(VALUE,100)
--------------
           100

十二：
用一条SQL语句 查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

create table chengji(
name varchar2(10),
kecheng varchar2(10),
fenshu int
);

insert into chengji values('张三','语文',81);
insert into chengji values('张三','数学',75);
insert into chengji values('李四','语文',76);
insert into chengji values('李四','数学',90);
insert into chengji values('王五','语文',81);
insert into chengji values('王五','数学',100);
insert into chengji values('王五','英语',90);

select * from chengji;
drop table chengji;

select distinct name from chengji where name not in (select distinct name from chengji where fenshu<=80);

十三：
学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息

create table student(
自动编号 int,
学号 varchar2(10),
姓名 varchar2(10),
课程编号 varchar2(10),
课程名称 varchar2(10),
分数 int
);

insert into student values(1,'2005001','张三','0001','数学',69);
insert into student values(2,'2005001','李四','0001','数学',89);
insert into student values(3,'2005001','张三','0001','数学',69);
insert into student values(4,'2005001','张三','0001','数学',69);

select * from student;
drop table student;

delete from student where 自动编号 not in (select min(自动编号) from student group by (学号,姓名,课程编号,课程名称,分数));

 

十四：
一个叫department的表，里面只有一个字段name,一共有4条纪录，
分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql
语句显示所有可能的比赛组合.

create table department(name varchar2(5));
insert into department values('a');
insert into department values('b');
insert into department values('c');
insert into department values('d');
select * from department;

select * from department a join department b on (a.name<b.name);