能够熟悉地利用SQL查询语句进行单表数据查询和数据排列处理、利用SQL查询语句进行夺标之间的数据联结查询、嵌套查询以及组合查询等。
①建库后建表
②录入记录
③使用查询分析器进行查询
①设计“老师”表结构:
根据属性需求设定相关字段>>根据字段需求设定适合的数据类型和字符长度>>设置主键(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、当来源表有多个同名列且目标列为同名列时,应明确列名,即在列名前明确其属于何表。