A表:(字段:ID,NAME,ADDRESS,PHONE,LOGDATE);B表:(字段:NAME,ADDRESS,PHONE)

  1. 将表A中的字段LOGDATE中为2001-02-11的数据更新为2003-01-01,请写出相应的SQL语句(该字段类型为日期类型)
  2. 请写出将表中NAME存在重复的记录都列出来的SQL语句(按NAME排序)
  3. 请写出题目2中,只保留重复记录的第一条,删除其余记录的SQL语句(即使表中不存在重复记录)
  4. 请写出将B表中的ADDRESS,PHONE更新到A表中的SQL语句(按NAME相同进行关联)
  5. 请写出将A表中第3-5行数据列出来的SQL语句

---------------------------------------答案:

----创建表

 1 create table test_A(
 2   id number,
 3   name varchar2(10),
 4   address varchar2(10),
 5   phone number(4),
 6   logdate date);
 7 
 8 create table test_B(
 9    name varchar2(10),
10    address varchar2(10),
11    phone number(4));

----插入数据

 1    insert into test_a values(1,'a','bei',2230,'11-2月-2001');
 2    insert into test_a values(2,'b','jing',2231,'11-2月-2002');
 3    insert into test_a values(3,'c','shang',2232,'11-3月-2002');
 4    insert into test_a values(4,'c','hai',2233,'13-3月-2002');
 5    insert into test_a values(5,'d','guang',2234,'12-2月-2001');
 6    commit;
 7 
 8    insert into test_b values('c','shen',2240);
 9    insert into test_b values('d','shen',2241);
10    commit;--提交
11    select * from test_a;--查询数据
12    truncate table test_a;--删除数据
  1. 将表A中的字段LOGDATE中为2001-02-11的数据更新为2003-01-01,请写出相应的SQL语句(该字段类型为日期类型)
    1 update test_a 
    2 set logdate = to_date('2003-01-01','yyyy-MM-dd') 
    3 where logdate = to_date('2001-02-11','yyyy-MM-dd');
    4 commit;
    5 select * from test_a;--查询数据
  2. 请写出将表中NAME存在重复的记录都列出来的SQL语句(按NAME排序)
    1 select * from (
    2        select a.name 
    3        from test_a a 
    4        group by a.name 
    5        having count(*)>1 
    6        order by name);
  3. 请写出题目2中,只保留重复记录的第一条,删除其余记录的SQL语句(即使表中不存在重复记录)
     1 delete test_a a 
     2 where a.name <> (
     3   select * from (
     4    select a.name 
     5    from test_a a 
     6    group by a.name 
     7    having count(*)>1 
     8    order by name));
     9    commit;
    10    select * from test_a;--查询数据
  4. 请写出将B表中的ADDRESS,PHONE更新到A表中的SQL语句(按NAME相同进行关联)
     1 update test_a a
     2 set a.address = decode((select distinct b.address 
     3                 from test_b b 
     4                 where a.name = b.name),null,a.address,(select distinct b.address 
     5                 from test_b b 
     6                 where a.name = b.name))
     7    ,a.phone = decode((select distinct b2.phone 
     8                 from test_b b2 
     9                 where a.name = b2.name),null,a.phone,(select distinct b2.phone 
    10                 from test_b b2 
    11                 where a.name = b2.name));
    12     commit;--提交
    13     select * from test_a;--查询数据
  5. 请写出将A表中第3-5行数据列出来的SQL语句
    1 select b.id,b.name,b.address,b.phone,b.logdate
    2 from (select rownum rn,id,name,address,phone,logdate 
    3     from test_a) b 
    4 where b.rn between 3 and 5;

-------------------------欢迎建议更优秀的代码

 posted on 2015-08-28 17:33  码农xk  阅读(344)  评论(0编辑  收藏  举报