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 不及格百分数