实验二 SQL Server的数据查询

最新推荐文章于 2024-05-20 20:26:39 发布

Macey~

最新推荐文章于 2024-05-20 20:26:39 发布

阅读量952

点赞数 22

文章标签：数据库 oracle 服务器 mysql java

本文链接：https://blog.csdn.net/weixin_48448623/article/details/136160698

版权

实验目的：

1．掌握基本的查询、嵌套子查询及连接查询

2．学习数据库保留字符的使用。

3．学习部分统计函数的使用。

实验要求：

1．熟练掌握基本的查询、嵌套子查询及连接查询

2．体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为综合应用打下良好的基础。

实验环境及学时安排：

1．SQL SERVER2008

2．学时：6学时

实验内容：

实验问题描述：

某学院有若干班级，每个班级有若干学生；学院每年每学期都开设有若干门课程；每门课程有多个学生选修，每个学生每学期可以同时选修多门课程，每个学生对于同一门课程可以多次选修，但每学期只能选修1次，课程选修成绩以综合成绩记录。请设计某学院简单的教学管理系统的E-R模型，要求给出每个实体、联系的属性。根据以上语义，我们可得了上图1如示的教学管理系统E-R图（属性未画出）。

根据学院提供的学生选课及其他的部分数据，将上图1所示的E-R图中的部分实体和联系转换为关系，得到如下5个关系模式和相应的数据字典描述。

A、关系模式

⑴ 班级：包括班级号、班级名、所属学院、年级、班级人数；

⑵ 学生：包括学号、姓名、性别、出生日期、籍贯、民族、所属班级；

⑶ 课程：包括课程编号、课程名称、学分、学时数、先修课程；

⑷ 学期：包括学期号、学期名、备注。

⑸ 成绩：包括学号、课程编号、学期、成绩。

B、数据字典

表
表名	中文名
Class	班级
Student	学生
Course	课程
Term	学期
Score	成绩

班级：Class
字段名	数据类型	可空性	主键	外键	默认值	字段含义
classNo	char(6)	NOT NULL	Yes	No		班级号
className	varchar(30)	NOT NULL	unique	No		班级名
institute	char(30)	NOT NULL	No	No		所属学院
grade	smallint	NOT NULL	No	No	0	年级
classNum	tinyint	NULL	No	No		班级人数

学生：Student
字段名	数据类型	可空性	主键	外键	默认值	字段含义
studentNo	char(7)	NOT NULL	Yes	No		学生学号
StudentName	varchar(20)	NOT NULL	No	No		学生姓名
sex	char(2)	NULL	No	No		性别
birthday	datetime	NULL	No	No		出生日期
native	varchar(20)	NULL	No	No		籍贯
nation	varchar(30)	NULL	No	No	汉族	民族
classNo	char(6)	NULL	No	Yes		所属班级

约束条件：

学生学号为7位数字字符；

所属班级classNo参照表Class表的classNo。

课程：Course
字段名	数据类型	可空性	主键	外键	默认值	字段含义
courseNo	char(3)	NOT NULL	Yes	No		课程号
courseName	varchar(30)	NOT NULL	unique	No		课程名
creditHour	numeric(1)	NOT NULL	No	No	0	学分
creditHourse	tinyint	NOT NULL	No	No		学时
priorCourse	char(3)	NULL	No	Yes		先修课程

约束条件：

先修课程priorCourse参照表Course表的courseNo。

学生选课：Term
字段名	数据类型	可空性	主键	外键	默认值	字段含义
TermNo	char(3)	NOT NULL	Yes	No		学期号
termName	varchar(30)	NOT NULL	No	No		学期名
remarks	varchar(10)	NULL	No	No		备注

成绩：Score
字段名	数据类型	可空性	主键	外键	默认值	字段含义
studentNo	char(7)	NOT NULL	Yes	Yes		学号
courseNo	char(3)	NOT NULL	Yes	Yes		课程号
termNo	char(3)	NOT NULL	Yes	Yes		学期号
score	NUMERIC(5,1)	NULL	No	No		综合成绩

约束条件：

综合成绩为百分制成绩；

