oracle sql语句练习

上一节中，在虚拟机中安装了oracle，现在就来练习一下sql语句。

 练习用到数据表

-----学生表
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)
 );

 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 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',80);
 insert into sc values ('s002','c001',81);
 insert into sc values ('s003','c001',82);
 insert into sc values ('s004','c001',60);
 insert into sc values ('s005','c001',55);
 insert into sc values ('s006','c001',65);
 insert into sc values ('s007','c001',70);
 insert into sc values ('s008','c001',90);
 insert into sc values ('s009','c001',93);
 insert into sc values ('s010','c001',98);
 insert into sc values ('s001','c002',83);
 insert into sc values ('s002','c002',73);
 insert into sc values ('s003','c002',91);
 insert into sc values ('s001','c003','59');
 commit;
 
 INSERT INTO teacher VALUES ('t001','刘阳');
 INSERT INTO teacher VALUES('t002','谭艳'); 
 COMMIT;
 

--1. 查询“c001”课程比“c002”课程成绩高的所有学生的学号

select s1.sno from sc s1 
join sc s2 on s1.sno=s2.sno 
where s1.cno='c001' and s2.cno='c002'; 

--2.查询平均成绩大于60 分的同学的学号和平均成绩，并按照平均成绩从大到小排序

select sno,avg(score) from sc
group by sno
having avg(score)>60
order by avg(score) desc;

--3.统计列印各科成绩,各分数段人数,要求查出课程ID，名称，人数
--例如：课程ID 课程名称 [100-85]  [85-70]  [70-60]  [ <60]
--      C001     英语      2       3      1       0
--      C002     ……      …

select sc.cno course_id,c.cname course_name,
SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "[100 - 85]"
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "[85 - 70]"
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "[70 - 60]"
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS "[60 -0]"
from sc,course c
where sc.cno=c.cno
group by sc.cno,c.cname

--4.查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名。

select distinct student.sno,sname from student
join sc on
student.sno=sc.sno
where cno in (select cno from sc where sno='s001')



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


select cno 课程id,max(score) 最高分,min(score) 最低分 from sc group by cno

--6. 查询全部学生都选修的课程的课程号和课程名

select course.cno,course.cname from course
join sc on course.cno=sc.cno
group by course.cno,course.cname
having count(*)=(select count(*) from student)


-- 7.按各科平均成绩从低到高和及格率的百分数从高到低排序 

select sc.cno as 课程id,avg(sc.score) as 平均成绩,
100*sum(case when sc.score>=60 then 1 else 0 end)/count(1)||'%' as 及格率
from sc
group by sc.cno
order by avg(sc.score) desc;


--8. 查询出所有学生学号，姓名，平均成绩及其名次
select row_number() over(order by avg_score desc),s1.sno,sname,s1.avg_score from student join
(select sno,avg(score) avg_score from sc
group by sno order by avg_score desc) s1 
on s1.sno=student.sno

总结：

• sum用法：使用sum聚合函数配合case when xx then x else xx end 进行统计满足条件时的和。使用sum聚合函数配合case when xx then x else xx end)进行统计当满足条件时的和。如sum(case when sc.score>=60 then 1 else 0 end)/count(1)就是表示当大于60分时加1，否则加0，最后除以总数。
• ||：连字符，用来连接字段
• row_number()：分组函数，用法 ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

请用一条SQL语句查询出scott.emp表中每个部分工资的前三位的数据显示结果如下

--分组排序
--ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
select deptno,sal,row_number() over (partition by deptno order by sal desc) as RN from emp

--decode
----decodedecode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
--如果RN=2,返回sal
select deptno,max(sal) sal1,max(decode(RN,2,sal)) sal2,min(sal) sal3
from
(select * from 
(select deptno,sal,row_number() over(partition by deptno order by sal desc) as RN from emp)
where RN<=3)
group by deptno

select row_number() over(order by avg_score desc) 名次,T1.sno,student.sname,T1.avg_score from system.student join (SELECT sno,AVG(score) AS avg_score 
FROM system.SC GROUP BY sno) T1
on T1.sno=student.sno