Slxner

Appearance

数据库复杂查询、索引和视图使用 ​

必要软件 ​

  • MySQL
  • Navicat Premium16（其它软件也可以，例如SQLyog）

如果不会安装MySQL，请查看我的MySQL安装篇

准备工作 ​

配置文件太大暂时无法上传

题目 ​

  1. 查询学生总人数。

  2. 查询选修了课程的学生人数。

  3. 计算2号课程的学生平均成绩。

  4. 查询选修3号课程的学生最高分数。

  5. 查询学生201615121选修课程的总学分数。

  6. 查询各个课程号及相应的选课人数。

  7. 查询各个系及相应的学生人数。

  8. 查询选修了2门以上课程的学生学号、课程数。

  9. 查询每个学生及其选修课程的情况。

  10. 查询每一门课的间接先修课（即先修课的先修课）。

  11. 查询选修2号课程且成绩在85分以上的所有学生。

  12. 查询每个学生的学号、姓名、选修的课程名及成绩。

  13. 使用连接查询，查询与“刘晨”在同一个系学习的学生。

  14. 使用嵌套查询，查询与“刘晨”在同一个系学习的学生。

  15. 使用连接查询，查询选修了课程名为“信息系统”的学生学号和姓名。

  16. 使用嵌套查询，查询选修了课程名为“信息系统”的学生学号和姓名。

  17. 使用连接查询，查询所有选修了1号课程的学生姓名。

  18. 使用EXISTS谓词的嵌套查询，查询所有选修了1号课程的学生姓名。

  19. 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

  20. 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

  21. 查询计算机科学系的学生及年龄不大于19岁的学生。

  22. 查询选修了课程1或者选修了课程2的学生。

  23. 查询计算机科学系的学生与年龄不大于19岁的学生的交集。

  24. 查询选修课程1的学生集合与选修课程2的学生集合的交集。

  25. 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

  26. 从student表中找出漏填了数据的学生信息。

  27. 选出选修1号课程的不及格的学生以及缺考的学生。

  28. 查询没有选修1号课程的学生姓名。

  29. 使用嵌套查询，找出每个学生超过他自己选修课程平均成绩的课程号。

  30. 使用基于派生表查询，找出每个学生超过他自己选修课程平均成绩的课程号。

  31. 基于student_new表，执行下列SQL语句，然后在姓名列创建一个索引，再次执行该SQL语句，比较查询效率。

    sql
    SELECT COUNT(*) FROM student_new
WHERE Sname LIKE '陈%';

  32. 基于student_new表，执行下列SQL语句，然后在性别列创建一个索引，再次执行该SQL语句，比较查询效率。

    sql
    SELECT Ssex, COUNT(*) FROM student_new
GROUP BY Ssex;

  33. 创建一个视图vw_sc，该视图定义了CS系各学生的全部个人信息、选修课程信息及成绩。

  34. 基于第（33）题创建的视图，查询CS系女生的个人及选课完整信息，查询结果以学号为序。

答案 ​

  1. sql
    SELECT COUNT(*) AS total_students
FROM (
    SELECT * FROM student
    UNION ALL
    SELECT * FROM student_new
) AS all_students;
  2. sql
    SELECT COUNT(DISTINCT Sno) AS student_count
FROM sc;
  3. sql
    SELECT AVG(Grade) AS ave_score
FROM sc
WHERE Cno = 2;
  4. sql
    SELECT MAX(Grade) AS max_score
FROM sc
WHERE Cno = 3;
  5. sql
    SELECT SUM(c.Ccredit) AS sum_credit
FROM sc s
JOIN course c ON s.Cno = c.Cno
WHERE Sno = '201615121';
  6. sql
    SELECT Cno, COUNT(DISTINCT Sno) AS num_students
FROM sc
GROUP BY Cno;
  7. sql
    SELECT Sdept, COUNT(DISTINCT Sno) AS total_students
FROM (
    SELECT Sno, Sdept FROM student
    UNION ALL
    SELECT Sno, Sdept FROM student_new
) AS all_students
GROUP BY Sdept;
  8. sql
    SELECT Sno, COUNT(DISTINCT Cno) AS course_count
FROM sc
GROUP BY Sno
HAVING COUNT(DISTINCT Cno) > 2;
  9. sql
    SELECT student_all.Sno, student_all.Sname, GROUP_CONCAT(sc.Cno ORDER BY sc.Cno) AS courses
