Oracle的基本操作

1查询学号在3到6之间的学生的学号，姓名，JAVA成绩，按成绩降序排列，成绩相同时 按学号降序排。

select student.sid ,sname ,sgrade from student, grade
where student.sid=grade.sid
and student.sid>=3 and student.sid<=6
and grade.type='java'
order by sgrade desc, student.sid desc;

	
2查询男生基本信息先按姓名降序排，在按学号降序排

select * from student,grade,lession
where student.sclass=lession.sclass
and student.sid=grade.sid
and lession.type=grade.type
and student.sex='男'
order by student.sname desc, student.sid desc;


3查询学号为1的同学所学的课程和讲课教师

select lession.type, lession.teacher from student,lession
where student.sclass=lession.sclass
and student.sid=1;


4查询姓名为张三的学生的课程名，老师 ，成绩

select lession.type, lession.teacher, sgrade from student,lession,grade
where student.sclass=lession.sclass
and student.sid=grade.sid
and lession.type=grade.type
and student.sname='张三';


5查询student表有多少条数据

select count(*) from student;


6查询1号同学的姓名和总成绩

select sname, sum(sgrade) from student, grade
where student.sid=grade.sid
and student.sid=1
group by sname;

 


7查询一班学生的学号，姓名，总成绩

select student.sid, sname, sum(sgrade) from student, grade
where student.sid=grade.sid
and student.sclass='一班'
group by student.sid, sname;


8查询平均分大于85，总分大于170的学生学号，姓名，平均成绩，总成绩，班级

select student.sid, sname, avg(sgrade), sum(sgrade), sclass from student, grade
where student.sid=grade.sid
having avg(sgrade)>85 and sum(sgrade)>170
group by student.sid, sname, sclass;


9查询3号同学同班同学的学号，姓名

select sid, sname from student
where sclass=
( select sclass from student
where sid=3
)


10查询总成绩比3号同学高的的人的姓名,总成绩

select sname, sum(sgrade) from student, grade
where student.sid=grade.sid
having sum(sgrade)>
(select sum(sgrade) from student, grade
where student.sid=grade.sid
and student.sid=3
)
group by student.sid, sname;




11查询一班总成绩最高的同学的学号，姓名，总成绩

select student.sid, sname, sum(sgrade) from student, grade
where student.sid=grade.sid
and student.sclass='一班'
having sum(sgrade)=
(
select max(sum(sgrade)) from student, grade
where student.sid=grade.sid
group by student.sid, sname
)
group by student.sid, sname;