SQL Server数据库实验（二）数据库的简单查询和连接查询

---

一、针对教学管理数据库SCT，进行以下各种简单查询

1.查询“计算机”学院的学生信息

select *
from STUDENT
where SDEPT = '计算机'

2.查询年龄在20至21（包含20至21）之间的学生信息

select *
from STUDENT
where SAGE>=20 and SAGE <=21

3.查询姓名中第二个字是“志”的学生信息

select *
from STUDENT
where SNAME like '_志%'

4.查询职称为“教授”的45岁以下女教师信息

select *
from TEACHER055
where TITLE = '教授' and TAGE<45 and TSEX = '女'

5.查询课程名含有“数学”的课程信息

select *
from COURSE055
where CNAME like '%数学%'

6.查询“数据库”、“操作系统”、“C语言”课程的信息

select *
from COURSE
where CNAME in('数据库','操作系统','C语言')

7.查询“CS-001”号课程的教学情况（教师号，学生号，成绩）

select TNO,SNO,GRADE
from SCT055
where CNO = 'CS-001'

8.查询成绩90分以上的教学信息

select *
from SCT055
where GRADE > 90

9.查询所有选课成绩中的2个最高分的选课信息

top关键字返回查出数据的前n条数据
（已经按GRADE建立升序索引所以可以得到2个最高分）

select TOP 2 *
from SCT055

10.查询选课门数最少的两个学生的学号

select top 2 SNO
from SCT
group by SNO
order by count(CNO)

11.查询有学生选修的课程号，要求取消查询结果表中的重复行

select distinct CNO
from SCT

12.查询选修了3门课程的学生学号

select SNO
from SCT
group by SNO
having count(CNO)=3

13.查询“CS-001”号课程的最高分、最低分、平均分，要求查询结果表有明确的列名

select MAX(GRADE) as '最高分',
		MIN(GRADE) as '最低分',
		AVG(GRADE) as '平均分'
from SCT055
where CNO = 'CS-001

14.查询学号为“P20160421”的学生选课的门数、平均分，要求查询结果表有明确的列名

select count(CNO) as '选课门数',avg(GRADE) as '平均分'
from SCT
where SNO = 'P20160421'

15.按照课程号分组计算每门课程的最高分、最低分、平均分，要求查询结果表有明确的列名

select max(GRADE) as '最高分',min(GRADE) as '最低分',avg(GRADE) as '平均分'
from SCT
group by CNO

16.按照学号分组计算每个学生的总分、最高分、最低分、平均分，要求查询结果表有明确的列名

select SNO,
		SUM(GRADE) as '总分',
		MAX(GRADE) as '最高分',
		MIN(GRADE) as '最小值', 
		AVG(GRADE) as '平均分'
from SCT055
group by SNO

二、针对教学管理数据库SCT，进行以下各种连接查询

1.查询“王红”同学的选课情况，结果包含姓名、课程名、学分

select SNAME,CNAME,CREDIT
from SCT055,STUDENT055,COURSE055
where SNAME = '王红' and STUDENT055.SNO = SCT055.SNO
		and COURSE055.CNO = SCT055.CNO

2.查询“数据库”课程的选课名单，结果包含学号、姓名

select CNAME,SCT055.SNO,SNAME
from COURSE055,SCT055,STUDENT055
where CNAME = '数据库' and
		SCT055.CNO = COURSE055.CNO and
		STUDENT055.SNO = SCT055.SNO

3.查询教“C语言”课程的教师的信息

select distinct TEACHER055.*
from TEACHER055,SCT055,COURSE055
where CNAME = 'C语言' and
		COURSE055.CNO = SCT055.CNO and
		SCT055.TNO = TEACHER055.TNO

4.给出“C语言”课程的成绩单，结果包含学号、姓名、成绩，按成绩从高到低排序

select STUDENT.SNO,SNAME,GRADE
from STUDENT,SCT,COURSE
where STUDENT.SNO=SCT.SNO and COURSE.CNO=SCT.CNO
	and CNAME='C语言'
order by GRADE

5.查询“C语言”课程成绩前三名学生的姓名、专业、成绩

top 3只是返回前三条记录，并非按照排序选择，此处已按GRADE建立升序索引

select top 3 SNAME,SPROF,GRADE
from STUDENT055,SCT055,COURSE055
where CNAME = 'C语言' and
		COURSE055.CNO = SCT055.CNO and
		SCT055.SNO = STUDENT055.SNO

6.查询“C语言”课程的最高分、最低分、平均分

select MAX(GRADE) as '最高分',
		MIN(GRADE) as '最低分',
		AVG(GRADE) as '平均分'
from SCT055,COURSE055
where CNAME = 'C语言' and
		COURSE055.CNO = SCT055.CNO

7.查询“林美”同学选修课程的门数、最高分、最低分、平均分

select count(*) as '选修课程的门数',
		MAX(GRADE) as '最高分',
		MIN(GRADE) as '最低分',
		AVG(GRADE) as '平均分'
from SCT055,STUDENT055
where SNAME = '林美' and
		STUDENT055.SNO = SCT055.SNO

8.查询“王平”同学选课获得的学分情况（成绩及格才能获得学分），结果包含课程名、成绩、学分

select CNAME,GRADE,CREDIT
from SCT055,COURSE055,STUDENT055
where SNAME = '王平' and
		GRADE > =60 and
		STUDENT055.SNO = SCT055.SNO and
		COURSE055.CNO = SCT055.CNO

9.查询“C语言”课程成绩不及格的学生名单，结果含课程名、学号、学生名、成绩

select CNAME,SCT055.SNO,SNAME,GRADE
from SCT055,COURSE055,STUDENT055
where CNAME = 'C语言' and
		GRADE < 60 and
		STUDENT055.SNO = SCT055.SNO and
		COURSE055.CNO = SCT055.CNO

10.查询选课成绩不及格的情况，结果含课程名、学生名、教师名、成绩，按课程名升序显示

select CNAME,SNAME,TNAME,GRADE
from SCT055,COURSE055,STUDENT055,TEACHER055
where GRADE < 60 and
		STUDENT055.SNO = SCT055.SNO and
		COURSE055.CNO = SCT055.CNO and
		TEACHER055.TNO = SCT055.TNO
order by CNAME

11.查询每门课程的选课人数（包括无人选修的课程），结果含课程名、选课人数，按选课人数的降序显示

select CNAME,count(SCT055.SNO) as '选课人数'
from COURSE055 left join SCT055
on COURSE055.CNO = SCT055.CNO
group by COURSE055.CNAME
order by count (*) desc