Multiple-Choice Questions About Developer Oracle

Question: 1

How deep of oracle group functions can be nested?EX: max(AVG(quantity))

Answer

2

5

3

Infinite!

Question: 2

What is the result of below SQL statement?

SELECT TO_CHAR(123456789, ‘$99,999’) FROM DUAL;

Answer

$12,345

########

12,345

12,345,678

$12,345,678

Question: 3

Which option ‘s SQL are the same result with below SQL?
 SELECT M.EMPLOYEE_ID, M.LAST_NAME, M.ADDR, R.MANAGER_ID
FROM EMPLOYEE_M M, EMPLOYEE_R R
WHERE M.EMPLOYEE_ID = R.EMPLOYEE_ID(+);

Answer

SELECT M.EMPLOYEE_ID, M.LAST_NAME, M.ADDR, R.MANAGER_ID
FROM EMPLOYEE_M M RIGHT OUTER JOIN  EMPLOYEE_R R
USING (EMPLOYEE_ID);

SELECT EMPLOYEE_ID, M.LAST_NAME, M.ADDR, R.MANAGER_ID
FROM EMPLOYEE_M M RIGHT OUTER JOIN  EMPLOYEE_R R
ON (M.EMPLOYEE_ID = R.EMPLOYEE_ID);

SELECT EMPLOYEE_ID, M.LAST_NAME, M.ADDR, R.MANAGER_ID
FROM EMPLOYEE_M M LEFT OUTER JOIN  EMPLOYEE_R R
USING (EMPLOYEE_ID);

SELECT M.EMPLOYEE_ID, M.LAST_NAME, M.ADDR, R.MANAGER_ID
FROM EMPLOYEE_M M LEFT OUTER JOIN  EMPLOYEE_R R
ON (M.EMPLOYEE_ID = R.EMPLOYEE_ID);

Question: 4

Table ‘TAB_1’ has 1 column ‘A1’, in which there are 4 rows of data – (A, C, E, X)
Table ‘TAB_2’ has 1 column ‘A2’, which has 5 rows of data – (A, B, C, D, X)
How does running the SQL below produce results?

(SELECT A1 A
  FROM TAB_1
UNION ALL
SELECT A2 A
 FROM TAB_2)
MINUS
(SELECT A1 A
  FROM TAB_1
INTERSECT
SELECT A2 A
      FROM TAB_2)

Answer

The result have no rows

A, C, X

A, B, C, D, E, X

B, D, E

Question: 5

The following statement is true()
Remark: The execution environment is oracle sql developer, Data format is YYYY/MM/DD

Answer

select add_months(to_date(‘20160606’, ‘yyyymmdd’), -3)    –return result is : 2016/9/6

select months_between(to_date(‘20160606’, ‘yyyymmdd’), to_date(‘20160706’)) from dual = = > The result is a positive number

select round(to_date(‘20160606’, ‘yyyymmdd’), ‘day’) from dual = = > return result is : 2016/6/5

select TRUNC(to_date(‘20160606’, ‘yyyymmdd’), ‘mm’) from dual –return result is : 2016/6/1

Question: 6

The following statements about the sequence, which is correct?

Answer

Sequence is provided by the oracle to generate a series of unique digital database objects

When you create a sequence, you can use the pseudo column CURRVAL to return the value of the current sequence

The use of sequence, need to use the sequence of two pseudo columns NEXTVAL and CURRVAL. The NEXTVAL will return the sequences generated by the next value, curent value and returns the CURRVAL sequece

The actual sequence will occupy the storage space

Question: 7

Assuming EMPLOYER has 10 rows of data (ROWID = 1 ~ 10), what is the purpose of the following SQL?
DELETE FROM EMPLOYER A
  WHERE ROWID < (SELECT MAX(B.ROWID)
     FROM EMPLOYER B
     WHERE A.WORK_ID = B.WORK_ID
      AND A.WORK_NAME = B.WORK_NAME);

Answer

Delete EMPLOYER in ROWID <first iteration of WORK_ID and WORK_NAME of ROWID data

Delete duplicate data of WORK_ID and WORK_NAME in EMPLOYER

Clear data is not identical of WORK_ID and WORK_NAME in EMPLOYER

Delete data of EMPLOYER with ROWID <10

Question: 8

How would you add a foreign key constraint on the dept_no column in the EMP  table,Referring to the ID column in the Dept Table ?

Answer

Use the ALTER TABLE command with the ADD clause in the DEPT table

Use the ALTER TABLE command with the ADD clause in the EMP table

Use the ALTER TABLE command with the MODIFY clause in the DEPT table

Use the ALTER TABLE command with the MODIFY clause in the EMP

Question: 9

Written a pl/sql loop,you need to test if the current FETCH(suppose name of cursor is    getdata) was successful.Which getdata cursor attribute would you use ?

Answer

getdata%isopen

getdata%rowcount

getdata%found

Cannot determine with a getdata cursor attribute

Question: 10

Which statement is true when writing a cursor FOR loop ?

Answer

You must explicitly open the cursor prior to the cursor FOR loop

You must explicitly close the cursor prior to the end of program

You do not need explicitly open and close a cursor within a cursor FOR loop

You must explicitly declare the record variable that holds the row returned from the  cursor

Question: 11

Evaluate this PL/SQL block below:

 Begin
     For I in 1..10 Loop
         If i=4 or i=6 then null;
         Else
             Insert into test(result) values(i);
         End If;
         Commit;
     End Loop;
     Rollback;
 End;
 How many values will be inserted into the Test table ?

Answer

0

4

6

8

Question: 12

You attemp to query the database with this command below:

 Select dept_no,AVG(MONTHS_BETWEEN(sysdate,hire_d))
 FROM employee
 Where AVG(MONTHS_BETWEEN(sysdate,hire_d))>60
 Group BY dept_no
 Order by AVG(MONTHS_BETWEEN(sysdate,hire_d));
 Why does this statement cause an error ?

Answer

A select clause cannot contain a group function

A where clause cannot be used to restrict groups

An order by clause cannot contain a group function

A group function cannot contain a single row column

Question: 13

A table declared as below, What operate between data type  are wrong ?

 create table transactions (trans_id number, amount number(10,2), trans_start timestamp, trans_end timestamp, ship_date date, expire_date interval day(0) to second(0));

Answer

trans_start – trans_end

to_timestamp(amount, ‘yy-mm-dd hh:mi:ss’)

trans_start + interval ‘4’ day

trans_start + ship_date

Question: 14

