2020/11/29 刘一辰的JAVA随笔

今日目标：Mysql学习32

实验内容：

-- 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree

SELECT * FROM score WHERE sc_degree > ALL (select sc_degree from score WHERE c_no = '3-245') AND c_no = '3-105';

+------+-------+-----------+

| s_no | c_no  | sc_degree |

+------+-------+-----------+

| 101  | 3-105 |        90 |

| 102  | 3-105 |        91 |

| 103  | 3-105 |        92 |

| 104  | 3-105 |        89 |

| 105  | 3-105 |        88 |

+------+-------+-----------+

 

进阶:查出学生的信息,课程名称,分数(s_name c_name,sc_degree)

SELECT s.s_name , c.c_name ,sc.sc_degree FROM score AS sc, student AS s,course AS c WHERE sc_degree > ALL (select sc_degree from score WHERE c.c_no = '3-245') AND c.c_no = '3-105' AND sc.s_no = s.s_no AND sc.c_no = c.c_no ;

+--------+------------+-----------+

| s_name | c_name     | sc_degree |

+--------+------------+-----------+

| 曾华   | 计算机导论 |        90 |

| 匡明   | 计算机导论 |        91 |

| 王丽   | 计算机导论 |        92 |

| 李军   | 计算机导论 |        89 |

| 王芳   | 计算机导论 |        88 |

| 赵铁柱 | 计算机导论 |        76 |

+--------+------------+-----------+