内容发布更新时间 : 2024/12/26 13:50:31星期一 下面是文章的全部内容请认真阅读。

实训7连接查询和子查询

班级： 学号： 姓名： 日期：

一、课堂练习

任务1 连接查询：练习教材的例5-34–例5-38

【例5-34】查询不及格学生的学号、姓名、课程号和成绩信息。查询结果如图5-27所示。

① ANSI连接语法形式如下：

SELECT XSQK.学号, 姓名, 课程号, 成绩 FROM XSQK INNER JOIN XS_KC ON XSQK.学号=XS_KC.学号 WHERE 成绩<60

② SQL Server连接语法形式如下：

SELECT XSQK.学号, 姓名, 课程号, 成绩 FROM XSQK, XS_KC

WHERE XSQK.学号=XS_KC.学号 AND 成绩<60

【例5-35】用左外连接方式查询学生的学号、姓名、课程号和成绩信息。查询结果如图5-28所示。

SELECT XSQK.学号, 姓名, 课程号, 成绩 FROM XSQK LEFT OUTER JOIN XS_KC ON XSQK.学号=XS_KC.学号

【例5-36】用右外连接方式查询不及格学生的学号、姓名、课程号和成绩信息。查询结果如图5-29所示。

SELECT XSQK.学号, 姓名, 课程号, 成绩 FROM XSQK RIGHT OUTER JOIN XS_KC ON XSQK.学号=XS_KC.学号 WHERE 成绩<60

【例5-37】用全外连接方式查询学号、姓名、课程号、成绩。查询结果如图5-30所示。

SELECT XSQK.学号, 姓名, 课程号, 成绩 FROM XSQK FULL OUTER JOIN XS_KC ON XSQK.学号=XS_KC.学号

【例5-38】在查询结果集中一行显示每个学生的两门课程成绩。查询结果如图5-31所示。

① ANSI连接语法形式如下：

SELECT A.学号, A.课程号, A.成绩, B.课程号, B.成绩 FROM XS_KC A JOIN XS_KC B ON A.学号=B.学号 WHERE A.课程号

② SQL Server连接语法形式如下：

SELECT A.学号, A.课程号, A.成绩, B.课程号, B.成绩 FROM XS_KC A, XS_KC B

WHERE A.学号=B.学号 AND A.课程号

任务2 子查询：练习教材的例5-39–例5-43

【例5-39】查询平均分低于60分的学生学号和姓名。查询结果如图5-32所示。

SELECT 学号, 姓名 FROM XSQK A

WHERE (SELECT AVG(成绩) FROM XS_KC B WHERE B.学号=A.学号)<60

【例5-40】查询有不及格学生的课程的授课教师。查询结果如图5-33所示。

SELECT DISTINCT 授课教师 FROM KC

WHERE 课程号=ANY(SELECT 课程号 FROM XS_KC B WHERE 成绩<60)

【例5-41】查询每门课程的最低分。查询结果如图5-34所示。

SELECT * FROM XS_KC A WHERE 成绩<=ALL

(SELECT 成绩 FROM XS_KC B WHERE B.课程号=A.课程号) 或

SELECT * FROM XS_KC A WHERE 成绩=

(SELECT MIN(成绩) FROM XS_KC B WHERE B.课程号=A.课程号)

【例5-42】将【例5-40】中的查询改为用“IN”运算符。查询结果如图5-35所示。

SELECT DISTINCT 授课教师 FROM KC

WHERE 课程号 IN( SELECT 课程号 FROM XS_KC WHERE 成绩<60 )

【例5-43】查询至少有一门课程不及格的学生信息。查询结果如图5-36所示。

SELECT DISTINCT 学号, 姓名 FROM XSQK A WHERE EXISTS

( SELECT * FROM XS_KC B WHERE B.成绩<60 AND B.学号=A.学号 )

二、作业调试

任务3：使用连接方式完成下列查询。

1. 查询不及格学生的学号、课程名、授课教师和开课学期的信息。

2. 查询选修了“网页设计”课程的学生学号、姓名、课程号、课程名和成绩。 任务4：使用子查询方式完成下列查询。 1. 2. 3. 4. 5.

查询“103”号课程不及格的学生学号、姓名和联系电话。 查询恰好有两门课程不及格的学生信息。 查询每门课程的最高分的学生记录。 查询每个学生的最低分课程记录。

查询每门课程的选修人数（提示：使用新增列完成）。

任务3： 1．

select 学号, 课程名, 授课教师, 开课学期, 成绩 from kc, xs_kc

where kc.学号=xs_kc.学号 and 成绩<60 2．

select xsqk.学号, 姓名, kc.课程号, 课程名, 成绩 from xsqk, kc, xs_kc

where xsqk.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号 and 课程名='网页设计'

任务4： 1.

select 学号, 姓名, 联系电话 from xsqk

where 学号 in(select 学号

from xs_kc

where 课程号=’103’ and 成绩<60)

2．

select 学号, 姓名 from xsqk

where (select count(课程号)

from xs_kc

where xsqk.学号=xs_kc.学号 and 成绩>=60 )=2

3．

select *

from xs_kc a

where 成绩=(select max(成绩)

from xs_kc b

where a.课程号=b.课程号)

4．

select *

from xs_kc a

where 成绩=(select min(成绩)

from xs_kc b

where a.学号=b.学号)

5．

select distinct a.课程号, 人数=

(select count(学号) from xs_kc b where b.课程号=a.课程号) from xs_kc a

order by a.课程号 asc