﻿ 网上盛行的学生选课表的比如ITeye - 超凡娱乐

# 网上盛行的学生选课表的比如ITeye

2019年03月13日10时30分51秒 | 作者: 绍晖 | 标签: 学号,查询,同学 | 浏览: 1817 Student(Sid,Sname,Sage,Ssex) 学生表

create table student (sid int(5),sname varchar(10),sage int(3),ssex varchar(5));
insert into student values(1,韩梅梅,26, 女);
insert into student values(2,李磊,27,男);
insert into student values(3,林涛,27,男);
insert into student values(4,吉姆,24,男);

Course(Cid,Cname,Tid) 课程表
create table course (cid int (5),cname varchar(15),tid int (5));
insert into course values (111,语文,201);
insert into course values (222,数学,301);
insert into course values (333,英语,401);
insert into course values (444,物理,201);

SC(Sid,Cid,score) 成果表
create table sc (sid int (5),cid int(5),score int(5));
insert into sc values (1,111,80);
insert into sc values (1,222,90);
insert into sc values (1,333,80);
insert into sc values (1,444,90);
insert into sc values (2,111,80);
insert into sc values (2,222,70);
insert into sc values (3,111,80);
insert into sc values (3,222,60);
insert into sc values (3,333,80);

Teacher(Tid,Tname) 教师表
create table teacher (tid int(5),tname varchar(10));
insert into teacher  values(201,张三);
insert into teacher  values(301,李四);
insert into teacher  values(401,王五);
insert into teacher  values(501,李奎);

select  a.sid from sc a,sc b where a.sid=b.sid and a.cid=111 and b.cid=222 and a.score b.score;

1、查询“111”课程比“222”课程成果高的一切学生的学号;
① select a.S# from (select sid,score from SC where cid=111) a,(select sid,score
from SC where cid=222) b
where a.score b.score and a.s#=b.s#;

②select a.sid from sc a ,sc b where a.sid=b.sid and a.cid=111 and b.cid=222 and a.score b.score;

2、查询均匀成果大于60分的同学的学号和均匀成果;
① select sid,avg(score) agvscore from sc group by sid having avg(score) 60

3、查询一切同学的学号、名字、选课数、总成果;//留意是一切同学,所以需求外衔接
① select a.sid,a.sname ,count(cid) ,sum(score) from student a left outer  join sc b on a.sid=b.sid  group by a.sid,a.sname; //吉姆不好好学习，没选课

4、查询姓“李”的教师的个数;
① select count(tid) from teacher where tname like 李%;
② select count(distinct(tname)) from teacher where tname like 李%;

5、查询没学过“张三”教师课的同学的学号、名字;
① select a.sid,a.sname  from student a where sid not in(
select sid  from sc b where b.cid in(
select c.cid from course c,teacher d where c.tid=d.tid and d.tname=张三));

② select sid,sname from student where sid not in(  select a.sid  from sc a ,course b, teacher c where a.cid=b.cid and b.tid=c.tid and c.tname=张三);

6、查询学过“111”而且也学过编号“222”课程的同学的学号、名字;

① select c.sid,c.sname from sc a ,sc b,student c where a.sid=b.sid and b.sid=c.sid and a.cid=111 and b.cid=222;
② select b.sid,b.sname from sc a,student b where a.sid=b.sid and  a.cid=111 and exists (select 1 from sc c where a.sid=c.sid and c.cid=222);

7、查询学过“张三”教师所教的一切课的同学的学号、名字; //有难度
① select * from student where sid in(
select sc.sid  from sc , course ,teacher where sc.cid=course.cid and course.tid=teacher.tid and teacher.tname=张三 group by sid having count(sc.cid)=
(select count(1) from course,teacher where course.tid=teacher.tid and teacher.tname=张三));

8、查询课程编号“222”的成果比课程编号“111”课程低的一切同学的学号、名字;
① select c.sid,c.sname from
(select sid,score from sc where cid=222)a,  (select sid,score from sc where cid=111) b ,student c
where a.sid=b.sid and b.sid=c.sid and a.score b.score ;
② select  a.sid,a.sname  from student a,sc b,sc c  where b.cid=222 and c.cid=111  and a.sid=b.sid and b.sid=c.sid and b.score c.score;

9、查询一切课程成果小于85分的同学的学号、名字;
①  select sid,sname from student where sid not in( select  distinct(sid) from sc where sc.score =85);

10、查询没有学全一切课的同学的学号、名字;
①  select sid,sname from student where sid in(
select  sid from sc group by sid  having count(cid) (select count(1) from course ));
②  select student.sid,sname from student,sc where student.sid=sc.sid group by student.sid,sname having count(cid) (select count(1) from course);

11、查询至少有一门课与学号为“3”的同学所学相同的同学的学号和名字;
select sid,sname from student where sid in(
select sid from sc where cid in(
select cid from sc where sid=3)
)
12、查询至少学过学号为“1”同学一切一门课的其他同学学号和名字;
①select distinct a.sid,a.sname from student a,sc b where a.sid=b.sid and b.cid in(select cid from sc where sid=1);

13、把“SC”表中“张三”教师教的课的成果都更改为此课程的均匀成果; //不知道怎么做

14、查询和“2”号的同学学习的课程完全相同的其他同学学号和名字;
①  select sc.sid from sc where  cid in (select cid from sc where sid=1) group by sid having count(cid)=(select count(1) from  sc where sid=1);

15、删去学习“张三”教师课的SC表记载;
① delete from sc  where cid in(
select b.cid from course b,teacher c  where b.tid=c.tid and c.tname=张三);

## 阅读排行

• 1 • 2 • 3 • 4 • 5 IT girl ——1751CTO博客

无愧,教师,一个
• 6 • 7 • 8 • 9 • 10 