数据库1数据查询

1、实验一 数据库查询课程名称：数据库原理实验实验类型：验证型实验名称数据库查询学时4学时实验目的：使学生掌握sql server query analyzer的使用方法，加深对sql和t-sql语言的查询语句的理解。熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。实验原理：select all|distinct ， from ， where group by having order by asc|desc；实验方法：将查询需求用t-sql语言表示；在sql server query analyzer的输入区中输入t-sql查询语句；设置 query analyz

2、er的结果区为standard execute（标准执行）或execute to grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验内容：1. 分别用带distinct和不带distinct关键字的selelct在student中进行查询.带distinct:select class_id from student不带distinct:select distinct class_id from student2. 将teacher表中各教师的姓名、教工号及工资按95发放的信息，并将工资按95发放后的列名改为预发工资select te

3、acher_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=course.course_id and course.credit2 and sc.grade80in：select * from student where student_id in( select stude

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

5、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 * from course where course_id=sc.course_id and course_name=计算机基础)11.

6、 查询选修了课程名为“数据库基础”的学生学号和姓名（分别用连接，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_idin：select student_id,student_name from student where student_id in( select stude

7、nt_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. 查询所有计算机系学生

8、的学号、选修课程号以及分数（分别用连接，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_idin：select* from sc where student_id in( select student_id from student where department_id=( select

9、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 s

10、tudent.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)6014查询“数据库开发技术”的平均成绩select avg

11、(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_idchar8

12、否学生学号student_namevarchar8否学生姓名sexbit1否性别ageint4否年龄class_id char6否班级号department_idchar6否系编号addressvarchar50否家庭住址course表字段名称数据类型长度允许空说明course_idchar10否课程号course_namevarchar20否课程名称book_idchar15否书标识sc表字段名称数据类型长度允许空说明course_idchar10否课程号student_idvarchar8否学生号gradetinyint否成绩credittinyint否学分teacher表字段名称数据类型

13、长度允许空说明teacher_idchar9否教师编号teacher_namenvarchar8否教师姓名department_idchar6否所在系号professionnvarchar16职称sexbit否性别phonenvarchar15是电话adressnchar40是住址salarynumeric7.2是工资birthsmalldatetime否出生日期postalcodenumeric6,0是邮编book表字段名称数据类型长度允许空说明book_idchar13否书号book_namevarchar30否书名publish_companyvarchar50否出版社authornvarchar8是作者pricenumeric