1.查询学生表的所有记录

select * from students;

2.查询students表中的所有记录的sname、ssex和class列

select sname,ssex,class from students;

3.查询teacher表所有的单位即不重复的depart列

select distinct depat from teacher;

4.查询scores表中成绩在60到80之间的所有记录

select * from scores where degree between 60 and 80;

5.查询scores表中成绩为85，86或88的记录

select * from scores where degree in (85,86,88);

6.查询students表中“95031”班或性别为“女”的同学记录

select * from students where class = ‘95031’ or ssex = ‘女’;

7.以班级class降序查询students表的所有记录

select * from students order by class desc;

8.以cno升序、degree降序查询scores表的所有记录

select * from scores order by cno,degree desc;

9.查询“95031”班的学生人数

select count(*) from students where class = ‘95031’;

10. 查询每个班的学生人数

select class,count(class) from students group by class;

11.查询scores表中的最高分的学生学号和课程号

select sno,sno from scores order by degree limit 1;

12.查询‘3-105’号课程的平均分

select avg(degree) from scores where cno = ‘3-105’;

13.查询最高分

select max(degree) from scores;

14.查询最低分

select min(degree) from scores;

15.查询最低分大于70，最高分小于90的sno列

select sno from scores group by sno having min(degree)>70 and max(degree)<90;

16.查询95033班和95031班全体学生的记录

select * from students where class in (95033,95031) order by class;

17.查询存在有85分以上成绩的课程cno

select distinct cno from scores where degree > 85;

18.查询所有教师和同学的name、sex和birthday

select teachers.tname,teachers.tsex,teachers.tbirthday from teachers union select students.sname,students.ssex,students.sbirthday from students;

19.查询所有“女”教师和“女”同学的name、sex和birthday

SELECT sname,ssex,sbirthday FROM students WHERE ssex=’女’
UNION SELECT tname,tsex,tbirthday FROM teachers WHERE tsex=’女’;

20.查询所有任课教师的tname和depart

SELECT tname,depart FROM teachers WHERE tno IN( SELECT tno FROM courses);

21.查询所有未讲课的教师的tname和depart

SELECT tname,depart FROM teachers WHERE tno not IN( SELECT tno FROM courses);

22.查询至少有2名男生的班号

SELECT class,COUNT(1) AS boycount FROM students WHERE ssex=’男’ GROUP BY class HAVING boycount>=2;

23.查询students表中姓“王”的同学记录

select * from students where sname like “王%”;

24.查询students表中不姓“王”的同学记录

select * from students where sname not like “王%”;

25.查询students表同名的同学记录

select * from students where sname in (select sname from students group by sname having count(sname)>1);

26.查询students表中每个学生的姓名和年龄

select sname,year(now())-year(sbirthday) as age from students;

27.查询students表中最大和最小的sbirthday日期值

select max(**sbirthday),min(sbirthday) from students;**

28.以班号和年龄从大到小的顺序查询student表中的全部记录

SELECT * FROM students ORDER BY class DESC,sbirthday ASC;

29.查询“男”教师及其所上的课程

SELECT teachers.tname,courses.cname FROM teachers INNER JOIN courses ON(teachers.tno=courses.tno) WHERE teachers.tsex=’男’;

30.查询最高分同学的sno、cno和degree列

SELECT * FROM scores GROUP BY cno HAVING degree=MAX(degree);

31.查询课程对应的老师姓名、职称、所属系

SELECT t.tname,t.prof,t.depart FROM (SELECT FROM teachers) t LEFT JOIN (SELECT FROM courses) c ON c.tno=t.tno;

效果一致：
SELECT teachers.tname,teachers.prof,teachers.depart FROM teachers LEFT JOIN courses ON teachers.tno=courses.tno;**
**