学号、课程号和学期号一起构成主码；

学号studentNo参照表student表的studentNo；

课程号courseNo参照表course表的courseNo；

学期号termNo参照表Term表的termNo。

设计任务：

一、创建ScoreDB数据库

利用实验给出的“ScoreDB.sql”脚本文件，完成scoreDB数据库及对象的创建、表对象中数据记录的插入等操作。

二、针对创建好的ScoreDB数据库，完成下列要求的查询操作

1．投影查询：

查询Student表中所有学生的studentNo、studentName和Sex，并在查询结果中将列名显示为：学号、姓名和性别。

SQL语句如下：

运行结果：

2．单表查询：

查询SCore表中成绩在60到80之间的所有记录。

SQL语句如下：

运行结果：

查询成绩低于60分或高于90分的学生学号。（提示：用NOT BETWEEN AND）

SQL语句如下：

运行结果：

查询所有姓王且籍贯为“南昌”的学生姓名。

SQL语句如下：

运行结果：

查询SCore表中成绩为85、86、88或90的记录。

SQL语句如下：

运行结果：

查询年龄小于22岁或蒙古族的学生姓名。

SQL语句如下：

查询选修了课程但没有参加考试的学生学号。

SQL语句如下：

3．对查询结果排序：

查询Score表中的所有记录，并且以CourseNo升序、Score降序显示。

SQL语句如下：

4．使用聚合函数：

统计Student表中汉族学生人数。

SQL语句如下：

运行结果：

统计Student表有多少个姓（假设所有姓氏均为单姓）。

SQL语句如下：

运行结果：

统计 “002”号课程的平均分。

SQL语句如下：

运行结果：

5．数据分组：

查询选修人数大于2人，并且平均分在80分以上的课程号。

SQL语句如下：

查询至少有两门课程的成绩在80分以上学生的学号。

SQL语句如下：

6．表的连接查询：

查询至少选修了两门以上课程的学生的学号、姓名和所在班级。

SQL语句如下：

运行结果：

查询所有学生（包括未选课的学生）的学号、姓名（SName）及该学生所选课程的平均分。提示：用外连接

SQL语句如下：

运行结果：

7．嵌套查询：（以下部分必须用嵌套查询来完成）

查询“刘晨”班上同学的学号和姓名。

SQL语句如下：

运行结果：

查询修选了“高等数学”课程的学生学号和姓名。

SQL语句如下：

运行截图：

查询选修学生人数少于3人的课程名称。

SQL语句如下：

运行结果：

查询成绩低于该门课程的平均分的学生的学号。

SQL语句如下：

运行结果：

查询所有学分为6的全部课程的学生姓名。

SQL语句如下：

运行结果：

查询“cS1601”班同学都选修的课程名称

SQL语句如下：

运行结果：

查询选修了所有课程的学生的学号，姓名，年龄

运行结果：

查询没有选修课程的学生姓名。

SQL语句如下：

运行结果：

8．集合操作：（必须通过集合的交、并、差实现）

查询至少选修两门以上课程（包括两门）的学生学号。

SQL语句如下：

运行结果：

查询没有选修课程的学生姓名。

SQL语句如下：

运行结果：

9．视图的定义及其操作

定义一个查询学生的学号、姓名、学期、最高成绩、最低成绩、平均成绩的视图studentScore。

SQL语句如下：

运行结果：

通过视图studentScore查询最高成绩在90分以上(含90分)且没有不及格成绩的学生学号、姓名、系名、班级。

SQL语句如下：

运行结果：

#	查询Student表中所有学生的studentNo、studentName和Sex，并在查询结果中将列名显示为：学号、姓名和性别。
SELECT studentNo,studentName,Sex FROM student;
#	查询SCore表中成绩在60到80之间的所有记录。
SELECT * FROM score WHERE score BETWEEN 60 AND 80;
#	查询成绩低于60分或高于90分的学生学号
SELECT studentNo FROM score WHERE score NOT BETWEEN 60 AND 90;
#	查询所有姓王且籍贯为“南昌”的学生姓名
SELECT studentName FROM student WHERE studentName LIKE '王%' AND native = '南昌';
#	查询SCore表中成绩为85、86、88或90的记录
SELECT * FROM score WHERE score IN (85,86,88,90);



