数据库的实例（求及格率等）

来源：互联网发布：php 支付宝api接口编辑：程序博客网时间：2024/04/27 14:01

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：select department||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 <t2.grade)) as A

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;

0 0