关于几道sql笔试题的疑问
===================================================
第一题:

select * from t19;

MON DEPTNO ACH
---------------- -------- ----------
m1 d1 10
m1 d2 10
m1 d3 5
m2 d2 8
m2 d4 9
m3 d3 8

select * from t20;

DEPTNO DNAME
-------- ----------------
d1 cd1
d2 cd2
d3 cd3
d4 cd4

select t.deptno,max(t.dname),max(t.m1),max(t.m2),max(t.m3) from (select
a.deptno,b.dname,decode(a.mon,'m1',ach,0) as m1,decode(a.mon,'m2',ach,0) as
m2,decode(a.mon,'m3',ach,0) as m3 from t19 a,t20 b where a.deptno=b.deptno) t
group by t.deptno order by t.deptno;

=================================================
第二题:

select * from t6;

NAME 语文 数学 英语
---------- ---------- ---------- ----------
SEKER 65 75 60
BLUES 60 90 0
PG 0 80 90
ZORRO 70 80 75

select rownum as id,a.name,a.subject,a.grade from (select name,'语文' as
subject,decode(语文,0,'',语文) as grade from t6 union all select name,'数学'
as subject,decode(数学,0,'',数学) as grade from t6 union all select
name,'英语' as subject,decode(英语,0,'',英语) as grade from t6) a;

ID NAME SUBJEC GRADE
---------- ---------- ------ ----------------------------------------
  1 SEKER 语文 65
  2 BLUES 语文 60
  3 PG 语文
  4 ZORRO 语文 70
  5 SEKER 数学 75
  6 BLUES 数学 90
  7 PG 数学 80
  8 ZORRO 数学 80
  9 SEKER 英语 60
  10 BLUES 英语
  11 PG 英语 90
  12 ZORRO 英语 75

请问如何优化或者调整以上两条红色的sql语句,以提高执行效率或者精简sql语句

==================================================
第三题:
no name
1 a
2 b
3 c
4 d
写一条sql语句显示以下结果
ab ac ad bc bd cd

虽然有最简单的sql写法,但估计不是出题者想要的,可以不用子查询直接用decode或者case when写出吗

=================================================
第四题个人认为比较变态的题目)
id a1 a2 a3 a4 a5 a6 a7 a8
1,1, 1, 2, 2, 3, 3, 4, 4 算两个重复的一行
2,1, 1, 2, 5, 6, 3, 4, 9算两个重复的一行
3,1, 1, 1, 3, 5, 7 ,2 ,6 算三个重复的一行
4,1, 1, 1 , 3, 3 ,3 8 ,8 算三个重复的一行
5,1, 1, 3, 3, 3 ,3 8 ,8 算四个重复的一行
... ....
请编写sql语句得到
有2列重复行的id: 1, 2
有3列重复行的id: 3, 4
有4列重复行的id: 5
.... ....

------解决方案--------------------
在家用mysql模拟出来，先上第四题答案

SQL code

select concat(t1.name,t2.name) from test t1,test t2 where t1.no<
t2.no;
换成ORACLE应该是
select t1.name||t2.name from test t1,test t2 where t1.no<
t2.no;

------解决方案--------------------

第1题：
SQL code

WITH t19 AS (
    SELECT 'm1' mon,'d1' deptno,10 ach FROM DUAL UNION ALL
    SELECT 'm1' mon,'d2' deptno,10 ach FROM DUAL UNION ALL
    SELECT 'm1' mon,'d3' deptno,5 ach FROM DUAL UNION ALL
    SELECT 'm2' mon,'d2' deptno,8 ach FROM DUAL UNION ALL
    SELECT 'm2' mon,'d4' deptno,9 ach FROM DUAL UNION ALL
    SELECT 'm3' mon,'d3' deptno,8 ach FROM DUAL
),
t20 AS (
    SELECT 'd1' deptno,'cd1' dname FROM DUAL UNION ALL
    SELECT 'd2' deptno,'cd2' dname FROM DUAL UNION ALL
    SELECT 'd3' deptno,'cd3' dname FROM DUAL UNION ALL
    SELECT 'd4' deptno,'cd4' dname FROM DUAL
)
SELECT a.deptno,
       MAX(DECODE(a.mon, 'm1', ach, 0)) m1,
       MAX(DECODE(a.mon, 'm2', ach, 0)) m2,
       MAX(DECODE(a.