MySQL 练习三

1. select sname,sex,class from student;
2. select distinct depart from teacher;
3. select * from student;
4. select * from score where degree between 60 and 80;
5. select * from score where degree in(85,86,88);
6. select * from student where class=’95031’ or sex=’女’;
7. select * from student order by class desc;
8. select * from score order by cno asc,degree desc;
9. select count(*) from student where class=98031;
10. select sno,degree from score where degree =(select max(degree) from score);
11. select avg(degree) from score where cno = '3-105';
12. select cno,avg(degree) from score where cno like ‘3%’ group by cno having count(*) > 5;
13. select cno,max(degree) a ,min(degree) b from score group by cno having a<90 and b> 70;
14. select s1.cno,s1.degree,s2.sname from score s1 join student s2 on s1.sno = s2.sno;
15. select s1.sname ,s3.cname,s2.degree from score s2 join student s1 on s1.sno=s2.sno join course s3 on s2.cno = s3.cno;;
16. select avg(degree) from score s1 join (select sno from student where class=95033) s2 on s1.sno = s2.sno group by cno;
17. select * from score where cno = '3-105' and degree > (select degree from score where sno = 109 and cno = '3-105');
18. select sno from score group by sno having count(*) > 1 and sno not in(select s1.sno from score s1 join(select cno,max(degree) a from score group by cno) s2 on s1.cno = s2.cno and s1.degree = s2.a)
19. select * from score where cno = '3-105' and degree > (select degree from score where sno = 109 and cno = '3-105');
20. select sno,sname,birthday from student where sno != 108 and year(birthday) = (select year(birthday) from student where sno = 108);
21. select degree from score where cno in( select cno from course where tno in (select tno from teacher where tname = '张旭'))
22. select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*) > 5));
23. select * from student where class in (95033,95031);
24. select distinct cno from score where degree > 85;
25. select sno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart = ‘计算机系’));
26. select t1.tname,t1.prof from teacher t1,teacher t2 where t1.prof != t2.prof and t1.tno != t2.tno and t1.depart = t2.depart;
27. select s1.cno,s1.sno,s1.degree from (select * from score where cno = '3-105') s1 join (select * from score where cno = '3-245') s2 on s1.sno = s2.sno and s1.degree > s2.degree order by degree desc；
28. select s1.cno,s1.sno,s1.degree from (select * from score where cno = '3-105') s1 join (select * from score where cno = '3-245') s2 on s1.sno = s2.sno and s1.degree > s2.degree;
29. select tname,depart from teacher where tno in (select tno from course);
30. select tname,depart from teacher where tno not in (select tno from course);
31. select sname,sex,birthday from student;select tname,sex,birthday from teacher;
32. select s2.sno from (select * from score where sno = 103) s1 join score s2 on s1.cno = s2.cno and s1.sno != s2.sno group by s2.sno having count(*) = (select count(*) from score where sno = 103)
33. select sname from student where sno in (select sno from score group by sno having count(*) = (select count(*) from course ));