SELECT t1.s_id as t1_s_id, t2.s_id as t2_s_id, t3.s_name, t1.s_score as s_score_01, t2.s_score as s_score_02 FROM (select s_id, c_id, s_score from Score WHERE c_id ='01') as t1 INNERJOIN (select s_id, c_id, s_score from Score WHERE c_id ='02') as t2 ON t1.s_id = t2.s_id INNERJOIN Student as t3 ON t1.s_id = t3.s_id where t1.s_score > t2.s_score
( SELECT s_id, count(c_id) as cnt FROM Score WHERE s_score <60 GROUPBY s_id ) a
INNERJOIN
( SELECT s_id, count(c_id) as cnt FROM Score GROUPBY s_id ) b ON a.s_id = b.s_id
INNERJOIN
Student as t ON a.s_id=t.s_id
WHERE a.cnt = b.cnt
(3). 查询所有学生的学号、姓名、选课数、总成绩
学号
姓名
课程编号
这门课成绩
1
小张
1
60
1
小张
3
70
1 2 3 4 5 6 7 8 9
SELECT t1.s_id, t1.s_name, COUNT( t2.c_id ), SUM(casewhen t2.s_score isNULLthen0else t2.s_score END) FROM Student AS t1 LEFTJOIN Score AS t2 ON t1.s_id = t2.s_id GROUPBY s_id, t1.s_name
(4). 查询姓“猴”的老师的个数(不重要)
1 2 3 4
SELECT count(t_id) FROM teacher WHERE t_name LIKE'张%'
1 2 3 4
SELECT count(distinct t_name) FROM teacher WHERE t_name LIKE'张%'
(5). 查询没学过“张三”老师课的学生的学号、姓名(重点)
学号
课程号
成绩
教师号
教师姓名
s_1
c_1
90
t_1
张三
1 2 3 4 5 6 7
SELECT s_id, s_name from Student WHERE s_id notin ( SELECT s_id FROM Score s INNERJOIN Course c ON s.c_id = c.c_id INNERJOIN Teacher t ON c.t_id = t.t_id WHERE t.t_name='张三' )
(6). 查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
1 2 3 4 5 6 7 8
SELECT st.s_id, st.s_name FROM Student as st INNERJOIN Score s ON s.s_id = st.s_id INNERJOIN Course c ON s.c_id = c.c_id INNERJOIN Teacher t ON c.t_id = t.t_id WHERE t.t_name='张三' )
SELECT*FROM STUDENT WHERE s_id IN ( SELECT a.s_id FROM (SELECT s_id, c_id FROM Score WHERE c_id ='01') a INNERJOIN (SELECT s_id, c_id FROM Score WHERE c_id ='02') b ON a.s_id = b.s_id )
(8). 查询课程编号为“02”的总成绩(不重点)
1 2 3
SELECTSUM(s_score) FROM Score wWHERE c_id ='02'
(10). 查询没有学全所有课的学生的学号、姓名(重点)
1 2
SELECT*FROM course SELECT*FROM Score
Error Version:
1 2 3 4 5 6 7
SELECT s_id, s_name FROM Student WHERE s_id IN ( SELECT s_id FROM Score GROUPBY s_id HAVINGcount(distinct c_id) < (SELECTCOUNT(distinct c_id) FROM Course) )
-- 一门课都没有学,上面的 SQL 就漏掉了.
Right Version:
1 2 3 4 5 6 7
SELECT st.*, sc.* FROM Student as st LEFTJOIN Score as sc ON st.s_id=sc.s_id GROUPBY st.s_id HAVINGcount(distinct sc.c_id) < (SELECTCOUNT(distinct c_id) FROM Course) )
Checking if Disqus is accessible...