数据库复习
博客专区 > E-C-Ares 的博客 > 博客详情
数据库复习
E-C-Ares 发表于3个月前
数据库复习
  • 发表于 3个月前
  • 阅读 8
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

摘要: 天大保研

数据库基本结构及内容

 

TABLE:

Students(sid, sname, age, sex, gpa) 学生表

Courses(cid, cname, time, rid, tid) 课程表

Points(sid, cid, score) 分数表

Teachers(tid, tname) 教师表

 

 

ddd

题目

  1. 查询 cid 为 6 的课程比 cid 为 7 的课程成绩高的所有学生的 sid;
  2. 查询平均成绩不低于60分的学生的 sid 和平均成绩;
  3. 查询所有同学的学号、姓名、选课数、总成绩;
  4. 查询姓“李”的老师的个数;
  5. 查询没学过'陈奕迅'老师课的同学的学号、姓名;
  6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
  7. 查询学过“叶平”老师所教的所有课的同学的学号、姓名;
  8. 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学学号姓名
  9. 查询所有课程成绩小于60分的同学的学号、姓名
  10. 查询没有学全所有课的同学的学号、姓名
  11. 查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名
  12. 查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名
  13. 把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
  14. 查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名
  15. 删除学习'陈奕迅'老师课的SC表记录
  16. 向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2号课的平均成绩
  17. 按平均成绩从高到低显示所有学生的“语文1”、“生物2”、“化学1”三门的课程成绩,按如下形式显示
  18. 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
  19. 按各科平均成绩从低到高和及格率的百分数从高到低顺序
  20. 查询如下课程平均成绩和及格率的百分数(用"1行"显示)
  21. 查询不同老师所教不同课程平均分从高到低显示
  22. 查询如下课程成绩第 3 名到第 6 名的学生成绩单:语文1(111),语文2(112),数学1 (113),数学2(114)
  23. 统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85] 优秀人数,[85-70] 良好人数,[70-60] 一般人数,[ <60] 刚及格人数
  24. 查询学生平均成绩及其名次
  25. 查询各科成绩前三名的记录:(不考虑成绩并列情况)
  26. 查询每门课程被选修的学生数
  27. 查询出只选修了一门课程的全部学生的学号和姓名
  28. 查询男生、女生人数
  29. 查询名字中有'黑'的学生名单
  30. 查询同名同性学生名单,并统计同名人数
  31. 查询王漢超gpa90分以上科目比70分以下科目多出来
  32. 查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
  33. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
  34. 查询课程名称为'语文1',且分数低于60的学生姓名和分数
  35. 查询所有学生的选课情况
  36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
  37. 查询不及格的课程,并按课程号从大到小排列
  38. 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
  39. 求选了课程的学生人数
  40. 查询选修“LiGang”老师所授课程的学生中,成绩最高的学生姓名及其成绩
  41. 查询各个课程及相应的选修人数
  42. 查询不同课程成绩相同的学生的学号、课程号、学生成绩
  43. 查询每门功成绩最好的前两名
  44. 统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列
  45. 检索至少选修两门课程的学生学号
  46. 查询全部学生都选修的课程的课程号和课程名
  47. 查询没学过'陈奕迅'老师讲授的任一门课程的学生姓名
  48. 查询两门以上不及格课程的同学的学号及其平均成绩
  49. 检索'114'课程分数小于60,按分数降序排列的同学学号
  50. 删除同学的'111'课程的成绩

 

解答

1. 查询 cid 为 6 的课程比 cid 为 7 的课程成绩高的所有学生的 sid

-- 查询 cid 为 6 的课程比 cid 为 7 的课程成绩高的所有学生的 sid;

/*using EXISTS*/
SELECT p1.sid
FROM Points p1
WHERE p1.cid = 6 AND EXISTS(
  SELECT 1
  FROM Points p2
  WHERE p2.cid = 7 AND p1.sid = p2.sid AND p1.score > p2.score
);

/*using 子查询*/
SELECT p1.sid
FROM (
  SELECT Points.sid, Points.score FROM Points WHERE Points.cid = 6) p1
