数据库1数据查询.doc

实验一 数据库查询
课程名称：
数据库原理实验
实验类型：
验证型
实验名称
数据库查询
学时
4学时

实验目的：
使学生掌握SQL Server Query Analyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。
实验原理：
SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]…
FROM <表名或视图名>[，<表名或视图名>]…
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[order by <列名2> [ASC|DESC]]；
实验方法：
将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验内容：
1. 分别用带DISTINCT和不带DISTINCT关键字的SELELCT在student中进行查询.
带distinct:
Select classid
from student

不带distinct:
select distinct class_id
from student

2. 将teacher表中各教师的姓名、教工号及工资按95％发放的信息，并将工资按95％发放后的列名改为‘预发工资’
select teacher_id,teacher_name,salary*0.95
as 预发工资
from teacher

3. 查询course表中所有学分大于2并且成绩不及格的学生的信息.
select distinct student.*
from student,course,sc
where student.student_id=sc.student_id
and sc.course_id=course.course_id
and course.credit>2
and sc.grade<60

4. 查询学分在4～8之间的学生信息.（用between..and和复合条件分别实现）
select distinct student.*
from student,course,sc
where student.student_id=sc.student_id
and sc.course_idcourse.course_id
and course.credit
between 4 and 8

5. 从student_course表中查询出学生为“g9940201”，“g9940202”的课程号、学生号以及学分，并按学分降序排列（用in实现）
select *
from sc,course
where student_id in('g9940201','g9940202')
and course.course_id=sc.course_id
order by course.credit desc

6. 从teacher表中分别检索出姓王的教师的资料，或者姓名的第2个字是远或辉的教师的资料
select *
from teacher
where teacher_name
like '王%'
or teacher_name
like'_远%'
or teacher_name
like '_辉%'

7. 查询每个学生及其选修课情况
select student_name,course_name
from student, sc,course
where sc.student_id=student.student_id
and sc.course_id=course.course_id

8. 以student表为主体列出每个学生的基本情况及其选课情况，如果学生没有选课，只输出其基本情况
select student.*,course.*
from student join sc
on student.student_id=sc.student_id
left join course
on course.course_id=sc.course_id

9. 查询选修dep04_s001号课程且成绩在80分以上的学生信息。（分别用连接，in和exists实现）
连接：
select student.*
from sc,student
where sc.student_id=student.student_id
and sc.course_id='dep04_s001'
and sc.grade>80

In：
select *
from student
where student_id
in(
select studentid
from sc
where course_id='dep04_s001'
and grade>80)

exists：
select *
from student
where exists(
select student_id
from sc
where student.student_id=sc.student_id
and course_id='dep04_s001'
and grade>80)

10. 查询所有上计算机基础课程的学生的学号、选修课程号以及分数（分别用连接，in和exists实现）
连接：
select sc.*
from sc,course
where course.course_name='计算机基础'
and course.course_id=sc.course_id

In：
select student_id,course_id,grade
from sc
where course_id
in(
select course_id
from course
where course_name='计算机基础')

exists：
select student_id,course_id,grade
from sc
where exists(
select *
from course
where course_id=sc.course_id
and course_name='计算机基础')

11. 查询选修了课程名为“数据库基础”的学生学号和姓名（分别用连接，in和exists实现）
连接：
select student.student_id,student.student_name
from sc,student,course
where course.course_name='数据库开发技术'
and sc.student_id=student.student_id
and course.course_id=sc.course_id

In：
select student_id,student_name
from student
where student_id
in(
select student_id
from sc
where course_id=(
select course_id
from course
where course_name='数据库开发技术' ) )

exists：
select student_id,student_name
from student
where exists(
select *
from sc
where sc.student_id=student.student_id
and sc.course_id=(
select course_id
from course
where course_name='数据库开发技术' ))

12. 查询所有计算机系学生的学号、选修课程号以及分数（分别用连接，in和exists实现）
连接：
select sc.*
from sc,department,student
where department.department_name='计算机科学'
and department.department_id=student.department_id
and sc.student_id=student.student_id

In：
select*
from sc
where student_id
in(
select student_id
from student
where department_id=(
select department_id
from department
where department_name='计算机科学'))

exists：
select*
from sc
where exists (
select * from student
where student.student_id =sc.student_id
and student.department_id=(
select department_id
from department
where department_name='计算机科'))

13．查询每个dep_04系学生的总成绩、平均成绩， 仅显示平均成绩及格的学生的记录。
select student.student_name ,sum(grade) as 总成绩,avg(grade) as 平均成绩
from sc, student,department
where sc.student_id=student.student_id
and student.department_id=department.Department_id
and department.department_id='dep_04'
group by student.student_id,student_name
having avg(grade)>60

14．查询“数据库开发技术”的平均成绩
select avg(grade) as 数据库开发平均成绩
from sc
where course_id in(
select course_id
from course
where course_name='数据库开发技术')

15．按职称查询教师的平均工资，并按总工资降序排列
select profession,avg(salary) as 平均工资,sum(salary) as 总工资
from teacher
group by profession
order by sum(salary) desc

附录1：
教务管理数据库jwgl结构

student表结构
列名称
数据类型
长度
允许空值
说明
Student_id
Char
8
否
学生学号
Student_name
Varchar
8
否
学生姓名
Sex
Bit
1
否
性别
Age
Int
4
否
年龄
Class_id
Char
6
否
班级号
Department_id
Char
6
否
系编号
Address
varchar
50
否
家庭住址

Course表
字段名称
数据类型
长度
允许空
说明
Course_id
Char
10
否
课程号
Course_name
Varchar
20
否
课程名称
Book_id
Char
15
否
书标识

SC表
字段名称
数据类型
长度
允许空
说明
Course_id
Char
10
否
课程号
Student_id
Varchar
8
否
学生号
grade
Tinyint

否
成绩
credit
Tinyint

否
学分

Teacher表
字段名称
数据类型
长度
允许空
说明
Teacher_id
Char
9
否
教师编号
Teacher_name
nvarchar
8
否
教师姓名
Department_id
Char
6
否
所在系号
Profession
Nvarchar
16

职称
Sex
Bit

否
性别
phone
Nvarchar
15
是
电话
adress
Nchar
40
是
住址
Salary
Numeric
7.2
是
工资
Birth
Smalldatetime

否
出生日期
Postalcode
Numeric
6,0
是
邮编
Book表
字段名称
数据类型
长度
允许空
说明
Book_id
Char
13
否
书号
Book_name
Varchar
30
否
书名
Publish_company
Varchar
50
否
出版社
Author
nvarchar
8
是
作者
Price
Numeric
5.2
是
价格
Class表
字段名称
数据类型
长度
允许空
说明
Class_id
Char
6
否
班级号
Monitor
Varchar
8
是
班主任姓名

Class_course表
字段名称
数据类型
长度
允许空
说明
Class_id
Char
6
否
班级号
Course_id
char
10
否
课程号

Department表
字段名称
数据类型
长度
允许空
说明
Department_id
Char
6
否
系编号
Department_name
nvarchar
20
否
系名称