第三章关系数据库语言1.ppt

* * 1、Select sex,avg(grade) as avg_g from Student group by sex 2、select sno,name from student where grade60 and sex=‘男’ order by grade desc (或 order by -grade) 3、select b.sno,b.name from student a,student b where b.grade=60 and b.gradea.grade and a.sno=‘005’ 或 select * from eg1_student where grade=60 and grade(select grade from eg1_student where sno=005) * Student/Course/Grade * 1、select b.cname,max(a.grade) max_grade from eg2_grade a,eg2_course b where a.cno=b.cno group by b.cname 2、select b.cname,b.dept,count(a.sno) cnt from eg2_grade a,eg2_course b where a.cno=b.cno group by b.cname,b.dept having count(a.sno)=2 order by cnt 3、select sno,sname,year(getdate())-year(sbirth) age from eg2_student where datediff(year,sbirth,getdate())24 * 1、select ename,pay+allowance+prize-deduction salary from eg3_employee a,eg3_wage b where a.eno=b.eno 2、select dname,min(ebirth) birth from eg3_department a,eg3_employee b where a.dno=b.dno group by dname 3、select dname,count(eno) cnt from eg3_department a,eg3_employee b where a.dno=b.dno group by dname having count(eno)=2 order by cnt desc D A(可作图分析) CHECK C 例子： create table tt ( f1 int check(f12) ) B SET AGE=AGE+1 用两个成绩表分别与学生信息表合并，作为中间表，然后再把中间表合并得到最终结果 Create view t1 as SELECT a.stu_id,name,score as mathscore FROM eg4_student a left JOIN eg4_math b ON a.stu_id=b.stu_id Create view t2 as SELECT a.stu_id,name,score as englishscore FROM eg4_student a left JOIN eg4_english b ON a.stu_id=b.stu_id select t1.*,t2.englishscore from t1 join t2 on t1.stu_id=t2.stu_id （思路同“中间表”法） select t1.*,t2.englishscore from (SELECT a.stu_id,name,score as mathscore FROM eg4_student a left JOIN eg4_math b ON a.stu_id=b.stu_id ) t1 join (SELECT a.stu_id,name,score as englishscore FROM eg4_student a left JOIN eg4_english b ON a.stu_id=b.stu_id ) t2 on t1.stu_id=t2.stu_id （以最完整的表为基准，进行两次左连接。） select a.stu_id,name,b.score englishscore,c.score mathscore from eg4_student a left join eg4_math b on a.stu_id=b.stu_id left join eg4_english c on