below SQl statement need complement missed code, what answers are right?

 select amt
 from hp_out
 where out_no like ‘A%’
  and ….

Answer

amt exists (select amt from hp_out2  where  out_no like ‘A%’)

amt > Any (select amt from hp_out2  where  out_no like ‘A%’)

amt > (select amt from hp_out2  where  out_no like ‘A%’)

amt > all (select amt from hp_out2  where  out_no like ‘A%’)

Question: 15

What below subqueries are work?

Answer

Select * From emp where sal> (select min(sal) from emp group by dept_id);

Select distinct dept_id from emp where sal > ANY (select AVG(sal) from emp Group  By dept_id);

Select dept_id from emp Where sal > all(select AVG(sal) from emp group by  AVG(sal));

Select distinct dept_id from emp where sal > ALL (select AVG(sal) from emp Group  By dept_id);

Question: 16

What statements about views are true ?

Answer

A view can be created as read only

A view can not have an ORDER BY clause in the SELECT statement

A view can be created as a join on two or more tables

A view must have aliases defined for the column names in the SELECT statement

Question: 17

What are DATETIME data types that can be used when specifying column  definitions ?

Answer

TIMESTAMP

INTERVAL MONTH TO DAY

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

Question: 18

About aggregate functions , what answers are right ?

Answer

You can use aggregate functions in any clause of SELECT statement

You can pass column names ,expressions,constants or functions as parameters to an  aggregate function

You can mix single row columns with aggregate functions in the column list of a SELECT  statement by grouping on the single row columns

You can use aggregate functions on a table, only by grouping the whole table as one single  group .

Question: 19

Which statement about SQL is true ?

Answer

Null values are displayed last in the ascending sequences

Data values are displayed in descending order by default

You can not specify a column alias in an order by sub clause

You can not sort query results by a column that is not included  in the select list

Question: 20

Which answer is Oracle database’s physical structure type?

Answer

Tables

Tablespaces

Views

Control file

Question: 21

You have decided to permanently remove all the data from the STUDENT table and you need the  table structure in the furture.Which single command performs this ?

Answer

Drop TABLE student;

TRUNCATE TABLE student KEEP STRUCTURE;

TRUNCATE TABLE student ;

DELETE * FROM student KEEP STRUCTURE;

Question: 22

Restricts STUDENT table NAME column not null, Which statement is right?

Answer

ALTER TABLE student ADD CONSTRAINT name(not null);

ALTER TABLE student ADD CONSTRAINT not null(name);

ALTER TABLE student modify CONSTRAINT name(not null);

ALTER TABLE student modify (name not null);

Question: 23

What are the join types for multi-table?

Answer

Cross Join

Inner Join

Full outer Join

Left outer join

Question: 24

What are the default values for the following types?

Answer

Default value of Date type is 1900-01-01

Default value of Bolean type is true

Default value of Time type is 00:00:00

Default value of String type is NULL

Question: 25

Which statement will be return result below :
  this is a quote’

Answer

select ‘this is a quote’||”” from dual

select ‘this is a quote’||””” from dual;

select ‘this is a quote’||”’ from dual;

all answers wrong

Question: 26

A column name start_date is date type,what aggregate functions are valid on the column ?

Answer

Sum(start_date)

Min(start_date)

AVG(start_date)

Count(start_date)

Question: 27

What cases the SQL statement would cause an exception error ?

Answer

Select statement does not return a row

Select statement return more than one row

Select statement contains a group by clause

Select statement does not have where

Question: 28

What are the various types of queries in oracle ?

Answer

Compound queries

Sub Queries

correlated subquery

Nested queries

Question: 29

What is the maximum number of triggers, can apply to a single table?

Answer

3

6

9

12

Question: 30

Explain  FUNCTION, PROCEDURE and PACKAGE,What the following descriptions are true?

Answer

Function must return a value. Can be called inside a query.

Procedure may or may not return value.

Procedure and Function always have return value.

Package is the collection of functions, procedures, variables which can be logically grouped together.

Question: 31

To show the staff the 2nd character in the name is “A” SQL statement, what are false?

Answer

SELECT last_name FROM EMP WHERE last_name =’_A%’;

SELECT last_name FROM EMP WHERE last_name =’*A%’;

SELECT last_name FROM EMP WHERE last_name  like ‘*A%’;

SELECT last_name FROM EMP WHERE last_name like’_A%’;

Question: 32

What are cursor attributes?

Answer

%ROWCOUNT

%SQLCODE

%NOTFOUND

%ISOPEN

Question: 33

select NVL2(‘PCC’, ‘A’, ‘B’) from dual;，return value?

Answer

PCC

A

B

NULL

Question: 34

If Oracle Database version 10g up does not modify any argument(Default) and execute group by clause：

Answer

It will accroding group by cloumns exactly ascending the retrieve data.

It will accroding group by cloumns exactly descending the retrieve data.

It will accroding group by cloumns ascending the retrieve data but not exactly.

It doesn’t sort anything!

Question: 35

We have a procedure like below:
procedure Pro_test
is
pragma AUTONOMOUS_TRANSACTION;
begin
insert into tab_A values(‘123’);
commit;
end;

if we run the program as below,what data will in tab_A at last?(assumed tab_A have no data at first.)

begin
insert into tab_A values( ‘abc’ );
Pro_test;
insert into tab_A values( ‘xyz’ );
rollback;
end;

Answer

abc
—–
123
——
xyz

abc
—–
123

123

abc
—–
xyz

Question: 36

select round(5555.5555,-2.5) from dual;

What is the result?

Answer

5555.55

5555.56

5556

5560

5600

Question: 37

What is v_counter and v_counter1’s value in below PL/SQL script?
DECLARE
v_counter Integer := 0;
v_counter1 Integer := 0;
BEGIN
<<LOOP_LAB>>
LOOP
v_counter := v_counter + 1 ;
EXIT WHEN v_counter > 10 ;
<<LOOP_LAB>>
LOOP
EXIT LOOP_LAB WHEN v_counter < 13 ;
v_counter1 := v_counter1 + 1 ;
END LOOP LOOP_LAB ;
END LOOP LOOP_LAB;
END ;

Answer

v_counter = 0
v_counter1 = 0

v_counter = 1
v_counter1 = 0

v_counter = 11
v_counter1 = 0

v_counter = 11
v_counter1 = 13

v_counter = 10
v_counter1 = 13

Question: 38

About Oracle object authority, which of the following statements is correct?

Answer

may only be granted INSERT, UPDATE, DELETE Table permission to other User

