看看这几道题是不是你想象中那么简单，先做出来先给分

Question

好像应该到SQL  SERVER版问

Answer 1

--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