INNER JOIN (
  SELECT Points.sid, Points.score FROM Points WHERE Points.cid = 7) p2
ON p1.sid = p2.sid
WHERE p1.score > p2.score;

 

2. 查询平均成绩不低于60分的学生的 sid 和平均成绩

-- 查询平均成绩不低于60分的学生的 sid 和平均成绩;

SELECT Points.sid, AVG(Points.score)
FROM Points
GROUP BY Points.sid
HAVING AVG(Points.score) >= 60

 

3. 查询所有同学的 sid, sname, 选课数, 总成绩

-- 查询所有同学的 sid, sname, 选课数, 总成绩

/* group by 的时候要注意选出来的字段是不是都是聚合函数或者分组的字段 */

SELECT Students.sid,
       MIN(Students.sname) AS 'sname',
       COUNT(Points.c#)  AS '选课数',
       SUM(Points.score) AS '总成绩'
FROM Students
LEFT JOIN Points ON Students.sid = Points.sid
GROUP BY Students.sid

 

4. 查询 tname 以 'Li' 开头的个数

--查询 tname 以 'Li' 开头的个数;

SELECT COUNT(Teachers.tname)
FROM Teachers
WHERE Teachers.tname LIKE 'Li%'

 

5.查询没学过tname 为 'ChenEason'的老师课的学生的 sid, sname;

-- 查询没学过tname 为 'ChenEason'的老师课的学生的 sid, sname;
/* 一对多关系的就会比较坑爹,记得用distinct并且要考虑是不是要做一个子查询 */
/* 当正面来可能比较困难的时候就要反方面来进行 */
/* EXISTS版 */

SELECT t5.s# AS ST_CODE,
       t5.sname AS ST_NAME
FROM student t5
WHERE NOT EXISTS (
                   SELECT 1
                   FROM (
                          SELECT DISTINCT t1.s# AS ST_CODE,
                                          t1.sname AS ST_NAME,
                                          t4.tname
                          FROM student t1
                          LEFT JOIN sc t2
                            ON t1.s# = t1.s#
                          LEFT JOIN course t3
                            ON t2.c# = t3.c#
                          LEFT JOIN teacher t4
                            ON t3.t# = t4.t#
                          WHERE t4.tname = '陈奕迅'
                         )
                   WHERE s# = t5.s#
                 );
/* 子查询版 */
SELECT t5.s# AS ST_CODE,
       t5.sname AS ST_NAME
FROM student t5
WHERE t5. EXISTS (
                   SELECT 1
                   FROM (
                          SELECT DISTINCT t1.s# AS ST_CODE,
                                          t1.sname AS ST_NAME,
                                          t4.tname
                          FROM student t1
                          LEFT JOIN sc t2
                            ON t1.s# = t1.s#
                          LEFT JOIN course t3
                            ON t2.c# = t3.c#
                          LEFT JOIN teacher t4
                            ON t3.t# = t4.t#
                          WHERE t4.tname = 'ChenEason'
                         )
                   WHERE s# = t5.s#
                 );

 

 

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
--不能在where条件下面写WHERE t2.c# = 111 and t2.c# = 112 因为是对应同一条记录的
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
LEFT JOIN sc t2
  ON t1.s# = t2.s#
WHERE t2.c# = 111

INTERSECT  --集合的交集

SELECT DISTINCT t1.s#,t1.sname
FROM student t1
LEFT JOIN sc t2
  ON t1.s# = t2.s#
WHERE t2.c# = 112

 

 

--7、查询学过'LiSen'老师所教的所有课的同学的学号、姓名;

SELECT DISTINCT t1.s# AS ST_CODE,
                t1.sname AS ST_NAME,
FROM student t1
LEFT JOIN sc t2
  ON t1.s# = t1.s#
LEFT JOIN course t3
  ON t2.c# = t3.c#
LEFT JOIN teacher t4
  ON t3.t# = t4.t#
WHERE t4.tname = 'LiSen'

 

--9、查询所有课程成绩小于60分的同学的学号、姓名;
SELECT T1.S#, T1.SNAME
FROM STUDENT T1
INNER JOIN SC T2 
  ON T1.S# = T2.S#
WHERE T2.C# < 60

 

--10、查询没有学全所有课的同学的学号、姓名;
/*子查询版*/
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
INNER JOIN SC t2
  ON t1.s# = t2.s#
WHERE t2.c# IN (
                SELECT t3.c#
                FROM Course t3
               )
