Oracle DML SQL题目篇2

--创建学生表CREATE TABLE student   (      s#    number,   --学生编号     sname varchar2(32), --学生姓名     sage  number, --学生年龄     ssex  varchar2(8) --学生性别  );--创建课程表 CREATE TABLE course   (      c#    number,  --课程编号     cname varchar2(32), --课程姓名     t#         number--老师编号  );--创建成绩表 CREATE TABLE sc   (      s#    number, --学生编号     c#    number, --课程编号     score number--成绩  );  --创建教师表CREATE TABLE teacher   (      t#    number, --老师编号     tname varchar2(16) --老师姓名  );   insert into Student select 1,'刘一',18,'男'  from dual union all select 2,'钱二',19,'女'  from dual union all select 3,'张三',17,'男'  from dual union all select 4,'李四',18,'女'  from dual union all select 5,'王五',17,'男'  from dual union all select 6,'赵六',19,'女' from dual;  insert into Teacher select 1,'叶平'  from dual union all select 2,'贺高'  from dual union all select 3,'杨艳'  from dual union all select 4,'周磊'from dual;   insert into Course select 1,'语文',1  from dual union all select 2,'数学',2  from dual union all select 3,'英语',3  from dual union all select 4,'物理',4 from dual;  insert into SC  select 1,1,56  from dual union all  select 1,2,78  from dual union all  select 1,3,67  from dual union all  select 1,4,58  from dual union all  select 2,1,79  from dual union all  select 2,2,81  from dual union all  select 2,3,92  from dual union all  select 2,4,68  from dual union all  select 3,1,91  from dual union all  select 3,2,47  from dual union all  select 3,3,88  from dual union all  select 3,4,56  from dual union all  select 4,2,88  from dual union all  select 4,3,90  from dual union all  select 4,4,93  from dual union all  select 5,1,46  from dual union all  select 5,3,78  from dual union all  select 5,4,53  from dual union all  select 6,1,35  from dual union all  select 6,2,68  from dual union all  select 6,4,71 from dual; select * from sc; select * from course; select * from student; select * from teacher; --natural join 自然连接 select * from course natural join sc natural join student natural join teacher; --inner join .. on .. 内联接 select * from student s inner join sc sc on s.s# = sc.s# inner join course c on sc.c# = c.c# inner join teacher t on c.t# = t.t#;--1.查询“语文”课程比“数学”课程成绩高的所有学生的学号  --语文、数学课程的编号  select c# from course where cname='语文';  select c# from course where cname='数学';  --语文成绩高于数学成绩的同学  select st.sname 姓名,st.sage 年龄,st.ssex 性别,sc1.score 语文成绩,sc2.score 数学成绩 from sc sc1,sc sc2,student st   where sc1.s#=sc2.s# and sc1.s#=st.s#   and sc1.c#=(select c# from course where cname='语文') and sc2.c#=(select c# from course where cname='数学')  and sc1.score<=sc2.score; --2.查询平均分大于60的学生的学号和平均分  select s#,round(avg(score),1) from course c natural join sc natural join student natural join teacher group by s# having avg(score) > 60;--3.查询所有学生的学号，姓名，选课数，总分  select sname,s#,count(sname),sum(score) from course c natural join sc natural join student natural join teacher group by sname,s#;--4.查询姓叶的老师的个数  select count(rowid) from teacher where tname like '叶%';--5.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；  --取交集  (select s#,sname from course c natural join sc natural join student natural join teacher where cname = '语文')  intersect  (select s#,sname from course c natural join sc natural join student natural join teacher where cname = '数学');  --拼接两张成绩表  select * from student st,sc sc1,sc sc2 where st.s#=sc1.s# and sc1.s#=sc2.s#   and sc1.c#=(select c# from course where cname='语文') and sc2.c#=(select c# from course where cname='数学');--6.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；  select st.s#,st.sname from sc s,sc s1,student st where st.s# = s.s# and s.s# = s1.s# and s.c# = '1' and s1.c# = '2' and s.score>s1.score;--7.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分  select cname 科目,c# 课程ID,max(score) 最高分,min(score)最低分 from course c natural join sc natural join student natural join teacher group by cname,c#;--8.查询不同老师所教不同课程平均分从高到低显示  select tname,cname,round(avg(score),1) from course c natural join sc natural join student natural join teacher group by tname,cname order by avg(score) desc--9.统计列印各科成绩,各分数段人数:       --课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]  select c.c#,c.cname,    (select count(s.s#) from sc s where s.c# = c.c# and s.score between 86 and 100) as 分数100到85,    (select count(s.s#) from sc s where s.c# = c.c# and s.score between 71 and 85 ) as 分数85到70,    (select count(s.s#) from sc s where s.c# = c.c# and s.score between 60 and 70 ) as 分数70到60,    (select count(s.s#) from sc s where s.c# = c.c# and s.score < 60 ) as 分数小于60   from course c;--10.查询出只选修了一门课程的全部学生的学号和姓名   select s#,sname,count(sname) from course c natural join sc natural join student natural join teacher group by sname,s# having count(sname) = 1;--11.查询没学过“叶平”老师课的同学的学号、姓名；  --叶平每教过的同学  SELECT * FROM student WHERE sid NOT IN (SELECT sid FROM sc INNER JOIN course c ON sc.cid=c.cid INNER JOIN teacher t ON c.tid=t.tid WHERE t.tname='叶平');   --取差集  (select s#,sname from course c natural join sc natural join student natural join teacher group by s#,sname)  minus   (select s#,sname from course c natural join sc natural join student natural join teacher where tname = '叶平' group by sname,s#);--12.查询学过“叶平”老师所教的所有课的同学的学号、姓名；  SELECT st.sname,st.sid,COUNT(*) FROM student st INNER JOIN sc sc ON st.sid=sc.sid INNER JOIN course c ON sc.cid=c.cid INNER JOIN teacher t ON c.tid=t.tid WHERE tname='叶平' GROUP BY sname,sid   HAVING COUNT(*)=(SELECT COUNT(*) FROM sc sc1 INNER JOIN course c1 ON sc1.cid=c1.cid INNER JOIN teacher t1 ON c1.tid=t1.tid WHERE tname='叶平' AND st.sid=sc1.sid); --13.查询所有课程成绩小于60分的同学的学号、姓名；  select s#,sname from  course c natural join sc natural join student natural join teacher group by s#,sname having max(score) < 60;--14.查询没有学全所有课的同学的学号、姓名；  --全部课程数  select count(rowid) from course;  --学生选修课程少于总课程数  select s#,sname,count(s#) from course c natural join sc natural join student natural join teacher group by sname,s# having count(s#) < (select count(rowid) from course);--15.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；  select s# 学号,sname 姓名 from course c natural join sc natural join student where s# <> 1   and cname in (select cname from course c natural join sc natural join student where s# = 1)  group by sname,s#;--16.查询与学号为“6”同学所学课门数相同的其他同学学号和姓名；  --6号同学所学课程数  select count(rowid) from sc where s#=6;  --课程数目与6号同学相同  select s#,sname,count(sc.rowid) from sc natural join student where s#<>6 group by s#,sname having count(sc.rowid)=(select count(rowid) from sc where s#=6);--17.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；  select s.s#,sname,count(*) from student s inner join sc on s.s#=sc.s# where sc.s# != 1 group by s.s#,sname   having count(sc.c#) in (select count(*) from course c inner join sc on c.c#=sc.c# and sc.s# = 1 ); --18.按各科平均成绩从低到高和及格率的百分数从高到低顺序  select s1.cname,grade 平均成绩,s2.c2/s1.c1*100||'%' 及格率 from        (select count(cname) c1,cname,avg(score) grade from sc inner join course c on sc.c#=c.c# group by cname ) s1 inner join       (select cname,count(cname) c2 from sc inner join course c on sc.c#=c.c# and sc.score>60 group by cname ) s2  on s1.cname=s2.cname order by 平均成绩 asc,s2.c2/s1.c1 desc;--19.查询每门功成绩最好的前两名   select c#,c.cname,(select max(score) from sc where c.c#=c#),(select max(score) from sc where c.c#=c# and score not in (select max(score) from sc where c.c#=c#))  from course c;