If permission is granted to PUBLIC, on behalf of the DB every User has permission

User can set permissions on objects which can not be
When not trigger Trigger

has a Table transaction authority but no Trigger permissions, transaction Table Data

Question: 39

What is the result of below program?
DECLARE
Type T_DATA Is Table Of VARCHAR2(10) Index By Binary_Integer ;
V_DATA T_DATA ;
BEGIN
V_DATA(1) := ‘X’;
V_DATA(2) := ‘J’;
V_DATA(3) := ‘W’;

dbms_output.put_line(V_DATA.FIRST) ;
dbms_output.put_line(V_DATA.LAST) ;
END;

Answer

X
W

X
J
W

1
3

1
2

Question: 40

Which the correct answer of GOTO syntax ？

Answer

GOTO :LABEL
:LABEL

GOTO :LABEL:
:LABEL:

GOTO LABEL
LABEL

GOTO LABEL
LABEL:

Question: 41

Which of the following statement about granting object privileges on a synonym in an Oracle database is CORRECT?

Answer

Synonym and the schema object it represents can  have separate privileges.

Granting object privileges on a synonym is actually granting privileges on the underlying object.

Synonym can only be owned by a special user group, PUBLIC, thus all database users have access rights to all synonym. No granting additional privileges is necessary.

All of above

Question: 42

Which of the following privilege is not required if an application system only needs to read, add, remove or modify data in a database?

Answer

SELECT

INSERT

DELETE

GRANT

Question: 43

About Oracle dynamic languange, which of the following statements is correct?

Answer

only use DBMS_SQL

In addition to performing some DML statements, but also can execute DDL statements

TCL statement can not execute

the implementation of a dynamic SELECT syntax can return multiple records

Question: 44

Which of the following statements execute successfully？
(id , amt, qty is number )

Answer

INSERT all
INTO KS_table2 values(id, amt, qty)
INTO KS_table3 values(id, amt, qty)
SELECT id, amt, qty from KS_table1 ;

INSERT first    
         INTO KS_table2 values(id, amt, qty)        
         INTO KS_table3 values(id, amt,qty)
 SELECT id, amt, qty from KS_table1  ;

Merge into KS_table1 a
Using (select 8 id from dual) b
On (a.qty > 1)
When matched then
Update set a.id = b.id
When not matched then
Insert (id, amt, qty) Values (b.id, b.id, b.id);

Merge into KS_table1 a
Using KS_table2 b
On (a.id = b.id)
When matched then
Update set a.id = b.id
When not matched then
Insert (id, amt, qty) Values (b.id, b.amt, b.qty);

Question: 45

Which of the following methods belong to the Oracle to throw exceptions ( ) ?

Answer

By PL/SQL runtime engine

Use RAISE statement

Call RAISE APPLICATION ERROP procedure

Above is not correct

Question: 46

Some users want to see I can see table information, then which one should query the view?

Answer

v$tables  

dba_tables

all_tables

user_tables

Question: 47

There are hundred records in the STUDENT table,you need to modify the Phone column to hold only numeric value.Which statement will modify the data type of the Phone column ?

Answer

ALTER TABLE student MODIFY phone number(9);

ALTER student TABLE MODIFY column phone number(9);

You cannot modify the data type of a column if there is data in the column

all answers right

Question: 48

The variables declared below, what expression return a date?

v_date  Date:=’03-Jul-96′;
v_odate Date:=’04-Jul-97′;

Answer

v_date + 7

v_date – v_odate

v_date + (12/24)

(v_date – v_odate) / 7

Question: 49

What is the maximum number of columns a Oracle database table can have ?

Answer

100

64

254

32767

Question: 50

How would running PL / SQL be output?

DECLARE
V_N number;
BEGIN
SELECT 1/0 INTO V_N FROM DUAL ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20001, ‘DUAL NO_DATA_FOUND’ );
WHEN ZERO_DIVIDE THEN
RAISE_APPLICATION_ERROR( -20002, ‘DUAL ZERO_DIVIDE’ );
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR( -20003, ‘DUAL SQL ERROR’ );
END;

Answer

execution error and SQLCODE = -20001

execution error and SQLCODE = -20002

execution error and SQLCODE = -20003

execution correct

Question: 51

Which describe is wrong about CAST() in Oracle?

Answer

Any number or date data type can be converted to character data type.

It can not truncate strings when varchar be converted to char.

Decimal be converted to integer, than it will be rounded or truncated values.

Character data type can be converted to any data type as long as the string is a valid constant data type.

Question: 52

Which answer match the where clause filtered result?
select col_1 from tab_A where col_1 LIKE ‘_E\_%’ ESCAPE ‘\’;

Answer

QE3AW

GOOE_OO

FE_ARE

HWE_33

Question: 53

Execute the following SQL,which values are correct?
SELECT SIGN(76.123), SIGN(0),ROUND(12.345, 2),ABS(-1.234) FROM DUAL ;

Answer

1,  0, 12.345, -1.234

1, -1, 12.35,    1.234

0 , 1, 12.35,    1.234

1 , 0, 12.35,    1.234

Question: 54

Abnormal processing of Oracle PL/SQL program, involving a variety of abnormal, which must use raise to display the exception is ()?

Answer

Predefined exception

Non predefined exception

Custom exception

uncertain

Question: 55

PL / SQL How to perform d:\123.sql statements inside?

Answer

@d:\123.sql

D:\123.sql

@d://123.sql

exec d:\123.sql

Question: 56

About Oracle’s datatype CLOB,NLOB,BLOB which statemant is wrong?

Answer

These datatype can save 4 Giga bytes data.

NLOB can only save single byte character data.

BLOB can save binary data.

CLOB can use to save multiple byte characters data.

Question: 57

In Oracle，which of the following index column to use in “Where” conditions ,that will cause Indexes are not be used?

Answer

use function

use calculation formula

use ‘NOT IN’ command

use ‘IN’ command

Question: 58

Which statements are true about WHERE and HAVING clause?

Answer

A WHERE clause can be used to restrict both rows and groups.

A WHERE clause can be used to restrict rows only.

A HAVING clause can be used to restrict both rows and groups.

A HAVING clause can be used to restrict groups only.

Question: 59

Related to data types in Oracle,what descriptions are true?

Answer

Varchar can store upto 2000 bytes

varchar2 can store upto 4000 bytes

The CLOB and NCLOB datatypes store up to 128 terabytes of character data in the database

Varchar can store upto 4000 bytes

Question: 60

If your DBMS supports formats of FETCH, which of the following options can use?

Answer