/*exists版*/               
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
INNER JOIN SC t2
  ON t1.s# = t2.s#
WHERE EXISTS (
              SELECT 1
              FROM Course t3
              WHERE t3.c# = t2.c#
             )

 

--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
/* 子查询 */
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
WHERE t2.c# IN (
                SELECT t4.c#
                FROM student t3
                INNER JOIN sc t4
                  ON t3.s# = t4.s#
                WHERE t3.s# = 1
               )
/* exists版 */
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
WHERE EXISTS  (
               SELECT 1
               FROM student t3
               INNER JOIN sc t4
                 ON t3.s# = t4.s#
               WHERE t4.c# = t2.c# 
                 AND t3.s# = 1              
              )
              

 

--12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
/* 子查询 */
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
  AND t1.s# <> 1
WHERE t2.c# IN (
                SELECT t4.c#
                FROM student t3
                INNER JOIN sc t4
                  ON t3.s# = t4.s#
                WHERE t3.s# = 1
               )
/* exists版 */
SELECT DISTINCT t1.s#,t1.sname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
  AND t1.s# <> 1
WHERE EXISTS  (
               SELECT 1
               FROM student t3
               INNER JOIN sc t4
                 ON t3.s# = t4.s#
               WHERE t4.c# = t2.c# 
                 AND t3.s# = 1               
              )

 

              
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc t4
SET t4.score = (
                WITH tmp AS 
                (
                  SELECT t1.c#,
                         AVG(t1.score) AS avg_score
                  FROM sc t1
                  INNER JOIN Course t2
                    ON t1.c# = t2.c#
                  INNER JOIN teacher t3
                    ON t2.t# = t3.t#
                  GROUP BY t1.c#
                )
                SELECT t5.avg_score 
                FROM tmp t5 
                WHERE t4.c# = t5.c#
               )
WHERE EXISTS (
               SELECT 1
               FROM sc t6
               INNER JOIN Course t7
                 ON t6.c# = t7.c#
               INNER JOIN teacher t8
                 ON t7.t# = t8.t#
                 AND t8.tname = '陈奕迅'
             )

 

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT DISTINCT t1.s#,
                t1.sname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
AND t2.c# = ALL  (
              SELECT t4.c#
              FROM student t3
              INNER JOIN sc t4
                ON t3.s# = t4.s#
              AND t4.s# = 7
             )
AND t1.s# <> 7

 

--15、删除学习'陈奕迅'老师课的SC表记录;
DELETE FROM sc t1
WHERE EXISTS (
               SELECT 1
               FROM teacher t2
               INNER JOIN Course t3
                 ON t2.t# = t3.t#
               WHERE t3.c# = t1.c#
                 AND t2.tname = '陈奕迅'
)

 

