顺丰大数据笔试题与答案

SELECT * FROM CLASS_221;
SELECT * FROM  Stu_221;
select * from SCORE_221;

1.用一条sql查询出每个年级参加每门科目考试的总人数及平均成绩。

SELECT  T2.年级,T1.课程
       ,COUNT(1) AS COUNT_1
       ,AVG(T1.分数) AS AVG_SC
FROM SCORE_221 T1 
INNER JOIN STU_221 T2 
        ON T1.学号 = T2.学号
GROUP  BY T2.年级,T1.课程;

2.用一条sql查询出每个年级参加了所有科目考试的学生的总人数。
--先确定这些学生是否参加了所有科目考试
实际参加的考试课程数  =  应该参加的考试课程数
SELECT T3.年级
      ,COUNT(1) AS 总人数
FROM (
      SELECT T2.学号,T2.年级
            ,COUNT(1) AS 实际参加的考试课程数
      FROM SCORE_221 T1 
      INNER JOIN STU_221 T2 
              ON T1.学号 = T2.学号
      GROUP BY T2.学号,T2.年级
     ) T3 
INNER JOIN (
            SELECT  T.年级
                    ,COUNT(1) AS 应该参加的考试课程数
            FROM CLASS_221 T
            GROUP BY T.年级
           ) T4
       ON T3.年级 = T4.年级
      AND T3.实际参加的考试课程数 = T4.应该参加的考试课程数
GROUP BY T3.年级;

3.用一条sql查询2年级参加了所有科目考试并且每门课程的成绩都在80分以上的学生信息。
SELECT T1.学号
      ,COUNT(1)
FROM SCORE_221 T1 
INNER JOIN STU_221 T2 
        ON T1.学号 = T2.学号
WHERE T2.年级 = 2
AND T1.分数 > 80
GROUP BY T1.学号
HAVING COUNT(1) = (
                   SELECT COUNT(2)
                   FROM CLASS_221  T
                   WHERE T.年级 = 2
                   );
                   

--CITY 
select * from zone;

SELECT T.NAME, T.ADDRESS
       ,SUBSTR(T.ADDRESS
              ,INSTR(T.ADDRESS,'-',1,1) + 1 --从哪个位置开始截取
              ,INSTR(T.ADDRESS,'-',1,2) - INSTR(T.ADDRESS,'-',1,1) -1  --截取的长度
              ) AS CITY
FROM ZONE T 

---运单快递
如何统计出在2018-03-01 13:00到2018-03-01 15：00之间在该网点有停留的运单数
select * from DD;

             13                15
 arr              leav                
 arr                                   leav   
                 arr   leav
                 arr                 leav
                 
SELECT COUNT(1)
FROM DD T 
WHERE T.ARR_TIME <> T.LEAVE_TIME
AND T.ARR_TIME <= TO_DATE(20180301150000,'YYYYMMDDHH24MISS') 
AND T.LEAVE_TIME >= TO_DATE(20180301130000,'YYYYMMDDHH24MISS')
-----------              
WHERE T.ARR_TIME <> T.LEAVE_TIME
AND ( T.ARR_TIME <= TO_DATE(20180301130000,'YYYYMMDDHH24MISS') 
       AND T.LEAVE_TIME > TO_DATE(20180301130000,'YYYYMMDDHH24MISS') 
      OR T.ARR_TIME BETWEEN TO_DATE(20180301130000,'YYYYMMDDHH24MISS')  AND TO_DATE(20180301150000,'YYYYMMDDHH24MISS') 
    )