FETCH FIRST

FETCH PRIOR

FETCH LAST

FETCH NEXT

Question: 61

Which of the following is not a schema object in Oracle?

Answer

Indexes

tables

public synonyms

packages

Question: 62

What the primary purpose of Oracle view object?

Answer

Security

Simplify long Query

Ad Hoc Query

Transfer data

Question: 63

The following SQL syntax is the result?

SELECT ADD_MONTHS(LAST_DAY(TO_DATE(‘20160229’, ‘yyyymmdd’)) + 1, -1) FROM DUAL;

Answer

20160201

20160202

20160229

20160301

Question: 64

PL / SQL How to define blocks ?

Answer

begin   end

declare

Label:

If  Else End

Question: 65

Which two statements correctly differentiate functions and procedures?

Answer

function can be called only as part of a SQL statement,
Whereas a procedure can be called only as a PL/SQL statement.

function must return a value to the calling environment,
Whereas a procedure can return zero or more values to its calling environment.

function can be called as part of a SQL statement or PL/SQL expression,
Whereas a procedure can be called only as a PL/SQL statement.

function may return one or more values to the calling environment,
Whereas a procedure must return a single value to its calling environment.

Question: 66

In Oracle,which statements shows 0 when the COMM field is empty and the value of the COMM field is displayed when it is not empty?

Answer

SELECT ENAME,NVL(COMM,0) FROM EMP;

SELECT ENAME,COALESCE(COMM,’0′) FROM EMP;

SELECT ENAME,NULL(COMM,0) FROM EMP;

SELECT ENAME,NULLIF(COMM,0) FROM EMP;

Question: 67

DECLARE

VS_A VARCHAR2(20) := ‘A’;
VS_B VARCHAR2(20);

BEGIN

VS_B := ‘B’;
DBMS_OUTPUT.PUT_LINE(VS_A||VS_B);

VS_A := NULL;
DBMS_OUTPUT.PUT_LINE(VS_A||VS_B);
END;

What is DBMS_OUTPUT will show?

Answer

AB
B

AB
Null

AB
AB

null
Null

Question: 68

Table A has a column N ， of type Number ， and has 5 rows of data ， distinguish 12, 26, Null, 12, 10 so what if the following SQL is executed?
SELECT COUNT( * ) || COUNT( N ) || COUNT( DISTINCT N ) FROM A;

Answer

543

544

554

555

Question: 69

Oracle Cursor statements, Which answer of below is correct?

Answer

DECLARE emp_curs CURSOR FOR SELECT emp_id, emp_name FROM EMPLOYEE;

CURSOR emp_curs IS SELECT emp_id, emp_name FROM EMPLOYEE;

DECLARE emp_curs IS SELECT emp_id, emp_name FROM EMPLOYEE;

CURSOR emp_curs  FOR SELECT emp_id, emp_name FROM EMPLOYEE;

Question: 70

ORACLE SQL. Which the syntax of below is not correct?

Answer

INSERT INTO dept (depno, dname, loc) VALUES (50, ‘MARKETING’, ‘SAN JOSE’);

INSERT INTO dept (depno, dname, loc) (SELECT ename, sal FROM emp WHERE sal > 1000);

INSERT INTO dept (depno, dname, loc) SELECT ename, sal FROM emp WHERE sal > 1000;

INSERT INTO dept (depno, dname, loc) VALUES (SELECT ename, sal FROM emp WHERE sal > 1000);

Question: 71

Which of the following is TRUE

select trunc(1.3659,2)+ ceil(2.16)+ round(1.54,1) from dual;

Answer

5.86

4.86

5.87

4.87

Question: 72

Which is the HAVING Clause function in Oracle ?

Answer

Used to filter data based on the group function

Used to sort group query results

Used to limit the number of rows of query results

Used to group query results

Question: 73

The following variable declaration, which is not valid?

Answer

Table.%column.Type

Number

Number(12)

Varchar2(20) :=’ ‘

Question: 74

This script, which answer of below is correct?
select ROUND(to_date(‘20161129′,’yyyymmdd’),’YEAR’) from dual

Answer

20161129

20171129

20170131

20170101

Question: 75

This script, what answer of below is correct? Ex: table em_monpay have 11 rows.

select c.pnl_no, c.salary, c.stt
from ( select b.pnl_no, b.salary, rownum as stt from (select a.pnl_no, a.salary
from em_monpay a
order by salary desc) b) c
where rownum = 10

Answer

No data

Run be error

1 row

10 rows

Question: 76

Which answer of below is correct?
select TO_NUMBER (SUBSTR(‘PY2014110002’,9,4)) + 1 * TO_CHAR (TO_NUMBER (SUBSTR(‘PY2014110002′,9,4)) + 1,’fm0000’)
from dual

Answer

9

5

0

Be error when running

Question: 77

This script, which answer of below is correct?

Select round((to_date(‘1459′,’hh24mi’) –  to_date(‘1429′,’hh24mi’)),2) from dual

Answer

0.02

0.5

30

1800

Question: 78

This script, Which  the return value  of below is correct?

SELECT SUBSTR(CONCAT(TRUNC(FLOOR (-1.1)*CEIL(9.9)/TRUNC (4.923,1),2),’ABC’),4,2)
FROM DUAL;

Answer

08

BC

01

AB

Question: 79

Which answer of below is correct?

select Sign(ACos(1)) from dual;

Answer

0

0.54

1

2

Question: 80

This script. Which answer of below is correct?  

select INSTR (‘a/x/y/z’, ‘/’, -1) + SUBSTR (‘0002axyz’, 1, 5 – 1)
from dual

Answer

6

8

0

11

Question: 81

Table A has one column N that datatype is Number, and three rows ( 12, Null, 15 ), If execute the following SQL, what the data will become？

SELECT NVL(AVG( N ),10) FROM A;

Answer

NULL

10

13.5

9

Question: 82

Table T_1 has one column C_1， that datatype is String, and four rows (S, F, F, T ),Table T_2 has one column C_2， that datatype is String, and two rows (C, T ),  If execute the following SQL, what the data will become？

SELECT  C_1   FROM  T_1  UNION ALL    SELECT  C_2     FROM    T_2;

Answer

S  F  F  T  C   T

S  F

S  F  T C

F C

Question: 83

If we want return the weeks ，what value must fill in below (P) position ?

SELECT   to_char(to_date(‘2015/10/14′,’yyyy/mm/dd’),(P))     FROM    DUAL;

Answer

‘CW’

‘WEEK’

‘WK’

‘IW’

