- 相關(guān)推薦
oracle數(shù)據(jù)庫(kù)面試題
oracle Certification Program (OCP認(rèn)證)的面試題目
(1) A 表中有100條記錄.
Select * FROM A Where A.COLUMN1 = A.COLUMN1
這個(gè)語(yǔ)句返回幾條記錄? (簡(jiǎn)單吧,似乎1秒鐘就有答案了:) 100條
(2) Create SEQUENCE PEAK_NO
Select PEAK_NO.NEXTVAL FROM DUAL --> 假設(shè)返回1
10秒中后,再次做
Select PEAK_NO.NEXTVAL FROM DUAL --> 返回多少? 2
(3) SQL> connect sys as sysdba
Connected.
SQL> into dual values ( Y);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> from dual;
commit;
-->DUAL里還剩幾條記錄? 1條
JUST TRY IT
一些高難度的SQL面試題
以下的null代表真的null，寫(xiě)在這里只是為了讓大家看清楚
根據(jù)如下表的查詢結(jié)果，那么以下語(yǔ)句的結(jié)果是(知識(shí)點(diǎn)：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
執(zhí)行語(yǔ)句：
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);
結(jié)果為：語(yǔ)句1( 0 ) 語(yǔ)句2 ( 3 )
A: 0 B:1 C:2 D:3 E:NULL
2
在以下的表的顯示結(jié)果中，以下語(yǔ)句的執(zhí)行結(jié)果是(知識(shí)點(diǎn)：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
執(zhí)行語(yǔ)句
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);
以上語(yǔ)句的執(zhí)行結(jié)果是：( ) ( )
A: 0 B: 1 C: 2 D: 3
根據(jù)以下的在不同會(huì)話與時(shí)間點(diǎn)的操作，判斷結(jié)果是多少，其中時(shí)間T1原始表記錄為;
select * from emp;
EMPNO DEPTNO SALARY
----- ------ ------
100 1 55
101 1 50
select * from dept;
DEPTNO SUM_OF_SALARY
------ -------------
1 105
2
可以看到，現(xiàn)在因?yàn)檫�沒(méi)有部門(mén)2的員工，所以總薪水為null，現(xiàn)在，
有兩個(gè)不同的用戶(會(huì)話)在不同的時(shí)間點(diǎn)(按照特定的時(shí)間順序)執(zhí)行了一系列的操作，那么在其中或最后的結(jié)果為：
time session 1 session2
----------- ------------------------------- -----------------------------------
T1 into emp
values(102,2,60)
T2 emp set deptno =2
where empno=100
T3 dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T4 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;
問(wèn)題一：這里會(huì)話2的查詢結(jié)果為：
T7 commit;
=======到這里為此，所有事務(wù)都已完成，所以以下查詢與會(huì)話已沒(méi)有關(guān)系========
T8 select sum(salary) from emp group by deptno;
問(wèn)題二：這里查詢結(jié)果為
T9 select * from dept;
問(wèn)題三：這里查詢的結(jié)果為
問(wèn)題一的結(jié)果( ) 問(wèn)題二的結(jié)果是( ) 問(wèn)題三的結(jié)果是( )
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
有表一的查詢結(jié)果如下，該表為學(xué)生成績(jī)表(知識(shí)點(diǎn)：關(guān)聯(lián)更新)
select id,grade from student_grade
ID GRADE
-------- -----------
1 50
2 40
3 70
4 80
5 30
6 90
表二為補(bǔ)考成績(jī)表
select id,grade from student_makeup
ID GRADE
-------- -----------
1 60
2 80
5 60
現(xiàn)在有一個(gè)dba通過(guò)如下語(yǔ)句把補(bǔ)考成績(jī)更新到成績(jī)表中，并提交：
student_grade s set s.grade =
(select t.grade from student_makeup t
where s.id=t.id);
commit;
請(qǐng)問(wèn)之后查詢：
select GRADE from student_grade where id = 3;結(jié)果為：
A: 0 B: 70 C: null D: 以上都不對(duì)
根據(jù)以下的在不同會(huì)話與時(shí)間點(diǎn)的操作，判斷結(jié)果是多少，
其中時(shí)間T1
session1 session2
-------------------------------------- ----------------------------------------
T1 select count(*) from t;
--顯示結(jié)果(1000)條
T2 from t where rownum <=100;
T3 begin
from t where rownum <=100;
commit;
end;
/
T4 truncate table t;
T5 select count(*) from t;
--這里顯示的結(jié)果是多少
A: 1000 B: 900 C: 800 D: 0
【oracle數(shù)據(jù)庫(kù)面試題】相關(guān)文章：
數(shù)據(jù)庫(kù)常見(jiàn)筆試面試題11-11
Oracle認(rèn)證11-14
oracle 技術(shù)筆試題02-18
Oracle筆試，攢RP中02-18
Oracle筆試，分享筆試內(nèi)容11-21
面試題精選02-18
益和電力Oracle筆試題分享11-21
分享面試題目 教育職業(yè)面試題11-20