数据库原理及安全技术教学实验报告SQL实践（七）

目录

一、实验目的

二、实验软硬件要求

三、实验预习

四、实验内容（实验步骤、测试数据等）

简单的数据查询

复杂数据查询

运行结果：

简单的数据查询

(1)查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别

    (2)查询所有同学，要求显示其学号s_no、姓名s_name。

    (3)查询所有男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。

    (4)查询所有出生日期在“1980一01一01”前的女同学，要求显示其学号s no、姓名

    (5)查询所有姓“李”的男同学，要求显示其学号s _no、姓名s _name、性别s _sex、出

    (6)查询所有姓名中含有“一”字的同学，要求显示其学号s _no、姓名s_ name。

    (7)查询所有职称不是“讲师”的教师，要求显示其教师号t _no、姓名t _name、职称t _title。

    (8)查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s _no。

    (9)查询所有考试不及格的同学，要求显示出这些同学的学号s _no、成绩score，并按成绩降序排列。

    (10)查询出课程号为01001，02001，02003的所有课程，要求显示出课程号course_no、

(11)查询所有在1970年出生的教师，要求显示其教师号t_no、姓名t_name、出生日期t_birthday。

(12)查询出各个课程号course_no及相应的选课人数。

(13)查询出教授2门以上课程的教师号t_no。

(14)查询出选修了01001课程的学生平均分数、最低分数及最高分数。

(15)查询1960年以后出生的，职称为讲师的教师的姓名t_name，出生日期t_birthday，并按出生日期升序排列。

复杂数据查询

(1)查询所有同学的选课及成绩情况，要求显示学生的学号s _no、姓名s_name、课程号

(2)查询所有同学的选课及成绩情况，要求显示学生的姓名s _name、课程名称course_

name、课程的成绩score，并将查询结果存放到一个新的数据表new_table中。

(3)查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号s_ no、姓名

s _name、课程号course _no、课程名称course_name、课程的成绩score。

(4)查询所有同学的学分情况(假设课程成绩>=60时可获得该门课程的学分)，要求显

示学生的学号s _no、姓名s_ name、总学分(将该列定名为：total_score)。(用JOIN)

(5)查询所有同学的平均成绩及选课门数，要求显示学生的学号s_ no、姓名s_ name、平均成绩(将该列定名为：average_score)、选课的门数(将该列定名为：choice_num)。

(6)查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号S_ no、姓名s_ name、课程号course_no、课程名称course_name。

(7)查询所有选修了课程但考试不及格(假设<60分为不及格)的所有同学及相应的课程，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course _name、课程成绩course_score。

(8)查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示学生的姓名s_ name、课程的成绩score。(使用ANY)

(9)查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_ no、姓名s _name、班级名称class _name、课程号course _no、课程名称course_name、课程的成绩score。3

(10)查询所有教师的任课情况，要求显示教师姓名t _name、担任课程的名称course _name。

（11）查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数（将该列定名为：course_number）。

（12）查询和“李建国”是同一班级的同学的姓名。（使用子查询）

（13）查询没有选修“计算机基础”课程的学生姓名。（用NOT EXISTS）

（14）查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用UNION）

（15）查询讲授了所有课程的教师的姓名。

先查课程没有被教的，再反下。（或者查老师没有课程是没有被教授的）

---

一、实验目的

1、掌握各种简单查询、复杂查询。

二、实验软硬件要求

1、SQL Server 2008

三、实验预习

1、单表查询、表连接等SQL语句。

四、实验内容（实验步骤、测试数据等）

书本P294综合练习：

在综合实验（一）的基础上，作如下查询：

1. 简单的数据查询

(1)查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别

S_sex、出生日期s_birthday。

    (2)查询所有同学，要求显示其学号s_no、姓名s_name。

    (3)查询所有男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。

    (4)查询所有出生日期在“1980一01一01”前的女同学，要求显示其学号s no、姓名

s_name、性别s_sex、出生日期s_birthday。

    (5)查询所有姓“李”的男同学，要求显示其学号s _no、姓名s _name、性别s _sex、出

生日期s _birthday。

    (6)查询所有姓名中含有“一”字的同学，要求显示其学号s _no、姓名s_ name。

    (7)查询所有职称不是“讲师”的教师，要求显示其教师号t _no、姓名t _name、职称t _title。

    (8)查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s _no。

    (9)查询所有考试不及格的同学，要求显示出这些同学的学号s _no、成绩score，并按

成绩降序排列。

    (10)查询出课程号为01001，02001，02003的所有课程，要求显示出课程号course_no、

course_name。(要求用in运算符)。

(11)查询所有在1970年出生的教师，要求显示其教师号t_no、姓名t_name、出生日期t_birthday。

(12)查询出各个课程号course_no及相应的选课人数。

(13)查询出教授2门以上课程的教师号t_no。

(14)查询出选修了01001课程的学生平均分数、最低分数及最高分数。

(15)查询1960年以后出生的，职称为讲师的教师的姓名t_name，出生日期t_birthday，并按出生日期升序排列。

1. 复杂数据查询