Question: 84

After executing the below SQL command, what is the result ?
SELECT  trunc(to_date(‘20151020′,’yyyymmdd’), ‘month’)  FROM DUAL;

Answer

2015/10/01 00:00:00

2015/11/01 00:00:00

2015/01/01 00:00:00

2015/01/31 00:00:00

Question: 85

In Oracle，Which  one of sql syntax can get following result?

testing ‘string’ for single-quotes

Answer

Select ‘testing |’string|’ for single-quotes’ from dual;

Select ‘testing &|’string&’ for single-quotes’ from dual;

Select ‘testing \’|string\’ for single-quotes’ from dual;

Select ‘testing ”string” for single-quotes’ from dual;

Question: 86

In Oracle, which of the following SQL syntax are correct?

Answer

SELECT ROUND(SYSDATE, ‘MONTH’)  FROM DUAL;

SELECT INSTR(lower(‘HappyOneDay’),’n’,0) FROM DUAL;

SELECT CONCAT(‘Happy’,NULL) FROM DUAL;

SELECT ROUND(-75.578,-1) FROM DUAL;

Question: 87

In Oracle,  in the following table TB_1 as an example,  which ORDER BY syntax are correct?

CL_1(NUMBER(6))        CL_2(NUMBER(6))
——                   ——–
  10                        2
  11                       NULL
  100                      50

Answer

SELECT CL_1 FROM TB_1 ORDER BY CL_2 AESC;

SELECT CL_1 FROM TB_1 ORDER BY 2,1;

SELECT CL_1 FROM TB_1 ORDER BY CL_1 * CL_2;

SELECT CL 1 A FROM TB_1 ORDER BY A;

Question: 88

SELECT SIGN (INSTR (‘Powerbuilder’, ‘W’))
FROM DUAL ;

Which answer below will be the result ?

Answer

0

1

2

3

Question: 89

Which answer is correct output result of (A)  in the example below?

DECLARE
   v_x   NUMBER;
BEGIN
   v_x   := v_x + 1;
   DBMS_OUTPUT.put_line (v_x); ———-(A)
END;

Answer

0

1

NULL

None of the above

Question: 90

Which are one of the Data Manipulation Language(DML) statements ?

Answer

INSERT

UPDATE

DELETE

All of the above

Question: 91

Doing the SQL below, there are “_” spaces. (How many spaces?)

SELECT RPAD( ‘ABCD’, 10 ) FROM DUAL;

Answer

4

6

8

10

Question: 92

Execute the following SQL, which the result of execution is ?

SELECT DECODE(3,1,1,2,2) FROM DUAL;

Answer

1

2

3

NULL

Question: 93

What is the result of below SQL statement?

select TRANSLATE( ‘x測試x’, ‘測x試‘, ‘AmB’ ) from dual ;

Answer

AmBx

A測試B

mABm

測m試

Question: 94

SELECT ‘X’ FROM DUAL WHERE :a = 1 AND :b = 2 OR :c = 3 AND :d = 4  AND :e = 5;

After executing upper SQL,the answer is X,which answer correct?

Answer

:a =  1, :b = 2, :c = 3 , :d = 5 , :e = 5

:a =  1, :b = 1, :c = 3 , :d = 2 , :e = 3

:a =  1, :b = 2, :c = 3 , :d = 4 , :e = 5

:a =  2, :b = 2, :c = 3 , :d = 4 , :e = 5

Question: 95

DECLARE
   v_custname VARCHAR2(300) := ‘Basketball’;
BEGIN
   DECLARE
      v_custname VARCHAR2(300) := ‘Baseball’;
   BEGIN
      DBMS_OUTPUT.PUT_LINE( v_custname ); ———-> (1)
      v_custname := v_custname;
      DBMS_OUTPUT.PUT_LINE( v_custname ); ———-> (2)
   END;
   DBMS_OUTPUT.PUT_LINE( v_custname ); ———-> (3)
END;

Which of the following is correct?

Answer

The value of posiotion (1) is ‘Basketball’

The value of posiotion (2) is ”Baseball’

The value of posiotion (3) is ”Baseball’

PL/SQL compilation error

Question: 96

Which funtion could  accept any type parameter ?

Answer

SUBSTR

NVL

ROUND

DECODE

Question: 97

Which Oracle objects are code by PL/SQL?

Answer

Procedure

Trigger

Fuction

Sequence

Question: 98

Which are PL/SQL variable declaration style?

Answer

V_DATE DATE DEFAULT SYSDATE + 10;

V_FACT CONSTANT VARCHAR2(6) := ‘X’;

V_EMP EMP%ROWTYPE;

V_EMP_NO EMP.EMP_NO%TYPE;

Question: 99

select add_months(to_date(‘20150228′,’yyyymmdd’),+1 ) from dual;
What is the result?

Answer

2015/3/28

2015/3/29

2015/3/30

2015/3/31

Question: 100

Which SQL excute will get result ‘20150228’?

Answer

select to_char ( add_months(to_date(‘20141228′,’yyyymmdd’),2),’yyyymmdd’ ) from dual ;

select to_char ( add_months(to_date(‘20141229′,’yyyymmdd’),2),’yyyymmdd’ ) from dual ;

select to_char ( add_months(to_date(‘20141230′,’yyyymmdd’),2),’yyyymmdd’ ) from dual ;

select to_char ( add_months(to_date(‘20141231′,’yyyymmdd’),2),’yyyymmdd’ ) from dual ;

Question: 101

There are a Table which table name is ‘A’ and Table A have a column which column name is ‘N’.
Table A have 5 rows(12,26,NULL,12,10).
What is the answer about below SQL statemant？

Select AVG(N) From A ;

Answer

15

12

NULL

0

Question: 102

What is the answer of the below SQL?
select to_char(to_date(‘2015/05/11′,’yyyy/mm/dd’) + 3/24,’yyyy/mm/dd hh24:mi:ss’) from dual ;

Answer

2015/05/11

2015/05/11 00:00:00

2015/05/11 03:00:00

2015/05/12 00:00:00

Question: 103

Which SQL excute will get result ‘ABC’?

Answer

SELECT ‘ABC’ || NULL FROM DUAL ;

SELECT TRIM(LPAD(‘ABC’,10,’ ‘))  FROM DUAL ;

SELECT ” ||’ABC’  FROM DUAL ;

SELECT RTRIM(LPAD(‘ABC’,10,’ ‘))  FROM DUAL ;

Question: 104

SELECT * FROM EMPLOYEE_A  ;

