OCP题

oracle Certification Program (OCP认证)的题目

  (
1 ) A 表中有100条记录.

  
Select   *   FROM  A  Where  A.COLUMN1  =  A.COLUMN1 

  这个语句返回几条记录? (简单吧,似乎1秒钟就有答案了:)

  (
2 Create  SEQUENCE PEAK_NO 

  
Select  PEAK_NO.NEXTVAL  FROM  DUAL  -- > 假设返回1

  10秒中后,再次做 


  
Select  PEAK_NO.NEXTVAL  FROM  DUAL  -- > 返回多少?

  (
3 ) SQL >  connect sys  as  sysdba

  Connected. 


  SQL
>   insert   into  dual  values  (  ' Y ' );

     
1  row created.

  SQL
>   commit ;

     
Commit  complete.

  SQL
>   select   count ( * from  dual;

     
COUNT ( * )

     
-- --------

     
2

  SQL
>   delete   from  dual; 

     
commit ;

     
-- >DUAL里还剩几条记录?

  JUST TRY IT 
一些高难度的SQL面试题

  以下的null代表真的null,写在这里只是为了让大家看清楚 


根据如下表的查询结果,那么以下语句的结果是(知识点:
not   in/not   exists+ null ) 
SQL
>   select   *   from  usertable; 
USERID           USERNAME 
-- ---------      ---------------- 
      1           user1 
     
2            null  
     
3           user3 
     
4            null  
     
5           user5 
     
6           user6 
      
SQL
>   select   *   from  usergrade; 
USERID         USERNAME           GRADE 
-- --------     ----------------   ---------- 
      1         user1               90  
     
2          null                 80  
     
7         user7               80  
     
8         user8               90  
执行语句: 

select   count ( * from  usergrade  where  username  not   in  ( select  username  from  usertable); 

select   count ( * from  usergrade g  where   not   exists  
(
select   null   from  usertable t  where  t.userid = g.userid  and  t.username = g.username); 

结果为:语句1(  
0  )   语句2  (   3  ) 

A: 
0      B: 1      C: 2      D: 3       E: NULL  


2  

在以下的表的显示结果中,以下语句的执行结果是(知识点:
in/exists+ rownum) 
SQL
>   select   *   from  usertable; 
USERID           USERNAME 
-- ---------      ---------------- 
      1           user1 
     
2           user2 
     
3           user3 
     
4           user4 
     
5           user5 
      
SQL
>   select   *   from  usergrade; 
USERNAME               GRADE 
-- --------------       ---------- 
user9                   90  
user8                  
80  
user7                  
80  
user2                  
90  
user1                  
100  
user1                  
80  

执行语句 
Select   count ( * from  usertable t1  where  username  in  
  (
select  username  from  usergrade t2  where  rownum  <= 1 ); 
  
Select   count ( * from  usertable t1  where   exists  
  (
select   ' x '   from  usergrade t2  where  t1.username = t2.username  and  rownum  <= 1 ); 

以上语句的执行结果是:(  )  (  ) 
  A:   
0         B:    1         C:    2        D:   3  

根据以下的在不同会话与时间点的操作,判断结果是多少,其中时间T1
< T2 < …… < Tn。(知识点:封锁与并发) 
原始表记录为; 
select   *   from  emp; 
EMPNO         DEPTNO       SALARY 
-- ---         ------       ------ 
100              1             55  
101              1             50  

select   *   from  dept; 
DEPTNO           SUM_OF_SALARY 
-- ----           ------------- 
1                  105  
2                  

可以看到,现在因为还没有部门2的员工,所以总薪水为null,现在, 
有两个不同的用户(会话)在不同的时间点(按照特定的时间顺序)执行了一系列的操作,那么在其中或最后的结果为: 

time              session 
1                             session2 
-- ---------      -------------------------------       ----------------------------------- 
T1                insert   into  emp 
                 
values ( 102 , 2 , 60
                  
T2                                                     
update  emp  set  deptno  = 2  
                                                        
where  empno = 100  

T3                                                    
update  dept  set  sum_of_salary  =  
                                                       (
select   sum (salary)  from  emp 
                                                     
where  emp.deptno = dept.deptno) 
                                                     
where  dept.deptno  in ( 1 , 2 ); 
                                                  
T4           
update  dept  set  sum_of_salary  =  
                (
select   sum (salary)  from  emp 
            
where  emp.deptno = dept.deptno) 
            
where  dept.deptno  in ( 1 , 2 ); 
              
T5                                                    
commit

T6                                                    
select   sum (salary)  from  emp  group   by  deptno; 
                                                     问题一:这里会话2的查询结果为:                                                   
T7          
commit
======= 到这里为此,所有事务都已完成,所以以下查询与会话已没有关系 ========  

T8          
select   sum (salary)  from  emp  group   by  deptno; 
            问题二:这里查询结果为 

T9         
select   *   from  dept; 
            问题三:这里查询的结果为 

问题一的结果(  )  问题二的结果是(  )  问题三的结果是(  ) 

A:                        B: 
-- --------------         ---------------- 
1          50                 1          50  
2          60                 2          55  

C:                        D: 
-- --------------         ---------------- 
1          50                 1         115  
2         115                 2          50  

E:                        F: 
-- --------------         ---------------- 
1         105                 1         110  
2          60                 2          55  


有表一的查询结果如下,该表为学生成绩表(知识点:关联更新) 
select  id,grade  from  student_grade 
ID                 GRADE 
-- ------           ----------- 
1                    50  
2                    40  
3                    70  
4                    80  
5                    30  
6                    90  

表二为补考成绩表 
select  id,grade  from  student_makeup 
ID                 GRADE 
-- ------           ----------- 
1                    60  
2                    80  
5                    60  

现在有一个dba通过如下语句把补考成绩更新到成绩表中,并提交: 
update  student_grade s  set  s.grade  =  
(
select  t.grade  from  student_makeup t 
   
where  s.id = t.id); 
commit
请问之后查询: 
select  GRADE  from  student_grade  where  id  =   3 ;结果为: 

A: 
0     B:   70    C:   null    D:  以上都不对 


根据以下的在不同会话与时间点的操作,判断结果是多少, 
其中时间T1
< T2 < …… < Tn。(知识点:DDL与封锁) 

                session1                              session2 
-- ------------------------------------     ---------------------------------------- 
T1          select   count ( * from  t; 
          
-- 显示结果(1000)条 
          
T2         
delete   from  t  where  rownum  <= 100

T3                                          
begin  
                                             
delete   from  t  where  rownum  <= 100 ;   
                                             
commit
                                           
end
                                           
/  
                                              
T4         
truncate   table  t; 

T5         
select   count ( * from  t; 
          
-- 这里显示的结果是多少 

A:  
1000         B:    900      C:    800      D:    0  

 

本文链接:https://my.lmcjl.com/post/6450.html

展开阅读全文

4 评论

留下您的评论.