【SQL】实验二 SQL数据查询

实验目的:

能够熟悉地利用SQL查询语句进行单表数据查询和数据排列处理、利用SQL查询语句进行夺标之间的数据联结查询、嵌套查询以及组合查询等。

实验内容：

①建库后建表
②录入记录
③使用查询分析器进行查询

实验步骤与过程：

一. 建库并建表

1、建库

2、建表

①设计“老师”表结构：
根据属性需求设定相关字段>>根据字段需求设定适合的数据类型和字符长度>>设置主键（NO）

②设计“学生”表结构：
根据属性需求设定相关字段>>根据字段需求设定适合的数据类型和字符长度>>设置主键（NO）

③设计“课程”表结构：
根据属性需求设定相关字段>>根据字段需求设定适合的数据类型和字符长度>>设置主键（CNO）

④设计“成绩”表结构：
根据属性需求设定相关字段>>根据字段需求设定适合的数据类型和字符长度>>设置主键（CNO）

注：【字段DEGREEE数据类型需为real，real可以进行计算，char则不行】

【这里需设置两个主键，因为该表是多对多的关系，单一NO或CNO字段不唯一】

二. 录入记录

①录入“老师”表记录：

②录入“学生”表记录：

③录入“课程”表记录：

④录入“成绩”表记录：

三. 使用查询分析器进行查询

1、显示 SCORES 表中成绩在 60 到 80 之间的所有记录

命令输入：

Select *
From SCORE
Where DEGREE Between 60 And 80;

查询输出：

2、显示 SCORES 表中成绩为 85，86，或 88 的记录

命令输入：

Select *
From SCORE
Where DEGREE In (85,86,88);

查询输出：

3、显示"95031"班或性别为"女"的同学记录

命令输入：

Select *
From STUDENTS
Where CLASS = '95031' Or SEX = '女';

查询输出：

4、以 CLASS 降序显示 STUDENT表的所有记录

命令输入：

Select *
From STUDENTS
Order By CLASS DESC;

查询输出：

5、显示 SCORES 表中最高分的学生学号和课程号

命令输入：

Select NO,CNO
From SCORE
Where DEGREE = (Select MAX(DEGREE)
                From SCORE);

查询输出：

6、显示‘3-105’号课程的平均分

命令输入：

Select AVG(DEGREE) 平均分
From SCORE
Where CNO = '3-105';

查询输出：

7、显示 SCORES 表中至少有5 名学生选修的并以3 开头的课程号的平均分

命令输入：

Select AVG(DEGREE) 平均分
From SCORE
Where CNO = '3*'
Group By No Having COUNT(NO) >= 5;

查询输出：

8、 显示最低分大于70，最高分小于 90 的 NO 列

命令输入：

Select NO
From SCORE
Where DEGREE >= '70' And DEGREE <= '90';

查询输出：

9、 显示所有同学的 NAME，CNAME 和 DEGREE 列

命令输入：

Select NAME,CNAME,DEGREE
From SCORE,STUDENTS,COURSES
Where STUDENTS.NO In (Select SCORE.NO
                      From STUDENTS
                      Where SCORE.CNO In(Select COURSES.CNO
                                         From SCORE)
                     );

查询输出：

10、显示"95033"班所选课程的平均分

命令输入：

Select AVG(DEGREE) 平均分
From SCORE
Where No In (Select NO
             From STUDENTS
             Where CLASS = '95033');

查询输出：

11、显示选修"3-105"课程的成绩高于"109"号同学成绩的所有同学的记录

命令输入：

Select *
From SCORE
Where CNO = '3-105' And DEGREE > All(Select DEGREE
                                     From SCORE
                                     Where NO ='109');

查询输出：

12、显示 SCORE 中选学多门课程的同学中分数为非最高分成绩的记录

命令输入：

Select x.NO,CNO,DEGREE
From SCORE x,(Select NO
              From SCORE
              Group By No Having COUNT(NO) > 1) As SC1
