综合练习第6套

已知员工考勤数据库YGKQ包含JBQK（职工基本情况）数据表和QQLX（缺勤信息）数据表，表结构如表1和表2所示：

表 1 JBQK（职工基本情况表结构） 字段名 zgh xm sj ts lx

表 2 QQLX（缺勤类型信息表结构） 字段名 lx mc ms

请用SQL语句完成以下操作：

1．查询每个职工的职工号、姓名、缺勤时间、缺勤天数和缺勤类型信息。 2．查询职工号为001的职工的姓名和缺勤天数。 3．查询所有姓“张”的职工的职工号、缺勤天数。 4．找出所有缺勤天数在2～3天的职工号和缺勤名称。 5．查询缺勤名称为“病假”的职工的职工号和姓名。 6．查询缺勤天数超过平均缺勤天数的职工的职工号和姓名。 7．求各缺勤类别的人数。

8．查询在职工基本情况表中没有出现过的缺勤类型及缺勤名称。 9．使用SQL语句将“旷工”人员的缺勤天数增加一天。

10．使用SQL语句创建一个名为zgqq（职工缺勤）的视图，要求能够使用该视图查询缺勤2天以上的职工的职工号、姓名、缺勤天数和缺勤名称。 参考答案：

答案不惟一，只要结果正确即给分

1．查询每个职工的职工号、姓名、缺勤时间、缺勤天数和缺勤类型信息。 select * from JBQK;

2．查询职工号为001的职工的姓名和缺勤天数。 selectxm,qqts from JBQK where zgh='001';

3．查询所有姓“张”的职工的职工号、缺勤天数。 select zgh,qqts from JBQK where xm like '张%';

4．找出所有缺勤天数在2～3天的职工号和缺勤名称。

selectzgh,qqmc from JBQK,QQLX where JBQK.qqlx= QQLX.qqlx and qqts between 2 and 3;

36

字段类型 CHAR CHAR VARCHAR 字段宽度 4 8 60 说明 缺勤类型，主码 缺勤名称 缺勤描述 字段类型 CHAR CHAR DATETIME INT CHAR 字段宽度 4 8 4 说明 职工号，主码 姓名 缺勤时间 缺勤天数 缺勤类型，外码

5．查询缺勤名称为“病假”的职工的职工号和姓名。

select zgh,xm from JBQK where qqlx in(select qqlx from QQLX where qqmc='病假'); 6．查询缺勤天数超过平均缺勤天数的职工的职工号和姓名。 selectzgh,xm from JBQK where qqts>(select AVG(qqts) from JBQK); 7．求各缺勤类别的人数。

selectqqlx,count(*) from JBQK group by qqlx;

8．查询在职工基本情况表中没有出现过的缺勤类型及缺勤名称。 本题给出三种参考答案：

selectqqlx,qqmc from QQLX where qqlx<>all(select distinct qqlx from JBQK); 或select qqlx,qqmc from QQLX where qqlx not in(select distinct qqlx from JBQK);

或select qqlx,qqmc from QQLX where not exists(select * from JBQK where qqlx=qqlx.qqlx); 9．使用SQL语句将“旷工”人员的缺勤天数增加一天。

update JBQK set qqts=qqts+1 where qqlx in(select qqlx from QQLX where qqmc='旷工');

10．使用SQL语句创建一个名为zgqq（职工缺勤）的视图，要求能够使用该视图查询缺勤2天以上的职工的职工号、姓名、缺勤天数和缺勤名称。 create view zgqq as

selectzgh,xm,qqts,qqmc from JBQK,QQLX where JBQK.qqlx=QQLX.qqlx and qqts>2;

37

综合练习第7套

已知研究生管理数据库YJSGL包含graduate（研究生信息）数据表和teacher（导师信息）数据表，表结构如表1和表2所示：

表 1 graduate（研究生信息表结构） 字段名 bh xm xb mz ly cj dsbh

表 2teacher（导师信息表结构） 字段名 dsbh dsxm zc dh

请用SQL语句完成以下操作：

1． 查询每个研究生的编号、姓名、性别、民族、入学成绩、来源地区和所选导师编号。 Select * from graduate

2． 查询学号为1001的学生的姓名和入学成绩。 select sname,score from graduate where sno=’1001’

3．查询所有姓“王”的学生的编号和来源地区。 select sno,source from graduate

where sname like ‘王%’

4．查询所有入学成绩在350和400分之间的学生的编号、姓名和所选导师的姓名及其职称。 select sno,sname,tname,title from graduate,teacher

where score between 350 and 400and graduate.tno=teacher.tno 5．查询选了“张一伟”为导师的研究生的编号和姓名。 select sno,sname from graduate,teacher

where tname=’张一伟’ and graduate.tno=teacher.tno 6．查询入学成绩低于平均入学成绩的研究生的编号、姓名、民族。

38

字段类型 char char char char 字段宽度 4 8 10 11 说明 导师编号(主码) 姓名 职称 联系电话 字段类型 char char char char char int char 字段宽度 4 8 2 20 20 4 说明 研究生编号(主码) 姓名 性别 民族 来源地区 入学成绩 导师编号(外码)

select sno,sname,nation from graduate

where score<( select AVG(score) from graduate) 7．查询不同来源地区的研究生人数。

select nation 来源地区,count(nation) 人数 from graduate group by nation

8．查询没有选导师的研究生的编号、姓名、性别和入学成绩。 select sno,sname,ssex,score from graduate where tno is null

9．将少数民族的研究生的入学成绩加10分。 update graduate set score=score+10 where nation<>’汉’

10．创建一个名为rxcj（入学成绩）的视图，要求使用该视图能够查询入学成绩超过平均入学成绩的研究生的编号、姓名、入学成绩和所选导师的姓名及联系电话。 create view rxcj as

select sno,sname,score,tname,tphone from graduate.teacher

where graduate.tno=teacher.tno and score>(select AVG(score) from graduate)

39