数据库1数据查询

1、实验一 数据库查询课程名称：数据库原理实验实验类型：验证型实验名称数据库查询学时4学时实验目的：使学生掌握SQL Server Query Analyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。实验原理：SELECT ALL|DISTINCT <目标列表达式>，<目标列表达式> FROM <表名或视图名>，<表名或视图名> WHERE <条件表达式> GROUP BY <列名1> HAVING <条件表达式> o

2、rder 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 class_id from student不带d

3、istinct:select distinct class_id from student2. 将teacher表中各教师的姓名、教工号及工资按95发放的信息，并将工资按95发放后的列名改为预发工资select teacher_id,teacher_name,salary*0.95 as 预发工资from teacher3. 查询course表中所有学分大于2并且成绩不及格的学生的信息.select distinct student.* from student,course,sc where student.student_id=sc.student_id and sc.course_id=

4、course.course_id and course.credit>2 and sc.grade<604. 查询学分在48之间的学生信息.（用between.and和复合条件分别实现）select distinct student.* from student,course,sc where student.student_id=sc.student_id and sc.course_id=course.course_id and course.credit between 4 and 85. 从student_course表中查询出学生为“g9940201”，“g9940202

5、”的课程号、学生号以及学分，并按学分降序排列（用in实现）select * from sc,course where student_id in('g9940201','g9940202') and course.course_id=sc.course_id order by course.credit desc6. 从teacher表中分别检索出姓王的教师的资料，或者姓名的第2个字是远或辉的教师的资料select * from teacher where teacher_name like '王%' or teacher_name like&#

6、39;_远%' 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_id8. 以student表为主体列出每个学生的基本情况及其选课情况，如果学生没有选课，只输出其基本情况 select student.*,course.* from student join sc on student.

7、student_id=sc.student_id left join course on course.course_id=sc.course_id9. 查询选修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>80In：select * from student where student_id

8、 in( select student_id 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实现）连接：se

9、lect sc.* from sc,course where course.course_name='计算机基础' and course.course_id=sc.course_idIn：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 * f

10、rom 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.cour

11、se_idIn：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.stude

12、nt_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=stu

13、dent.student_idIn：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.depar

14、tment_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.Dep

15、artment_id and department.department_id='dep_04' group by student.student_id,student_name having avg(grade)>6014查询“数据库开发技术”的平均成绩select avg(grade) as 数据库开发平均成绩 from sc where course_id in( select course_id from course where course_name='数据库开发技术')15按职称查询教师的平均工资，并按总工资降序排列select profes

16、sion,avg(salary) as 平均工资,sum(salary) as 总工资 from teacher group by profession order by sum(salary) desc附录1：教务管理数据库jwgl结构student表结构列名称数据类型长度允许空值说明Student_idChar8否学生学号Student_nameVarchar8否学生姓名SexBit1否性别AgeInt4否年龄Class_id Char6否班级号Department_idChar6否系编号Addressvarchar50否家庭住址Course表字段名称数据类型长度允许空说明Course_i

17、dChar10否课程号Course_nameVarchar20否课程名称Book_idChar15否书标识SC表字段名称数据类型长度允许空说明Course_idChar10否课程号Student_idVarchar8否学生号gradeTinyint否成绩creditTinyint否学分Teacher表字段名称数据类型长度允许空说明Teacher_idChar9否教师编号Teacher_namenvarchar8否教师姓名Department_idChar6否所在系号ProfessionNvarchar16职称SexBit否性别phoneNvarchar15是电话adressNchar40是住址SalaryNumeric7.2是工资BirthSmalldatetime否出生日期PostalcodeNumeric6,0是邮编Book表字段名称数据类型长度允许空说明Book_idChar13否书号Book_nameVa