(1)查询所有同学的选课及成绩情况，要求显示学生的学号s _no、姓名s_name、课程号

Course_no和课程的成绩score。

(2)查询所有同学的选课及成绩情况，要求显示学生的姓名s _name、课程名称course_

name、课程的成绩score，并将查询结果存放到一个新的数据表new_table中。

(3)查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号s_ no、姓名

s _name、课程号course _no、课程名称course_name、课程的成绩score。

(4)查询所有同学的学分情况(假设课程成绩>=60时可获得该门课程的学分)，要求显

示学生的学号s _no、姓名s_ name、总学分(将该列定名为：total_score)。(用JOIN)

(5)查询所有同学的平均成绩及选课门数，要求显示学生的学号s_ no、姓名s_ name、平

均成绩(将该列定名为：average_score)、选课的门数(将该列定名为：choice_num)。

(6)查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号

S_ no、姓名s_ name、课程号course_no、课程名称course_name。．

(7)查询所有选修了课程但考试不及格(假设<60分为不及格)的所有同学及相应的课

程，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course _name、课程成绩course_score。

(8)查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示学生的姓名s_ name、课程的成绩score。(使用ANY)

(9)查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_ no、姓名s _name、

班级名称class _name、课程号course _no、课程名称course_name、课程的成绩score。3

(10)查询所有教师的任课情况，要求显示教师姓名t _name、担任课程的名称course _name。

（11）查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数（将该列定名为：course_number）。

（12）查询和“李建国”是同一班级的同学的姓名。（使用子查询）

（13）查询没有选修“计算机基础”课程的学生姓名。（用NOT EXISTS）

（14）查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用UNION）

（15）查询讲授了所有课程的教师的姓名。

运行结果：

简单的数据查询

(1)查询所有同学的基本信息，包括：学号s_no、班级号class_no、姓名s_name、性别

S_sex、出生日期s_birthday。

use Study

select s_no,class_name,s_name,s_sex,s_birthday

from Student,Class

 

    (2)查询所有同学，要求显示其学号s_no、姓名s_name。

use Study

select s_no,s_name

from Student

    (3)查询所有男同学，要求显示其学号s_no、姓名s_name、出生日期s_birthday。

use Study

select s_no,s_name,s_birthday

from Student

where s_sex='男'

    (4)查询所有出生日期在“1980一01一01”前的女同学，要求显示其学号s no、姓名

s_name、性别s_sex、出生日期s_birthday。

use Study

select s_no,s_name,s_sex,s_birthday

from Student

where s_birthday < '1980-01-01'

and s_sex='女'

    (5)查询所有姓“李”的男同学，要求显示其学号s _no、姓名s _name、性别s _sex、出

生日期s _birthday。

use Study

select s_no,s_name,s_sex,s_birthday

from Student

where  s_sex ='男'

and s_name like '李%'

    (6)查询所有姓名中含有“一”字的同学，要求显示其学号s _no、姓名s_ name。

use Study

select s_no,s_name

from Student

where   s_name like '%一%'

    (7)查询所有职称不是“讲师”的教师，要求显示其教师号t _no、姓名t _name、职称t _title。

use Study

select t_no,t_name,t_title

from Teacher

where t_title not like '讲师'

    (8)查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s _no。

use Study

select s_no

from Choice

where score is null

and course_no is not null

    (9)查询所有考试不及格的同学，要求显示出这些同学的学号s _no、成绩score，并按成绩降序排列。

use Study

select s_no,score

from Choice

where score <60

order by score desc

    (10)查询出课程号为01001，02001，02003的所有课程，要求显示出课程号course_no、

course_name。(要求用in运算符)。

use Study

select course_no,course_name

from Course

where course_no in (01001,02001,02003)

(11)查询所有在1970年出生的教师，要求显示其教师号t_no、姓名t_name、出生日期t_birthday。

bewteen包含两端极值。

use Study

select t_no,t_name,t_birthday

from Teacher

where t_birthday between '1970-1-1' and '1970-12-31'

(12)查询出各个课程号course_no及相应的选课人数。

use Study

select course_no,count(course_no) as 选修人数

from Choice

group by course_no

(13)查询出教授2门以上课程的教师号t_no。

use Study

select t_no as 教授2门以上课程的教师号

from Teaching

group by t_no

having count(t_no)>=2

(14)查询出选修了01001课程的学生平均分数、最低分数及最高分数。

use Study

select  avg(score) 平均分,max(score) 最大分,min(score) 最小分

from Choice

where course_no=01001

(15)查询1960年以后出生的，职称为讲师的教师的姓名t_name，出生日期t_birthday，并按出生日期升序排列。

use Study

select  t_name,t_birthday

from Teacher

where t_birthday > '1959-12-31'

order by t_birthday

复杂数据查询

(1)查询所有同学的选课及成绩情况，要求显示学生的学号s _no、姓名s_name、课程号

Course_no和课程的成绩score。

use Study

select Student.s_no,Student.s_name,Choice.course_no,Choice.score

from Choice,Student

where Student.s_no=Choice.s_no

