UDN-企业互联网技术人气社区

板块导航

浏览  : 4892
回复  : 0

[面试相关] 简单SQL面试题

[复制链接]
追风少年铁柱的头像 楼主
发表于 2015-6-1 11:47:10 | 显示全部楼层 |阅读模式
一.简单SQL查询:
1):统计每个部门员工的数目
view plaincopy to clipboardprint?
select dept,count(*) from employee group by dept;
select dept,count(*) from employee group by dept;
2):统计每个部门员工的数目大于一个的记录
view plaincopy to clipboardprint?
select dept,count(*) from employee group by dept having count(*)>1;
select dept,count(*) from employee group by dept having count(*)>1;
3):统计工资超过1200的员工所在部门的名称
view plaincopy to clipboardprint?
select e.first_name,salary,d.name
from s_emp e, s_dept d
where e.dept_id = d.id
and salary > 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):查询哪个部门没有员工
view plaincopy to clipboardprint?
select e.empno, d.deptno
from emp e, dept d
where e.deptno(+) = d.deptno
and e.deptno is null;
select e.empno, d.deptno
from emp e, dept d
where e.deptno(+) = d.deptno
and e.deptno is null;

二.复杂SQL查询
有3个表(15分钟):(SQL)
Student 学生表 (学号,姓名,性别,年龄,组织部门)
Course 课程表 (编号,课程名称)
Sc 选课表 (学号,课程编号,成绩)
表结构如下:

1) 写一个SQL语句,查询选修了’JAVA’的学生学号和姓名(3分钟)
答:SQL语句如下:
view plaincopy to clipboardprint?
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”;
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语句如下:
view plaincopy to clipboardprint?
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”;
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语句如下:
view plaincopy to clipboardprint?
select stu.sno, stu.sname
from student stu
where (select count(*) from sc where sno=stu.sno) = 5;
select stu.sno, stu.sname
from student stu
where (select count(*) from sc where sno=stu.sno) = 5;

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

2)通过使用rowid来实现。
view plaincopy to clipboardprint?
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)也可以。
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只能使用
view plaincopy to clipboardprint?
select * from s_emp where rownum
select * from s_emp where rownum

–查询公司工资最高的3个人
/*select * from emp
where rownum
order by sal desc;*/ 错误的
view plaincopy to clipboardprint?
select * from (select * from emp order by sal desc)
where rownum
select * from (select * from emp order by sal desc)
where rownum

五.分页查询:–查询第1-5条记录
view plaincopy to clipboardprint?
select * from (select rownum num, s_emp.* from s_emp)
where num >=1 and num
select * from (select rownum num, s_emp.* from s_emp)
where num >=1 and num

–按工资排序,五条一页,查找第二页
view plaincopy to clipboardprint?
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;
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部