SCAU数据库实验2习题

1. 查询所有计算机系（Comp. Sci.）开出的、3个学分的课程名。

SELECT

  title AS taget

FROM

  university.course

WHERE

  dept_name = 'Comp. Sci.'

  AND title IN ( SELECT title FROM university.course WHERE credits = 3 );

2. 查询ID为12345的学生所选修的所有课程的课程号与课程名。

SELECT title,course_id

FROM university.course NATURAL JOIN university.takes

WHERE ID = '12345';

3. 使用聚集函数查询ID为12345的学生所选修的所获得的总学分

SELECT SUM(credits)

FROM university.course

WHERE course_id IN(SELECT course_id FROM university.takes WHERE ID = '12345');

4. 统计每位学生选修的总学分数，显示学生姓名和总学分。对没选修课程的学生，总学分对应位置显示0。

SELECT

  s.NAME,

  COALESCE( SUM( credits ), 0 ) AS total_credits

FROM

  university.student s

  LEFT JOIN ( university.takes LEFT JOIN university.course ON takes.course_id = course.course_id ) ON s.ID = takes.ID

GROUP BY

  s.ID;

5. 查询每位选修过计算机系（Comp. Sci.）开出某一门课程的学生的姓名，注意去掉重复的学生姓名。

SELECT DISTINCT

  `name`

FROM

  university.student

  LEFT JOIN university.takes ON student.ID = takes.ID

WHERE

  course_id IN ( SELECT course_id FROM university.course WHERE dept_name = 'Comp. Sci.' );

6. 显示未上过课（未在teaches表注册讲授课程）的教师的ID和姓名。

SELECT ID , `name`

FROM university.instructor

WHERE ID NOT IN(SELECT ID FROM university.teaches)

7. 查询课程选修人数的最大和最小值，及人数最多和最少的课程的信息（course_id, sec_id, semester, year）。没有学生选修的section不用显示。

(SELECT course_id AS max_course_id, COUNT(*) AS choice_num ,semester,`year`,sec_id

    FROM takes

    GROUP BY course_id,semester,`year`,sec_id

    ORDER BY choice_num DESC

    LIMIT 1

   )

   

UNION ALL

(SELECT course_id AS min_course_id, COUNT(*) AS choice_num ,semester,`year`,sec_id

    FROM takes 

    GROUP BY course_id,semester,`year`,sec_id

    ORDER BY choice_num ASC

    LIMIT 1

    );

8. 查询课程选修人数的最大和最小值，及人数最多和最少的课程的信息（course_id, sec_id, semester, year），要求没有学生选修的section对应人数显示为0。

( SELECT

  course_id,

  semester,

  `year`,

  sec_id,

  choice_num

  FROM

    (

      SELECT

        course_id,

        choice_num,

        semester,

        `year`,

        sec_id

      FROM

        course

        LEFT JOIN (

          SELECT

            course_id AS max_course_id,

            COALESCE( COUNT( course_id ), 0 ) AS choice_num,

            semester,

            `year`,

            sec_id

          FROM

            takes

          GROUP BY

            course_id,

            semester,

            `year`,

            sec_id

        ) AS max_table ON course.course_id = max_table.max_course_id

    ) AS res1

  ORDER BY

    choice_num DESC

    LIMIT 1

) UNION ALL

(

  SELECT

    course_id,

    semester,

    `year`,

    sec_id,

    COALESCE( choice_num, 0 ) AS choice_num

  FROM

    (

      SELECT

        course_id,

        semester,

        `year`,

        sec_id,

        choice_num

      FROM

        course

        LEFT JOIN (

          SELECT

            course_id AS min_course_id,

            COALESCE( COUNT( course_id ), 0 ) AS choice_num,

            semester,

            `year`,

            sec_id

          FROM

            takes

          GROUP BY

            course_id,

            semester,

            `year`,

            sec_id

        ) AS min_table ON course.course_id = min_table.min_course_id

    ) AS res2

  ORDER BY

    choice_num ASC

    LIMIT 1

);

9. 查询所有名称以“CS-1”开头的课程的ID号。

SELECT course_id

FROM university.course

WHERE course_id LIKE 'CS-1%';

10. 查询所有讲授了所有名称以“CS-1”开头的课程的教师的ID号和姓名（用两种方法（1）使用NOT EXISTS EXCEPT或类似结构的SQL语句，（2）统计教师讲授的以“CS-1”开头的课程门数及系统中以“CS-1”开头的课程门数，判断二者是否相等。）

（1）方法1：

SELECT i.ID ,i.`name`

FROM instructor i

WHERE NOT EXISTS(

  SELECT a.course_id

  FROM course a

  WHERE NOT EXISTS(

  SELECT e.course_id

  FROM course e JOIN teaches t ON e.course_id = t.course_id

  WHERE e.course_id LIKE 'CS-1%' AND i.ID = t.ID

  )

) ;

         方法2：

SELECT i.ID ,i.`name`

FROM instructor i JOIN teaches t ON i.ID = t.ID

WHERE t.course_id LIKE 'CS-1%';

（2）SELECT SUM(num1)

FROM(

  SELECT COUNT(course_id) AS num1

  FROM instructor i JOIN teaches t ON i.ID = t.ID

  WHERE course_id LIKE 'CS-1%'

  GROUP BY course_id

)AS sumulation1

GROUP BY num1;

SELECT SUM(num2)

FROM(

  SELECT COUNT(course_id) AS num2

  FROM course

  WHERE course_id LIKE 'CS-1%'

  GROUP BY course_id

)AS sumulation2

GROUP BY num2;

可见两个结果不相同，原因是第一个查询中不同的教授也教授了同样的课程，这一部分并没有去除，去除掉这一部分后结果就相同了。

思考题及完成情况：

1. 分组查询中的WHERE和HAVING有什么区别？ 请举例说明。

WHERE用于过滤行数据，它在聚合函数执行之前应用，不能使用聚合函数；HAVING用于过滤聚合结果，它在聚合函数执行之后应用，可以使用聚合函数。

举例：统计takes中被选次数大于1次的课程的id

使用having：

SELECT COUNT(*) AS tol_num,course_id

FROM takes

GROUP BY course_id

HAVING tol_num > 1;

使用where：

SELECT tol_num,course_id

FROM (

      SELECT COUNT(*) AS tol_num,course_id

      FROM takes

      GROUP BY course_id

      ) AS tab1

WHERE tol_num > 1

二者结果相同但是写法不同。

2. 不在GROUP BY子句中出现的属性，是否可以出现在SELECT子句中？请举例说明。

不可以，如果我们定义在GROUP BY子句中的属性不在SELECT子句中，那么数据库将不知道如何处理这部分数据，会报错。

3. 在GROUP BY子句中出现的属性，是否可以不出现在SELECT子句中？请举例说明。

可以