sql整理_2查询练习_oracle

--1 查询含有杨字学生信息

select * from student t where t.sname like '%杨%';

-- 2 查询统计同龄学生人数

select t.sage,count(*) from student t group by t.sage;

--3 查询每门课程平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序

select * from 课程;

select * from 成绩表;

select c.cid,avg(c.cscore) from 成绩表 c group by c.cid order by avg(c.cscore),c.cid desc;

select c.cid,k.cname,avg(c.cscore) from 课程 k,成绩表 c where k.cid = c.cid group by c.cid,k.cname order by avg(c.cscore),c.cid desc;

--4、按平均成绩从高到低显示所有学生的平均成绩

select sid,avg(Cscore) from 成绩表 group by sid order by avg(Cscore) desc;

--5、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分

select sc.课程ID, c.cname 课程name, sc.最高分, sc.最低分, sc.平均分

  from (select s.cid 课程ID,

              max(s.cscore) 最高分,

              min(s.cscore) 最低分,

              avg(s.cscore) 平均分

          from 成绩表 s

        group by s.cid) sc

  left join 课程 c

    on sc.课程ID = c.cid;

select s.cid 课程ID,c.cname 课程name,max(s.cscore) 最高分,min(s.cscore) 最低分,avg(s.cscore) 平均分 from 成绩表 s left join 课程 c on s.cid = c.cid group by s.cid,c.cname;

--6、按各科平均成绩从低到高顺序排序

select sc.cid,avg(sc.cscore) from 成绩表 sc group by sc.cid order by avg(sc.cscore) asc;

--7、查询学生的总成绩并进行排名

select sc.sid,sum(sc.cscore) from 成绩表 sc group by  sc.sid order by sum(sc.cscore) desc;

--8、检索至少选修两门课程的学生学号

select sid/*,count(sc.cid)*/ from 成绩表 sc group by sc.sid having count(sc.cid) > 1;

--9、查询每门课程被选修的学生数

select sc.cid,count(sc.sid) from 成绩表 sc group by sc.cid;

--10、查询不及格的课程，并按课程号从大到小排列

select sc.cid from 成绩表 sc where sc.cscore < 60 order by sc.cid desc;

select sc.cid,sc.cscore from 成绩表 sc group by sc.cid,sc.cscore having sc.cscore < 60 order by sc.cid desc;

--11、查询出只选修了一门课程的全部学生的学号和姓名

select sc.sid,s.sname from 成绩表 sc left join student s on sc.sid = s.sid group by sc.sid,s.sname having count(sc.cid) = 1;

select sc.sid,s.sname from 成绩表 sc left join student s on sc.sid = s.sid group by sc.sid,s.sname having count(sc.cid) = 1;

select sid,sname from student where sid in(select sid from 成绩表 group by sid having count(sid) = 1);

--12、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；

select s.sid,s.sname from (select sc.sid from 成绩表 sc where sc.cid in ('c01') and sc.cscore > 60) ss left join student s on ss.sid = s.sid;

select s.sid,s.sname from student s where s.sid in (select sc.sid from 成绩表 sc where sc.cid ='c01' and sc.cscore > 60);

--13、查询“01”课程比“02”课程成绩高的所有学生的学号；

select sc1.sid from 成绩表 sc1,成绩表 sc2 where sc1.sid = sc2.sid and (sc1.cid = 'c01' and sc2.cid = 'c02') and sc1.cscore > sc2.cscore;

--14、查询平均成绩大于60分的同学的学号和平均成绩；

select sc.sid,avg(sc.cscore) from 成绩表 sc group by sc.sid  having avg(sc.cscore) > 60;

--15、查询姓“李”的老师的个数；

select count(k.cteacher) from 课程 k where k.cteacher like '%李%';

select count(1) from 课程 k where k.cteacher like '%刘%';

--16、查询没学过“李丽”老师课的同学的学号、姓名；

select cj.sid from 成绩表 cj where cj.cid not in (select k.cid from 课程 k where k.cteacher like '李丽' ) group by cj.sid;

select s.sid,s.sname from student s where s.sid not in (select cj.sid from 成绩表 cj where cj.cid in (select k.cid from 课程 k where k.cteacher like '李丽' ));

select s.sid,s.sname from student s left join (select cj.sid from 成绩表 cj where cj.cid not in (select k.cid from 课程 k where k.cteacher like '李丽' ) group by cj.sid) cj on s.sid = cj.sid;

--17、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；

select s.sid,s.sname from student s where s.sid in (select cj1.sid from 成绩表 cj1,成绩表 cj2 where cj1.sid = cj2.sid and cj1.cid = 'c01' and cj2.cid = 'c02');

--18、查询没学过"李丽"老师讲授的任一门课程的学生姓名

