oracle sql面试题2

一．简单SQL查询：
1）:统计每个部门员工的数目
select dept,count(*) from employee group by dept;
2）:统计每个部门员工的数目大于一个的记录
select dept,count(*) from employee group by dept having count(*)>1;
3）:统计工资超过1200的员工所在部门的名称
select e.first_name,salary,d.name
from s_emp e, s_dept d
where e.dept_id = d.id
and salary > 1200;
4）:查询哪个部门没有员工
select e.empno, d.deptno
from emp e, dept d
where e.deptno(+) = d.deptno
and e.deptno is null;

 

or

 

select *

from dept d

where not exists(select 1 from emp e

                            where e.deptno = d.deptno

                           );

 

 

二．复杂SQL查询
有3个表（15分钟）：(SQL)
Student 学生表 (学号，姓名，性别，年龄，组织部门)
Course 课程表 (编号，课程名称)
Sc 选课表 (学号，课程编号，成绩)
表结构如下：
 
1)    写一个SQL语句，查询选修了’JAVA’的学生学号和姓名（3分钟）
答：SQL语句如下：
select stu.sno, stu.sname
from student stu, course c, sc
where stu.sno = sc.sno
and sc.cno = c.cno
and c.cname=’JAVA’;
2)    写一个SQL语句，查询’a’同学选修了的课程名字（3分钟）
答：SQL语句如下：
select stu.sname, c.cname
from student stu, course c, sc
where stu.sno = sc.sno
and sc.cno = c.cno
and stu.sname = ’a’;
3)    写一个SQL语句，查询选修了5门课程的学生学号和姓名（9分钟）
答：SQL语句如下：
select stu.sno, stu.sname
from student stu
where (select count(*) from sc where sno=stu.sno) = 5;

三. 在SQL中删除重复记录的方法:（用到rowid (oracle伪列)）
1）通过建立临时表来实现
SQL>create table temp_emp as (select distinct * from employee) 
SQL>truncate table employee; (清空employee表的数据）
SQL>rename temp_emp to employee; (再将表重命名）

2）通过使用rowid来实现。
SQL>delete from employee where rowid not in (
        select max(t1.rowid) from employee t1 group by
        t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。

四. TOP N问题：（用到rownum (oracle伪列)）
 --rownum只能使用<=或<的关系比较运算符
 select * from s_emp where rownum <= 2;

 --查询公司工资最高的3个人
 /*select * from emp
 where rownum <= 3
order by sal desc;*/ 错误的
select * from (select *

                      from emp

                      order by sal desc)
  where rownum <= 3;

五.分页查询：
  --查询第1-5条记录
   select * from (select rownum num,

                                    s_emp.*

                         from s_emp)
   where num >=1 and num <= 5;

 

--按工资排序，五条一页,查找第二页

select ename,sal
from ( select s.* , rownum rm
           from emp s
           order by sal desc
        )
where rm between 6 and 10