中国移动sql面试题：
create table test(
id number(10) primary key,
type number(10) ,
t_id number(10),
value varchar2(5)
);
insert into test values(100,1,1,‘张三‘);
insert into test values(200,2,1,‘男‘);
insert into test values(300,3,1,‘50‘);
insert into test values(101,1,2,‘刘二‘);
insert into test values(201,2,2,‘男‘);
insert into test values(301,3,2,‘30‘);
insert into test values(102,1,3,‘刘三‘);
insert into test values(202,2,3,‘女‘);
insert into test values(302,3,3,‘10‘);
请写出一条查询语句结果如下：
姓名 性别 年龄
--------- -------- ----
张三 男 50
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.一道SQL语句面试题，关于group by
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table tmp(rq varchar2(10),shengfu varchar2(5));
insert into tmp values(‘2005-05-09‘,‘胜‘);
insert into tmp values(‘2005-05-09‘,‘胜‘);
insert into tmp values(‘2005-05-09‘,‘负‘);
insert into tmp values(‘2005-05-09‘,‘负‘);
insert into tmp values(‘2005-05-10‘,‘胜‘);
insert into tmp values(‘2005-05-10‘,‘负‘);
insert into tmp values(‘2005-05-10‘,‘负‘);
4.create table STUDENT_SCORE
(
name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values (‘张三‘, ‘语文‘, 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘张三‘, ‘数学‘, 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘张三‘, ‘英语‘, 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘李四‘, ‘语文‘, 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘李四‘, ‘数学‘, 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘李四‘, ‘英语‘, 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘王五‘, ‘语文‘, 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘王五‘, ‘数学‘, 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values (‘王五‘, ‘英语‘, 91.0);
4.1得到类似下面的结果
姓名 语文 数学 英语
王五 89 56 89
4.2有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
5.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，
只是作为一个格式向大家请教。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门dep 一月份 二月份 三月份
--------------------------------------
01 10
02 10 8
03 5 8
04 9
------------------------------------------
create table yj01(
month varchar2(10),
deptno number(10),
yj number(10)
)
insert into yj01(month,deptno,yj) values(‘一月份‘,01,10);
insert into yj01(month,deptno,yj) values(‘二月份‘,02,10);
insert into yj01(month,deptno,yj) values(‘二月份‘,03,5);
insert into yj01(month,deptno,yj) values(‘三月份‘,02,8);
insert into yj01(month,deptno,yj) values(‘三月份‘,04,9);
insert into yj01(month,deptno,yj) values(‘三月份‘,03,8);
create table yjdept(
deptno number(10),
dname varchar2(20)
)
insert into yjdept(deptno,dname) values(01,‘国内业务一部‘);
insert into yjdept(deptno,dname) values(02,‘国内业务二部‘);
insert into yjdept(deptno,dname) values(03,‘国内业务三部‘);
insert into yjdept(deptno,dname) values(04,‘国际业务部‘);
select
max(decode(type,1,value)) 姓名,
max(decode(type,2,value)) 性别,
max(decode(type,3,value)) 年龄
from test group by t_id;
select
rq,
count(decode(shengfu,‘胜‘,1)) 胜,
count(decode(shengfu,‘负‘,2)) 负
from tmp
group by rq
order by rq
select
rq,
decode(shengfu,‘胜‘,1) 胜,
decode(shengfu,‘负‘,2) 负
from tmp
order by rq
select
name,
max(decode(subject,‘语文‘,score)) 语文,
max(decode(subject,‘数学‘,score)) 数学,
max(decode(subject,‘英语‘,score)) 英语
from student_score
group by name
select
name,
case
when max(decode(subject,‘语文‘,score))>=80 then ‘优秀‘
when max(decode(subject,‘语文‘,score))>=60 then ‘及格‘
when max(decode(subject,‘语文‘,score))<60 then ‘不及格‘
else ‘未知‘ END 语文,
case
when max(decode(subject,‘数学‘,score))>=80 then ‘优秀‘
when max(decode(subject,‘数学‘,score))>=60 then ‘及格‘
when max(decode(subject,‘数学‘,score))<60 then ‘不及格‘
else ‘未知‘ END 数学,
case
when max(decode(subject,‘英语‘,score))>=80 then ‘优秀‘
when max(decode(subject,‘英语‘,score))>=60 then ‘及格‘
when max(decode(subject,‘英语‘,score))<60 then ‘不及格‘
else ‘未知‘ END 英语
from student_score
group by name
select y.dname,yj.one,yj.two,yj.three from(
select
deptno,
max(decode(month,‘一月份‘,yj)) one,
max(decode(month,‘二月份‘,yj)) two,
max(decode(month,‘三月份‘,yj)) three
from YJ01 group by deptno)yj
left outer join YJDEPT y on yj.deptno =y.deptno