-- 检索计算机系的学生
SELECT * 
FROM student
WHERE deptNo = (SELECT deptNo FROM department WHERE dept_name = "计算机科学与技术");

-- 检索年龄大于20岁的女学生的学号和姓名
SELECT sno, sname, age
FROM student
WHERE gender = "F" AND age > 20;

-- 检索姓名以王字打头的所有学生的姓名和年龄
SELECT sname, age
FROM student
WHERE sname LIKE "王%";

-- 输出C001课程的成绩单，要求成绩按从高到低排序
SELECT sno, grade
FROM sc
WHERE cno = "C001"
ORDER BY grade DESC;

-- 每个学生选修的课程门数
SELECT sno, COUNT(*) AS course_count
FROM sc
GROUP BY sno;

-- 检索至少选修两门课程的学生学号
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(*) > 2;

-- 求每个学生平均成绩
SELECT sno, AVG(grade) AS avg_grade
FROM sc
GROUP BY sno;

-- 统计每门课程的学生选修人数。查询结果按人数降序排列，若人数相同，按课程号生序排列
SELECT cno, COUNT(sno) AS stu_num
FROM sc
GROUP BY cno
ORDER BY stu_num DESC, cno ASC;