测试面试必会sql（1）

 

 

 测试一般各种查询语句用的较多，下面的查询语句都是需要熟悉的

 

 

Course表

Score表

Student表

 

 Teacher表

1，查询课程编号为“02”的总成绩

SELECT * FROM `Score` where c_id=02;

 

2，查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

SELECT st.*,a.s_core,b.s_core
FROM Student st
INNER JOIN (SELECT s_id,s_core from Score where c_id='01') a ON st.s_id=a.s_id
INNER JOIN (SELECT s_id,s_core FROM Score WHERE c_id='02') b ON a.s_id=b.s_id
WHERE a.s_core>b.s_core;

 

3，查询没学过“张三”老师课的学生的学号、姓名

SELECT st.s_id,st.s_name
from Student st
WHERE st.s_id not in
(SELECT sc.s_id from Score sc INNER JOIN Course co on sc.c_id=co.c_id
INNER JOIN Teacher te on te.t_id=co.t_id
WHERE te.t_name='张三');

 

4，查询没有学全所有课的学生的学号、姓名

SELECT st.s_id,st.s_name FROM Student st
INNER JOIN Score sc on st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(c_id)<(SELECT COUNT(DISTINCT c_id)from Course);

 

5，查询平均成绩大于60分的学生的学号和平均成绩

SELECT s_id as "学号",avg(s_core) as "平均成绩" from Score GROUP BY s_id HAVING avg(s_core)>60   

 

6，查询所有学生的学号、姓名、选课数、总成绩

SELECT Student.s_id,Student.s_name,count(Score.c_id),sum(Score.s_core)
FROM Student
JOIN Score
on Student.s_id=Score.s_id
GROUP BY Student.s_id;

 

7，查询姓“猴”的老师的个数

SELECT count(t_id)
FROM `Teacher`
where t_name like"猴%";

 

8，查询学过“张三”老师所教的所有课的同学的学号、姓名

SELECT st.s_id as '学号',st.s_name as '名称'
from Student st
WHERE st.s_id in
(SELECT sc.s_id from Score sc INNER JOIN Course co on sc.c_id=co.c_id
INNER JOIN Teacher te on te.t_id=co.t_id
WHERE te.t_name='张三');

 

9，查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

SELECT *
FROM Student St
INNER JOIN Score Sc
on Sc.s_id=St.s_id
WHERE c_id = '01'and c_id ='02';

 

10，查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

SELECT st.s_id,st.s_name
from Student st
INNER JOIN Score sc
on st.s_id=sc.s_id
WHERE c_id IN (SELECT c_id from Score where s_id = 01) and st.s_id <> 01
GROUP BY st.s_id