现在有4个表

admin · 发表于2016-08-07 08:13:37

teacher(

id number 主键

name varchar2(100) 名字

)教师表

-------------------------

course(

id number 主键

name varchar2(100) 名字

teacherid number 教课老师，对应教师表的id

)课程表

-------------------------

student(

id number 主键

name varchar2(100) 名字

)学生表

-------------------------

sc(

studentid number 学生id，对应学生表的id

courseid number 课程id，对应课程表的

score number 分数

)课程表

(1) 查询课程"001"成绩比课程"002"高的学生的姓名

(2) 查询所有成绩都大于60分的学生姓名

(3) 查询所有"李老师"的课成绩大于60分的学生姓名

(4) 查询所有没选"李老师"的课的学生的姓名

(5) 查询平均成绩大于60的学生姓名

song · 发表于2016-09-09 17:45:02

(1) 查询课程"001"成绩比课程"002"高的学生的姓名

select sname

from student ,sc

where student.sid=sc.sid and cid='001' and score<(select score from sc where cid='002')

(2) 查询所有成绩都大于60分的学生姓名

select sname
from student ,sc
where student.sid=sc.sid and sc.sid not in(select score from sc where grade<60)

(3) 查询所有"李老师"的课成绩大于60分的学生姓名

select sname
from teacher,student ,sc,course
where teacher.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid
and tname like '李%' and sc.sid not in(select score from sc where grade<60)

(4) 查询所有没选"李老师"的课的学生的姓名

select sname
from student,(select tid from teacher where tname not like '李%')t ,course,sc
where t.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid

(5) 查询平均成绩大于60的学生姓名
select sname
from student ,(select sid from sc group by sid having avg(score)>60)g
where student.sid =g.sid

一个人的时光 · 发表于2016-09-09 19:12:34

(1) 查询课程"001"成绩比课程"002"高的学生的姓名
select student.name
from student,
       (select score,sid from sc where cid = '001') t1,
       (select score from sc where cid = '002') t2
where t1.sid = student.sid
   and t1.score > t2.score
(2) 查询所有成绩都大于60分的学生姓名
select name
   from student, (select sid from sc group by sid having min(score) > 60) t
where student.sid = t.sid
(3) 查询所有"李老师"的课成绩大于60分的学生姓名
select student.name
from teacher t, course c, student s, sc,(select sid from sc group by sid having min(score) > 60) t1
where teacher.name like '李%'
   and score > 60
   and t.tid = c.tid
   and c.cid = sc.cid
   and s.sid = sc.sid
   and s.sid = t1.sid
(4) 查询所有没选"李老师"的课的学生的姓名
select student.name
from teacher t, course c, student s, sc
where teacher.name not like '李%'
   and t.tid = c.tid
   and c.cid = sc.cid
   and s.sid = sc.sid
(5) 查询平均成绩大于60的学生姓名
select student.name
from student, (select sid from sc group by sid having avg(score) > 60) t
where student.sid = t.sid

梦紫菱0124 · 发表于2016-09-09 18:52:19

1、select name from student where id=(select studenid from sc where group by studentid having (select score from sc where courseid="001")>(select score from sc where courseid="002"))

2、select name from student where id not in(select studentid from sc where score<60)

3、select name from student where id=( select sc.studentid from sc where courseid=(select c.id from course c,teacher t where c.teacherid=t.id and t.name like "李%") and score>60)

4、select name from student id in (select distinct studentid from sc where courseid not in(select c.id from course c,teacher t where c.teacherid=t.id and t.name like "李%"))

5、select name from student id in (select studentid from sc where group by studentid having avg(score)>60)

renmihe · 发表于2016-09-10 18:48:32

(1) 查询课程"001"成绩比课程"002"高的学生的姓名

select sname

from student ,sc

where student.sid=sc.sid and cid='001' and score<(select score from sc where cid='002')

(2) 查询所有成绩都大于60分的学生姓名

select sname
from student ,sc
where student.sid=sc.sid and sc.sid not in(select score from sc where grade<60)

(3) 查询所有"李老师"的课成绩大于60分的学生姓名

select sname
from teacher,student ,sc,course
where teacher.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid
and tname like '李%' and sc.sid not in(select score from sc where grade<60)

(4) 查询所有没选"李老师"的课的学生的姓名

select sname
from student,(select tid from teacher where tname not like '李%')t ,course,sc
where t.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid

(5) 查询平均成绩大于60的学生姓名
select sname
from student ,(select sid from sc group by sid having avg(score)>60)g
where student.sid =g.sid

马鹏磊 · 发表于2016-09-10 14:54:09

drop table tbl_教师表
--创建教师表
create table tbl_教师表(
id number not null primary key,
name varchar2(100)
)
--创建成绩表
drop table tbl_课程表

