2018-1-7

面试常考题型：

1、/*使用子查询统计全部学生的高等数学的总成绩*/

/*思路：1、查询出高等数学这门课的编号  2、查询出该编号对应的分数之和*/

select sum(sc.mark) from s_course sc where sc.cno=

(select c.cno from course c where c.cname like '%高等数学%')  

2、/*查询大学英语最高分的学生信息，包括：姓名、年龄、性别、成绩*/

   /*思路：1、查询英语最高分是多少

        2、查询该分数是哪一个同学考的

        3、展现该同学信息*/

select s.sname,

       to_char(sysdate, 'yyyy') - to_char(s.birthday, 'yyyy') age,

       s.sex,

       sc1.mark

  from student s, s_course sc1, course c1

 where s.sno = sc1.sno

   and sc1.cno = c1.cno

   and c1.cname = '大学英语'

   and sc1.mark = (select max(sc.mark)

                     from s_course sc, course c

                    where sc.cno = c.cno

                      and c.cname = '大学英语')

3、/*查询所有成绩都在70以上的学生信息*/

方法1、select *from student s where s.sno not in (

select sc.sno from s_course sc where sc.mark<70);

方法2、select * from student s where s.sno in(

select sc.sno from s_course sc

group by sc.sno

having min(sc.mark)>=70)

4、/*查找至少两门成绩在65以上的学生信息*/

select * from student s where s.sno in (

select sc.sno from s_course sc where sc.mark >=65

group by sc.sno

having  count(*)>=2)

5、/*查询‘java基础’成绩高于'高等数学'的学生信息*/

select * from student s where s.sno in (

select sc.sno from s_course sc,s_course sc1

where sc.sno =sc1.sno

and sc.cno =(select c.cno from course c where c.cname = 'java基础')

and sc1.cno =(select c1.cno from course c1 where c1.cname = '高等数学')

and sc.mark > sc1.mark  ）

 

 

 

解题技巧

1、临时表

   使用with 临时表名 as（sql语句）就可以将sql语句查询出来的结果集存放到一张临时表中，最后再写一条总的sql来查询最终要求的

  例：/*查询个人平均成绩高于总平均成绩的学生信息和平均成绩*/

with t1 as (

    select s.sno,avg(sc.mark) peravg from student s,s_course sc

    where s.sno=sc.sno

    group by s.sno

    ),

    t2 as(

    select avg(sc1.mark) sumavg from s_course sc1

    )

select s2.*,t1.peravg from student s2,t1,t2

where s2.sno=t1.sno

and t1.peravg > t2.sumavg

 

 /*列出女生比男生平均成绩好的课程名单*/

with t1 as(

    select sc.cno,avg(sc.mark) nanavg from student s,s_course sc

    where s.sex='男' and s.sno=sc.sno

    group by sc.cno

    ),

    t2 as(

    select sc1.cno,avg(sc1.mark) nvavg from student s1,s_course sc1

    where s1.sex='女' and s1.sno=sc1.sno

    group by sc1.cno

    ）

select c.cname from course c ,t1,t2

where c.cno=t1.cno

and t1.cno=t2.cno

and t1.nanavg < t2.nvavg

 

/*找出偏科厉害的同学的信息 要求：1存在不及格 2个人均分大于总均分*/

with t2 as(

    select s2.* from student s2,s_course sc2

    where s2.sno=sc2.sno

    and sc2.mark<60

     ),

     t1 as(

     select sc1.sno,avg(sc1.mark) peravg from s_course sc1

     group by sc1.sno

     ),

     t3 as(

     select avg(mark) sumavg from s_course

     )

     select s.* from student s ,t1,t2,t3

     where s.sno=t1.sno

     and s.sno=t2.sno

     and t1.peravg>t3.sumavg

2、数值截取

round（数值，小数位数）——以四舍五入的方式来截取若干位小数

trunc（数值，小数位数）——以直接舍弃的方式来截取若干位小数

例：

     select sc1.sno,round(avg(sc1.mark),1) peravg from s_course sc1

     group by sc1.sno

     select sc1.sno,trunc(avg(sc1.mark),1) peravg from s_course sc1

     group by sc1.sno

3、条件判断

case

   when 条件1 then 结果1

   when 条件2 then 结果2

   ······

else

结果n

end

注意：case语句写在select语句后面

例：/*查询学生的学号、姓名、均分、和均分评价

       如果均分小于60 评价显示差

       如果均分大于等于90 评价显示优秀

       如果均分大于等于80，小于90 评价显示良

       其他情况 评价显示中等*/

       select s.sno,s.sname,round(avg(sc.mark),1) avgmark,

       case 

         when round(avg(sc.mark),1)<60 then '差'

         when round(avg(sc.mark),1)>=90 then '优秀'

         when round(avg(sc.mark),1)>=80 and round(avg(sc.mark),1)<90 then '良'

         else

           '中等'

         end

        from student s,s_course sc

        where s.sno=sc.sno

        group by s.sno,s.sname

4、字符串截取

 从左往右截取：

 substr(‘要截取的字符串’，从第几位开始截取，需要截取多少位)

   例：  select substr('zaijian xu',4,2) from dual

  从右往左截取：

   substr(‘要截取的字符串’，length（该字符串）-要截取的位数-1，需要截取的位数)

   例：select substr('2018-1-7',length('2018-1-7')-4-1,4) from dual

5、视图

   视图可以看做是存储起来的select语句，视图中的数据来源于其背后的那张基表

   视图的作用：1、控制数据的访问权限

2、与基表数据同步

               3、简化查询

  创建视图的格式：

       create view 视图名

       as select语句

  例：  create view teacherview

        as

        select t.tno,t.tname,t.tage,t.birplace from teacher t

        where t.dno =3 

6、视图是否可以增删改操作？

简单视图：单表查询的视图

复杂视图：含有分组函数或者多表查询的试图

对于简单视图可以做增删改操作，对视图的增删改操作实际上是对其背后的那张基表进行增删改操作

而对于复杂视图，则不允许做增删改操作

7、屏蔽简单视图的增删改操作

   在视图的创建语句后增加with read only 即可

   例： create view teacherview1 (编号，姓名，年龄，籍贯)

        as

        select t.tno,t.tname,t.tage,t.birplace from teacher t

        where t.dno =2 

        with read only;

8、删除视图

   drop view 视图名

9、可以像创建视图一样去创建一张表

   格式：create table 表名 as select语句

10、分页查询

1、rownum ——伪列

          记录了表中每一条记录的行号，该行号指的是记录的插入先后顺序

          在取结果集前若干条记录时，针对rownum加条件，需要将rownum写在结果集的外层

  例：      select * from (

            select * from student t

            order by t.birthday desc)

        where rownum <=5