1. You view a card, ANN_SAL, that is based on the worker table. The structure of the ANN_SAL view is:
NAME NULL TYPE
WORKERNO NOT NULL NUMBER(4)
YEARLY_SAL NUMBER(9,2)
MONTHLY_SAL NUMBER(9,2)
Which statement retrieves data from the ANN_SAL view?
a. SELECT * FROM ANN_SAL
b. SELECT * FROM WORKER
c. SELECT * FROM VIEW ANN_SAL
d. SELECT * FROM VIEW ANN_SAL IS DON WORKER
2. Evaluate the following:
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
3. The PARTICIPANTS table contains the following columns:
ID NUMBER(9)
NAME VARCHAR(2)
COACHID NUMBER(9)
Evaluate the following statements:
SELECT p.name,c.name
FROM participant p,participant c
WHERE c-id= c.coach-id;
SELECT p.name,c.name
FROM participant p,player c
WHERE c.coach-id=p.id;
How will the results of the two statements differ?
a. The first statement will not execute; the second statement will.
b. The first statement will execute; the second statement will not execute.
c. The first statement is a self join; the second statement is not.
d. The results will be the same, but will be displayed differently.
4. How would you declare a PL/SQL table of records to hold the rows selected from the WORKER table?
a. DECLARE
worker-table is TABLE OF worker%ROWTYPE
b. BEGIN
TYPE worker-table is TABLE of worker%ROWTYPE
worker-table worker-table-type;
c. DECLARE
TYPE worker-table is TABLE of worker%ROWTYPE
INDEX BY WHOLE NUMBER:
worker-table worker-table-type;
d. DECLARE
TYPE worker-table is TABLE of worker%ROWTYPE
INDEX BY BINARY INTEGER.
worker-table worker-table-type;
5. Which type of cursor should be created when you want to create a cursor that can be used several times in a block, selecting a different active set each time it is opened?
a. A loop cursor.
b. A multiple selection cursor.
c. A cursor for each active set.
d. A cursor that uses parameters.
6. Which of the following is true when writing a cursor for loop?
a. You must explicitly fetch the rows within a cursor for loop.
b. You must explicitly open the cursor prior to the cursor for loop.
c. You must explicitly close the cursor prior to the end of the program.
d. You do not explicitly open, fetch, or close a cursor within a cursor for loop.
7. The structure of the HOUSE table is as follows:
Name Null? Type
HOUSE NO Not NULL Number(25)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Examine the following:
DECLARE
house-rec house%ROWTYPE:
BEGIN
SELECT*
INTO house-rec
FROM house.
WHERE houseno=10;
END;
Which PL/SQL statement displays the location of a selected department?
a. DBMS-OUTPUT.PUT_LINE(house-rec):
b. DBMS.OUTPUT.PUT_LINE(house-rec-loc);
c. DBMS.OUTPUT.PUT_LINE(house-rec(1).loc);
d. You can’t display a single file in the record because they are not specially identified in the declarative section.
8. Which of the following statements about implicit cursors is true?
a. They are declared implicitly only for DML statements.
b. The are declared implicitly for all DML and SELECT statements.
c. Implicit cursors must be closed before the end of PL/SQL programs.
d. Implicit cursors can be declared using the cursor type in the declaration section.
9. Evaluate the following:
DECLARE
v-result NUMBER(2);
BEGIN
DELETE
FROM worker
WHERE division-id IN(10,20,30);
v-result:= SQL/ROWCOUNT;
COMMIT;
END;
What will be the value of v_result if no rows are deleted?
a. 0
b. 1
c. True
d. Null
10. Which two conditions in a PL/SQL block cause an exception error to occur?
a. Select statement does not return a row.
b. Select statement returns more than one row.
c. Select statement contains a group by clause.
d. Select statement does not have a WHERE clause.
11. You must create a program to insert records into the worker table. Which of the following successfully uses the INSERT command?
a. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(workernp, wname, hiredate, divisionno)
VALUES(workerno-sequence.nextval, ‘and name’, v_hiredate and divisionno)
b. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(workernp, wname, hiredate, divisionno)
c. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(workernp, wname, hiredate)
VALUES(workerno-sequence.nextval, ‘and name’, v_hiredate and divisionno)
END:
d. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(wordernp, wname, v_hiredate and divisionno)
Job=Clerk
END:
12. Evaluate the following:
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;
ROLL BACK;
END.
How many values will be inserted into the test table?
a. 0
b. 4
c. 6
d. 8
e. 12
13. You issue the following command:
CREATE public synonym WORKER for ed.worker;
What is the result of the command?
a. The object can be accessed by all users.
b. All users are given object privileges to the table.
c. The need to qualify the object name with its schema is eliminated only for the commanding issuer.
d. The need to qualify the object name with its schema is eliminated for all users.
14. In which order does an Oracle Server evaluate clauses?
a. HAVING, WHERE, GROUPBY
b. WHERE, GROUPBY, HAVING
c. GROUPBY, HAVING, WHERE
d. WHERE, HAVING, GROUPBY
15. You query a database with the following command:
SELECT section_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data))
FROM worker WHERE AVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60
GROUP BY by section_no
ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date));
a. A SELECT clause cannot contain a group function.
b. A WHERE clause cannot be used to restrict groups.
c. An ORDER BY clause cannot contain a group function.
d. A group function cannot contain a single row function.
16. The path table contains the following columns:
ID NUMBER(7) PK
COST NUMBER(7,2)
PRODUCT_ID NUMBER(7)
Evaluate the following SQL statements:
SELECT ROUND(max(cost),2)’
ROUND(min(cost),2), round(sum(cost),2),
ROUND(AVG(cost),2)
FROM part;
SELECT product_id, ROUND(max(cost),2),
ROUND(min(cost),2), ROUND(sum(cost),2),
ROUND(AVG(cost),2)
FROM part
GROUPBY product_id;
How will the results of the two statements differ?
a. The results will be the same, but displayed differently.
b. The first statement will only display one row of results; the second statement can display more than one row of results.
c. The first statement will display a result for each part; the second statement will display a result for each product.
d. One of the statements will cause an error.
17. In which section of a PL/SQL block is a user-defined exception written?
a. Heading
b. Executable
c. Declarative
d. Exception handling
18. Examine the following:
SET SERVER OUTPUT ON
DECLARE
v_char_val varchar2(100);
BEGIN
v_char_val:= ‘Welcome Home’,
DBMS_OUTPUT.PUT_LINE(v_char_val);
END
SET SERVER OUTPUT OFF
This code is stored in a script file named welcome.sql. Which of the following statements will execute the code in the script file?
a. welcome.sql
b. RUN welcome.sql
c. START welcome.sql
d. EXECUTE welcome.sql
19. Which of the following statements regarding nesting blocks is true?
a. Variable names must be unique between blocks.
b. A variable defined in the outer block is visible in the inner block.
c. A variable defined in the inner block is visible in the outer block.
d. A variable in an inner block may have the same name as a variable in an outer block only if the data types are different.
20. Which of the following statements is valid within the executable section of a PL/SQL block?
a. BEGIN
Worker_rec worker%ROWtype
END;
b. WHEN NO_DATA FOUND THEN
DBMS_OUTPUT PUT.LIN(“Nothing found”);
c. SELECT wname,sal
INTO w_ename,w_sal
FROM worker
WHERE
workno=106;
d. Procedure cal_max(n1 NUMBER n2 NUMBER, p_max OUT NUMBER)
IS
BEGIN
If n1>n2 then
p_max:=n1;
Else
p_max=n2;
END.
21. What command will send the output of an SQL* Plus session to a text file named LOG.LST?
a. SAVE LOG.LST
b. SPOOL LOG.LST
c. PRINT LOG.LST
d. SEND LOG.LST
22. The merchandise table contains the following columns:
CODE NUMBER(9) PK
COST NUMBER(7,2)
SALE_PRICE NUMBER(7,2)
Your supervisor asks you to calculate net revenue per unit for each product if the cost of each product is increased 10% and the sale price of each product is increased 25%. You issue the following:
SELECT code, sale_price * 1.25 - cost * 1.10
FROM merchandise;
What conclusion can be drawn from the results?
a. Only the required results are displayed.
b. The results provide more information than management requested.
c. A function needs to be included in the SELECT statement to achieve the desired result.
d. The order of the statement must be changed to get the requested results.
23. You have been instructed to create a report that shows different jobs in each division within your company. No duplicate roles can be displayed. Which of the following SELECT statements should be used?
a. SELECT divisionno, job
FROM worker;
b. SELECT no duplicate divisionno, job
FROM worker;
c. SELECT distinct divisionno, job
FROM worker;
d. CREATE report
DISPLAY divisionno, job
24 Mrs. Jensen is president of her company. Four managers report to her, and all other employees report to the four managers. Examine the following:
SELECT worker.wname
FROM work worker
WHERE worker, workno not in
SELECT manager.mgr
FROM work manager;
The above statement returns no rows. Why?
a. All employees have a manager.
b. None of the employees have a manager.
c. A null value is returned from the subquery.
d. An operator is not allowed in subqueries.
