一、 操作表
select A.student_id from
(select student_id,number asbiology from score left join course on score.course_id = course.cid wherecourse.cname = 'biological') as A
left join
(select student_id,number as ph from score left join course onscore.course_id = course.cid where course.cname = 'physics') as B
on A.student_id = B.student_idwhere ph >biology;
查询平均成绩大于60分的同学的学号和平均成绩;
select student_id,avg(number) from score group bystudent_id ;
2. 查询所有同学的学号、姓名、选课数、总成绩;
selectscore.student_id,sum(score.number),count(score.student_id),student.sname
from
score left join student onscore.student_id = student.sid
group by score.student_id
3. 查询姓“李”的老师的个数;
selectcount(tid) from teacher where tname like '李%';
4. 查询没学过“叶平”老师课的同学的学号、姓名;
SELECT sid fromstudent WHERE sid not IN(
SELECT DISTINCTB.student_id from(
SELECTstudent_id,tname from
(selectsname,student_id,course_id,teach_id,number from student LEFT JOIN score onstudent.sid=score.student_id)as A
LEFT JOIN
teacher ONA.teach_id=teacher.tid
)as B WHEREtname='叶平'
);
5. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
SELECT *from
(
SELECT sidfrom
(SELECT student_id as student_idA from score WHEREcourse_id=1)as A
LEFT JOIN
student on student.sid=A.student_idA) as C
inner join
(
SELECT sid from
(SELECT student_id as student_idB from score WHEREcourse_id=2)as B
LEFT JOIN
student on student.sid=B.student_idB)AS D onC.sid=D.sid;
1. 查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT sid from student WHERE sid not IN(
SELECT DISTINCT B.student_id from(
SELECT student_id,tname from
(select sname,student_id,course_id,teach_id,number from student LEFTJOIN score on student.sid=score.student_id)as A
LEFT JOIN
teacher ON A.teach_id=teacher.tid
)as B WHERE tname='叶平'
);
2. 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
SELECT sname,student_id FROM
(select A.student_id from
(select student_id,number as biology from score left join course onscore.course_id = course.cid where course.cid=1) as A
left join
(select student_id,number as phfrom score left join course on score.course_id = course.cid where course.cid=2)as B
on A.student_id = B.student_id where ph<biology
)as C
LEFT JOIN
student on C.student_id=student.sid;
3. 查询有课程成绩小于60分的同学的学号、姓名;
SELECTstudent_id,sname from
(SELECTstudent_id,sname,number from score LEFT JOIN student on score.student_id=student.sidWHERE number<60 GROUP BY sname)as A;
)
4. 查询没有学全所有课的同学的学号、姓名;
SELECT student_id,sname from student LEFT JOINscore on student.sid=score.student_id GROUP BY student_id
HAVING COUNT(course_id)<(SELECT COUNT(cid)fromcourse);