【Oracle】sql易错题集

/*
下面是一个"学生与课程"的数据库,三个关系模式为：
学生表关系模式S(S#,SNAME,AGE,SEX,MAJOR)
成绩表关系模式SC(S#,C#,SCORE)
课程表关系模式C(C#,CNAME,T#)
教师表关系模式T(T#,TNAME)
其中S#为学号,SNAME为学生名字,AGE为年龄,SEX为性别,MAJOR为专业
C#为课程号,CNAME为课程名,SCORE为成绩,T#为教师号,TNAME为教师名
*/

--1.查询学号的最后一位不是2、3、5的学生情况。
select * from S where S# not like '%2' and S# not like '%3' and S# not like '%5';

--2.统计选修了课程的学生的人数
select count(S#) from SC,C where SC.C# = C.C# group by C# having count(S#) > 0;

--3.统计每个专业的学生人数
select count(S#) from S group by MAJOR;

--4.学习课程号为C2的学生学号与成绩
select S#,SCORE from SC where C# = 'C2';

--5.查询平均成绩大于60分的同学的学号和平均成绩
select S#,avg(SCORE) from SC group by S# having avg(SCORE) > 60;

--6.查询所有同学的学号，姓名，选课数，总成绩
select S.S#,SNAME,count(C#),SUM(SCORE) from S,SC where S.S# = SC.S# group by S.S#,SNAME; 

--7.查询所有课程成绩小于60分的同学的学号，姓名
select S.S#,SNAME from S,SC where S.S# = SC.S# group by S.S#,SNAME having max(SCORE) < 60; 

--8.删除学习'叶平'老师课的SC表记录
1)delete from SC where C# in (select C# from C,T where C.T# = T.T# and TNAME = '叶平');
2)delete from SC where C# in(select C# from C where T# in(select T# from T where Tname = '叶平'));

   注：delete from后面只能跟单个表(基本表/结果表)

--9.查询各科成绩最高和最低的分：以如下形式显示：课程ID, 最高分，最低分
select C# 课程ID,max(SCORE) 最高分,min(SCORE) 最低分 from SC group by C#;

--10.查询学号为10001学生的所有科目的成绩总分
select sum(SCORE) from SC where S# = '10001' group by S#;

--11.查询学生表中存在同一学生，同一科目是否有重复行，并返回该学生的学号、姓名、年龄、专业
select S#,SNAME,AGE,MAJOR from S where S# in (select S# from SC group by S# having count(C#) >1);

/*
书籍表Book，字段BookID，Bookname，Price，PublicID，
出版社表Public，字段PublicID，PublicName，PublicTEL
*/

--12.统计出每个出版社里所有书的平均价格
select avg(Price) from Book group by PublicID;

/*
有两张表: 部门表dept 部门编号deptno 部门名称dname
员工表emp 员工编号empno 员工姓名ename 部门编号deptno 工资sal
*/

--13.列出工资最高的员工姓名
1)select ename from emp where sal = (select max(sal) from emp);
2)select ename from emp group by ename having max(sal) = (select max(sal) from emp);

--14.求每个部门中的最大工资值和最小工资值，并且它的最小值小于5000，最大值大于10000
select max(sal),min(sal) from emp group by deptno having max(sal) > 10000 and min(sal) < 5000;