Oracle面试题目
一.简单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;
二. 在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 salary,first_name
from(
select s.*, rownum rm
from (select *
from s_emp
order by salary d
) s
)
where rm between 6 and 10
2. 有3个表(15分钟):(SQL)
Student 学生表 (学号,姓名,性别,年龄,组织部门)
Course 课程表 (编号,课程名称)
Sc 选课表 (学号,课程编号,成绩)
表结构如下:
1) 写一个SQL语句,查询选修了’计算机原理’的学生学号和姓名(3分钟)
答:SQL语句如下:
select stu.sno, stu.sname from Student stu
where (select count(*) from sc where sno=stu.sno and cno =
(select cno from Course where cname='计算机原理')) != 0;
2) 写一个SQL语句,查询’周星驰’同学选修了的课程名字(3分钟)
答:SQL语句如下:
select cname from Course where cno in (select cno from sc where sno=(select sno from Student where sname='周星驰'));
3) 写一个SQL语句,查询选修了5门课程的学生学号和姓名(9分钟)
答:SQL语句如下:
select stu.sno, stu.sname from student stu
where (select count(*) from sc where sno=stu.sno) = 5;