表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
以下用T-SQL语句基于SQL Server完成
1、 建表Employee与Training，分析表1和表2的结构，自行设置主键。
2、 用SQL语句把上述两表的数据分别插入建好的表中，分别用一条SQL语句完成。
3、 统计出各部门的人数和各部门姓“李”的人数，用一条SQL语句完成。
4、 列出所有员工参加培训的情况，要求显示EID、Name、Department、Course，用一条SQL语句完成。
5、 筛选出未参加培训的人员名单，按表1的格式显示，用一条SQL语句完成。
6、 更新员工的Email，规则为：员工所在部门名称加员工姓名再加“@dhcc.com.cn”，用一条SQL语句完成。
7、 列出所有各课成绩最高的员工信息，要求显示EID、Name、Department、Course、Grade，用一条SQL语句完成。
8、 把所有表2有但表1没有的员工编号插入到表1中，用一条SQL语句完成。
9、 分析表1与表2的关系，建立表1与表2之间的引用关系并实现级联操作。
10、 用触发器实现第10题的相关操作。
11、 统计列印各门课程成绩各分数段人数：
课程ID，课程名称，[100-85]，[84-70]，[69-60]，[<60]
12、 按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)
课程ID,课程名称,平均成绩,不及格百分数
13、 根据成绩排出各课自己的名次，并更新到表2的Order列（允许出现并列的情况），用Procedure实现（需要用两种方式）：
a.用一条SQL语句完成。
b.用游标完成。
--1. 建表Employe
create table test.Employee
(
EID int PRIMARY KEY,
Name varchar2(20),
Department varchar2(20),
Job varchar2(20),
Email varchar2(50),
Password varchar2(20)
)
--drop table test.Employee
--建表Training
create table test.Training
(
CourseID int,
EID int,-- CONSTRAINT fk_employeeno REFERENCES test.Employee(EID),
Course varchar2(20),
Grade int,
Orders int
)
--drop table test.Training
--2. 数据分别插入建好的Employee表
insert all
into test.Employee values(10001,'李明','SBB','EG','','')
into test.Employee values(10003,'李筠平','LUKE','ITM','','')
into test.Employee values(11045,'李洁','SBB','EG','','')
into test.Employee values(10044,'胡斐','MTD','ETN','','')
into test.Employee values(10009,'徐仲刚','SBB','EG','','')
into test.Employee values(10023,'李燕','SBB','ETN','','')
into test.Employee values(20460,'陆明生','MTD','ETN','','')
into test.Employee values(20078,'张青','MMM','EG','','')
into test.Employee values(20001,'李立','LUKE','ETN','','')
SELECT COUNT(EID) FROM test.Employee
--数据分别插入建好的Training表
insert all
into test.Training values(1,10001,'T-SQL',60,'')
into test.Training values(3,11045,'Oracle',71,'')
into test.Training values(2,20460,'Java',34,'')
into test.Training values(1,10003,'T-SQL',59,'')
into test.Training values(3,10001,'Oracle',90,'')
into test.Training values(2,20001,'Java',12,'')
into test.Training values(2,20078,'Java',76,'')
into test.Training values(2,10003,'Java',78,'')
into test.Training values(3,30001,'Oracle',71,'')
into test.Training values(3,20048,'Oracle',36,'')
SELECT COUNT(CourseID) FROM test.Training
--3. 统计出各部门的人数和各部门姓"李"的人数
select Department, count(Name), (select count(name) from test.Employee b
where b.Department=a.Department and b.name like '李%' group by
b.Department) as li_count from test.Employee a group by Department
--4. 列出所有员工参加培训的情况，要求显示EID、Name、Department、Course
select e.EID, e.Name, e.Department, t.Course from test.Employee e right
outer join test.Training t on e.EID = t.EID order by e.EID
--5. 筛选出未参加培训的人员名单，按Employee表的格式显示
select * from test.Employee e where e.EID not in (select distinct(t.EID) from test.Training t)
--6. 更新员工的Email，规则为：员工所在部门名称加员工姓名再加"@dhcc.com.cn"
update test.Employee set Email=Department || Name || '@dhcc.com.cn'
--7. 列出所有各课成绩最高的员工信息，要求显示EID、Name、Department、Course、Grade
select e.EID, e.Name, e.Department, t.Course, t.Grade
from (select t1.*
from test.Training t1,
(select Course, max(Grade) as Grade from test.Training group by Course) t2
where t1.Course=t2.Course and t1.Grade=t2.Grade) t
left outer join test.Employee e on e.EID = t.EID
--insert into test.Training values(3,10003,'Oracle',90)
--8. 把所有表2有但表1没有的员工编号插入到表1
insert into test.Employee (EID)
select EID from test.Training where EID not in (select distinct(e.EID) from test.Employee e)
--select * from test.Employee
--9. 分析表1与表2的关系，建立表1与表2之间的引用关系并实现级联操作
alter table test.Training
add CONSTRAINT fk_employeeid
foreign key (EID)
REFERENCES test.Employee(EID)
--11. 统计列印各门课程成绩各分数段人数：课程ID，课程名称，[100-85]，[84-70]，[69-60]，[<60]
select distinct CourseID, Course,
(select count(CourseID) from test.Training where Grade>=85 and Grade<=100 and CourseID = t.CourseID) as Grade_100_85,
(select count(CourseID) from test.Training where Grade>=70 and Grade<=84 and CourseID = t.CourseID) as Grade_84_70,
(select count(CourseID) from test.Training where Grade>=60 and Grade<=69 and CourseID = t.CourseID) as Grade_69_60,
(select count(CourseID) from test.Training where Grade<60 and CourseID = t.CourseID) as Grade_less_60
from test.Training t group by CourseID, Course order by CourseID
--12. 按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)
--课程ID,课程名称,平均成绩,不及格百分数
select CourseID, Course, AVG(Grade) as avg_grade, to_char(null) as percent
from test.Training
group by CourseID, Course
order by AVG(Grade) desc
select CourseID, Course, to_char(null) as avg_grade,
(select count(CourseID) from test.Training where CourseID=t.CourseID and
Grade < 60) * 100 / count(CourseID) as under_60_rate
from test.Training t
group by CourseID, Course
order by ((select count(CourseID) from test.Training where CourseID=t.CourseID and Grade < 60) * 100 /count(CourseID))
--13. 根据成绩排出各课自己的名次，并更新到表2的Order列（允许出现并列的情况）
update test.Training t set Orders=(SELECT COUNT(Grade) + 1 FROM test.Training WHERE Grade > t.Grade and CourseID=t.CourseID)
--select * from test.Training