create table tbl_课程表(
    id number not null primary key,
    name varchar2(100),
    teacherid number,
    foreign key(teacherid) references tbl_教师表(id)
)
--创建学生表
drop table tbl_学生表
create table tbl_学生表(
    id number not null primary key,
    name varchar2(100)
)
--创建成绩表
drop table tbl_成绩表
create table tbl_成绩表(
    studentid number,
    courseid number,
    score number,
    foreign key(studentid) references tbl_学生表(id),
    foreign key(courseid) references tbl_课程表(id)
)
--教师表插入数据
insert into tbl_教师表
values(1,'李老师')
insert into tbl_教师表
values(2,'王老师')
insert into tbl_教师表
values(3,'张老师')
--课程表插入数据
insert into tbl_课程表
values(001,'数学',1)
insert into tbl_课程表
values(002,'语文',2)
insert into tbl_课程表
values(003,'英语',3)
--学生表插入数据
insert into tbl_学生表
values(101,'马鹏磊')
insert into tbl_学生表
values(102,'张嘉龙')
--成绩表插入数据
insert into tbl_成绩表
values(101,1,100)
insert into tbl_成绩表
values(101,2,50)
insert into tbl_成绩表
values(101,3,60)

insert into tbl_成绩表
values(102,2,60)
insert into tbl_成绩表
values(102,3,50)

(1) 查询课程"001"成绩比课程"002"高的学生的姓名
--先查询课程002的成绩
--在查询成绩大于001
select st.name
from tbl_学生表 st,tbl_成绩表 sc
where st.id=sc.studentid
and courseid=001
and score>all(select score from tbl_成绩表 where courseid=002)

(2) 查询所有成绩都大于60分的学生姓名
--在学生表里面查询学生姓名在成绩表里查询成绩小于60分的学生id
select st.name
from tbl_学生表 st
where st.id not in(select distinct sc.studentid from tbl_成绩表 sc where score<60)

(3) 查询所有"李老师"的课成绩大于60分的学生姓名
select st.name
from tbl_学生表 st, tbl_成绩表 sc,tbl_教师表 tt,tbl_课程表 so
where st.id=sc.studentid
and sc.courseid=tt.id
and so.teacherid=tt.id
and tt.name like '李%'
and sc.studentid not in(select score from tbl_成绩表 sc where score<60)

(4) 查询所有没选"李老师"的课的学生的姓名
--先选出来选了李老师课程的学生的名字
--再选出来没有选李老师课程的学生的姓名
select name
from tbl_学生表
where name not in
       (select st.name
          from tbl_学生表 st, tbl_成绩表 sc, tbl_教师表 tt, tbl_课程表 so
         where st.id = sc.studentid
           and sc.courseid = tt.id
           and so.teacherid = tt.id
           and tt.name like '李%')

(5) 查询平均成绩大于60的学生姓名
select st.name
from tbl_学生表 st,(select studentid from tbl_成绩表 group by studentid having avg(score)>60)g
where st.id=g.studentid

(4) 查询所有没选"李老师"的课的学生的姓名

select sname
from student,(select tid from teacher where tname not like '李%')t ,course,sc
where t.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid

(5) 查询平均成绩大于60的学生姓名
select sname
from student ,(select sid from sc group by sid having avg(score)>60)g
where student.sid =g.sid

Cracia · 发表于2016-09-11 16:24:35

(1)select t1.sid from (select studentid,score from sc where courseid='001') t1

(select studentid,score from sc where courseid='002') t2

where t1.studentid=t2.studentid and t1.score>t2.score

(2)select studentid,avg(score) from score group by studentid having avg(score)>60

(3) select studentid from score,(select course.id cid fromcourse,(select id from teacher t where t.name='李老师') tb where tb.id=course.te) t2

where t2.cdi=score.course.id and score.score>60

(4)select s.id,s.name from student where sid not in(

select distinct(t1.studentid) from score t1,course t2,teacher t3

where t1.courseid=t2.id and t3.name='李老师'

);

(5)select studentid from score group by studentid having avg(score)>60

740317431 · 发表于2017-08-21 17:15:05

(1)select st.name

from sc sc, student st

where sc.studentid=st.id and (select score from sc where courseid='001')>(select score from sc where courseid='002')

(2) select st.name

from sc sc, student st

where sc.studentid=st.id and score>60

(3)select st.name

from sc sc, student st

where sc.studentid=st.id and score>60 and courseid=(select id from course where teacherid =(select id from teacher where name='李老师'))

(4) select st.name

from sc sc, student st

where sc.studentid=st.id and courseid!=(select id from course where teacherid =(select id from teacher where name='李老师'))

(5) select st.name

from sc sc, student st