FROM (
	SELECT Sno, Sname FROM student
	UNION ALL
	SELECT Sno, Sname FROM student_new
) AS student_all
LEFT JOIN sc ON student_all.Sno = sc.Sno
GROUP BY student_all.Sno, student_all.Sname;
  10. sql
    SELECT c1.Cno AS Cno, c1.Cname AS Cname,
       c2.Cno AS Cpno, c2.Cname AS Cpname,
       c3.Cno AS Cppno, c3.Cname AS Cppname
FROM course c1
LEFT JOIN course c2 ON c1.Cpno = c2.Cno  -- 直接先修课
LEFT JOIN course c3 ON c2.Cpno = c3.Cno;  -- 间接先修课
  11. sql
    SELECT Sno, Grade FROM sc
WHERE Cno = 2 AND Grade > 85;
  12. sql
    SELECT s.Sno, s.Sname, c.Cname, sc.Grade
FROM (
    SELECT * FROM student
    UNION ALL
    SELECT * FROM student_new
) AS s
LEFT JOIN sc ON s.Sno = sc.Sno
LEFT JOIN course c ON sc.Cno = c.Cno;
  13. sql
    SELECT s1.Sno, s1.Sname, s1.Sdept
FROM (
	SELECT Sno, Sname, Sdept FROM student
	UNION ALL
	SELECT Sno, Sname, Sdept FROM student_new
) as s1
JOIN (
    SELECT Sdept FROM student WHERE Sname = '刘晨'
) AS s2 ON s1.Sdept = s2.Sdept;
  14. sql
    SELECT s.Sno, s.Sname, s.Sdept
FROM (
	SELECT Sno, Sname, Sdept FROM student
	UNION ALL
	SELECT Sno, Sname, Sdept FROM student_new
) as s
WHERE Sdept = (
	SELECT Sdept
	FROM student
	WHERE Sname = '刘晨'
)
  15. sql
    SELECT s.Sno, s.Sname
FROM (
	SELECT Sno, Sname FROM student
	UNION ALL
	SELECT Sno, Sname FROM student_new
) as s
JOIN sc ON s.Sno = sc.Sno
JOIN course c ON sc.Cno = c.Cno
WHERE c.Cname = '信息系统'
  16. sql
    SELECT s.Sno, s.Sname
FROM (
	SELECT Sno, Sname FROM student
	UNION ALL
	SELECT Sno, Sname FROM student_new
) as s
WHERE s.Sno IN (
	SELECT sc.Sno
	FROM sc
	WHERE sc.Cno = (
		SELECT c.Cno
		FROM course c
		WHERE c.Cname = '信息系统'
	)
);
  17. sql
    SELECT s.Sname
FROM (
	SELECT Sno, Sname FROM student
	UNION ALL
	SELECT Sno, Sname FROM student_new
) as s
JOIN sc ON sc.Sno = s.Sno
WHERE sc.Cno = 1;
  18. sql
    SELECT s.Sname
FROM (
	SELECT Sno, Sname FROM student
	UNION ALL
	SELECT Sno, Sname FROM student_new
) as s
WHERE EXISTS (
    SELECT 1
    FROM sc
    WHERE sc.Sno = s.Sno
    AND sc.Cno = 1
);
  19. sql
    SELECT s.Sname, s.Sage
FROM (
	SELECT * FROM student
	UNION ALL
	SELECT * FROM student_new
) as s
WHERE s.Sdept != 'CS'
AND s.Sage < ANY (
	SELECT s1.Sage
	FROM student s1
	WHERE s1.Sdept = 'CS'
);
  20. sql
    SELECT s.Sname, s.Sage
FROM (
	SELECT * FROM student
	UNION ALL
	SELECT * FROM student_new
) as s
WHERE s.Sdept != 'CS'
AND s.Sage < (
	SELECT MIN(s1.Sage)
	FROM student s1
	WHERE s1.Sdept = 'CS'
);
  21. sql
    SELECT s.Sname, s.Sage
FROM (
	SELECT * FROM student
	UNION ALL
	SELECT * FROM student_new
) as s
WHERE s.Sdept = 'CS'
OR s.Sage < 20
  22. sql
    SELECT s.Sno, s.Sname, s.Sage, sc.Cno, sc.Grade
