1.
Which cursor attribute evaluates to TRUE if the most
recent SQL statement affects one or more rows?
A). SQL%FOUND. B) .SQL%ISOPEN. C).SQL%ROWCOUNT.
D). SQL%NOTFOUND.
2.
Evaluate this PL/SQL block:
DECLARE
v_quota BOOLEAN := TRUE;
v_stock BOOLEAN := NULL;
v_approval BOOLEAN;
BEGIN
v_approval := v_quota AND v_stock;
END;
Which value is assigned to the
V_APPROVAL?
A). TRUE
B). Null C). False D).None
3.
Which character function converts mixed case character
strings to lowercase?
A). NVL B). UPPER C) . LOWER D).
LENGTH
E). SUBSTR F). INITCAP
4.
The TEACHER table contains these columns:
ID
NUMBER(9)
SALARY
NUMBER(7,2)
SUBJECT_ID NUMBER(3)
You need to create a SQL script that will prompt the user to
input an id number and a percent increase value. Each teacher's salary should be multiplied by
the percent increase provided.
Which SQL*Plus script would you use to achieve the desired
results?
A.
UPDATE
teacher
SET salary = salary * &increase;
B.
UPDATE
teacher
SET salary = salary * &increase
WHERE id = &id;
C.
UPDATE
teacher
SET salary = &salary
WHERE subject_id = &subject_id
D.
UPDATE teacher
SET salary = salary * &increase
WHERE subject_id = &subject_id
5.
Which characteristic relates to a single row function?
A.
Can be nested.
B.
Can only be used in a WHERE clause.
C.
May return more than one result per row.
D.
Can use a column position as an argument.
6.
Which characteristic applies to an implicit cursor?
A.
Will process only one row.
B.
Will attempt only one fetch.
C.
Allows the programmer to control the number of fetches
performed.
D.
Will perform only one fetch and will process all of the
rows returned by the query.
7.
Which two clauses must be included in a SELECT
statement? (Choose two.)
A). FROM B). WHERE C).
SELECT
D). HAVING E). ORDER BY F).
GROUP BY
8.
Which operator would it be most appropriate to use with
a single row subquery?
A). = B). IN C).
LIKE D). BETWEEN
9.
Which PL/SQL section contains SQL statements to
manipulate data in the database?
A). Header B). Exception C).
Executable D). Declarative.
10.
You attempt to query the database with this command:
SELECT NVL (100 / quantity, none)
FROM inventory;
Why does this statement cause an
error when QUANTITY values are null?
A.
The expression attempts to divide by a null value.
B.
The datatypes in the conversion function are
incompatible.
C.
The character string none should be enclosed in single
quotes (' ').
D.
A null value used in an expression cannot be converted
to an actual value.
11.
You issue this command:
CREATE FORCE VIEW parts_vu
(company, contact)
AS SELECT manufacturer_name,
contact_name
FROM inventory
WITH READ ONLY;
Which command can be issued on the
PARTS_VU view?
A). UPDATE B). DELETE C). SELECT D).
INSERT
12.
You logged onto the database to update the INVENTORY
table. After your session began, you
issued three UPDATE commands and then you issued an ALTER table command to add
a column constraint. You were about to
issue a COMMIT command when the system crashed.
Which changes were made to the INVENTORY table?
A.
Only the UPDATE commands.
B.
Only the ALTER TABLE command.
C.
both the UPDATE commands and the ALTER TABLE command
D.
NONE.
13.
On which two objects can a view be based? (Choose two.)
A). VIEW B). QUERY C). TABLE
D). COMMAND E). TRANSACTION
14.
Which privilege is an object privilege?
A). INDEX B). DROP USER C). CREATE SESSION
D). BACKUP ANY TABLE
15.
Which guideline relates to the GROUP BY clause?
A). Are in ascending order B). Can use a column alias.
C). Can use a positional notation. D). Can be followed by a WHERE clause.
16.
Which type of variable has a colon (:) prefix in a
PL/SQL statement?
A.
declared PL/SQL variable
B.
SQL*Plus global variable
C.
PL/SQL function variable
D.
PL/SQL procedure variable
17.
Evaluate this SQL script:
CREATE USER hr IDENTIFIED BY hr01;
CREATE ROLE hr_director;
GRANT hr_director TO hr;
GRANT SELECT ON teacher TO
hr_director;
CREATE OR REPLACE ROLE hr_director
/
How many users are granted the
HR_DIRECTOR role and how many privileges are granted to the HR_DIRECTOR role?
A.
1 user and 1 privilege
B.
1 user and no privileges
C.
no users and 1 privilege
D.
no users and no privileges
18.
In which section of a PL/SQL block could a new value be
assigned to an initialized variable?
A). END B). HEADER C). EXECUTEABLE
D). DECLARATIVE E).
EXCEPTION HANDLING
19.
Evaluate this PL/SQL loop:
WHILE v_price < &&p_count
LOOP
v_amount := v_amount + 1;
v_price := v_amount *
&&p_pct_increase;
END LOOP ;
If V_PRICE is 45, P_COUNT is 75, and V_AMOUNT is 5, which
P_PCT_INCREASE value would cause the loop to terminate?
A). 0 B). 15 C).
20 D). 45 E). 75
20.
You attempt to query the database with this command:
SELECT 100/NVL(quantity, 0)
FROM inventory;
Why does this statement cause an
error when QUANTITY values are null?
A.
The expression attempts to divide by zero.
B.
The expression attempts to divide by a null value.
C.
The datatypes in the conversion function are
incompatible.
D.
A null value used in an expression cannot be converted
to an actual value.
21.
Evaluate this command:
SELECT id_number
"Part Number", SUM(price) "Price"
FROM inventory
WHERE price > 5.00
GROUP BY "Part
Number"
ORDER BY 2;
Which clause will cause an error?
A.
ORDER BY 2;
B.
FROM inventory
C.
WHERE price > 5.00
D.
GROUP BY "Part Number"
E.
SELECT id_number "Part Number", SUM(price)
"Price"
22.
For which task would you use the TO_DATE function?
A.
to convert a number value to a date value
B.
to convert a date value to a number value
C.
to convert a date value to a VARCHAR2 character string
D.
to convert a character string representing a date to a
date value
23.
You query the database with this command:
SELECT id_number, description, price
"Cost Per Unit"
FROM inventory
WHERE price > 5.00
ORDER BY "Cost Per Unit";
How is the data sorted?
A). Randomly B). ascending numerically C). descending numerically
D). ascending alphabetically E). descending alphabetically
24.
Evaluate this command:
SELECT COUNT(*)
FROM inventory
WHERE price > 5.00
GROUP BY manufacturer_id
HAVING COUNT(*) > 10
ORDER BY order_date;
Which clause specifies which rows
will be returned from the INVENTORY table?
A.
WHERE price > 5.00
B.
HAVING COUNT(*) > 10
C.
ORDER BY order_date;
D.
GROUP BY manufacturer_id;
25.
When sorting query results from a table, what is the
sort limit?
A). one column B). the number of
columns in the table
C). the number of columns in the
SELECT list D). none
26.
Which guideline relates to a cursor FOR loop?
A.
requires an OPEN statement
B.
requires a terminating condition
C.
does not require a FETCH statement
D.
must declare the record that controls the loop
27.
Evaluate this IF statement:
IF v_num < 10 THEN
v_example := 1;
ELSIF v_num < 15 THEN
v_example := 2;
ELSIF v_num < 20 THEN
v_example := 3;
ELSIF v_num < 39 THEN
v_example := 4;
ELSE v_example := 5;
END IF;
If V_NUM is 15, which value would
be assigned to V_EXAMPLE?
A). 1 B). 2 C).
3 D). 4 E). 5
28.
Evaluate this PL/SQL block:
BEGIN
UPDATE teacher
SET salary = salary *
1.05
WHERE subject_id IN (101, 102,
103);
COMMIT;
EXCEPTION
WHEN SQL%NOTFOUND = TRUE THEN
dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT));
END;
What would cause output to be
displayed?
A). An error occurs. B). No rows were updated.
C). Only one row was updated. D). More than one row was updated.
29.
Which type of commands are supported by PL/SQL?
A). DDL B). DCL C). DML D).
All are supported by PL/SQL.
30.
Which operator could be used to compare a known value
to a NULL value?
A). = B). != C). <> D). IS NULL
31.
Which two commands would cause an implicit COMMIT
command? (Choose two.)
A). GRANT B). UPDATE C). CREATE D). COMMIT
E). SELECT F). ROLLBACK
32.
Which clause restricts the groups of rows displayed to
those groups meeting a specified condition?
A). FROM B). WHERE C). SELECT
D). HAVING E). GROUP BY F).
ORDER BY
33.
Which clause would you use in an ALTER TABLE command to
drop the PRICE column from the INVENTORY table?
A). DROP B). ALTER C). DELETE
D). REMOVE E). A column cannot be dropped from a table.
34.
Which privilege is a system privilege?
A). SELECT B). UPDATE C).
DELETE
D). REFERENCES E). CREATE TABLE
35.
You query the database with this command:
SELECT
price
FROM
inventory
WHERE price (BETWEEN 1 AND 50)
OR
(price IN(25, 70, 95)
AND price BETWEEN 25 AND
75);
Which value could the statement
retrieve?
A). 30 B). 51 C).
75 D). 95
36.
What are two advantages of using the %TYPE attribute to
declare a PL/SQL variable? (Choose two.)
A.
The data type of the underlying database column may be
unknown.
B.
The data type of the underlying database column may
change at runtime.
C.
All column constraints are applied to variables
declared using %TYPE.
D.
The number and data types of the underlying database
columns may be unknown.
E.
The number and data types of the underlying database
columns may change at runtime.
37.
Which statement would you use to query the database for
the ID_NUMBER and DESCRIPTION values of each item that was ordered before January 1, 1997 and whose
price is less than 1.00 or greater than 5.00?
A.
ELECT
id_number, description
FROM inventory
WHERE price IN (1.00, 5.00)
OR order_date < '01-JAN-97';
B.
SELECT
id_number, description
FROM inventory
WHERE price BETWEEN 1.00 AND 5.00
OR order_date < '01-JAN-97';
C.
SELECT
id_number, description
FROM inventory
WHERE price < 1.00
OR price > 5.00
AND order_date < '01-JAN-97';
D.
SELECT
id_number, description
WHERE (price <1.00 OR price > 5.00)
AND order_date < '01-JAN-97';
FROM inventory
38.
Which type of constraint can be defined at the column
or table level and can apply to any column in the table, not just the column on
which it is defined?
A). CHECK B). UNIQUE C).
NOT NULL
D). FOREIGN KEY E). PRIMARY KEY
39.
If you are writing a SELECT statement to join three
tables, what is the minimum number of join conditions needed?
A). 0 B). 1 C).
2 D). 3
40.
Which ALTER TABLE statement would you use to add a
primary key constraint on the MANUFACTURER_ID column of the INVENTORY table?
A.
ALTER TABLE inventory
ADD CONSTRAINT manufacturer_id
PRIMARY KEY;
B.
ALTER TABLE inventory
ADD CONSTRAINT PRIMARY KEY
(manufacturer_id);
C.
ALTER TABLE inventory
MODIFY manufacturer_id CONSTRAINT
PRIMARY KEY;
D.
ALTER TABLE inventory
MODIFY CONSTRAINT PRIMARY KEY
manufacturer_id;
41.
Evaluate this command:
1. CREATE TABLE sale_price
2. (id_number$ NUMBER(35)
3. CONSTRAINT sale_price_pk PRIMARY KEY,
4. description# VARCHAR2(200),
5. price& NUMBER(8,2));
Which line contains an error?
A). 1 B).
2 C). 3 D). 4 E). 5
42.
Evaluate this command:
SELECT id_number
FROM inventory
WHERE
manufacturer_id IN
(SELECT manufacturer_id
FROM inventory
WHERE price < 1.00
OR price > 6.00);
How many values can the subquery
return?
A). 0 B). ONLY 1 C).
UP TO 2 D). UNLIMITED
43.
Which two operators can be used in an outer join
condition? (Choose two.)
A). = B). OR C).
IN D).
AND
44.
You attempt to query the database with this command:
SELECT inventory.id_number,
manufacturer.id_number
FROM inventory i, manufacturer m
WHERE i.manufacturer_id = m.id_number
ORDER BY 1;
Which clause causes an error?
A.
ORDER BY 1;
B.
FROM inventory i, manufacturer m
C.
WHERE i.manufacturer_id = m.id_number
D.
SELECT inventory.id_number, manufacturer.id_number
45.
For which condition would you use an equijoin query?
A.
The INVENTORY table has two corresponding columns.
B.
The INVENTORY and MANUFACTURER tables have corresponding columns.
C.
The INVENTORY and MANUFACTURER tables do not have any corresponding
columns.
D.
The INVENTORY and MANUFACTURER tables have corresponding columns, but
the column in the INVENTORY table contains null values that need to be
displayed.
46.
In which clause would you place the outer join condition?
A). FROM B).
WHERE C). SELECT
D). HAVING E).
GROUP BY F). ORDER BY
47.
You query the database with this command:
SELECT
manufacturer_id
FROM
inventory
WHERE
manufacturer_id LIKE '%N\%P\%O%' ESCAPE \;
For
which character pattern is the LIKE operator searching?
A). NPO B).
N\P\O C). N%P%O D). N\%P/%O
48.
You issue this command:
GRANT update
ON inventory
TO joe
WITH GRANT OPTION;
Which
task has been accomplished?
A.
Only a system privilege was given to user JOE.
B.
Only an object privilege was given to user JOE.
C.
User JOE was granted all privileges on the object.
D.
Object privilege and a system privilege were given to user JOE.
49.
Which select statement will execute successfully.
A)
select * from emp
Where
deptno=10and sal > 2000;
B)
Select * from emp
Where
deptno=10andsal > 2000;
C)
Select * from emp
Where
deptno=10 andsal > 2000;
D)
None of the above.
50.
If the buffer is empty then you can go into buffer by Edit command.
A) True B)
False
0 comments:
Post a Comment