已知一个学生选课管理系统,有学生信息管理(student),课程管理(course),老师信息管理(teacher),选课管理(sc) 1.用SQL语句求出学生张三的所有代课老师姓名
Select t.tname from teacher t,sc where t.tid=sc.tid and sc.cid in (
Select sc.cid from student s sc where sc.sid=s.sid and s.sname=’张三’); 2.用SQL语句求出学生选的最多的一门的代课老师名字 3.用SQL语句删除课程名为‘离散数学’的课程。
Delete from sc where sc.cid=(select cid from course where cname=’ 离散数学’);
Delete from course where cname=’离散数学’; 1,3题我做对了没有,2题想了半天没想出来
Select t.tname from teacher t,sc where t.tid=sc.tid and sc.cid in (
Select sc.cid from student s sc where sc.sid=s.sid and s.sname=’张三’); 2.用SQL语句求出学生选的最多的一门的代课老师名字 3.用SQL语句删除课程名为‘离散数学’的课程。
Delete from sc where sc.cid=(select cid from course where cname=’ 离散数学’);
Delete from course where cname=’离散数学’; 1,3题我做对了没有,2题想了半天没想出来
Create table student (sid number primary key,sname varchar2(10));
Create table course(cid number primary key,cname varchar2(10),tid number references teacher(tid));
Create table teacher(tid number,tname varchar2(10));
Create table sc(sid references student(sid),cid references course(cid),unique(sid,cid));
from course c, teacher t
where c.tid = t.tid
and c.cid =
(select cid
from sc
group by sc.cid
having count(1) = (select max(count(1)) from sc group by a))
select cid,max(sid_num) from (select cid,count(sid) sid_num from sc group by cid)
group by cid
--然后和教师表和课程表关联即可以取到代课老师名字
select t.tname
from course c,teacher t,
(select cid,max(sid_num) from (select cid,count(sid) sid_num from sc group by cid)
group by cid
) a
where c.cid=a.cid and c.tid=t.tid
group by cid 返回的是多行