数据库1数据查询.docx
《数据库1数据查询.docx》由会员分享,可在线阅读,更多相关《数据库1数据查询.docx(16页珍藏版)》请在冰豆网上搜索。
数据库1数据查询
实验一数据库查询
课程名称:
数据库原理实验
实验类型:
验证型
实验名称
数据库查询
学时
4学时
实验目的:
使学生掌握SQLServerQueryAnalyzer的使用方法,加深对SQL和T-SQL语言的查询语句的理解。
熟练掌握表的基本查询,连接查询和嵌套查询,以及掌握数据排序和数据分组的操作方法。
实验原理:
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[orderby<列名2>[ASC|DESC]];
实验方法:
将查询需求用T-SQL语言表示;在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句;设置QueryAnalyzer的结果区为StandardExecute(标准执行)或ExecutetoGrid(网格执行)方式;发布执行命令,并在结果区中查看查询结果;如果结果不正确,要进行修改,直到正确为止。
实验内容:
1.分别用带DISTINCT和不带DISTINCT关键字的SELELCT在student中进行查询.
带distinct:
Selectclass_id
fromstudent
不带distinct:
selectdistinctclass_id
fromstudent
2.将teacher表中各教师的姓名、教工号及工资按95%发放的信息,并将工资按95%发放后的列名改为‘预发工资’
selectteacher_id,teacher_name,salary*0.95
as预发工资
fromteacher
3.查询course表中所有学分大于2并且成绩不及格的学生的信息.
selectdistinctstudent.*
fromstudent,course,sc
wherestudent.student_id=sc.student_id
andsc.course_id=course.course_id
andcourse.credit>2
andsc.grade<60
4.查询学分在4~8之间的学生信息.(用between..and和复合条件分别实现)
selectdistinctstudent.*
fromstudent,course,sc
wherestudent.student_id=sc.student_id
andsc.course_id=course.course_id
andcourse.credit
between4and8
5.从student_course表中查询出学生为“g9940201”,“g9940202”的课程号、学生号以及学分,并按学分降序排列(用in实现)
select*
fromsc,course
wherestudent_idin('g9940201','g9940202')
andcourse.course_id=sc.course_id
orderbycourse.creditdesc
6.从teacher表中分别检索出姓王的教师的资料,或者姓名的第2个字是远或辉的教师的资料
select*
fromteacher
whereteacher_name
like'王%'
orteacher_name
like'_远%'
orteacher_name
like'_辉%'
7.查询每个学生及其选修课情况
selectstudent_name,course_name
fromstudent,sc,course
wheresc.student_id=student.student_id
andsc.course_id=course.course_id
8.以student表为主体列出每个学生的基本情况及其选课情况,如果学生没有选课,只输出其基本情况
selectstudent.*,course.*
fromstudentjoinsc
onstudent.student_id=sc.student_id
leftjoincourse
oncourse.course_id=sc.course_id
9.查询选修dep04_s001号课程且成绩在80分以上的学生信息。
(分别用连接,in和exists实现)
连接:
selectstudent.*
fromsc,student
wheresc.student_id=student.student_id
andsc.course_id='dep04_s001'
andsc.grade>80
In:
select*
fromstudent
wherestudent_id
in(
selectstudent_id
fromsc
wherecourse_id='dep04_s001'
andgrade>80)
exists:
select*
fromstudent
whereexists(
selectstudent_id
fromsc
wherestudent.student_id=sc.student_id
andcourse_id='dep04_s001'
andgrade>80)
10.查询所有上计算机基础课程的学生的学号、选修课程号以及分数(分别用连接,in和exists实现)
连接:
selectsc.*
fromsc,course
wherecourse.course_name='计算机基础'
andcourse.course_id=sc.course_id
In:
selectstudent_id,course_id,grade
fromsc
wherecourse_id
in(
selectcourse_id
fromcourse
wherecourse_name='计算机基础')
exists:
selectstudent_id,course_id,grade
fromsc
whereexists(
select*
fromcourse
wherecourse_id=sc.course_id
andcourse_name='计算机基础')
11.查询选修了课程名为“数据库基础”的学生学号和姓名(分别用连接,in和exists实现)
连接:
selectstudent.student_id,student.student_name
fromsc,student,course
wherecourse.course_name='数据库开发技术'
andsc.student_id=student.student_id
andcourse.course_id=sc.course_id
In:
selectstudent_id,student_name
fromstudent
wherestudent_id
in(
selectstudent_id
fromsc
wherecourse_id=(
selectcourse_id
fromcourse
wherecourse_name='数据库开发技术'))
exists:
selectstudent_id,student_name
fromstudent
whereexists(
select*
fromsc
wheresc.student_id=student.student_id
andsc.course_id=(
selectcourse_id
fromcourse
wherecourse_name='数据库开发技术'))
12.查询所有计算机系学生的学号、选修课程号以及分数(分别用连接,in和exists实现)
连接:
selectsc.*
fromsc,department,student
wheredepartment.department_name='计算机科学'
anddepartment.department_id=student.department_id
andsc.student_id=student.student_id
In:
select*
fromsc
wherestudent_id
in(
selectstudent_id
fromstudent
wheredepartment_id=(
selectdepartment_id
fromdepartment
wheredepartment_name='计算机科学'))
exists:
select*
fromsc
whereexists(
select*fromstudent
wherestudent.student_id=sc.student_id
andstudent.department_id=(
selectdepartment_id
fromdepartment
wheredepartment_name='计算机科'))
13.查询每个dep_04系学生的总成绩、平均成绩,仅显示平均成绩及格的学生的记录。
selectstudent.student_name,sum(grade)as总成绩,avg(grade)as平均成绩
fromsc,student,department
wheresc.student_id=student.student_id
andstudent.department_id=department.Department_id
anddepartment.department_id='dep_04'
groupbystudent.student_id,student_name
havingavg(grade)>60
14.查询“数据库开发技术”的平均成绩
selectavg(grade)as数据库开发平均成绩
fromsc
wherecourse_idin(
selectcourse_id
fromcourse
wherecourse_name='数据库开发技术')
15.按职称查询教师的平均工资,并按总工资降序排列
selectprofession,avg(salary)as平均工资,sum(salary)as总工资
fromteacher
groupbyprofession
orderbysum(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
否
系名称