where sc.studentid=st.id and avg(score)>60

王之神判 · 发表于2017-08-22 18:08:53

select *
from score_tab s2,(select * from score_tab s1 where s1.course_id='002') t

where s2.course_id='001' and t.user_id=s2.user_id and s2.score>t.score;

select s.name
from (
select st.name,min(sc.score) score
from score_tab sc,student_tab st
where sc.user_id=st.id
group by st.id,st.name) s
where s.score>60;

select te.name
from(
select st.id,st.name,count(1) co
from score_tab sc,student_tab st,teacher_tab t,course_tab c
where sc.user_id=st.id and sc.course_id=c.id and c.teacher_id=t.id
and t.name like '李%' and sc.score>60
group by st.id,st.name) te
where te.co=(select count(1) from course_tab c,teacher_tab t where c.teacher_id=t.id and t.name like '李%' group by c.teacher_id);

select distinct st.name
from score_tab sc,student_tab st,teacher_tab t,course_tab c
where sc.user_id=st.id and sc.course_id=c.id and c.teacher_id=t.id
and c.id not in(
select c.id
from course_tab c,teacher_tab t
where c.teacher_id=t.id and t.name like '李%'
group by c.id);

select st.name
from score_tab sc,student_tab st
where sc.user_id=st.id
group by st.id,st.name
having avg(sc.score)>60;

wulinan · 发表于2017-08-24 22:27:49

--(1) 查询课程"001"成绩比课程"002"高的学生的姓名 

select sname 

from student ,sc

where student.sid=sc.sid and cid='001' and score<(select score from sc where cid='002') 




--(2) 查询所有成绩都大于60分的学生姓名 

select sname
from student ,sc
where student.sid=sc.sid and sc.sid not in(select score from sc where grade<60) 




--(3) 查询所有"李老师"的课成绩大于60分的学生姓名 

select sname
from teacher,student ,sc,course
where teacher.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid
      and tname like '李%' and sc.sid not in(select score from sc where grade<60) 




--(4) 查询所有没选"李老师"的课的学生的姓名 

select sname
from  student,(select tid from teacher where tname not like '李%')t ,course,sc
where t.tid=course.tid and student.sid=sc.sid and course.cid=sc.cid 




--(5) 查询平均成绩大于60的学生姓名
select sname
from student ,(select sid from sc group by sid having avg(score)>60)g
where student.sid =g.sid

sangpeng · 发表于2017-08-31 20:13:38

drop table teacher
create table teacher(
       id number not null primary key,
       name varchar2(100)
)
drop table course
create table course(
       id number not null primary key,
       name varchar2(100),
       teacherId number,
       foreign key(teacherId) references teacher(id)
)
drop table student
create table student(
       id number not null primary key,
       name varchar2(100)
)
drop table sc
create table sc(
       studentId number,
       courseId number,
       score number,
       foreign key(studentId) references student(id),
       foreign key(courseId) references course(id)
)

1

select a.studentid,a.score as q,b.score as w from(select * from sc where sc.courseid='001') a
left join
(select * from sc where sc.courseid='002') b
on a.studentid=b.studentid
where a.score>b.score

2

select s.name from sc sc,student s where (sc.score>60 and sc.courseid='001' ) and (sc.score>60 and sc.courseid='002')

3

select s.name from teacher t,student s,sc sc,course c where t.id=c.teacherid and s.id=sc.studentid and c.teacherid=t.id
and t.name='李老师' and sc.score>60

4

select s.name from student s
where s.name not in(select s.name from teacher t ,student s,sc sc,course c where c.teacherid=t.id and t.id=c.teacherid and s.id=sc.studentid and
t.name='李老师' )

5

select s.name,avg(sc.score) from teacher t ,student s,sc sc,course c where c.teacherid=t.id and t.id=c.teacherid and s.id=sc.studentid
group by s.name
having avg(sc.score)>60

m1359292934 · 发表于2019-08-29 14:42:32

SELECT * FROM tblScore ts1, tblScore ts2

WHERE ts1.stuid = ts2.stuid AND ts1.courseid = '001' AND ts2.courseid = '002

AND ts1.score > ts2.score

SELECT stu.stuid, AVG(score.score)FROM tblstudent stu, tblscore score

WHERE stu.stuid = score.stuid GROUP BY stu.stuid

HAVING AVG(score.score) > 60

SELECT stu.stuid, stu.stuname,COUNT(score.courseid),SUM(score.score)

FROM tblstudent stu, tblCourse course, tblScore score

WHERE stu.stuid = score.stuid AND course.courseid = score.courseid

GROUP BY stu.stuid,stu.stuname ORDER BY stuid

用户名			找回密码
密码			立即注册

学习平台

现在有4个表