1. You need to create a report to display the ship date and order totals of your ordid table. If the order
has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the ordid table the
ship date column has a data type of date the total column has a data type of number.

Which statement do you use to create this report?

A.
Select ordid, shipdate В“Not shippedВ”, total В“Not availableВ”
FROM order;
B. Select ordid, NVL (shipdate В‘Not shippedВ’),
NVL (total, В“Not
availableВ”)
FROM order;
C. Select ordid, NVL (TO_CHAR (shipdate), В‘Not shippedВ’),
NVL (TO_CHAR (total), В‘Not availableВ’)
FROM order;

D. Select ordid, TO_CHAR (shipdate, В‘Not shippedВ’)
TO_CHAR (total, В‘Not availableВ’)
FROM order;

Ans: C

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

A.
DROP TABLE student;
B. TRUNCATE TABLE student;
C. DELETE* FROM student;
D. TRUNCATE TABLE student KEEP
STRUCTURE;
E. DELETE* FROM student
KEEP
STRUCTURE.

Ans: B

3. Evaluate this IF statement:

IF v_value>100 THEN
v_new-value:=2*v-value;
ELSIF v-value>200 THEN
v-new-
value:=3*v-value;
ELSIF v-value>300 THEN
v-new-value:=4*v-value;
ELSE
v-new-value:=5*v-value;
END IF

What would be assigned to
v_new_value if v_value=250?

A. 250
B. 500
C. 750
D. 1000

Ans: B

4. You need to create a PL/SQL program to insert records into
employee table. Which block of code successfully uses the insert command?

A.DECLARE
v_hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,
ename, hiredate, deptno)
VALUES(empno_sequence.nextval, В‘and nameВ’,v_hirerdate and deptno)

B. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN

INSERT INTO emp(empnp,ename,hiredate,deptno)

C. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,hiredate)

VALUES(empno_sequence.nextval, name, v_hirerdate)
END:

D. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO
emp(empnp,ename,heridate,deptno)
VALUES(empno_sequence.nextval, В‘and nameВ’,v_herdate and deptno)
Job=Clerk
END:

Ans: C

5. Examine
the code:

SET SERVER OUTPUT ON
DECLARE
v_char_val varchar2(100);
BEGIN
v_char_val:= В‘Hello WorldВ’,
DBMS_OUTPUT.PUT_LINE(v_char_val);

END
SET SERVER OUTPUT OFF

This code is stored in a script file name В“myproc,sqlВ”. Which statement executes the code in the script file?


A. Myproc.sql
B. RUN myproc,sql
C. START myproc.sql
D. EXECUTE myproc.sql
E. BEGIN myproc.sql END;

Ans: C

6. Which statement is
true about nesting blocks?

A. Variable name must be unique between blocks.

B. A variable defined in the outer block is visible in the inner
blocks.

C. A variable defined in the inner block is visible in the outer blocks.

D. A variable is in an inner block may have the same name
as a variable in an outer block only if the data types are different.

Ans: B

7. In SQL Plus You issued this command:

Delete from dept
where dept_id=901

You received an integrated constraint error because the child record was found. What could you do to make the statement
execute?

A. Delete the child record first.

B. You cannot make the command
execute.

C. Add a fourth keyword to the
command.
D. Add
the constraints cascade option to the command.

Ans: A

8. Examine this block of code:

SET SERVER OUTPUT ON
Declare
X NUMBER;
V_SAL
NUMBER;
V_found VARCHAR2(10) := В‘TRUEВ’
Begin
X:=1;
V_SAL :=1000;
Declare
V_found VARCHAR2(10);
Y NUMBER;
Begin
IF (V_sal>500)
THEN
V_found := В‘YESВ’;
END IF;
DBMS_OUTPUT.PUT_LINE(В‘value of V_found isВ’ || V_found);
DBMS_OUTPUT.PUT_LINE (В‘value of V_sal isВ’ ||
V_sal);
Y:20;
END
DBMS_OUTPUT.PUT_LINE (В‘value of V_found isВ’ || V_found);
DBMS_OUTPUT.PUT_LINE (В‘value of Y isВ’ || TO_CHAR (Y);
END


Why does this code produce an error when executed?

A. The value of V_found cannot be YES.
B. Variable V_found is declared at more than one
location.
C. Variable Y is declared in the inner block and referenced in the outer block.
D. Variable V_sal is declared in the outer block and
referenced in the inner block.

Ans: C

8. Examine code:

SET SERVEROUTPUT ON
DECLARE
v_name emp.ename%TYPE;
v_num NUMBER;
v_sal
NUMBER(8,2);
BEGIN
--- This code displays salaries if larger than 10,000.
SELECT ename, sal
INTO v_name, v_sal
FROM emp
WHERE empno=101;

IF(v_sal.GT.10000) THEN
DBMS_OUTPUT.PUT_LINE(В‘Salary is В‘||В’ v_sal
|| В‘for employeeВ’ || v_name);
END IF;
END
SET SERVER OUTPUT OF


This statement produces a compilation error when above PL/SQL block is executed?

A. v_num NUMBER;
B. v_name emp.ename%TYPE;
C. IF
(v_sal.GT.10000) THEN
D. ---- This code displays salaries if larger than 10000.
E. SELECT ename, sal
INTO v_name, v_sal
FROM emp
WHERE
empno=101;

Ans: C

9. Which data type should you use for interest rates with varying and unpredictable decimal places such as 1.234, 3.4, 5
and 1.23?

A. LONG.
B. NUMBER.
C. NUMBER(p, s)

Ans: B

10. Which SELECT statement would you use in a PL/SQL block to query the
employee table and retrieve the last name and salary of the employee whose ID is 3?

A. SELECT last_name,salary
FROM employee;
B. SELECT
last_name,salary
FROM employee;
WHERE id=3;
C. SELECT last_name,salary
INTO v_last_name,v_salary
FROM employee
WHERE id=3;
D. SELECT l
last_name,salary
FROM employee;
INTO v_last_name,v_salary
WHERE id=3;
E. SELECT last_name,salary
INTO v_last_name,v_salary
WHERE id=3;


Ans: C

---