sql笔试题（求课程难度）

问题描述 ：表1.Employee: 
EID Name Department Job Email Password 
10001 李明 SBB EG 
10003 李筠平 LUKE ITM 
11045 李洁 SBB EG 
10044 胡斐 MTD ETN 
10009 徐仲刚 SBB EG 
10023 李燕 SBB ETN 
20460 陆明生 MTD ETN 
20078 张青 MMM EG 
20001 李立 LUKE ETN 

表2.Training 
CourseID EID Course Grade Order 
1 10001 T-SQL 60 
3 11045 Oracle 71 
2 20460 Java 34 
1 10003 T-SQL 59 
3 10001 Oracle 90 
2 20001 Java 12 
2 20078 Java 76 
2 10003 Java 78 
3 30001 Oracle 71 

3 20048 Oracle 36 

1.更新员工的Email，规则为：员工所在部门名称加员工姓名再加“@dhcc.com.cn”，用一条SQL语句完成。

2.统计出各部门的人数和各部门姓“李”的人数，用一条SQL语句完成。 

3.筛选出未参加培训的人员名单，按表1的格式显示，用一条SQL语句完成。 

4.按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)

答案：

1.update Employee set Email=Department || Name || '@dhcc.com.cn' 

2.select Department,count(Name),(select count(name) from Employee b where b.Department = a.Department and b.name like '李%') as li_count from Employee a group by Department;

3.select a.eid,a.name,a.department,a.job,a.email,a.password from employee a where
 not exists (select * from training b where b.eid = a.eid);

 select * from employee a where a.eid in (select b.eid from training b where b.eid = a.eid);

4.
select CourseID, Course,avg(grade) as avg_grade,
(select count(CourseID) from Training where CourseID = t.CourseID and Grade < 60) * 100 / count(CourseID) as under_60_rate from Training t
 group by CourseID, Course order by under_60_rate,avg_grade desc;

SELECT a.courseid, MAX(a.course) AS 课程名称,AVG(a.grade) AS 平均成绩,100 * SUM(CASE WHEN a.grade < 60 THEN
 1 ELSE 0 END) / COUNT(*) AS 不及格百分数
  FROM training a
 GROUP BY a.courseid
 ORDER BY 不及格百分数