【讨论】大公司面试题，测测你的SQL水平

jinaqu

hitech560 发表于 2014-12-29 06:44
表 t_score 的字段 stu_id 不应该是 primary key吧。

哦哦。为何？？？？

DylanX

jinaqu 发表于 2014-12-29 09:12
哦哦。为何？？？？

原因：t_score表结构的primary key和后续的insert语句矛盾有主键冲突
另外
第二题，稍加改动：

1. 
   
2. SELECT max(lession_name) "课程",
   
3.        max(decode(rn,1,stu_name||'+'||to_char(score))) "第一名(姓名+分数) ",
   
4.        max(decode(rn,2,stu_name||'+'||to_char(score))) "第二名(姓名+分数) ",
   
5.        max(decode(rn,3,stu_name||'+'||to_char(score))) "第三名(姓名+分数) "
   
6. FROM (SELECT t1.stu_name,
   
7.              t2.lession_name,
   
8.              t.score,
   
9.              row_number() over (PARTITION BY t.lession_id ORDER BY t.score DESC) rn
   
10.             FROM t_score t, t_stu_profile t1, t_lession t2
    
11.             WHERE t1.stu_id = t.stu_id
    
12.             AND t2.lession_id = t.lession_id
    
13.             ORDER BY t.lession_id, rn) a
    
14. WHERE  rn <= 3
    
15. GROUP BY lession_name
    
16. ORDER BY lession_name
    
17. ;
    

复制代码

highroom

1).写出学生没有参加考试的课程,以下形式显示 ，我写的SQL
select A.STU_NAME, A.CLASS_ID, B.LESSION_NAME from t_stu_profile A, t_lession B WHERE (A.STU_ID,B.LESSION_ID) NOT IN (select STU_ID, LESSION_ID from t_score)

highroom

B.2.        找出课程的前三名，以下列形式显示 ,做得有点费力哦

select a1.lession_id, MAX(a1.第一名), MAX(a1.第二名), MAX(a1.第三名) from
(
SELECT A.LESSION_ID,
(CASE WHEN ORDER_ID=1 THEN STU_NAME||SCORE ELSE '' END) 第一名,
(CASE WHEN ORDER_ID=2 THEN STU_NAME||SCORE ELSE '' END) 第二名,
(CASE WHEN ORDER_ID=3 THEN STU_NAME||SCORE ELSE '' END) 第三名
FROM
(
select T.STU_ID,X.STU_NAME, T.LESSION_ID, Y.LESSION_NAME,T.SCORE, ROW_NUMBER() OVER (PARTITION BY T.LESSION_ID ORDER BY T.LESSION_ID) ORDER_ID from t_score T, t_stu_profile X, t_lession Y
WHERE T.STU_ID=X.STU_ID AND T.LESSION_ID=Y.LESSION_ID
) A
)a1 GROUP BY a1.lession_id

初识orcl

好东西，mark一下，研究一下。

jinaqu

这些东东都是面试的时候，面试官随便出的，有点措手不及，并且当时没有电脑，只能写在纸上，大家好好研究研究吧，哈哈

newkid

jinaqu 发表于 2015-1-19 09:25
这些东东都是面试的时候，面试官随便出的，有点措手不及，并且当时没有电脑，只能写在纸上，大家好好研究研 ...

他随便说说你都能记得住？

solomon_007

SQL>
SQL> create table t_score(stu_id varchar2(3),lession_id varchar2(4),score number);

Table created
SQL> insert into t_score values('001',        'L001',        90);

1 row inserted
SQL> insert into t_score values('001',        'L002',        86);

1 row inserted
SQL> insert into t_score values('002',        'L001',        84);

1 row inserted
SQL> insert into t_score values('002',        'L004',        75);

1 row inserted
SQL> insert into t_score values('003',        'L003',        85);

1 row inserted
SQL> insert into t_score values('004',        'L005',        98);

1 row inserted
SQL> commit;

Commit complete
SQL> create table t_stu_profile(stu_id varchar2(3),stu_name varchar2(12),sex varchar2(1),age number,class_id varchar2(4));

Table created
SQL> insert into t_stu_profile values('001',        '郭东',        'F',        16,        '0611');

1 row inserted
SQL> insert into t_stu_profile values('002',        '李西',        'M',        18,        '0612');

1 row inserted
SQL> insert into t_stu_profile values('003',        '张北',        'F',        16,        '0613');

1 row inserted
SQL> insert into t_stu_profile values('004',        '钱南',        'M',        17,        '0611');

1 row inserted
SQL> insert into t_stu_profile values('005',        '王五',        'F',        17,        '0614');

1 row inserted
SQL> insert into t_stu_profile values('006',        '赵七',        'F',        16,        '0615');

1 row inserted
SQL> commit;

Commit complete
SQL> create table t_lession(lession_id varchar2(4),lession_name varchar2(8));

Table created
SQL> insert into t_lession values('L001',        '语文');

1 row inserted
SQL> insert into t_lession values('L002',        '数学');

1 row inserted
SQL> insert into t_lession values('L003',        '英语');

1 row inserted
SQL> insert into t_lession values('L004',        '物理');

1 row inserted
SQL> insert into t_lession values('L005',        '化学');

1 row inserted
SQL> commit;

Commit complete

SQL>
SQL> select d.stu_name,d.class_id,d.lession_name
  2    from (
  3          select a.stu_id,
  4                 a.stu_name,
  5                 a.class_id,
  6                 b.lession_id,
  7                 b.lession_name
  8            from t_stu_profile a,
  9                 t_lession     b
10          ) d,
11          t_score c
12   where d.stu_id     = c.stu_id     (+)
13     and d.lession_id = c.lession_id (+)
14     and c.lession_id is null
15   order by d.stu_id,d.class_id,d.lession_id;

STU_NAME     CLASS_ID LESSION_NAME
------------ -------- ------------
郭东         0611     英语
郭东         0611     物理
郭东         0611     化学
李西         0612     数学
李西         0612     英语
李西         0612     化学
张北         0613     语文
张北         0613     数学
张北         0613     物理
张北         0613     化学
钱南         0611     语文
钱南         0611     数学
钱南         0611     英语
钱南         0611     物理
王五         0614     语文
王五         0614     数学
王五         0614     英语
王五         0614     物理
王五         0614     化学
赵七         0615     语文

STU_NAME     CLASS_ID LESSION_NAME
------------ -------- ------------
赵七         0615     数学
赵七         0615     英语
赵七         0615     物理
赵七         0615     化学

24 rows selected

SQL>

visual2006

恭喜！您刚拣到ITPUB送出的 马上有房 。

jinaqu

newkid 发表于 2015-1-20 04:43
他随便说说你都能记得住？

嗯，只记得几个印象深的，当场是要在纸上做出来的，并且还需要说出思路