T1：

查询工资最高的前两名教师的信息。

Select Top 2 * -----Top <number> 取前2个

From Teacher

Order By Tincome Desc; -----Asc降序 Desc升序

为teacher表的tname列创建一个唯一索引。

Create Unique Index Idx_Name -----不要忘了索引名

On Teacher (Tname);

T2：

查询各系学生人数及对应系教师的平均收入（要求显示无教师的系）

SELECT s.sdept,

COUNT(DISTINCT s.sno) AS student_count,

AVG(t.Tincome) AS avg_income

FROM student s

LEFT JOIN teacher t ON s.sdept = t.sdept

GROUP BY s.sdept

ORDER BY s.sdept ,avg_income DESC;

T3：

查询各职称教师数量与对应指导学生人数的比例

SELECT Title,

COUNT(DISTINCT t.Tno) AS teacher_count,

COUNT(DISTINCT s.sno) AS student_count,

ROUND(COUNT(DISTINCT s.sno) / COUNT(DISTINCT t.Tno),2) AS ratio

FROM teacher t

LEFT JOIN student s ON t.sdept = s.sdept

GROUP BY t.Title

HAVING COUNT(DISTINCT s.sno) > 0

ORDER BY ratio DESC;

T4：

查询年龄在【30，40】岁的老师职称情况，去除重复项

SELECT distinct ttitle -----distinct放在select后

FROM Teacher

WHERE tage BETWEEN 30 AND 40;

-----包括边界值可以用BETWEEN

T5：

查询选修了课程2并且最低成绩大于等于80分的学生的姓名

select sname

from student,Course,sc

where Student.Sno = SC.Sno AND SC.Cno = Course.Cno and course.cno='2'

group by sname

having min(sc.grade)>80;

-----MySQL单表查询语句的执行顺序为： from...where...group by...having...select...order by...

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据 (desc为降序，asc为升序)

T6：

查询比所有女教师收入高的男教师信息

SELECT *

FROM teacher

WHERE tsex = '男'

AND tincome > ALL (

-----ALL是一个集合，>ALL则比每个元素都大

SELECT tincome

FROM teacher

WHERE tsex = '女'

);

T7：

查询平均成绩高于85分的学生及其导师（若存在）

SELECT s.sname, t.tname

FROM student s

LEFT JOIN teacher t ON s.sdept = t.sdept

WHERE s.sno IN (

SELECT sno

FROM sc

GROUP BY sno

HAVING AVG(grade) > 85

);

T8：

创建学生成绩视图（含学号、姓名、课程名、成绩）

CREATE VIEW v_StudentGrade AS

SELECT s.sno, s.sname, c.cname, sc.grade

FROM student s

JOIN sc ON s.sno = sc.sno

JOIN course c ON sc.cno = c.cno;

附录