1.职工表:Employee:
ID Name Department Job Email Password
10000 李明 SBB
10001 李筠平 LUK
11045 李洁 SBB
10044 胡斐 MTD
10009 徐仲刚 SBB
10023 李燕 SBB
20460 陆明生 MTD
8 张青 MMM
20001 李立 LUK
2.培训表:Train:
CourseID ID Course Grade TOrder
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 8 Java 78
2 10003 Java 78
3 30001 Oracle 71
3 8 Oracle 36
1、建表Employee与Train，分析这两个表的结构，自行设置主键。
create table employee(
ID int primary key,
name varchar2(30),
department char(10),
job varchar2(20),
Email varchar2(30),
Password varchar2(10));
create table Train (
courseID int,
ID int,
course varchar2(10),
grade int,
Torder char(10));
2、用Insert SQL语句把上述两表的数据分别插入建好的表。
insert into employee values(10000 ,'李明','SBB',' ',' ',' ');
insert into employee values(10001,'李筠平','LUK',' ',' ',' ');
insert into employee values(11045,'李洁','SBB',' ',' ',' ');
insert into employee values(11044,'胡斐','MTD',' ',' ',' ');
insert into employee values(10009,'徐仲刚','SBB',' ',' ',' ');
insert into employee values(10023,'李燕','SBB',' ',' ',' ');
insert into employee values(20460,'陆明生','MTD',' ',' ',' ');
insert into employee values(8,'张青','MMM',' ',' ',' ');
insert into employee values(20001,'李立','LUK',' ',' ',' ');
insert into Train values(1,10001,'T-SQL',60,' ');
insert into Train values(3,11045,'Oracle',71,' ');
insert into Train values(2,20460,'Java',34,' ');
insert into Train values(3,10001,'Oracle',90 ,' ');
insert into Train values(1,10003,'T-SQL',59,' ');
insert into Train values(2,20001,'Java',12,' ');
insert into Train values(2,8,'Java',78 ,' ');
insert into Train values(2,10003,'Java',78 ,' ');
insert into Train values(3,30001,'Oracle',71 ,' ');
insert into Train values(3,8,'Oracle',36 ,' ');
3、统计出各部门的人数和各部门姓“李”的人数，用一条SQL语句完成。
select a.dept,a.sum,b.Li
from ( select department dept,count(id) sum
from employee
group by department) a left join (select department dept,count(id) Li
from employee
where name like '李%'
group by department) b on a.dept=b.dept;
4、列出所有员工参加培训的情况，要求显示ID、Name、Department、Course，用一条SQL语句完成。
select a.ID,a.name,a.department,b.course from employee a,Train b where a.ID=b.ID;
5、筛选出未参加培训的人员名单，按职工表的格式显示，用一条SQL语句完成。
select *
from employee
where employee.ID not in(select ID from Train);
6、更新员工的Email，规则为：员工所在部门名称加员工姓名再加“@”，用一条SQL语句完成。
oracle：selectdepartment||name||'@'as email from employee;
mysql：select concar(department,name,'@') as email from employee;
7、列出所有各课成绩最高的员工信息，要求显示EID、Name、Department、Course、Grade，用一条SQL语句完成。
select E.ID EID,E.name,E.department,T.course,T.grade
from employee E, Train T,(select ID,course from Train
where not exists(select ID from Train t2
where Train.course=t2.course and Train.grade
where A.ID=T.ID and E.ID =A.ID and A.course=T.course;
或者
select T.ID,M.course,T.grade,E.name,E.department
from employee E,Train T,(select course,max(grade) maxgrade
from Train
group by course) M
where M.course=T.course and T.grade =M.maxgrade and T.ID=M.ID;
8、把所有Train有但Employee没有的员工编号插入到职工表中，用一条SQL语句完成。
oracle：
insert into employee(ID)
select t2.ID from employee t1,Train t2
where t2.ID not in(select t1.ID
from employee t1);
Mysql:
replace employee(ID)
select t2.ID from employee t1,Train t2
where t2.ID not in(select t1.ID
from employee t1);
9、分析Train与Employee的关系，建立表1与表2之间的引用关系并实现级联操作。
oracle：
alter table Train
add constraint fk_train ID references employee(ID) on delete cascade;
Mysql:
alter table Train add constraint fk_train foreign key(ID) references employee(ID)
on delete cascade;
10、统计列印各门课程成绩各分数段人数：
课程ID，课程名称，[100-85]，[84-70]，[69-60]，[<60]
select distinct T.course, T.courseID,A.[100-85]
from Train T,(select ID,count(ID) as [100-85]
from Train
where grade between 85 and 100 group by ID) A
where T.ID=A.ID;
select distinct T.course, T.courseID,A.[84-70]
from Train T,(select ID,count(ID) as [84-70]
from Train
where grade between 70 and 84 group by ID) A
where T.ID=A.ID;
select distinct T.course, T.courseID,A.[69-60]
from Train T,(select ID,count(ID) as [69-60]
from Train
where grade between 60 and 69 group by ID) A
where T.ID=A.ID;
select distinct T.course, T.courseID,A.[<60]
from Train T,(select ID,count(ID) as [<60]
from Train
where grade < 60 group by ID) A
where T.ID=A.ID;
11、按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表
示): (就是分析哪门课程难)
课程ID,课程名称,平均成绩,不及格百分数
select distinct T.course, T.courseID,average,A.HC/C.count as failrate
from Train T,(select ID,count(ID) as HC
from Train where grade < 60 group by ID) A,
(select courseID,avg(grade) average,count(ID) as count
from Train group by courseID) C
where T.ID=A.ID and T.courseID=C.courseID;