16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2号课的平均成绩;
INSERT INTO sc(s#,c#,score)

SELECT min(t5.s#),112,AVG(t4.score)
FROM sc t4
INNER JOIN student t5
  ON t4.s# = t5.s#
WHERE t4.s# NOT IN (
                SELECT t2.s#
                FROM student t2
                INNER JOIN sc t3
                  ON t2.s# = t3.s#
                AND t3.c# = 113
                )
AND t4.c# = 112
GROUP BY t4.c# 

 

/*====================================*/
/*=========== 习题开始 =============*/
/*=================================
             






--17、按平均成绩从高到低显示所有学生的“语文1”、“生物2”、“化学1”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
/* 行转列除了可以用poivt外还可以分别select出来,再和主表的字段关联 */
SELECT t1.S# as 学生ID,
       (SELECT score FROM SC t2 WHERE t2.S#=t1.S# AND t2.C#=111) AS 语文1,
       (SELECT score FROM SC t3 WHERE t3.S#=t1.S# AND t3.C#=118) AS 生物2, 
       (SELECT score FROM SC t4 WHERE t4.S#=t1.S# AND t4.C#=121) AS 化学1, 
       COUNT(*) AS 有效课程数,
       AVG(t1.score) AS 平均成绩 
FROM SC t1
GROUP BY t1.S# 
ORDER BY avg(t1.score)  

--18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT t1.c#,
       MIN(t1.score) AS min_score,
       MAX(t1.score) AS max_score
FROM sc t1
GROUP BY t1.c# 

--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t1.c#,
       AVG(t1.score) AS avg_score,
       100 * SUM(
                 CASE 
                  WHEN nvl(t1.score,0)>=60 THEN 
                    1 
                  ELSE 
                    0 
                  END
                )/COUNT(*) AS pass_pct
FROM sc t1 
GROUP BY t1.c#
ORDER BY avg_score ASC,pass_pct DESC

--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 语文1(111),语文2(112),数学1(113),数学2(114)
SELECT SUM(CASE WHEN t1.c# =111 THEN score ELSE 0 END)/SUM(CASE t1.c# WHEN 111 THEN 1 ELSE 0 END)                      AS 语文1           ,
       100 * SUM(CASE WHEN t1.c# = 111 AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN t1.c# = 111 THEN 1 ELSE 0 END) AS 语文1及格百分比 ,
       SUM(CASE WHEN t1.c# = 112 THEN score ELSE 0 END)/SUM(CASE t1.c# WHEN 112 THEN 1 ELSE 0 END)                     AS 语文2           ,
       100 * SUM(CASE WHEN t1.c# = 112 AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN t1.c# = 112 THEN 1 ELSE 0 END) AS 语文2及格百分比 ,
       SUM(CASE WHEN t1.c# = 113 THEN score ELSE 0 END)/SUM(CASE t1.c# WHEN 113 THEN 1 ELSE 0 END)                     AS 数学1           ,
       100 * SUM(CASE WHEN t1.c# = 113 AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN t1.c# = 113 THEN 1 ELSE 0 END) AS 数学1及格百分比 ,
       SUM(CASE WHEN t1.c# = 114 THEN score ELSE 0 END)/SUM(CASE t1.c# WHEN 114 THEN 1 ELSE 0 END)                     AS 数学2           ,
       100 * SUM(CASE WHEN t1.c# = 114 AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN t1.c# = 114 THEN 1 ELSE 0 END) AS 数学2及格百分比 
FROM SC t1

--21、查询不同老师所教不同课程平均分从高到低显示
SELECT t3.tname,
       t2.cname,
       AVG(t1.score) AS avg_score
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
INNER JOIN teacher t3
  ON t2.t# = t3.t#
GROUP BY t3.tname,t2.cname
ORDER BY avg_score DESC

--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(111),马克思(112),UML (113),数据库(114) 
SELECT sub_query.cname,
       sub_query.rank      
FROM(
      SELECT t2.cname,
             t1.score,
             rank() over(PARTITION BY t1.c# ORDER BY t1.score DESC) AS rank  
      FROM sc t1
      INNER JOIN course t2
        ON t1.c# = t2.c#
      WHERE t1.c# IN (111,112,113,114)
    ) sub_query
WHERE sub_query.rank BETWEEN 3 AND 6;

--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85] 优秀人数,[85-70] 良好人数,[70-60] 一般人数,[ <60] 刚及格人数
SELECT t1.c#,
       t2.cname,
       SUM(CASE WHEN t1.score > 85 AND t1.score <= 100 THEN 1 ELSE 0 END) AS 优秀人数,
       SUM(CASE WHEN t1.score > 70 AND t1.score <= 85 THEN 1 ELSE 0 END)  AS 良好人数,
       SUM(CASE WHEN t1.score > 60 AND t1.score <= 70 THEN 1 ELSE 0 END)  AS 一般人数,
       SUM(CASE WHEN t1.score <= 60 THEN 1 ELSE 0 END)                    AS 刚及格人数
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
GROUP BY t1.c#,t2.cname

--24、查询学生平均成绩及其名次 
SELECT t1.sname,
       t1.s#,
       AVG(t2.score) AS avg_score,
       dense_rank() over(ORDER BY AVG(t2.score) DESC) AS dense_rank
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
GROUP BY t1.sname,t1.s#

--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT sub_query.cname,
       sub_query.s#,
       sub_query.sname,
       sub_query.score,
       sub_query.rank
FROM ( 
       SELECT t3.cname,
              t1.s#,
              t1.sname,
              t2.score,
              rank() over(PARTITION BY t2.c# ORDER BY t2.score DESC) AS rank
       FROM student t1
       INNER JOIN sc t2
         ON t1.s# = t2.s#
       INNER JOIN course t3
         ON t2.c# = t3.c#
      ) sub_query
WHERE sub_query.rank < 4

--26、查询每门课程被选修的学生数 
SELECT t2.cname,
       COUNT(t1.s#) AS sum_stu
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
GROUP BY t2.cname

--27、查询出只选修了一门课程的全部学生的学号和姓名 
/* GROUP BY 语句中选择出来的只有备份组的字段和聚合函数 */
SELECT t1.s#,
       t1.sname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
GROUP BY t1.s#,t1.sname
HAVING COUNT(t2.c#) = 1

--28、查询男生、女生人数
SELECT t1.ssex,
       COUNT(t1.ssex) AS sex_num  
FROM student t1
GROUP BY t1.ssex

--29、查询名字中有'黑'的学生名单
SELECT t1.s#,
       t1.sname
FROM student t1
WHERE t1.sname LIKE '%黑%'

--30、查询同名同性学生名单,并统计同名人数
/* 写了个错的 
   原因:student表中有两个小粉,on条件只是县限定了sname相等,所以导致了2X2的笛卡尔乘积*/
SELECT t1.sname,
       COUNT(t1.s#) AS same_name_count
FROM student t1
INNER JOIN student t2
  ON t1.sname = t2.sname
GROUP BY t1.sname,t2.sname
HAVING COUNT(t1.sname) > 1
/* 正确的写法 */
SELECT t1.sname,
       COUNT(t1.sname) AS same_name_count
FROM student t1
GROUP BY t1.sname
HAVING COUNT(t1.sname) > 1

--32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
SELECT t2.cname,
       t2.c#,
       AVG(t1.score) AS avg_score       
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
GROUP BY t2.cname,t2.c#
ORDER BY avg_score ASC,t2.c# DESC

--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT t1.s#,
       t1.sname,
       AVG(t2.score) AS avg_score
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
GROUP BY t1.s#,t1.sname
HAVING AVG(t2.score) > 85

--34、查询课程名称为'语文1',且分数低于60的学生姓名和分数
SELECT t1.sname,
       t2.score
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
INNER JOIN course t3
  ON t2.c# = t3.c#
WHERE t3.cname = '语文1'
  AND t2.score < 60
  
--35、查询所有学生的选课情况;
/* 可能不是这样写,有更好的写法 */
SELECT t1.s#,
       t1.sname,
       t3.cname
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
INNER JOIN course t3
  ON t2.c# = t3.c#
  
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT t1.s#,
       t1.sname,
       t3.cname,
       t2.score
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
INNER JOIN course t3
  ON t2.c# = t3.c#
WHERE t2.score > 70

--37、查询不及格的课程,并按课程号从大到小排列
SELECT t1.c#,
       t2.cname,
       t1.score
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
WHERE t1.score < 60
ORDER BY t1.c# DESC

--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
SELECT t1.s#,
       t1.sname,
       t2.score
FROM student t1
INNER JOIN sc t2
  ON t1.s# = t2.s#
INNER JOIN course t3
  ON t2.c# = t3.c#
WHERE t2.c# = 113
  AND t2.score > 80 
  
--39、求选了课程的学生人数
SELECT SUM(1)
FROM (
      SELECT 1
      FROM sc t1
      GROUP BY t1.s#
      )
      
--40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
SELECT t4.s#,
       t4.sname,
       t5.score,
       t6.cname
FROM student t4
INNER JOIN sc t5
  ON t4.s# = t5.s#
INNER JOIN course t6
  ON t5.c# = t6.c#
WHERE (t5.score,t6.cname) IN (
                              SELECT MAX(t1.score),
                                     t2.cname
                              FROM sc t1
                              INNER JOIN course t2
                                ON t1.c# = t2.c#
                              INNER JOIN teacher t3
                                ON t2.t# = t3.t#
                              WHERE t3.tname = '陈奕迅'
                                GROUP BY t2.cname
                             )
                             
--41、查询各个课程及相应的选修人数
SELECT t1.c#,
       t2.cname,
       COUNT(t1.s#)
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
GROUP BY t1.c#,t2.cname

--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT t1.s#,
       t1.c#,
       t1.score
FROM sc t1
INNER JOIN sc t2
  ON t1.score = t2.score
  AND t1.c# <> t2.c#
  AND t1.s# = t2.s#
  
--43、查询每门功成绩最好的前两名
SELECT sub_query.s#,
       sub_query.sname,
       sub_query.cname,
       sub_query.score,
       sub_query.rank
FROM (
      SELECT t1.s#,
             t3.sname,
             t2.cname,
             t1.score,
             dense_rank() over(PARTITION BY t1.c# ORDER BY t1.score DESC) AS rank
      FROM sc t1
      INNER JOIN course t2
        ON t1.c# = t2.c#
      INNER JOIN student t3
        ON t1.s# = t3.s#
      ) sub_query
WHERE sub_query.rank < 3

--44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT t1.c#,
       COUNT(t1.s#) AS stu_num
FROM sc t1
GROUP BY t1.c#
  HAVING COUNT(t1.s#) > 10
ORDER BY stu_num DESC,t1.c# ASC

--45、检索至少选修两门课程的学生学号
SELECT t1.s#
FROM sc t1
GROUP BY t1.s#
  HAVING COUNT(t1.c#) > 1
  
--46、查询全部学生都选修的课程的课程号和课程名
SELECT t1.c#,
       t2.cname,
       COUNT(t1.s#)
FROM sc t1
INNER JOIN course t2
  ON t1.c# = t2.c#
GROUP BY t1.c#,t2.cname
  HAVING COUNT(t1.s#) = (
                          SELECT COUNT(t3.s#)
                          FROM student t3
                        )
                        
--47、查询没学过'陈奕迅'老师讲授的任一门课程的学生姓名
SELECT t5.s#, 
       t5.sname
FROM student t5
WHERE t5.s# NOT IN (
                     SELECT DISTINCT t1.s#
                     FROM student t1
                     INNER JOIN sc t2
                       ON t1.s# = t2.s#
                     INNER JOIN course t3
                       ON t2.c# = t3.c#
                     INNER JOIN teacher t4
                       ON t3.t# = t4.t#
                     WHERE t4.tname = '陈奕迅'
                    )
                    
--48、查询两门以上不及格课程的同学的学号及其平均成绩
/* 不能直接用下面的方式,因为有where条件过滤了一部分的成绩
SELECT t1.s#,
       AVG(t1.score)
FROM sc t1
WHERE t1.score < 60
GROUP BY t1.s#
  HAVING COUNT(t1.c#) > 2 */                  
SELECT t2.s#,
       AVG(t2.score)
FROM sc t2
WHERE t2.s# IN (
                SELECT t1.s#
                FROM sc t1
                WHERE t1.score < 60
                GROUP BY t1.s#
                  HAVING COUNT(t1.c#) > 2
               )
GROUP BY t2.s#

--49、检索'114'课程分数小于60,按分数降序排列的同学学号
SELECT t1.s#
FROM sc t1
WHERE t1.c# = 114
  AND t1.score < 60
ORDER BY t1.s# DESC

--50、删除'2'同学的'111'课程的成绩
DELETE FROM sc t1
WHERE t1.s# = 2
  AND t1.c# = 111

复制代码

 

6、删除表格

复制代码

/*====================================*/
/*=========== DROP TABLE =============*/
/*====================================*/
DROP TABLE STUDENT;
DROP TABLE COURSE;
DROP TABLE SC;
DROP TABLE TEACHER;

复制代码

共有 人打赏支持
粉丝 2
博文 12
码字总数 78073
×
E-C-Ares
若見文有用, 请随性打赏
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: