DB Programming - ORACLE

Question 1

SQL is

Accepted Answer

Case insensitive

Answer

Case sensitive

Question 2

Which option displays all real PL/SQL Packages

Accepted Answer

DBMS_OUTPUT
UTL_FILE
UTL_HTTP

Answer

UTL_OUTPUT
UTL_FILE
UTL_HTTP

Answer

DBMS_OUTPUT
UTL_FILE
DBMS_HTTP

Question 3

Select the correct syntax

Accepted Answer

SELECT db_name "Name", 
db_age "Age",
db_height "Height"
FROM tbl_people

Answer

SELECT db_name 'Name', 
db_age 'Age',
db_height 'Height' 
FROM tbl_people

Answer

Either is fine

Question 4

Select the correct syntax for constants:

Accepted Answer

TBL_NAME CONSTANT VARCHAR2(10) := 'tbl_name';

Answer

TBL_NAME CONST VARCHAR2(10) := 'tbl_name';

Answer

TBL_NAME VARCHAR2(10) AS CONST := 'tbl_name';

Answer

TBL_NAME VARCHAR2(10) AS CONSTANT := 'tbl_name';

Question 5

Select which statement is not true?

Accepted Answer

SUBSTR('This is a test', 6)
Result: 'This i'

Answer

SUBSTR('This is a test', 6)
Result: 'is a test'

Answer

SUBSTR('This is a test', 6, 2)
Result: 'is'

Answer

SUBSTR('TechOnTheNet', -6, 3)
Result: 'The'

Question 6

Which statement is not true?

Accepted Answer

SUBSTR('abcdefg',1,4) 
abcde

Answer

SUBSTR('abcdefg',3,4) 
cdef

Answer

SUBSTR('abcdefg',-5,4) 
cdef

Question 7

Which statement is true?

Accepted Answer

NVL(NULL, 50)
Result: 50

Answer

NVL(NULL, 50)
Result: NULL

Answer

NVL(NULL, 50)
Result: 'NULL'

Question 8

Select the correct syntax

Accepted Answer

FOR i in 1..10
LOOP
..
END LOOP

Answer

FOR i 1...10
LOOP
..
END LOOP

Answer

FOR i 1..10
LOOP
..
END LOOP

Question 9

Select CURSOR structure

Accepted Answer

DECLARE

CURSOR c1 is
SELECT name, age, address FROM 
tbl_people 
WHERE 
age > 18;

BEGIN
FOR r1 in c1 
LOOP

DBMS_OUTPUT.PUT_LINE(r1.name|| ' ' || r1.age);

END LOOP;

END;

Answer

DECLARE

CURSOR c1 is
SELECT name, age, address FROM 
tbl_people 
WHERE 
age > 18;

BEGIN
FOR r1 in c1 
LOOP

DBMS_OUTPUT.PUT_LINE(c1.r1.name|| ' ' || c1.r1.age);

END LOOP;

END;

Answer

DECLARE

CURSOR c1 is
SELECT name, age, address FROM 
tbl_people 
WHERE 
age > 18;

BEGIN
FOR r1 in c1 
LOOP

DBMS_OUTPUT.PUT_LINE(c1.name|| ' ' || c1.age);

END LOOP;

END;

Question 10

EX / IN CURSORS

Accepted Answer

Explicit cursor
When you declare a query in the declaration 
section of a block, you create an explicit cursor.
You will generally use an explicit cursor when 
you work with multiple records

Implicit cursor
A cursor that is defined for you by the system
eg SELECT…..INTO or any SQL DML statement
INSERT, UPDATE, DELETE

Answer

Implicit cursor
When you declare a query in the declaration 
section of a block, you create an implicit cursor.
You will generally use an implicit cursor when 
you work with multiple records

Explicit cursor
A cursor that is defined for you by the system
eg SELECT…..INTO or any SQL DML statement
INSERT, UPDATE, DELETE

Question 11

Select correct record structure:

Accepted Answer

DECLARE
TYPE DeptRec IS RECORD (
 dept_id dept.deptno%TYPE,
 dept_name VARCHAR2(14),
 dept_loc VARCHAR2(13),
 processed BOOLEAN);

v_dept_rec DeptRec;

BEGIN
..

END;

Answer

DECLARE
TYPE DeptRec IS %ROWTYPE(
 dept_id dept.deptno%TYPE,
 dept_name VARCHAR2(14),
 dept_loc VARCHAR2(13),
 processed BOOLEAN);

v_dept_rec DeptRec;

BEGIN
..

END;

Answer

DECLARE
TYPE DeptRec IS RECORD (
 dept_id dept.deptno%TYPE,
 dept_name VARCHAR2(14),
 dept_loc VARCHAR2(13),
 processed BOOLEAN);

v_dept_rec %ROWTYPE%DeptRec;

BEGIN
..

END;

Question 12

How does decode work?

Accepted Answer

DECODE(gender, 'M', 'Mr', 'F', 'Mrs', NULL)

Answer

DECODE(gender, 'M' || 'Mr', 'F' || 'Mrs', NULL)

Answer

DECODE(gender, 'M', 'Mr', 'F', 'Mrs')

Answer

DECODE('M', 'Mr', 'F', 'Mrs', gender)

Question 13

What are collections?

Accepted Answer

a datatype that offers a way to store singly dimensioned arrays in PL*Sql

Answer

a datatype that offers a way to store doubly dimensioned arrays in PL*Sql

Question 14

What is the visible DBMS result of this query? (assuming it's the 19/06/2016, at 11 in the morning)

DBMS_OUTPUT.PUT_LINE(TRUNC(SYSDATE));

Accepted Answer

'19/JUN/2016'

Answer

'19/06/2016'

Answer

'19/06/2016 11:00:00'

Answer

'19/JUN/2016 11:00:00'

Answer

Query fails

Question 15

How do you define variables in SQL?

Accepted Answer

define v_test = 'Example'

Answer

v_test VARCHAR2(10) := 'Example';

Answer

v_test VARCHAR2(10);
v_test := 'Example';

Question 16

Which statement returns only Saturday?

Accepted Answer

SELECT * WHERE
day_of_week_name LIKE '%URDAY'

Answer

SELECT * WHERE
day_of_week_name LIKE 'URDAY%'

Answer

SELECT * WHERE
day_of_week_name LIKE '%SAT'

Answer

SELECT * WHERE
day_of_week_name LIKE '%DAY'

Question 17

How do you log something in the server logs?

Accepted Answer

common.log()

Answer

DBMS_OUTPUT.log()

Answer

FILE_DIRECTORY.LOG()

Question 18

What key does oracle use to escape strings?

Accepted Answer

\

Answer

/

Answer

%

Answer

$

Question 19

IF / ELSE IF / ELSE structure in oracle

Accepted Answer

IF 
...
THEN
...
ELSIF
...
ELSE
...
END IF

Answer

IF 
...
THEN
...
ELSE IF
...
ELSE
...
END IF

Answer

IF 
...
THEN
...
ELSIF
...
ELSE
...
ENDIF

Answer

IF 
...
THEN
...
ELSE IF
...
ELSE
...
ENDIF

Question 20

Which is the better join method

Accepted Answer

SELECT m.merchantlastname, sum(to_number(trans.TRANSACTIONAMOUNT)) FROM 
fss_transactions trans join
fss_terminal term on term.TERMINALID = trans.terminalid
join fss_merchant m on term.merchantid = m.merchantid
GROUP BY m.merchantlastname;

Answer

SELECT * FROM fss_transactions trans, 
fss_terminal term, fss_merchant m
WHERE
term.terminalid = trans.terminalid AND
term.merchantid = m.merchantid;

Question 21

Select the correct result for:
LPAD('Heres a string', 20, 'x')

Accepted Answer

xxxxxxHeres a string

Answer

<5 white spaces>Heres a string

Answer

xxxxxHeres a string

Answer

<6 white spaces>Heres a string

Question 22

Which statement correctly displays '99999.98727' to 2 decimal places?

Accepted Answer

to_char(99999.98727, '9999999.99')

Answer

to_number(99999.98727, '9999999.99')

Answer

to_char(99999.98727, 'f99999.99')

Question 23

How do you create and use a SEQUENCE?

Accepted Answer

Create:
create sequence my_key
start with 1
maxvalue 99999999
increment by 1

Use:
my_key.CURRVAL
my_key.NEXTVAL

Answer

Create:
create sequence my_key
start in 1
maxvalue 99999999
increment by 1

Use:
my_key.CURRENTVAL
my_key.NEXTVAL

Answer

Create:
create sequence my_key
start with 1
maxvalue 99999999
increment by 1

Use:
my_key.CURRVALUE
my_key.NEXTVALUE

Answer

Create:
create sequence my_key
start in 1
maxvalue 99999999
increment by 1

Use:
my_key.CURRENTVALUE
my_key.NEXTVALUE

Question 24

Select correct syntax for creating a procedure

Accepted Answer

create or replace procedure prod_name(p_var VARCHAR2)
IS
...
BEGIN
...
END

Answer

create or replace procedure prod_name(p_var VARCHAR2(10))
IS
...
BEGIN
...
END

Answer

create or replace procedure prod_name(p_var VARCHAR2)
IS
DECLARE
...
BEGIN
...
END

Answer

create or replace procedure prod_name(p_var VARCHAR2(10))
IS
DECLARE
...
BEGIN
...
END

Question 25

Select correct syntax for creating a function

Accepted Answer

create or replace FUNCTION test_function(p_test VARCHAR2)
RETURN VARCHAR2 is
...
BEGIN
...
RETURN 'return this string';

END;

Answer

create or replace FUNCTION test_function(p_test VARCHAR2)
RETURN VARCHAR2 is
DECLARE
...
BEGIN
...
RETURN 'return this string';

END;

Answer

create or replace FUNCTION test_function(p_test VARCHAR2)
RETURN VARCHAR2 is
...
BEGIN
...
END;

Answer

create or replace FUNCTION test_function(p_test VARCHAR2)
RETURN VARCHAR2 is
...
BEGIN
...
END

RETURN 'return this string';

Question 26

Which statement declares 'v_test' as an hour before sysdate?

Accepted Answer

v_test := sysdate - 1/24

Answer

v_test := sysdate - 60

Answer

v_test := sysdate - 1

Answer

v_test := sysdate - 1*24

Question 27

Which is correct?

Accepted Answer

create or replace procedure here_we_go_magic (p_param VARCHAR2 DEFAULT NULL)
...

Answer

create or replace procedure here_we_go_magic (p_param VARCHAR2 DEFAULT IN NULL)
...

Answer

create or replace procedure here_we_go_magic (p_param VARCHAR2(10) DEFAULT IN NULL)
...

Answer

create or replace procedure here_we_go_magic (p_param VARCHAR2(10) DEFAULT NULL)
...

Question 28

Where do we put the 'INTO' statement

Accepted Answer

SELECT age INTO v_age
FROM tbl_person;

Answer

SELECT age
FROM tbl_person
INTO v_age;

Answer

SELECT age
FROM tbl_person
WHERE INTO v_age;

Question 29

What are commits, rollbacks and autonomous transactions

Accepted Answer

Autonomous transaction
A block where the main transaction is suspended while the autonomous transaction is performed

Transaction
A transaction is a logical unit of work

Commit
Save the changes, ran in code

Rollback
Remove changes, run in code

Answer

Autonomous transaction
A block where the contents are only run once the main transaction is completed

Transaction
A transaction is a logical unit of work

Commit
Save the changes, ran in code

Rollback
Remove changes, run in code

Question 30

How do you display the OTHERS error in the EXCEPTION block in DBMS_OUTPUT?

Accepted Answer

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM || ' ' || SQLCODE);

Answer

EXCEPTION
WHERE OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM || ' ' || SQLCODE);

Answer

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERR || ' ' || SQLCODE);

Answer

EXCEPTION
WHERE OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERR || ' ' || SQLCODE);

Question 31

What is PRAGMA?

Accepted Answer

Code that is to be executed at compile time

Answer

Code that is to be executed at run time

Answer

Code that is to be executed after run time

Question 32

Package structure:

Accepted Answer

CREATE PACKAGE test_pack AS
procedure transmit_data;
END test_pack;

CREATE PACKAGE BODY test_pack AS

procedure transmit_data IS
BEGIN
..
END;

END test_pack;

Answer

CREATE PACKAGE test_pack AS
transmit_data;
END test_pack;

CREATE PACKAGE BODY test_pack AS

procedure transmit_data IS
BEGIN
..
END;

END test_pack;

Answer

CREATE PACKAGE test_pack AS
procedure transmit_data;
END test_pack;

CREATE PACKAGE BODY test_pack AS

procedure transmit_data IS
BEGIN
..
END;

END PACKAGE BODY;

Answer

CREATE PACKAGE test_pack AS
transmit_data;
END test_pack;

CREATE PACKAGE BODY test_pack AS

procedure transmit_data IS
BEGIN
..
END;

END PACKAGE BODY;

Question 33

How do you use UTL?

Accepted Answer

DECLARE
v_file UTL_FILE.file_type;
v_buffer VARCHAR2(255);
...

BEGIN
v_file := UTL_FILE.fopen(DIRECTORY, FILENAME, MODE)
...
UTL_FILE.PUT_LINE(v_file, 'heres a string')
...
UTL_FILE.GET_LINE(v_file, v_buffer)
...
END;

Answer

DECLARE
v_file UTL_FILE.file_type;
v_buffer VARCHAR2(255);
...

BEGIN
v_file := UTL_FILE.fopen(DIRECTORY, FILENAME, MODE)
...
UTL_FILE.PUT_LINE(v_buffer, 'heres a string')
...
UTL_FILE.GET_LINE(v_buffer, v_file)
...
END;

Answer

DECLARE
v_file UTL_FILE%file_type;
v_buffer VARCHAR2(255);
...

BEGIN
v_file := UTL_FILE.fopen(DIRECTORY, FILENAME, MODE)
...
UTL_FILE.PUT_LINE(v_file, 'heres a string')
...
UTL_FILE.GET_LINE(v_file, v_buffer)
...
END;

Answer

DECLARE
v_file UTL_FILE%file_type;
v_buffer VARCHAR2(255);
...

BEGIN
v_file := UTL_FILE.fopen(DIRECTORY, FILENAME, MODE)
...
UTL_FILE.PUT_LINE(v_buffer, 'heres a string')
...
UTL_FILE.GET_LINE(v_buffer, v_file)
...
END;

DB Programming - ORACLE

Crie uma conta grátis para terminar este quiz

Perguntas do quiz (33)

Informações

Fale Conosco

O que você acha do novo visual?