#*******	查询年龄小于22岁或蒙古族的学生姓名
SELECT * FROM student WHERE birthday > DATE_SUB(DATE('2020-05-26'),INTERVAL 22 YEAR) OR nation = '蒙古族';
SELECT studentName FROM student WHERE TIMESTAMPDIFF(YEAR,birthday,'2020-10-01') < 22 OR nation = '蒙古族';
#	查询选修了课程但没有参加考试的学生学号
SELECT studentNo FROM score WHERE score IS NULL;
#	查询Score表中的所有记录，并且以CourseNo升序、Score降序显示。
SELECT * FROM score ORDER BY CourseNo ASC,Score DESC;
#		统计Student表中汉族学生人数。
SELECT COUNT(*) FROM student WHERE nation = '汉族';


#********	统计Student表有多少个姓（假设所有姓氏均为单姓）。
SELECT COUNT(DISTINCT LEFT(studentName,1))  FROM student; 
SELECT DISTINCT SUBSTR(studentName,1,1) FROM student;
#	统计 “002”号课程的平均分。
SELECT AVG(score) FROM score WHERE courseNo = 002;
#	查询选修人数大于2人，并且平均分在80分以上的课程号。
SELECT courseNo FROM  score GROUP BY courseNo HAVING COUNT(*) > 2 and AVG(score) > 80; 

#**********	查询至少有两门课程的成绩在80分以上学生的学号。
#ERRORS:1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scoredb.score.courseNo' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# SELECT studentNo,courseNo,COUNT(*) coursenumber FROM score GROUP BY studentNo HAVING coursenumber >=2 AND score >=80;
# count(*) 无法和其他未被统计或者分组的字段名同时打印出来，报错编号1055，解决：写在having后面作为条件筛选
SELECT studentNo FROM score WHERE score >= 80 GROUP BY studentNo HAVING  COUNT(*) >=2;

#******	查询至少选修了两门以上课程的学生的学号、姓名和所在班级。
#SELECT studentNo,COUNT(*) number1  FROM score GROUP BY studentNo HAVING number1 >=2;
-- SELECT DISTINCT s.studentName,c.studentNo,s.classNo FROM student AS s ,score AS c WHERE s.studentNo = c.studentNo; 
#内连接（inner join）：取出两张表中匹配到的数据，匹配不到的不保留
#外连接（outer join）：取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL

SELECT student.studentNo AS 学号, student.studentName AS 姓名, class.className 
FROM ( student INNER JOIN score ON student.studentNo = score.studentNo )
INNER JOIN class ON student.classNo = class.classNo
GROUP BY student.studentNo
HAVING COUNT(*) >= 2;

#	查询所有学生（包括未选课的学生）的学号、姓名（SName）及该学生所选课程的平均分。提示：用外连接
#SELECT studentName,studentNo,AVG(score) FROM student AS s ,score AS c  GROUP  BY studentNo 1052 - Column 'studentNo' in field list is ambiguous
SELECT student.studentName,student.studentNo,AVG(score.score) 
FROM student  LEFT JOIN score ON student.studentNo = score.studentNo
GROUP  BY student.studentNo 

#	查询“刘晨”班上同学的学号和姓名。
SELECT studentNo,studentName FROM student WHERE classNo = (SELECT classNo FROM student WHERE studentName = '刘晨');

#	查询修选了“高等数学”课程的学生学号和姓名。（嵌套查询）
SELECT DISTINCT student.studentNo,student.studentName 
FROM student INNER JOIN score ON student.studentNo = score.studentNo
WHERE courseNo = (SELECT courseNo FROM course WHERE courseName = '高等数学');
#最好将=换为in，满足其一即可，包括唯一的情况

-- SELECT DISTINCT student.studentNo,student.studentName 
-- FROM student
-- WHERE studentNo 
-- = (select studentNo 
-- FROM score 
-- WHERE courseNo = (SELECT courseNo FROM course WHERE courseName = '高等数学'));
#mysql错误：Subquery returns more than 1 row  解决：在子查询前加any关键字

