面试中两个关于数据库的题目,大家试试自己的实力
面试中两个关于数据库的题目,大家试试自己的实力 - Oracle / 开发
用我们大家都熟悉的s_dept表来说明
1)update s_dept set dept_id=10 where id=2;如果我的s_dept表中有十个id=2的记录,这条语句会把这十条记录的dept_id值都改为10,现在我想把这十个dept_id的值依次改为1,2,3.....10,怎么做?(要使用update语句)
2)现在有两个表:表A,表B
A表中有aid,name两个字段
B表中有bid,name两个字段
现在AB中都有多条记录,其中name的值都是一样的,但是顺序不一样,要求查询A中的aid,并且以B表中的name为顺序,显示出来
A表 B表
1 a 1 b
2 b 2 c
3 c 3 a
要求输出结果
2
3
1
------解决方案--------------------
1) update s_dept set dept_id=(select rownum from s_dept where id=2) where id=2
2) 而题没太明白,按要求应该是输出: 3、1、2
------解决方案--------------------
1。
create table s_dept (
id number,
dept_id number,
name varchar2(10));
delete from s_dept;
insert into s_dept values(1,2, 'dasdfasf ');
insert into s_dept values(3,2, 'adsfsafsa ');
insert into s_dept values(2,1, 'tom ');
insert into s_dept values(2,1, 'jerry ');
insert into s_dept values(2,2, 'cat ');
insert into s_dept values(2,2, 'kk ');
insert into s_dept values(2,4, 'ccc ');
update s_dept set dept_id=rownum where id=2
2。
create table a(
aid number,
name varchar2(10));
create table b(
bid number,
name varchar2(10));
insert into a values(1, 'a ');
insert into a values(2, 'b ');
insert into a values(3, 'c ');
insert into b values(1, 'b ');
insert into b values(2, 'c ');
insert into b values(3, 'a ');
select aid from a,b where aid=bid order by b.name;
------解决方案--------------------
用我们大家都熟悉的s_dept表来说明
1)update s_dept set dept_id=10 where id=2;如果我的s_dept表中有十个id=2的记录,这条语句会把这十条记录的dept_id值都改为10,现在我想把这十个dept_id的值依次改为1,2,3.....10,怎么做?(要使用update语句)
2)现在有两个表:表A,表B
A表中有aid,name两个字段
B表中有bid,name两个字段
现在AB中都有多条记录,其中name的值都是一样的,但是顺序不一样,要求查询A中的aid,并且以B表中的name为顺序,显示出来
A表 B表
1 a 1 b
2 b 2 c
3 c 3 a
要求输出结果
2
3
1
------解决方案--------------------
1) update s_dept set dept_id=(select rownum from s_dept where id=2) where id=2
2) 而题没太明白,按要求应该是输出: 3、1、2
------解决方案--------------------
1。
create table s_dept (
id number,
dept_id number,
name varchar2(10));
delete from s_dept;
insert into s_dept values(1,2, 'dasdfasf ');
insert into s_dept values(3,2, 'adsfsafsa ');
insert into s_dept values(2,1, 'tom ');
insert into s_dept values(2,1, 'jerry ');
insert into s_dept values(2,2, 'cat ');
insert into s_dept values(2,2, 'kk ');
insert into s_dept values(2,4, 'ccc ');
update s_dept set dept_id=rownum where id=2
2。
create table a(
aid number,
name varchar2(10));
create table b(
bid number,
name varchar2(10));
insert into a values(1, 'a ');
insert into a values(2, 'b ');
insert into a values(3, 'c ');
insert into b values(1, 'b ');
insert into b values(2, 'c ');
insert into b values(3, 'a ');
select aid from a,b where aid=bid order by b.name;
------解决方案--------------------
- SQL code
[TEST@ora10gr1#2009-11-04/08:37:07] SQL>--第一个 [TEST@ora10gr1#2009-11-04/08:37:07] SQL>create table ta(taid int,id int); Table created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from ta; TAID ID ---------- ---------- 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 10 rows selected. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>update ta set taid = rownum where id = 2; 10 rows updated. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from ta; TAID ID ---------- ---------- 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 2 10 rows selected. [TEST@ora10gr1#2009-11-04/08:37:07] SQL> [TEST@ora10gr1#2009-11-04/08:37:07] SQL>--第一个,同意2楼观点你说的有问题 [TEST@ora10gr1#2009-11-04/08:37:07] SQL>create table a(aid int, name char(1)); Table created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>create table b(bid int, name char(1)); Table created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into a values(1,'a'); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into a values(2,'b'); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into a values(3,'c'); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into b values(1,'b'); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into b values(2,'c'); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into b values(3,'a'); 1 row created. [TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from a; AID N ---------- - 1 a 2 b 3 c [TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from b; BID N ---------- - 1 b 2 c 3 a [TEST@ora10gr1#2009-11-04/08:37:07] SQL>--按照B表bid排序 [TEST@ora10gr1#2009-11-04/08:37:07] SQL>select a.aid from a inner join b on a.name=b.name order by b.bid; AID ---------- 2 3 1 [TEST@ora10gr1#2009-11-04/08:37:07] SQL>--按照B表name排序 [TEST@ora10gr1#2009-11-04/08:37:07] SQL>select a.aid from a inner join b on a.aid=b.bid order by b.name; AID ---------- 3 1 2