表
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
表的介绍：
学生表student ： sno学号 sname学生姓名 sage学生年龄 ssex学生性别
教师表teacher ： tno教师编号 tname教师姓名
课程表course ： cno课程编号 cname课程名称 tno该课程对应的老师编号
成绩表sc ：sno对应学生的编号 cno对应课程的编号 score成绩分数
数据
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select sno from sc
WHERE score> some(
select score FROM sc
WHERE cno='c002')
and cno='c001';
2、查询平均成绩大于60 分的同学的学号和平均成绩；
select avg_score ,sno from(
select avg(score) avg_score , sno from sc
group by sno)
where avg_score >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select sname,st.sno,A,score from student st join (
select sno,score,s.cno,a from sc s join (
select cno ,count(*) a from course
group by cno) b
on s.cno in b.cno) c
on st.sno =c.sno;
4、查询姓“刘”的老师的个数；
select count(*) from teacher
where tname='刘阳';
5、查询没学过“谌燕”老师课的同学的学号、姓名；
select sno,sname from student
where sno in (
select sno from sc
where cno in(
select cno from course
where tno in(select tno from teacher
where tname<>'谌燕')));
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select sno from sc
where cno='c002'
and sno in (select sno from sc
where cno='c001');
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select sno,sname from student
where sno in (
select sno from sc
where cno in(
select cno from course
where tno =(select tno from teacher
where tname='谌燕')));
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select sno, sname from student
where sno in (select sno from sc
WHERE score< any(
select score FROM sc
WHERE cno='c002')
and cno='c001');
9、查询所有课程成绩小于60 分的同学的学号、姓名；
select sno, sname from student
where sno in(
select sno from sc
where score <60);
10、查询没有学全所有课的同学的学号、姓名；
select sno ,sname from student
where sno in (select sno from (select sno, count(*) count_course from sc
group by sno)
where count_course<(select count(*) from course));
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select sno ,sname from student
where sno in (select sno FROM sc
where cno in (select cno from sc
where sno='s001')
and sno<>'s001');
12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
update sc set score=( select avg(score) from sc where cno in(
select cno from course where tno=( select tno from teacher where tname='谌燕')))
where cno in(select cno from course where tno=( select tno from teacher where tname='谌燕'));
14、删除学习“谌燕”老师课的SC 表记录；
DELETE from sc
where cno in (select cno from course
where tno=(select tno from teacher
where tname='谌燕'));
15.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cno ,max(score) max_score ,min(score) min_score from sc
group by cno;
16.查询不同老师所教不同课程平均分从高到低显示
17.查询每门课程被选修的学生数
select cno, count(*) from sc
group by cno;
18.查询出只选修了一门课程的全部学生的学号和姓名
select sno,sname from student
where sno=(select sno from (select sno, count(*) sno_count from sc
group by sno)
where sno_count=1);
19.查询男生、女生人数
select ssex, count(*) from student
GROUP by ssex;
20.查询姓“张”的学生名单
select sno ,sname from student
where substr(sname, 1, 1)='张'；
21.查询同名同性学生名单，并统计同名人数
select sname, count(*) from (select s1.sname,s1.ssex from student s1 join student s2
on s1.sno<> s2.sno
and s1.ssex= s2.ssex and s1.sname= s2.sname)
group by sname;
22.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cno,avg(score) avg_score from sc group by cno order by avg_score,cno desc
23.查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
select s1.sname,s1.sno,avg_score from student s1 join (select sno, avg(score) avg_score from sc
group by sno) s2 on s1.sno=s2.sno
where avg_score>85;
24.查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
select sname, s1.sno, score from student s1
join (select sno ,score from sc
where cno=(select cno from course
where cname='数据库')) s2 on
s1.sno=s2.sno
;
25.查询所有学生的选课情况；
select c1.cname,c1.cno,sname,sno from course c1 join (SELECT cno,sname,s1.sno from sc s1 join (select sno,sname from student) s2
on s1.sno =s2.sno) s3 on c1.cno=s3.cno;
26.查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select c1.cname,sname ,s from course c1 join (select sname,cno,s from student s1 join (select sno,cno,score s from sc
where score >70) s2
on s1.sno=s2.sno) s3
on c1.tno=s3.cno;
27.查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select sno ,sname from student
where sno in (select sno from sc
where cno='c001'
and score>80);
28.查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select s1.sname ,score from student s1 join (
select sno,score from sc
where score = any( select max_score from (select cno,max(score) max_score from sc
group by cno)
where cno in (select cno from course
where tno =(select tno from teacher
where tname='谌燕')))
and cno in (select cno from course
where tno =(select tno from teacher
where tname='谌燕'))) s3 ON s1.sno=s3.sno;
29.查询每门功课成绩最好的前两名
30.删除“s002”同学的“c001”课程的成绩
delete sc score
where sno='s002'
and
cno='coo1';
4798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