SELECT DISTINCT student.studentNo,student.studentName 
FROM student
WHERE studentNo 
= ANY(select studentNo 
FROM score 
WHERE courseNo = (SELECT courseNo FROM course WHERE courseName = '高等数学'));

#	查询选修学生人数少于3人的课程名称。
SELECT courseName 
FROM course 
WHERE courseNo = (SELECT courseNo FROM score GROUP BY courseNo HAVING COUNT(*) <= 3 );

#	查询所有学分为6的全部课程的学生姓名。
SELECT DISTINCT studentName
FROM student 
WHERE studentNo 
= ANY(select studentNo 
FROM score
WHERE courseNo = (SELECT courseNo FROM course WHERE creditHour = 6));

SELECT DISTINCT studentName
FROM student INNER JOIN score ON student.studentNo = score.studentNo

WHERE courseNo = (SELECT courseNo FROM course WHERE creditHour = 6);

#	查询“cS1601”班同学都选修的课程名称
SELECT course.courseName 
FROM course INNER JOIN score ON course.courseNo = score.courseNo
GROUP BY score.courseNo 
HAVING COUNT(*) =(SELECT COUNT(*) FROM student WHERE classNo = 'cS1601'); 

#	查询选修了所有课程的学生的学号，姓名，年龄
SELECT student.studentNo,student.studentName,TIMESTAMPDIFF(YEAR,birthday,CURDATE())
FROM student INNER JOIN score ON student.studentNo = score.studentNo
GROUP BY score.studentNo
HAVING COUNT(*) =(SELECT COUNT(*) FROM course );

#	查询没有选修课程的学生姓名。
SELECT studentName
FROM student
WHERE studentNo IN (SELECT student.studentNo FROM student WHERE student.studentNo NOT IN (SELECT studentNo FROM score));

SELECT a.* from
(select studentNo 
From student
union all 
select studentNo
From score
GROUP BY studentNo
Having Count(*) > 2) as a

#   查询至少选修两门以上课程（包括两门）的学生姓名。
SELECT studentName FROM student 
WHERE studentNo IN 
(select studentNo 
FROM score
GROUP BY studentNo
HAVING COUNT(*) >= 2);

#	查询没有选修课程的学生姓名。
SELECT studentName
FROM student
WHERE studentNo IN (SELECT student.studentNo FROM student WHERE student.studentNo NOT IN (SELECT studentNo FROM score));






#******	定义一个查询学生的学号、姓名、学期、最高成绩、最低成绩、平均成绩的视图studentScore。
#GROUP BY score.studentNo 单个分组不正确？
#1054 - Unknown column 'term.termNo，student.studentName' in 'group statement'  有中文符号
#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scoredb.term.termName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中，即只能展示group by的字段，其他均都要报1055的错；

CREATE VIEW studentScore AS
SELECT student.studentNo AS 学号,
student.studentName,
term.termName,
avg(score),
max(score),
MIN(score)
FROM (student INNER JOIN score ON student.studentNo = score.studentNo)
INNER JOIN term ON score.termNo = term.termNo
GROUP BY score.studentNo,term.termNo,student.studentName;

# 通过视图studentScore查询最高成绩在90分以上(含90分)且没有不及格成绩的学生学号、姓名、系名、班级。

Macey~

关注

22
点赞
踩
23

收藏

觉得还不错? 一键收藏
1
评论
实验二 SQL Server的数据查询

每门课程有多个学生选修，每个学生每学期可以同时选修多门课程，每个学生对于同一门课程可以多次选修，但每学期只能选修1次，课程选修成绩以综合成绩记录。根据学院提供的学生选课及其他的部分数据，将上图1所示的E-R图中的部分实体和联系转换为关系，得到如下5个关系模式和相应的数据字典描述。2．体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为综合应用打下良好的基础。⑵ 学生：包括学号、姓名、性别、出生日期、籍贯、民族、所属班级；⑴ 班级：包括班级号、班级名、所属学院、年级、班级人数；
复制链接

扫一扫