(2)查询所有同学的选课及成绩情况，要求显示学生的姓名s _name、课程名称course_

name、课程的成绩score，并将查询结果存放到一个新的数据表new_table中。

use Study

select s_name,course_name,score

into new_table

from Choice,Student,Course

where Student.s_no = Choice.s_no and Choice.course_no=Course.course_no

(3)查询“计算机99-1”班的同学的选课及成绩情况，要求显示学生的学号s_ no、姓名

s _name、课程号course _no、课程名称course_name、课程的成绩score。

use Study

select Student.s_no,Student.s_name,Course.course_no,course_name,score

from Choice,Student,Course,Class

where Student.s_no = Choice.s_no

and Choice.course_no=Course.course_no

and Student.class_no=Class.class_no

and Class.class_name= '计算机99-1'

(4)查询所有同学的学分情况(假设课程成绩>=60时可获得该门课程的学分)，要求显

示学生的学号s _no、姓名s_ name、总学分(将该列定名为：total_score)。(用JOIN)

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

use Study

select Choice.s_no,Student.s_name,sum(Course.course_score) as total_score

from Choice inner join Student on Choice.s_no=Student.s_no

join Course on Choice.course_no=Course.course_no

where Choice.score>=60

group by Choice.s_no,Student.s_name

(5)查询所有同学的平均成绩及选课门数，要求显示学生的学号s_ no、姓名s_ name、平均成绩(将该列定名为：average_score)、选课的门数(将该列定名为：choice_num)。

use Study

select Choice.s_no,Student.s_name,avg(Choice.score) asaverage_score,count(Choice.s_no) as choice_num

from Choice inner join Student on Choice.s_no=Student.s_no

group by Choice.s_no,Student.s_name

(6)查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号S_ no、姓名s_ name、课程号course_no、课程名称course_name。

use Study

select Choice.s_no,Student.s_name,Course.course_no,Course.course_name

from Choice left join Student on Choice.s_no=Student.s_no

join Course on Choice.course_no=Course.course_no

where Choice.score is null

(7)查询所有选修了课程但考试不及格(假设<60分为不及格)的所有同学及相应的课程，要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course _name、课程成绩course_score。

use Study

select Choice.s_no,Student.s_name,Course.course_no,Course.course_name,course_score

from Choice left join Student on Choice.s_no=Student.s_no

join Course on Choice.course_no=Course.course_no

where Choice.score < 60

(8)查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示学生的姓名s_ name、课程的成绩score。(使用ANY)

use Study

select Student.s_name,Choice.score

from Choice left join Student on Choice.s_no=Student.s_no

join Course on Choice.course_no=Course.course_no

where Course.course_no= any( 

select Course.course_no

from Course

where Course.course_name='程序设计语言')

(9)查询“计算机系”的所有同学及成绩情况，要求显示学生的学号s_ no、姓名s _name、班级名称class _name、课程号course _no、课程名称course_name、课程的成绩score。3

use Study

select Student.s_no,Student.s_name,Class.class_name,Course.course_no,Course.course_name,Choice.score

from Student,Class,Choice,Course

where Student.s_no=Choice.s_no

and Student.class_no=Class.class_no

and Choice.course_no=Course.course_no

and Class.class_dept='计算机系'

(10)查询所有教师的任课情况，要求显示教师姓名t _name、担任课程的名称course _name。

use Study

select Teacher.t_name,Course.course_name

from Teacher,Teaching,Course

where Teacher.t_no=Teaching.t_no

and Teaching.course_no=Course.course_no

（11）查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数（将该列定名为：course_number）。

use Study

select Teacher.t_name,count(Teacher.t_no) ascourse_number

from Teacher,Teaching

where Teacher.t_no=Teaching.t_no

group by Teacher.t_name

（12）查询和“李建国”是同一班级的同学的姓名。（使用子查询）

use Study

select Student.s_name

from Student

where Student.class_no=(

select Student.class_no

from Student

where Student.s_name='李建国')

and Student.s_name!='李建国'

（13）查询没有选修“计算机基础”课程的学生姓名。（用NOT EXISTS）

use Study

select s_name

from Student

where not exists (

select Student.s_name

from Course,Choice

where Choice.course_no = Course.course_no

and Student.s_no =Choice.s_no

and Course.course_name='计算机基础' )

（14）查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用UNION）

use Study

select t_name

from Teacher,Teaching,Course

where Teacher.t_no= Teaching. t_no

and  Teaching.course_no = Course.course_no

and Course.course_name = '数据库原理与应用'

union

select t_name

from Teacher,Teaching,Course

where Teacher.t_no= Teaching. t_no

and  Teaching.course_no = Course.course_no

and Course.course_name =  '数据结构'

（15）查询讲授了所有课程的教师的姓名。

先查课程没有被教的，再反下。（或者查老师没有课程是没有被教授的）

use Study

select t_name

from Teacher

where not exists (

select *

from Course

where not exists (

select *

from Teaching

where Course.course_no = Teaching.course_no

and Teacher.t_no = Teaching.t_no)

)

五、实验体会

操作不够熟练，需要加强连续。