（MySQL）笔记——实训四

题目

对XSCJ数据库完成以下查询。

1、单表查询
(1) 查询XS表中男同学的学号、姓名和总学分，结果中各列的标题使用中文，分别为:学号、姓名和总学分。

select s_no as 学号,s_name as 姓名,s_credit as 总学分
		from XS
		where s_gender = '男';

(2) 查询101课程的平均成绩、最高分和最低分。

select avg(score) as 平均分,max(score) as 最高分,min(score) as 最低分
		from XS_KC
		where k_no = 101;

(3) 按学分统计课程数。

select k_credit 学分,count(*) 课程数 
		from KC
		group by k_credit;

(4) 查询不在 1990年出生于的学生信息，且按出生日期从早到晚排序。

	select * from XS
		where s_brith not between '1990-1-1' and '1990-12-31'
		order by s_brith;

(5) 查找XSCJ数据库中平均成绩在80分以上的学生的学号和平均成绩。

select s_no,round(avg(score))
		from XS_KC
		group by s_no
		having avg(score)>80;

2、连接查询
(6) 查找在XSCJ数据库中选修了课程号为102的课程的学生的姓名、学号。

select XS.s_name,XS.s_no
		from XS
		join XS_KC on XS_KC.s_no=XS.s_no
		where k_no = '102';

(7) 将计算机专业学生的“计算机基础”课程成绩按降序排列，输出字段包括姓名、课程名和成绩。

select s_name,k_name,score
		from XS_KC
		join XS on XS.s_no=XS_KC.s_no
		join KC on KC.k_no=XS_KC.k_no
		where k_name='计算机基础' and s_pro = '计算机'
		order by score desc;

(8) 查找在第2和第3学期开课的课程选修情况,没有被选修的课程也要查询出来。

SELECT * 
		FROM kc LEFT JOIN xs_kc
		ON kc.k_no=xs_kc.k_no
		WHERE k_term in(2,3);

(9) 查询和数据结构学分相同的课程号、课程名。

SELECT s2.k_name as 课程名,s2.k_no as 课程号 
		FROM kc as s1 JOIN kc as s2
		ON s2.k_credit=s1.k_credit
		WHERE s1.k_name='数据结构' and s2.k_name != '数据结构';

3、子查询
(10) 查找未选修计算机基础课程学生的姓名、学号、性别。

select s_name,s_no,s_gender
		from XS
		where s_no not in(select s_no 
						from XS_KC 
						where k_no =(select k_no
									from KC
									where k_name = '计算机基础')
							);

(11) 将计算机专业学生按其平均成绩降序排列,输出学号和平均成绩。

	select s_no,round(avg(score))
		from XS_KC
		where s_no = any (select s_no
						from XS
						where s_pro = '计算机')
		group by s_no
		order by avg(score) desc;

(12) 查找XS表中比所有男学生年龄都大的女学生的学号、姓名、出生日期。

select s_no,s_name,s_brith
		from XS
		where s_gender = '女' and s_brith <all(select s_brith
											from XS
											where s_gender = '男');

**or**

	select s_no,s_name,s_brith
		from XS
		where s_gender = '女' and s_brith <
		(select min(s_brith)
		from XS
		where s_gender = '男');