FROM (
	SELECT * FROM student
	UNION ALL
	SELECT * FROM student_new
) as s
JOIN sc ON sc.Sno = s.Sno
WHERE sc.Cno IN (1, 2);
  23. sql
    SELECT s.Sname, s.Sage
FROM (
	SELECT * FROM student
	UNION ALL
	SELECT * FROM student_new
) as s
WHERE s.Sdept = 'CS'
AND s.Sage < 20
  24. sql
    SELECT s.Sno, s.Sname, s.Sage, sc1.Cno AS Course1, sc2.Cno AS Course2, sc1.Grade AS Grade1, sc2.Grade AS Grade2
FROM (
    SELECT * FROM student
    UNION ALL
    SELECT * FROM student_new
) AS s
JOIN sc AS sc1 ON sc1.Sno = s.Sno AND sc1.Cno = 1
JOIN sc AS sc2 ON sc2.Sno = s.Sno AND sc2.Cno = 2
WHERE sc1.Cno = 1 AND sc2.Cno = 2;
  25. sql
    SELECT s.Sno, s.Sname, s.Sage
FROM (
    SELECT * FROM student
    UNION ALL
    SELECT * FROM student_new
) AS s
LEFT JOIN (
	SELECT Sno FROM student
	WHERE Sage <= 19
	UNION ALL
	SELECT Sno FROM student_new
	WHERE Sage <= 19
) AS age_group ON s.Sno = age_group.Sno
WHERE s.Sdept = 'CS' AND age_group.Sno IS NULL;
  26. sql
    SELECT *
FROM student
WHERE Sno IS NULL
	OR Sname IS NULL
	OR Sage IS NULL
	OR Sdept IS NULL;
  27. sql
    SELECT s.Sno, s.Sname, s.Sage, sc.Grade
FROM (
    SELECT * FROM student
    UNION ALL
    SELECT * FROM student_new
) AS s
JOIN sc ON sc.Sno = s.Sno
WHERE sc.Cno = 1 AND (sc.Grade < 60 OR sc.Grade IS NULL);
  28. sql
    SELECT s.Sno, s.Sname, s.Sage, sc.Grade
FROM (
    SELECT * FROM student
    UNION ALL
    SELECT * FROM student_new
) AS s
LEFT JOIN sc ON sc.Sno = s.Sno AND sc.Cno = 1
WHERE sc.Cno IS NULL
  29. sql
    SELECT s.Sname, sc.Grade, sc.Cno, 
       (SELECT AVG(sc1.Grade)
        FROM sc sc1
        WHERE sc1.Sno = s.Sno
        GROUP BY sc1.Sno) AS avg_score
FROM student s, sc
WHERE s.Sno = sc.Sno
  AND sc.Grade > (
      SELECT AVG(sc1.Grade)
      FROM sc sc1
      WHERE sc1.Sno = s.Sno
      GROUP BY sc1.Sno
  );
  30. sql
    SELECT s.Sname, sc.Cno, sc.Grade, avg_score_table.avg_score
FROM student s
JOIN sc sc ON s.Sno = sc.Sno
JOIN (
    SELECT sc1.Sno, AVG(sc1.Grade) AS avg_score
    FROM sc sc1
    GROUP BY sc1.Sno
) AS avg_score_table ON s.Sno = avg_score_table.Sno
WHERE sc.Grade > avg_score_table.avg_score;
  31. sql
    SELECT COUNT(*) FROM student_new
WHERE Sname LIKE '陈%';
CREATE INDEX idx_sname ON student_new(Sname);
  32. sql
    SELECT Ssex, COUNT(*) FROM student_new
GROUP BY Ssex;
CREATE INDEX idx_ssex ON student_new(Sname);
  33. sql
    CREATE VIEW vw_sc AS
SELECT s.Sno, s.Sname, s.Sage, s.Ssex, s.Sdept, 
       sc.Cno, sc.Grade
FROM (
	SELECT * FROM student
	UNION ALL
	SELECT * FROM student_new
) AS s
LEFT JOIN sc ON s.Sno = sc.Sno
WHERE s.Sdept = 'CS';
  34. sql
    SELECT * FROM vw_sc
WHERE Sdept = 'CS' AND Ssex = '女'
ORDER BY Sno;