mysql及格率70以上_数据库实例（统计最高分学生信息，不及格率等等）

1.职工表:Employee:

ID   Name   Department Job Email Password

10000 李明   SBB

10001 李筠平 LUK

11045 李洁   SBB

10044 胡斐   MTD

10009 徐仲刚 SBB

10023 李燕   SBB

20460 陆明生 MTD

20078 张青   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        20078  Java    78

2        10003  Java    78

3        30001  Oracle  71

3        20048  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(20078,'张青','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,20078,'Java',78 ,' ');

insert into Train values(2,10003,'Java',78 ,' ');

insert into Train values(3,30001,'Oracle',71 ,' ');

insert into Train values(3,20048,'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，规则为：员工所在部门名称加员工姓名再加“@163.com”，用一条SQL语句完成。

oracle：selectdepartment||name||'@163.com'as email from employee;

mysql：select concar(department,name,'@51testing.com') 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;