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 评论