Sql语句综合练习课件.ppt

SQL语句综合练习;;select count(distinct Sno) from SCourse; ;select Sno,avg(score) from SCourse group by Sno having avg(score) 60;   ;select Sno, count(Cno),sum(score) from SCourse group by Sno;  ;select count(ssex) 男生人数 from St group by ssex having ssex= 男;select count(ssex) 女生人数 from St group by ssex having ssex= 女;;select Sname,count(*) from St group by Sname having count(*)1;;select Cno,avg(score) from SCourse group by Cno order by avg(score),Cno desc; ;select Sno,avg(score) from SCourse where Sno in (select Sno from SCourse where score 60 group by Sno having count(*)2) group by Sno; ;select Sno,avg(score) from SCourse where score 60 group by Sno having count(*)2;;select count (Tname) from Teacher where Tname like 李%;   ; select distinct A.Sno,A.Cno, A.score from SCourse A ,SCourse B where A.Score=B.Score and A.Cno!=B.Cno and A.Sno=B.Sno;  ;select St.Sname,score from St,SCourse,C,Teacher where St.Sno=SCourse.Sno and scourse.cno=c.cno and C.Tno=Teacher.Tno and Teacher.Tname=张山 and score= (select max(score) from SCourse,C,Teacher where scourse.cno=c.cno and C.Tno=Teacher.Tno and Teacher.Tname=张山);;select St.Sname,score from St,SCourse,C,Teacher where St.Sno=SCourse.Sno and scourse.cno=c.cno and C.Tno=Teacher.Tno and Teacher.Tname=张山 and score= (select max(score) from SCourse where cno in (select cno from C where Tno in (select Tno from Teacher where Tname=张山)));;select St.Sname,max(score) from St,SCourse,C,Teacher where St.Sno=SCourse.Sno and scourse.cno=c.cno and C.Tno=Teacher.Tno and Teacher.Tname=张山 ;;St (Sno,Sname,Sage,Ssex) 学生表 C (Cno,Cname,Tno) 课程表 SCourse(Sno,Cno,score) 成绩表 Teacher(Tno,Tname) 教师表 ;St (Sno,Sname,Sage,Ssex) 学生表 C (Cno,Cname,Tno) 课程表 SCourse(Sno,Cno,score) 成绩表 Teacher(Tno,Tname) 教师表 ;select Sno from SCourse group by Sno having count(Cno)(select count(Cno) from C);;update SCourse set score=(select avg(score) from SCourse where cno in (select cno from c where tno in (select tno from teacher where