2018 东华软件股份有限公司 ETL开发工程师笔试题

1、

delete from score where score < 60 ;

2、

select count(*)

from teacher where tname like '李%' ;

3、

select

使用row_numbering函数对学生按分数降序排列生成行号并命名为rn_value，
子查询从student表中选择sname字段并根据学号匹配后命名为sub_query。

score
from score s , course c

where s.cno=c.cno
and c.cname='数学';

方式二：

select t1.sname ,t3.score

from student t1 ,course t2, score t3

where t1.sno=t3.sno

and t2.cno=t3.cno

and t2.cname='数学'

order by t3.score desc ;

4、

select cno,

max(score) 最高分 ,

min(score) 最低分
from score group by cno ;

5、

update score set score = score + 5 where score <= 60-5

第二种思路：

update score set score =

( case when score < 55 then score + 5

when score >= 55 then 60

end )

where score <= 60-5

6、

select cno,cname

from course c
where not exists (

select 1 from score s where c.cno=s.cno

);

方式二：

select cno , cname

from course

where cno not in (select cno from score);

7. 查询所有同学， 就表示 需要以学生表为主表 左连接

select t1.sno,

t1.sname,

count(t2.cno) as ct,

sum(t2.score) as 总成绩

from student t1

left join score t2

on t1.sno = t2.sno

group by t1.sno, t1.sname

8、

选择学号和学生姓名
从成绩表和学生表获取信息
其中子查询中获取学号t.cno等于‘011’的结果作为条件
并满足cno等于‘002’
并且scores中的学号等于students中的学号

方式二：

select t3.sno,t3.sname

from score t1, score t2 , student t3

where cno='011' and cno='002'

and t1.sno=t2.sno

and t1.sno=t3.sno

9、

select t1.sno,t1.sname

from student t1 inner join score t2

on t1.sno=t2.sno

group by t1.sno,t1.sname

having max(t2.score)<60

方式二：

select sno , sname

from student

where sno in ( select sno from score group by sno having max(score) < 60 );

10、

insert into score ( sno, cno, score ) select

sno ,
(select cno from course c where c.cname='历史') ,
80
from student

11、行转列 多行转成一行

SELECT 学号, 姓名, 平均分 AS 学科平均分, 平均分 AS 任课教师平均分, 平均分 AS 修课人数, 平均成绩 FROM 学生表
LEFT JOIN (
SELECT 学号, 课程编号, 课程名称, COALESCE(AVG(成绩), 0) AS 学科平均分
FROM 成绩表 ON 课程编号 = 课程编号
GROUP BY 学号, 课程编号, 课程名称
) AS 表1,
(SELECT 学号, 课程编号, 课程名称, COALESCE(AVG(成绩), 0) AS 任课教师平均分,
COALESCE(COUNT(学号), 0) AS 修课人数,
COALESCE(AVG(成绩), NULL) AS 平均成绩 FROM 成绩表 ON 满足条件 GROUP BY 学号,课程编号,课程名称 ) AS 表2

连接条件设定为T1的学号等于T2的学号，并且T2的课程名称等于"数据库"。该操作通过LEFT JOIN与主表T3相连接。主表T3由以下子表生成：SELECT列包含T1的学生编号、课程编号以及对应课程名称和平均分（AS平均分）。此SELECT语句基于以下条件生成：从SCORE表T1和COURSE表T2中选择上述字段，在满足学生编号和课程编号相等的前提下进行分组统计。

ON T1.学号 = T3.公司名称 WHERE T3.公司名称 = '企业管理'
LEFT JOIN
(SELECT 学号, 课程编号, 公司名称, 平均分 AS 平均分
FROM SCORE表T1, COURSE表T2
WHERE 学号 = 课程编号 AND 课程编号 = '企业管理'
GROUP BY 学号, 课程编号, 公司名称) AS子查询

在T1的学生编号与T3的学生姓名相等的条件下进行连接
外连接查询结果集
该查询选择自T1的学号、计算其出现次数以及平均分数
并按学号进行分组形成的子查询结果集与主查询表T5进行外连接

ON T1.sno = T3.sno