整理的oracle实现的基础sql语句查询

/*
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--difficult point：
--the use of "case field when condition then result else result end" 
--aggregate function combine group function .
--the use of group function with one more conditions.
-- the use of left join , the request is query all student , even though she/he has no any course.
--cast(expression as charactertype )
SELECT t1.sno 学生编号,t1.sname 学生姓名,
MAX(CASE t3.cname WHEN N'语文' THEN t2.score ELSE NULL END) 语文,
MAX(CASE t3.cname WHEN N'数学' THEN t2.score ELSE NULL END) 数学,
MAX(CASE t3.cname WHEN N'英语' THEN t2.score ELSE NULL END) 英语,
CAST(avg(t2.score) AS DECIMAL(18,2)) 平均分
FROM
student t1 
LEFT JOIN
sc t2
ON
t1.sno = t2.sno
LEFT JOIN
course t3
ON
t2.cno = t3.cno
GROUP BY 
t1.sno,t1.sname
ORDER BY 平均分 DESC
*/
-------------------------------------------------------------------------------------------------------------------------------------
/*
--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--difficult point:
--1,cast function cast(expression as characate type )
--2,"/" should keep up with the end of previous expression or the start of next expression 
--3,one more table combine,,,
select b.cno as 课程ID , b.cname as 课程name , max(a.score) 最高分, min (a.score) 最低分 ,cast(avg(a.score) as decimal(18,2)) 平均分 ,
cast(
       (select count(1) from sc where cno = b.cno and score >= 60)*100/
       (select count(1) from sc where cno = b.cno)
       as  decimal(18,2) 
    ) 及格率,
cast(
       (select count(1) from sc where cno = b.cno and score > 70 and score <= 80)*100/
       (select count(1) from sc where cno = b.cno)
       as  decimal(18,2) 
    )中等率,
cast(
       (select count(1) from sc where cno = b.cno and score > 80 and score <= 90)*100/
       (select count(1) from sc where cno = b.cno)
       as  decimal(18,2) 
    ) 优良率,
cast(
       (select count(1) from sc where cno = b.cno and score > 90)*100/
       (select count(1) from sc where cno = b.cno)
       as  decimal(18,2) 
    ) 优秀率               
from
sc a, course b
where a.cno = b.cno
group by 
b.cno,b.cname
*/
/*


--------------------------------------------------------------------------------------------------------------------------------
--21、查询不同老师所教不同课程平均分从高到低显示
select (select t4.cname from course t4 where t4.tno = t2.tno) subject,t2.tname,cast(avg(t3.score) as decimal(18,2)) avg_score
from course t1,teacher t2,sc t3
where t1.tno = t2.tno and t1.cno = t3.cno
group by
t2.tno,t2.tname
order by avg_score desc


--------------------------------------------------------------------------------------------------------------------------------
--28、查询男生、女生人数 
select 
      sum(
         case when t.ssex = N'男' then 1 else 0 end 
      ) 男生人数,
      sum(
        case when t.ssex = N'女' then 1 else 0 end
      ) 女生人数
from student t 


--------------------------------------------------------------------------------------------------------------------------------
--29、查询名字中含有"风"字的学生信息
select t.* from student t where t.sname like N'%风%'


--we should choose the right function when we used different database.
--oracle-inster(expression,condition) | sql server charindex(condition,expression)
select t.* from student t where instr (t.sname , N'风') > 0
select t.* from student t where charindex( N'风',t.sname) > 0


--------------------------------------------------------------------------------------------------------------------------------
--30、查询同名同性学生名单，并统计同名人数 
select t.sname 姓名 ,t.ssex 性别, count(*) 人数 from student t
group by t.sname ,t.ssex having count(*) > 1


--------------------------------------------------------------------------------------------------------------------------------
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) 
select * from student t where substr (to_char(t.sage,'yyyy-mm-dd'),0,4) = '1990'
--difficult point the use of extract function!
select t.*  from student t where extract(year from t.sage) = 1990;
--get the current year | month | day from system use sql
select to_char(sysdate , 'yyyy') from dual
select to_char(sysdate , 'mm') from dual
select to_char(sysdate , 'dd') from dual


--------------------------------------------------------------------------------------------------------------------------------
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select  b.cno , b.cname, 
cast (
     avg(a.score) as decimal(18,2)
) 平均成绩 
from
sc a , course b
where a.cno = b.cno
group by 
b.cno ,b.cname 
order by 
平均成绩 desc,
b.cno


--------------------------------------------------------------------------------------------------------------------------------
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
--difficult point : the result columns alias could not be used for conditions
select a.sno , a.sname ,
cast(avg(b.score) as decimal(18,2)) avg_score
from 
student a left join sc b on a.sno = b.sno 
group by 
a.sno ,a.sname 
having 
cast(avg(b.score) as decimal(18,2)) > 85
order by 
a.sno


--------------------------------------------------------------------------------------------------------------------------------
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
--i can feel my ablity about sql query is improving intensely!even though the exercise is very fundamental
select a.sname , b.score , c.cname
from student a , sc b , course c
where  a.sno = b.sno and b.cno = c.cno 
and c.cname = N'数学' and b.score < 60


--------------------------------------------------------------------------------------------------------------------------------
--35、查询所有学生的课程及分数情况；
-- so easy and simple
select a.sno , a.sname ,b.cno , b.score ,c.cname
from student a 
left join sc b on a.sno = b.sno
left join course c on b.cno = c.cno
order by a.sno


--------------------------------------------------------------------------------------------------------------------------------
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
--1,show all result 
select a.sno , a.sname ,b.cno , b.score ,c.cname
from student a , sc b , course c 
where a.sno = b.sno and b.cno = c.cno and b.score > 70
order by a.sno
--2,just show name,supreme score
select a.sno , a.sname  , max(b.score) supreme_score 
from student a , sc b , course c 
where a.sno = b.sno and b.cno = c.cno
group by a.sno ,a.sname having max(b.score) > 70
order by a.sno


--------------------------------------------------------------------------------------------------------------------------------
--37、查询有不及格的课程的学生的信息及不及格课程名及分数
select a.sno , a.sname ,b.cno , b.score ,c.cname
from student a , sc b , course c 
where a.sno = b.sno and b.cno = c.cno
and b.score < 60


--------------------------------------------------------------------------------------------------------------------------------
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select a.sno , a.sname ,b.cno , b.score ,c.cname
from student a , sc b , course c 
where a.sno = b.sno and b.cno = c.cno
and b.score >= 80  and c.cno = '01' 


--------------------------------------------------------------------------------------------------------------------------------
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
--40.1 当最高分只有一个时
--difficult point: i could not use top function to get the last result 
--but all roads lead to Rome ,just change my think methods to another one 
select sno , sname ,cno , score ,cname ,rownum 
from
(
      select a.sno , a.sname ,b.cno , b.score ,c.cname 
      from student a , sc b , course c ,teacher d
      where a.sno = b.sno and b.cno = c.cno and c.tno = d.tno
      and d.tname = N'张三' order by b.score desc  
)where rownum < 2
--40.2 当最高分出现多个时
select a.sno , a.sname ,b.cno , b.score ,c.cname 
from student a , sc b , course c ,teacher d
where a.sno = b.sno and b.cno = c.cno and c.tno = d.tno
and d.tname = N'张三' and b.score = 
(
      select max(b.score) 
      from student a , sc b , course c ,teacher d
      where a.sno = b.sno and b.cno = c.cno and c.tno = d.tno
      and d.tname = N'张三'    
)


--------------------------------------------------------------------------------------------------------------------------------
--42、查询每门课程成绩最好的前两名 
--difficult point: i can not resolve it!
select t1.sno,t1.cno from sc t1 where t1.score in
(select score from sc where sc.cno = t1.cno order by score)


--------------------------------------------------------------------------------------------------------------------------------
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
select t.cno, count(t.sno) elective_subject from sc t group by t.cno having count(t.sno) > 5 order by elective_subject desc ,t.cno 


select Course.Cno , Course.Cname , count(*) 学生人数
from Course , SC 
where Course.Cno = SC.Cno
group
by Course.Cno , Course.Cname
having
count(*) >=
5
order
by
学生人数
desc , Course.Cno 


--------------------------------------------------------------------------------------------------------------------------------
--44、检索至少选修两门课程的学生学号
select t1.sno,t.sname,t.sage, t.ssex from sc t1,student t where t1.sno = t.sno group by t1.sno,t.sname,t.sage, t.ssex
having count(t1.cno) > 1 order by t1.sno


--------------------------------------------------------------------------------------------------------------------------------
--45、查询选修了全部课程的学生信息 
select t1.sno,t.sname,t.sage, t.ssex from sc t1,student t where t1.sno = t.sno group by t1.sno,t.sname,t.sage, t.ssex
having count(t1.cno) >= (select count(1) from course) order by t1.sno


--46、查询各学生的年龄
select t.sno ,t.sname ,extract (year from sysdate ) - extract( year from t.sage) age , t. ssex from student t 
*/
--48、查询下周过生日的学生