ID              NAME
————   ————-
A               TOM
B               JERRY
C               LOUIS

SELECT * FROM EMPLOYEE_B  ;

ID              NAME
————   ————-
A               TOMMY
B               JERRY
D               LOUIS

SELECT ID , NAME
FROM EMPLOYYE_A
INTERSECT
SELECT ID , NAME
FROM EMPLOYEE_B ;


After excute SQL , how many rows dispaly ?

Answer

1

2

3

4

5

Question: 105

SQL> SELECT * FROM ATABLE;
COL1        COL2
———   ———
01          Apple
02          Pineapple
03          Watermelon
04          Orange
05          Cherry

SELECT COL2 FROM ATABLE
 WHERE COL1 LIKE ‘0_’  
   AND COL2 NOT LIKE ‘%_a%’;

What is the result?

Answer

Pineapple、Watermelon、Orange

Apple、Pineapple、Watermelon、Orange

Cherry

Apple、Cherry

Question: 106

There’re three rows in a table (TABLE_1) as below:

COL_A
—–     
1
2        
<null>

SELECT COUNT(COL_A) + SUM(COL_A) FROM TABLE_1;

Which is the answer ?

Answer

3

4

5

6

Question: 107

SELECT DECODE (SIGN (9000 -10000) , 1 , ‘AAA’, -1 ,’BBB’ , 0 ,’CCC’ , ‘DDD’) FROM DUAL ;
Which answer is right ?

Answer

AAA

BBB

CCC

DDD

Question: 108

SELECT months_between(to_date(‘2015/09/01′,’yyyy/mm/dd’), to_date(‘2014/03/01′,’yyyy/mm/dd’)) as months from dual;
What is the result?

Answer

17

18

19

20

Question: 109

How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?

Answer

UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’

MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’

UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’

MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen

Question: 110

What will be the output of the following statement?
SELECT ROUND(123.89, -1)?

Answer

120

123

123.9

124

Question: 111

With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” starts with an “a”? 

Answer

SELECT * FROM Persons WHERE FirstName=’%a%’

SELECT * FROM Persons WHERE FirstName LIKE ‘%a’

SELECT * FROM Persons WHERE FirstName=’a’

SELECT * FROM Persons WHERE FirstName LIKE ‘a%’

Question: 112

SQL> SELECT * FROM TABLE_1;
COL_A   
—–     
B
A     

SQL> SELECT * FROM TABLE_2;
COL_B   
—–     
C
B    

SELECT * FROM TABLE_1 UNION SELECT * FROM TABLE_2;

After executing upper SQL, what’s the value?

Answer

A
B
C

B
A
C
B

B
A
C

B
C
A
B

Question: 113

How to add multiple column like Address and DOB , DeptNo to emp table?

Answer

ALTER TABLE EMP add ( Address Char(24),Dob date,DeptNo number(8));

ALTER TABLE EMP add ( Address Char(24)), add( Dob date), add (DeptNo number(8));

