实验：数据库单表查询

实验：数据库单表查询

一、实验目的

1. 掌握SELECT语句的基本语法和查询条件表示方法； 2. 掌握查询条件表达式和使用方法； 3. 掌握GROUP BY 子句的作用和使用方法； 4. 掌握HAVING子句的作用和使用方法； 5. 掌握ORDER BY子句的作用和使用方法。 二、实验环境

已安装SQL Server 2005 企业版的计算机； 具有局域网环境,有固定IP; 三、实验学时

2学时 四、实验要求

1. 了解数据库查询；

2. 了解数据库查询的实现方式； 3. 完成实验报告； 五、实验内容及步骤

以《实验：管理SQL Server表数据》数据库中数据为基础，请使用T-SQL 语句实现以下操作：

1. 列出所有不姓刘的所有学生；

USE student GO

SELECT * FROM student WHERE Sname NOT LIKE '刘%' GO

2. 列出姓?沈?且全名为3个汉字的学生；(表中没有符合的，故用刘代替)

USE student GO

SELECT * FROM student WHERE Sname LIKE '刘_[^ ]' GO

3. 显示在1985年以后出生的学生的基本信息；

USE student GO

SELECT * FROM student

WHERE YEAR(GETDATE())-1985>Sage GO

4. 按照?性别、学号、姓名、年龄、院系?的顺序列出学生信息，其中性别按

以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为?条件不明?；

USE student GO

SELECT Ssex=

CASE Ssex

WHEN '男' THEN '男生' WHEN '女' THEN '女生'

ELSE '情况不明' END,

Sno, Sname, Sage, Sdept FROM student GO

5. 查询出课程名含有?数据?字串的所有课程基本信息；

USE student GO

SELECT * FROM course WHERE Cname LIKE '%数据%' GO

6. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性

别、年龄及院系；

USE student GO

SELECT Sno,Sname,Ssex,Sage,Sdept FROM student

WHERE Sno LIKE '_______[1-4,9]%' OR Sno LIKE '________[1-4,9]' GO

7. 列出选修了‘1’课程的学生，按成绩的降序排列；

USE student GO

SELECT student.Sno,Sname,Ssex,Sage,Sdept,Grade FROM student,sc WHERE Cno=1 AND student.Sno=sc.Sno ORDER BY Grade DESC GO

8. 列出同时选修?1?号课程和?2?号课程的所有学生的学号；

USE student GO

SELECT a.Sno FROM sc a,sc b

WHERE a.Cno=1 AND b.Cno=2 AND a.Sno=b.Sno GO

9. 列出课程表中全部信息，按先修课的升序排列；

USE student GO

SELECT * FROM course ORDER BY Cpno ASC GO

10. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；

USE student GO

SELECT * FROM student

WHERE Sage>(SELECT AVG(Sage) FROM student) ORDER by Sage DESC GO

11. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在

结果集中列标题分别指定为?学号，姓名，性别，出生年份，院系?；

USE student GO

SELECT Sno AS '学号',Sname AS '姓名',Ssex AS '性别',YEAR(GETDATE())-Sage

AS '出生年份',Sdept AS '院系' FROM student

WHERE Sage>(SELECT AVG(Sage) FROM student) ORDER by Sage DESC GO

12. 按照院系降序显示所有学生的 ?院系，学号、姓名、性别、年龄?等信息，

其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；

USE student GO

SELECT '院系'= CASE Sdept

WHEN 'CS' THEN '计算机系' WHEN 'IS' THEN '信息系' WHEN 'MA' THEN '数学系' WHEN 'EN' THEN '外语系' WHEN 'CM' THEN '中医系' ELSE '系院不明'

END, '学号'=Sno, '姓名'=Sname,'性别'=Ssex,'年龄'=Sage FROM student ORDER by Sdept ASC GO

13. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段

?院系规模?，其中若该院系人数>=5则该字段值为?规模很大?，若该院系人数大于等于4小于5则该字段值为?规模一般?， 若该院系人数大于等于2小于4则该字段值为?规模稍小?，否则显示?规模很小?；

USE student GO

SELECT distinct '院系'=Sdept, '院系规模'=CASE

WHEN COUNT(*)>=5 THEN '规模很大'

WHEN COUNT(*)>=4 AND COUNT(*)<5 THEN '规模一般' WHEN COUNT(*)>=2 AND COUNT(*)<4 THEN '规模稍小'

ELSE '规模很小' END FROM student GROUP BY Sdept GO

14. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及

成绩；

USE student GO

SELECT Sno,Cno,Grade FROM sc WHERE Grade BETWEEN 79 AND 80 ORDER BY Cno DESC, Grade DESC GO

15. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为

?学生总人数，平均年龄?；

USE student GO

SELECT '学生总人数'=COUNT(*),'平均年龄'=AVG(Sage) FROM student GO

16. 显示选修的课程数大于3的各个学生的选修课程数；

USE student GO

SELECT '学号'=Sno,'选修课程数'=COUNT(Cno) FROM sc GROUP BY Sno

HAVING COUNT(Cno)>3 GO

17. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

USE student GO

SELECT DISTINCT Cno,'选修人数'=COUNT(Sno),'最高成绩'=MAX(Grade),'最低成绩'=MIN(Grade),'平均成绩'=AVG(Grade) FROM sc GROUP BY Cno ORDER BY Cno DESC

GO

18. 显示平均成绩大于?200515001?学生平均成绩的各个学生的学号、平均成

绩；

USE student GO

SELECT Sno, '平均成绩'=AVG(Grade) FROM sc GROUP BY Sno

HAVING AVG(Grade)>( SELECT AVG(Grade) FROM sc WHERE Sno='200515001') GO

19. 显示选修各个课程的及格的人数、及格比率；

USE student GO

SELECT DISTINCT Cno, '及格人数'=SUM(CASE WHEN GRADE>1 THEN 1 ELSE 0 END),'及格率'=SUM(CASE WHEN GRADE>1 THEN 1 ELSE 0 END)/COUNT(*) FROM sc GROUP BY Cno GO

20. 显示选修课程数最多的学号及选修课程数最少的学号；

USE student GO

SELECT DISTINCT '选修最多的课程'=Sno,'选修课程数'=COUNT(*) FROM sc GROUP BY Sno

HAVING COUNT(*)>=ALL( ) GO

SELECT DISTINCT '选修最少的课程'=Sno,'选修课程数'=COUNT(*) FROM sc GROUP BY Sno

HAVING COUNT(*)<=ALL( ) GO

SELECT COUNT(Cno) FROM sc GROUP BY Sno

SELECT COUNT(Cno) FROM sc GROUP BY Sno

21. 显示各个院系男女生人数，其中在结果集中列标题分别指定为?院系名称、

男生人数、女生人数?；

USE student GO

SELECT DISTINCT '院系名称'=Sdept, '男生人数'=SUM(

CASE WHEN Ssex='男' THEN 1 ELSE 0 END ),'女生人数'=SUM(

CASE WHEN Ssex='女' THEN 1 ELSE 0 END ) FROM student GROUP BY Sdept GO

22. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

USE student GO

SELECT DISTINCT Sno, AVG(Grade) FROM sc GROUP BY Sno

HAVING SUM(CASE WHEN Grade<=60 THEN 1 ELSE 0 END)>=2 GO

六、出现问题及解决办法

如：某些查询操作无法执行，如何解决？

本文来源：https://www.bwwdw.com/article/9be.html