实训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