ALTER TABLE EMP add ( Address Char(24), Dob( date), add (DeptNo number(8));

INSERT INTO EMP (Address Char(24),Dob date,DeptNo number(8));

INSERT INTO EMP add ( Address Char(24)), add( Dob date), add (DeptNo number(8));

Question: 114

You are developing a new database. The database contains two tables named SalesOrderDetail and Product. You need to ensure that all products referenced in the SalesOrderDetail table have a corresponding record in the Product table.
Which method should you use?

Answer

JOIN

DDL trigger

Foreign key constraint

Primary key constraint

Question: 115

You have two tables named Customers and Orders.
for customers that have placed at least one order, you need to produce a list of customer names and the number of orders for each customer.
Which query should you use?

Answer

SELECT c.CustomerName, SUM(o.OrderID) AS [OrderCount]
FROM Customers c  JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName

SELECT COUNT(o.OrderId) AS [OrderCount]
FROM CUSTOMERS c  JOIN
ORDERS o ON c.CUSTOMERID = o.CUSTOMERID

SELECT c.CustomerName, COUNT(o.OrderID) AS [OrderCount]
FROM Customers c  JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c. CustomerID

SELECT c.CustomerName, COUNT(o.OrderId) AS [OrderCount]
FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerName

Question: 116

Which result will the same with below SQL?

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  e1.sal > ALL (SELECT e2.sal
                     FROM   emp e2
                     WHERE  e2.deptno = 20);

Answer

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE e1.sal NOT IN (SELECT e2.sal
                           FROM emp e2
                           WHERE e2.deptno = 20) ;

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  NOT (e1.sal <= ANY (SELECT e2.sal
                           FROM emp e2
                           WHERE e2.deptno = 20));

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE EXISTS (SELECT e2.sal
                   FROM emp e2
                   WHERE e2.deptno = 20
                   AND   e1.sal <= e2.sal);

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  NOT EXISTS (SELECT e2.sal
                   FROM emp e2
                   WHERE e2.deptno = 20
                   AND   e1.sal <= e2.sal);

Question: 117

Which options’ cursor is Explicit cursor Type?

Answer

Declare
   CURSOR cur_emp IS
   SELECT ename FROM emp;
   v_ename emp.ename%TYPE ;
BEGIN
   OPEN cur_emp;
   LOOP
      FETCH cur_emp INTO v_ename ;
      EXIT WHEN cur_emp%NOTFOUND ;
      DBMS_OUTPUT.PUT_LINE(v_ename) ;
   END LOOP ;
   CLOSE cur_emp ;
END ;

Declare
   CURSOR cur_emp IS
   SELECT ename FROM emp;
BEGIN
   FOR I IN cur_emp LOOP
      DBMS_OUTPUT.PUT_LINE(i.ename) ;
   END LOOP ;
END ;

Declare
   v_ename emp.ename%TYPE ;
BEGIN
   SELECT ename INTO v_ename FROM emp WHERE rownum = 1;
   DBMS_OUTPUT.PUT_LINE(v_ename)
END ;

BEGIN
   FOR I IN (SELECT ename FROM emp) LOOP
      DBMS_OUTPUT.PUT_LINE(i.ename) ;
   END LOOP ;
END ;

Question: 118

Execute the following SQL, which the result of execution is ?

SELECT NVL2( NULL, ‘A’, ‘B’ ) FROM DUAL;

Answer

A

B

NULL

Error

Question: 119

Which are the Data Manipulation Language(DML) command?

Answer

INSERT

UPDATE

DELETE

TRUNCATE

Question: 120

Execute the following SQL, which answer is 7 ?

Answer

SELECT  LENGTH(‘11123’) + 2 FROM DUAL;

SELECT  POWER(7,1)  FROM DUAL;

SELECT  CEIL(7.1) FROM DUAL;

SELECT  ABS(-7)  FROM DUAL;

Question: 121

Related to between TRUNCATE and DELETE command of Oracle,Which of the description is true?

Answer

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed

TRUNCATE removes all rows from a table

TRUNCATE can be rolled back

No triggers will be fired when execute Truncate

Question: 122

This script, which answer of below is correct?

Select round(months_between (to_date (‘2003/03/01’, ‘yyyy/mm/dd’), to_date (‘2003/05/15’, ‘yyyy/mm/dd’) ),1) From dual

Answer

2.5

-2.5

2

-2

Question: 123

Which of the following script in PL/SQL will run if script error processing or unusual conditions?

Answer

Others

Exception

Vserrm

No_Data_Found

Question: 124

Which answer of below is correct?

SELECT ROUND(MONTHS_BETWEEN( TO_DATE(‘20160603′,’YYYYMMDD’), TO_DATE(DECODE(LENGTH(trim(‘19850321’)) ,4, trim(‘19850321’) || ‘0101’,’19850321′), ‘YYYYMMDD’))/12,2)
FROM DUAL;

Answer

31

31.2

32

32.2

Question: 125

Which answer of below is correct?   

select  TO_CHAR(LAST_DAY(TO_DATE( ‘20160614’, ‘YYYYMMDD’)), ‘D’) – TO_CHAR(LAST_DAY( TO_DATE(‘20160614’, ‘YYYYMMDD’)), ‘DD’) from dual;

Answer

0

25

-25

-9

Question: 126

which the Function is error when compile?

Answer

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN
  SELECT to_char(sysdate,’yyyymmdd’) into v_result
FROM dual;
  RETURN  ( v_result);
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN
  SELECT to_char(sysdate,’yyyymmdd’) into :v_result
FROM dual;
  RETURN  ( NULL);
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN
  SELECT to_char(sysdate,’yyyymmdd’) into v_result
FROM dual;  
  RETURN  ( NULL);
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN   
  RETURN  ( v_result);
  SELECT to_char(sysdate,’yyyymmdd’) into v_result
FROM dual;
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

Question: 127

Which answer of below is correct?
Select 100*CEIL(2.1234/100)+ CEIL(4.5234/1000) from dual

Answer

100

101

1000

1001

Question: 128

Which answer of below is correct?

SELECT TO_CHAR( TO_DATE( NVL(”,’19990101′), ‘YYYYMMDD’), ‘YYYYMMDD’) + TO_CHAR(‘19990101′,’YYYYMMDD’) FROM DUAL

Answer

Run be error

39980202

19990101

No data

Question: 129

This script, what answer of below is correct?

select MOD(TO_NUMBER(SUBSTR(‘20150401’,4)),4)
from dual               

Answer

3

2

1

0

Question: 130

This script, what answer of below is correct?
select FLOOR(TRUNC(-11.1,1))
from dual                 

Answer

0

-11

-12

-13

Question: 131

This script, what answer of below is correct?

SELECT TRANSLATE(‘PRESIDENT’,’AR’,’IT’)|| LENGTH(TRANSLATE(‘ACCOUNTING’, ‘AS’,’A’))
from dual

Answer

PTESIDENT10

PRESIDENT10

PTESIDENT11

PTESIDENT9

Question: 132

What answer below is correct for declare and used Cursor?

Answer

DECLARE  CURSOR C1 IS
SELECT………….
OPEN C1;
LOOP
FETCH C1 INTO……..
EXIT WHEN C1%NOTFOUND;
………………
END LOOP;
CLOSE C1 ;   
END;

DECLARE                                                                                  CURSOR C1 IS
SELECT…………. ;
OPEN C1;
LOOP
FETCH C1 INTO……..;
………………;                                                                                       EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1 ;                                                                                   END;

DECLARE                                                                                  CURSOR C1 IS
SELECT………….
OPEN C1;
LOOP                                                                                               EXIT WHEN C1%NOTFOUND;
FETCH C1 INTO……..
………………
END LOOP;
CLOSE C1 ;                                                                                   END;

DECLARE                                                                                  CURSOR C1 IS
SELECT………….
OPEN C1;                                                                                    FETCH C1 INTO……..
LOOP
EXIT WHEN C1%NOTFOUND;
………………
END LOOP;
CLOSE C1 ;                                                                                   END;

Question: 133

What answer below is correct for declare and used Cursor?

Answer

DECLARE                                                                                  CURSOR C1 IS
SELECT………….
OPEN C1;
LOOP
FETCH C1 INTO……..
EXIT WHEN C1%NOTFOUND;
………………
END LOOP;
CLOSE C1 ;                                                                                   END;

DECLARE                                                                                  CURSOR C1 IS
SELECT…………. ;
OPEN C1;
LOOP
FETCH C1 INTO……..;
………………;                                                                                       EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1 ;                                                                                   END;

DECLARE                                                                                  CURSOR C1 IS
SELECT………….
OPEN C1;
LOOP                                                                                               EXIT WHEN C1%NOTFOUND;
FETCH C1 INTO……..
………………
END LOOP;
CLOSE C1 ;                                                                                   END;

DECLARE                                                                                  CURSOR C1 IS
SELECT………….
OPEN C1;                                                                                    FETCH C1 INTO……..
LOOP
EXIT WHEN C1%NOTFOUND;
………………
END LOOP;
CLOSE C1 ;                                                                                   END;

Question: 134

which is the Function have error when compile?

Answer

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN
  SELECT pnl_no into v_result
FROM em_pnl
    WHERE emp_id = p_Emp_id; 
  RETURN  ( v_result);
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN
  SELECT pnl_no into :v_result
FROM em_pnl
    WHERE emp_id = p_Emp_id; 
  RETURN  ( NULL);
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN
  SELECT pnl_no into v_result
FROM em_pnl
    WHERE emp_id = p_Emp_id; 
  RETURN  ( NULL);
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

CREATE OR REPLACE FUNCTION get_sal(p_Emp_id   IN  number)
RETURN VARCHAR2
IS
v_result      VARCHAR2(100);
BEGIN   
  RETURN  ( v_result);
  SELECT pnl_no into v_result
FROM em_pnl
    WHERE emp_id = p_Emp_id;
EXCEPTION
  WHEN others THEN
    v_result:=’Loi’ || SQLERRM;
END;

Question: 135

What are the various Column constraints used in Oracle?

Answer

NULL – It is to indicate that particular column can contain NULL values

NOT NULL – It is to indicate that particular column cannot contain NULL values

CHECK – Validate that values in the given column to meet the specific criteria

DEFAULT – It is to indicate the value is assigned to default value

Question: 136

Which of the following statements about Trigger is correct?

Answer

When Trigger is applied to [INSERT], you can specify the field to trigger

When Trigger is set to [DELETE], you can use NEW and OLD objects

COMMIT / ROLLBACK can be executed in Trigger

NEW and OLD objects can be used as trigger constraints of Trigger, and a leading colon must be added when using them.
Example: (:NEW or:OLD)

Question: 137

Choose the format not use for TRUNC ( date [, format ] ) function.

Answer

D

H

Q

W

Question: 138

Which of the following script not belong DML group?

Answer

UPDATE

LOCK TABLE

ALTER

DELETE

Question: 139

this Function, what answer of below is correct? Ex: P_GIOBD = 1.2345
create or replace
FUNCTION FUN_OVER_ROUND (P_GIOBD IN NUMBER)
   RETURN NUMBER
IS
   PN   NUMBER (2);
   TP   NUMBER (3, 2);
   KQ   NUMBER (3, 1);
BEGIN
   PN := P_GIOBD – MOD (P_GIOBD, 1);
   TP := MOD (P_GIOBD, 1);

   IF TP <= 0.25 THEN
      TP := 0;
   ELSIF TP <= 0.75 THEN
      TP := 0.5;
   ELSE
      TP := 1;
   END IF;

   KQ := PN + TP;
   RETURN (KQ);
END ;

Answer

1

1.25

0.25

0.5

Question: 140

When use Add_Months function for date 2012/02/28 plus a month , get the result is 2012/03/28.

Answer

2013/03/28

2013/03/29

2013/03/30

2013/03/31

Question: 141

Which SQL excute will get result ‘ABC’

Answer

SELECT ‘ABC’ || NULL FROM DUAL ;

SELECT TRIM(LPAD(‘ABC’,10,’ ‘))  FROM DUAL ;

SELECT ” ||’ABC’  FROM DUAL ;

SELECT RTRIM(LPAD(‘ABC’,10,’ ‘))  FROM DUAL ;

Question: 142

You are writing a query that returns a list of products that have grossed more than $10,000.00 during the year 2007.
You need to insert the following filter expression into the query.
SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000 Into which clause should you insert this expression?

Answer

A. ON

B. WHERE

C. HAVING

D. GROUP BY

Question: 143

If Table A ( 3 row) and Table B ( 4 row),
executing select * from a, b;
How many rows after querying?

Answer

3

4

7

12

Question: 144

Which of the commands in the Oracle database allows you to modify an existing view directly?

Answer

ALTER Command

CREATE Command

MODIFY Command

CREATE OR REPLACE Command   

Question: 145

select 120 + ‘220’ || null from dual;
what is answer?

Answer

340

null

120

220

Question: 146

CREATE TABLE T1(
  COL_1  NUMBER,
  COL_2  NUMBER );
INSERT INTO T1 (COL_1, COL_2) VALUES(1, 0);
INSERT INTO T1 (COL_1, COL_2) VALUES(2, 1);
INSERT INTO T1 (COL_1) VALUES(3);
COMMIT;
 
SELECT COUNT(*) FROM T1 WHERE COL_2 >= 0;

Answer

0

1

2

3

Question: 147

Which SQL excute will get result ‘ABC’?

Answer

SELECT ‘ABC’ || NULL FROM DUAL ;

SELECT TRIM(LPAD(‘ABC’,10,’ ‘))  FROM DUAL ;

SELECT ” ||’ABC’  FROM DUAL ;

SELECT RTRIM(LPAD(‘ABC’,10,’ ‘))  FROM DUAL ;

Question: 148

Which of the following is correct about aggregate functions?

Answer

SUM(Column)，To increase the total，data can use number、string or date

count(Column) and count(*)，If the different number of calculated, there maybe a null result。

count(Column) include null result。

None of the above。

Question: 149

which of the following statements are wrong?

Answer

select rpad(lpad(‘hello’,6,’a’),8,’a’)from dual; the result of execution is helloaa

select length(to_char(90.01)) + substr(‘12345’,2,4) from dual ; the result of execution is 2349

select initcap(‘HAPPY birthday’) from dual; the result of execution is HAPPY Birthday

select instr(‘congratulations’,’at’,1,2) from dual ; the result of execution is 10

Question: 150

SQL> select * from TEST_TABLE;

COL1 COL2
——————– ——————–
A1 1
A2 NULL
A3 1
A4 2
which of the following statements are correct?

Answer

select count(*) from TEST_TABLE; the result of execution is 4

select count(col2) from TEST_TABLE; the result of execution is 3

select count(distinct col2) from TEST_TABLE; the result of execution is 2

select count(1) from TEST_TABLE; the result of execution is 4

select sum(col2) from TEST_TABLE; the result of execution is 4

Question: 151

Run this powerscript code,the messagebox will be show what message?

string lsData,lsCode
lsCode = ‘A’
setnull(lsData)
select decode(to_number(:lsCode),’A’,’ans1′,65,’ans2′) into :lsData from dual;
messagebox(”,lsData) 

Answer

ans1

ans2

null

not show any message

Question: 152

there are two table Order &Customer (picture)
Which of following SQL commands will list the customers, even who have not made any orders yet.

Answer

SELECT * FROM Customer  C INNER JOIN  Order  O ON  O.CUSTOMER_ID=C.CUSTOMER_ID

SELECT * FROM Customer  C RIGHT JOIN  Order  O ON  O.CUSTOMER_ID=C.CUSTOMER_ID

SELECT * FROM Customer  C LEFT JOIN  Order  O ON  O.CUSTOMER_ID=C.CUSTOMER_ID

SELECT * FROM Customer  C FULL OUTER JOIN Order O ON O.CUSTOMER_ID = C.CUSTOMER_ID

Question: 153

Which SELECT statement will get the result ‘elloworld’ from the string ‘HelloWorld’?

Answer

SELECT SUBSTR( ‘HelloWorld’,1) FROM dual

SELECT INITCAP(TRIM (‘HelloWorld’, 1,1)) FROM dual

SELECT LOWER(SUBSTR(‘HelloWorld’, 1, 1) FROM dual

SELECT LOWER(SUBSTR(‘HelloWorld’, 2, 1) FROM dual

SELECT LOWER(TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual