一.有以下几张表及表结构
Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,Score) 成绩表
Teacher(Tid,Tname) 教师表
查询平均成绩大于60分的同学的学号和平均成绩;
select Sid,avg(Score)
from SC
group by Sid
having avg(Score)>60;
查询所有同学的学号、姓名、选课数、总成绩
select Sid,Sname,Count(Cid),sum(Score)
from Student,SC
group by Sid
select Student.Sid,Student.Sname,count(SC.Cid) ,sum(Score) //如果存在两个表,要写清字段是属于哪个表的
from Student left outer join SC //以学生表为基准,
on Student.Sid=SC.Cid //筛选出id相同的行
group by Student.Sid,Sname; //进行合并
查询姓“李”的老师的个数;
select count(Teacher.Tid)
from Teacher
where Teacher.Tname like “李”;
15.删除学习“叶平”老师课的SC表记录;
delete from sc s
where s.cid in
(select c.cid
from teacher t,course c
where t.tid = c.tid
and tname=‘叶平’)
因为SC表没有老师名字,只有teacher表有名字,则要让SC表和teacher产生关联,首先是SC表与Course表有相同的Cid字段,然后Course与teacher有tname字段,因此要有两个关联表
5.查询没学过“叶平”老师课的同学的学号、姓名;
select Student.Sid,Student.Sname
from Student
where Sid not in
(select distinct(SC.Sid)
from SC,Course,Teacher
where SC.Cid = Course.Cid
and Teacher.Tid=Course.Tid
and Teacher.Tname = ‘和平’ );//括号里面查询的是学过“叶平”老师的课的Sid。条件是要有名字的表,那就是要查询teacher表,又要查出Sid号,那就是要SC表,仅仅有SC表和teacher是构成不了联系的,需要有Course表,因为SC表与Course表的Sid相同,Course表和teacher表的Tid相同,则要有这三个表
distinct:去重复
9.查询所有课程成绩小于60分的同学的学号、姓名;
SELECT sid,Sname
FROM Student
WHERE sid not in
(SELECT Student.sid
FROM Student,SC
WHERE Student.sid=SC.sid AND score>60);
//先在子查询找到符合条件的id号,在用这id号去student表找id号和name,name是捆绑搜索