select s.sname from student s where s.sid not in (select distinct(cj.sid) from 成绩表 cj where cj.cid in (select k.cid from 课程 k where k.cteacher like '李丽'));

--19、查询所有同学的学号、姓名、选课数、总成绩

select cj.sid 学号,s.sname 姓名,count(cj.cid) 选课数,sum(cj.cscore) 总成绩 from student s,成绩表 cj where s.sid = cj.sid group by cj.sid,s.sname;

--20、查询学过“韩诊”老师所教的课的同学的学号、姓名；

select s.sid,s.sname from student s where s.sid in( select distinct(cj.sid) from 成绩表 cj where cj.cid in (select k.cid from 课程 k where k.cteacher like '韩诊'));

select sid, sname from student where sid in(select distinct(sc.sid) from 成绩表 sc

    left join 课程 c on sc.cid=c.cid where c.Cteacher = '韩诊') order by sid;

--21、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；

select s.sid,s.sname from student s where s.sid in (

select cj1.sid from 成绩表 cj1,成绩表 cj2 where cj1.sid = cj2.sid and cj1.cid='c01' and cj2.cid='c02' and cj1.cscore < cj2.cscore);

--22、查询所有课程成绩小于60分的同学的学号、姓名；

select sid, sname from student where sid not in( select distinct(sid) from 成绩表 where cscore >= 60) and sid in( select sid from 成绩表 group by sid having count(sid) != 0);

select sid, sname from student where sid not in(select s.sid from student s

    left join 成绩表 sc on s.sid=sc.sid where Cscore > 60 or Cscore is null);

select sid, sname from student where sid in(select sc.sid from 成绩表 sc group by sid having max(sc.Cscore)<60);

---23、查询没有学全所有课的同学的学号、姓名；

select s.sid,s.sname from student s where s.sid not in (select cj.sid from 成绩表 cj group by cj.sid having count(cj.cid) = (select count(*) from 课程));

select s.sid, s.sname from student s left join 成绩表 sc on s.sid=sc.sid group by s.sid,s.sname having count(s.sid) != (select count(1) from 课程);

--24、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；

select s.sid,s.sname from student s where s.sid in (select distinct(cj.sid) from 成绩表 cj where cj.cid in (select cj.cid from 成绩表 cj where cj.sid = 's01') and cj.sid != 's01');

select s.sid,s.sname from student s left join 成绩表 cj1 on s.sid = cj1.sid where cj1.cid in (select cj.cid from 成绩表 cj where cj.sid = 's01') and cj1.sid != 's01' group by  s.sid,s.sname;

--25、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

select s.sid,s.sname from student s left join 成绩表 sc on s.sid = sc.sid where sc.cid in(select cid from 成绩表 where sid = 's01') group by s.sid,s.sname having count(s.sid) = (select count(cid) from 成绩表 where sid='s01') and s.sid != 's01';

--26、把“成绩”表中“刘里”老师教的课的成绩都更改为此课程的平均成绩；

select avg(cj.cscore) from 成绩表 cj where cj.cid in (select kc.cid from 课程 kc where kc.cteacher like '刘里');

select avg(sc.Cscore) from 课程 c left join 成绩表 sc on c.cid = sc.cid where Cteacher = '刘里';

update 成绩表 set Cscore = (select avg(sc.Cscore) from 课程 c left join 成绩表 sc on c.cid = sc.cid where Cteacher = '刘里') where cid in (select cid from 课程 where Cteacher = '刘里');

--27、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.sid,s.sname,avg(sc.cscore) from student s left join 成绩表 sc on s.sid = sc.sid where s.sid in(select sid from 成绩表 sc where Cscore < 60 group by sid having count(sid) >= 2) group by s.sid,s.sname;

--28、检索"01"课程分数小于60，按分数降序排列的学生信息

select cj.sid,cj.cscore,s.sname,s.sage from 成绩表 cj right join student s on cj.sid = s.sid where cj.cid = 'c01' and cj.cscore < 60 order by cj.cscore desc;

select * from student s left join 成绩表 sc on s.sid=sc.sid where sc.Cscore < 60 and sc.cid = 'c01' order by sc.Cscore desc;

--29、查询不同老师所教不同课程平均分从高到低显示

select k.cteacher,k.cname,nvl(avg(cj.cscore),0) from 课程 k left join 成绩表 cj on k.cid = cj.cid group by k.cteacher,k.cname order by nvl(avg(cj.cscore),0) desc;

--30、查询选修“高磊”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select s.sname,cj.cscore from student s left join 成绩表 cj on s.sid = cj.sid where cj.cscore =

(select max(cj.cscore) 成绩 from 成绩表 cj where cj.cid = (select k.cid from 课程 k where k.cteacher = '高磊')) and cj.cid = (select k.cid from 课程 k where k.cteacher = '高磊');