17.6 T-SQL高级查询.doc

17.6 T-SQL高级查询一、目的1.掌握SELECT语句在单表查询中的应用（分组、统计函数等）2.掌握基于多表的连接查询3.掌握嵌套子查询二、内容1. 上机验证第章所有实例2上机完成第章有关练习3利用SQL Server Management Studio把JWGL数据库还原，然后进行下面的操作。17.6 T-SQL高级查询一、目的1.掌握SELECT语句在单表查询中的应用（分组、统计函数等）2.掌握基于多表的连接查询3.掌握嵌套子查询二、内容1. 上机验证第章所有实例2上机完成第章有关练习3利用SQL Server Management Studio把JWGL数据库还原，然后进行下面的操作。1)查询数据库中班的平均成绩。select avg(grade) from result where student_id in(select student_id from student where class_id in (select class_id from class where class_id =200302)2)查询程序设计这门课的最高分、最底分及其之间的分差。select max(grade),min(grade), max(grade)-min(grade) from result where course_id in(select course_id from course where name=程序设计)3)查询总分大于分的学生的学号及总成绩。select student_id,sum(grade) from result group by student_id having sum(grade)3504)统计学生来自多少个城市。select count(distinct left(home_addr,3) from student5)查询每个教师的教师号及其任课门数。select teacher_id, count(distinct course_id) from teaching group by teacher_id6)查询选修三门以上课程的学生学号和选课门数。 Select student_id,count(distinct course_id) from RESULT group by STUDENT_id7)统计男、女学生各自的总人数和平均年龄。Select sex,count(student_id),AVG(year(getdate()-(year(birthday)From student group by sex8)查询刘老师所讲授的课程名称。Select from course where course_id in(Select course_id from teaching where teacher_id in(Select teacher_id from teacher where like 刘%)9)查询比杨勇年龄大的学生基本信息。Select * from student where birthday(select birthday from student where name=杨勇)10)查询所有选课学生的学号、姓名、选课名称及成绩。Select s.student_id,,,r.grade from student s,course c,result r where s.student_id=r. student_id andc.course_id=r. course_id11)查询成绩有不及格的学生的学号和姓名。Select s.student_id,,,r.grade from student s,course c,result r where s.student_id=r. student_id andc.course_id=r. course_idand r.grade70 and =数据库14)用COMPUTE BY子句按学号汇总出result表中每个学生的学号及各门课的平均成绩和总成绩。Select student_id,course_id, grade from result order BY student_idCompute avg(grade),sum(grade)BY student_id15)查询程序设计课程分数最高的学生的基本信息。Select * from student where student_id=( Select student_id from result Where grade= (Select max(grade) from result where course_id=(select course_id from course where name=程序设计)16) 查找不同课程但成绩相同的学生数、课程数和成绩。select count(distinct student_id) 学生数,count(distinct course_id) 课程数,grade 成绩from result group by grade17)查询姓张的学生的学号及其所选修的课程名称和所修成绩。Select ,s.student_id,,r.grade from student s,course c,result r where s.student_id=r. student_id andc.course_id=r. course_idand like 张%18)统计选修了微机原理的学生名单。Select name from student where student_id in (select student_id from result where course_id=(select course_id from course where name=微机原理)19) 查询没有成绩的学生姓名和对应的课程名称。Select , from student s,course c,result r where r.grade is null and c.course_id=r. course_id and s.student_id=r.student_id20) 查询其他系中比信息系任一教师工资高的教师的姓名和工资。Select name,salary from teacher where salary(select max(salary) from teacher where department_id=(select department_id from department where department=信息系)21)查询不讲授课程号为C01的教师姓名。Select name from teacher where teacher_id in(select distinct teacher_id from teaching where course_id!=C01)22) 把所有教师的工资提高到平均工资的.2倍。Declare a as moneySelect a=avg(salary) from teacherUpdate teacher Set sa