Where x.NO = SC1.NO And DEGREE > (Select MIN(DEGREE)
                                  From SCORE y
                                  Where y.NO = x.NO);

查询输出：

13、显示成绩高干学号为"109"，课程号为"3-105"的成绩的所有记录

命令输入：

Select *
From SCORE
Where CNO = '3-105' And DEGREE > (Select DEGREE
                                  From SCORE
                                  Where NO = '109' AND CNO = '3-105');

查询输出：

14、显示"张旭"老师任课的学生成绩

命令输入：

Select DEGREE) 
From SCORE
Where CNO In(Select CNO
             From COURSES
             Where TNO = (Select NO
                          From TEACHER
                          Where NAME = '张旭')
            );

查询输出：

15、显示选修某课程的同学人数多于5 人的教师姓名

命令输入：

Select NAME
From TEACHER
Where NO = (Select TNO
             From COURSES
             Where CNO = (Select CNO
                          From SCORE
                          Group By CNO Having COUNT(CNO) > 5)
            );

查询输出：

16、列出 95033 班和 95031 班全体学生的记录

命令输入：

Select *
From STUDENTS
Where CLASS = '95033' Or CLASS = '95031';

查询输出：

17、列出"计算机系"教师所教课程的成绩表

命令输入：

Select SCORE.*
From SCORE
Where CNO IN(Select CNO
             From COURSES
             Where TNO In(Select NO
                          From Teacher
                          Where DEPART = '计算机')
            );

查询输出：

18、列出"计算机系"与"电子工程系"不同职称的教师的NMAE 和 PROF

命令输入：

Select NAME,PROF
From TEACHER
Where PROF NOT IN(Select PROF
           	      From TEACHER
                  Where DEPART = '计算机'）
                  And
      PROF NOT IN(Select PROF
                  From Teacher
                  Where DEPART = '电子工程系');

查询输出：

19、列出编号为"3-105"课程且成绩至少高于选修编号为"3-245"的同学 CNO、NO 和DEGREE，并按 DEGREE 从高到低排序

命令输入：

Select CNO.NO,DEGREE
From SCORE
Where CNO = '3-105' And DEGREE > Any(Select DEGREE
                                     From SCORE
                                     Where CNO = '3-245')
Order By DEGREE DESC;              

查询输出：

20、列出所有教师和同学的NAME 、SEX 和 BIRTHDAY

命令输入：

Select NAME,SEX,BIRTHDAY
From TEACHER
UNION
Select NAME,SEX,BIRTHDAY
From STUDENTS ;

查询输出：

21、显示所有任课老师的 name 和 depart

命令输入：

Select NAME,DEPART
From TEACHER,COURSES
Where TNO In(Select NO
             From COURSES);

查询输出：

22、显示所有未讲课的老师的 name 和 depart

命令输入：

Select NAME,DEPART
From TEACHER,COURSES
Where NO Not In (Select TNO
                 From COURSES);

查询输出：

23、查询选修所有课程的学生姓名和学号码（两种方法）

命令输入：

【方法一】

Select SCORE.NO,STUDENTS.NAME
From SCORE,STUDENTS,(Select NAME,SCORE.NO
                     From SCORE Join STUDENTS On SCORE.NO = STUDENTS.NO
                     Group By Name,SCORE.NO Having COUNT(CNO) = (Select COUNT(CNO) 
                                                                 From COURSES) 
                     )As SC1
Where STUDENTS.NAME = SC1.NAME And SCORE.NO = SC1.NO
Group By SCORE.NO,STUDENTS.NAME HAVING COUNT(CNO) = (Select COUNT(CNO)
                                                     From COURSES);

【方法二】

Select No,NAME
From STUDENTS
Where NO In(Select NO
            From SCORE
            Group By No Having COUNT(*) In(Select NO
                                           From COURSES)
            );

查询输出：

实验反思：

1、Order By要升降序时，中间应输入列名。

2、子查询返回值不止一个时，应用“In”来作为返回的谓词。

3、当来源表有多个同名列且目标列为同名列时，应明确列名，即在列名前明确其属于何表。