日常面试简单SQL整理分享

日常面试简单SQL整理分享

日常工作中需要执行一些SQL，本人从建表语句写起，直观显示运行结果，以供参考，后期会陆续完善…

一、建表
常用案例：STUDENT 学生表、SCORE 成绩表、COURSE 课程表
create table STUDENT
(
ID NUMBER not null,
NAME VARCHAR2(10) not null,
SEX VARCHAR2(5) not null,
CLASSID NUMBER not null,
SAD VARCHAR2(10),
“courseID” NUMBER
)

插入测试数据：

create table SCORE
(
ID NUMBER not null,
STU_ID NUMBER not null,
SCORE NUMBER,
“courseID” NUMBER not null
)

create table COURSE
(
“courseID” NUMBER not null,
COURSENAME VARCHAR2(10)
)

二、SQL语句
1、查询课程1001成绩大于课程1002的学生信息,课程明细；
语句：select s.ID,s.NAME,a1.SCORE,a2.SCORE,c.COURSENAME from SCORE a1,SCORE a2,STUDENT s,COURSE c where a1.STU_ID=a2.STU_ID
and a1.STU_ID=s.ID and s.“courseID”=c.“courseID”
and a1.“courseID”=1001 and a2.“courseID”=1002 and a1.SCORE>a2.SCORE;

运行结果：

2、查询平均分大于60的学生信息,按成绩排序；
语句：select
s.ID,s.NAME, avg(a.SCORE) from SCORE a,STUDENT s where a.STU_ID=s.ID
group by s.ID, s.NAME having avg(a.SCORE)>=60
order by avg(a.SCORE);

运行结果：

3、查询高于平均分的学生信息
语句：select * from STUDENT s,SCORE s1
where s1.SCORE>=(select avg(s2.SCORE) from SCORE s2
where s2.“courseID”=1002)
and s1.“courseID”=1002
and s.ID=s1.STU_ID;

运行结果：

4、查询成绩为优良级别（＞80）的学生信息；
语句：select
s1.NAME,s2.SCORE,c1.COURSENAME from STUDENT s1,SCORE s2,COURSE c1
where s1.ID=s2.STU_ID and s2.“courseID”=c1.“courseID”
group by s1.NAME, s2.SCORE, c1.COURSENAME
having min(s2.SCORE) >80;

执行结果：

5、查询学生总成绩和选课数
语句：select
s.ID,s.NAME,sum(s1.SCORE),count(c.“courseID”) from STUDENT s,SCORE s1,COURSE c where s.ID=s1.STU_ID
and c.“courseID”=s1.“courseID”
group by s.ID, s.NAME
order by s.ID;

运行结果：

6、查询学生所有课程成绩并按总成绩降序排列
语句：select
s1.NAME, c4.cs1 java成绩,c4.cs2 sql成绩,c4.cs 总成绩 from
(select c1.STU_ID,c1.SCORE cs1,c2.SCORE cs2,c3.sum cs from
(select s3.STU_ID,s3.SCORE from SCORE s3
where s3.“courseID”=1001) c1
left join
(select s4.STU_ID,s4.SCORE from SCORE s4
where s4.“courseID”=1002 ) c2
on c1.STU_ID=c2.STU_ID
left join
(select s5.STU_ID,sum(s5.SCORE) sum from SCORE s5
group by s5.STU_ID) c3
on c2.STU_ID=c3.STU_ID order by c3.sum desc ) c4
left join STUDENT s1 on s1.ID=c4.STU_ID

执行结果

未完待续Continuing…