1. Which data type should be used for calculating statistical probabilities with varying decimal placements (ie, 5.1236, 5, 5.1, 5.001)?
a. LONG
b. NUMBER
c. NUMBER(p,s)
d. INTEGER
2. If a DROP TABLE command is executed on a table, ______.
a. Any appending transactions are rolled back.
b. The structure of the table remains in the database and the indexes are deleted.
c. The DROP TABLE command can be executed on a table on which there are pending transactions.
d. The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed.
3. Examine the structure of the PUPILS table:
Name Null Type
PUPIL ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
ADDRESS VARCHAR2(50)
GRADUATION DATE
What statement will add a new column after the NAME column to hold phone numbers?
a. ALTER TABLE pupils
ADD COLUMN3(phone varchar2(9))
b. ALTER TABLE pupils
ADD COLUMN3(phone varchar2(9)AS COLUMN3;
c. ALTER TABLE pupils
ADD COLUMN3(phone varchar2/9)POSITION 3;
d. Position cannot be specified when a new column is added.
4. Which three SQL arithmetic expressions will return a date?
a. ‘09-dec-99’ + 6
b. ‘09-dec-99’ - 12
c. ‘09-dec-99’ + (12/24)
d. ‘09-dec-99’ - ‘10-dec-99’
e. (‘09-dec-99’ - ‘10-dec-99’) /6
f. (‘09-dec-99’ - ‘10-dec-99’) /12
5. Which statement should be used to add and immediately enable a primary key constraint to the customer table using the id-number column?
a. This cannot be done.
b. ALTER TABLE customer
ADD CONSTRAINT cus-id-pk PRIMARY key(id-number);
c. ALTER TABLE customer
ADD (id-number CONSTRAINT cus-id-pk PRIMARY KEY);
d. ALTER TABLE customer
MODIFY(id-number CONSTRAINT cus-id-pk PRIMARY KEY);
6. Which of the following SELECT statements will query the worker table and retrieve the last name and salary of the employee whose idea is 5?
a. SELECT last-name,salary
FROM worker;
b. SELECT last-name,salary
FROM worker;
WHERE id=5;
c. SELECT last-name,salary
INTO v-last-name,v-salary
WHERE id=5;
d. SELECT last-name,salary
FROM worker;
INTO v-last-name,v-salary
WHERE id=5;
7. The structure of the division table is as follows:
Name Null? Type
DIVISION NO Not NULL Number(20)
DNAME VARCHAR2(12)
LOC VARCHAR2(13)
LOC VARCHAR2(13)
Examine the following declaration:
DECLARE
TYPE division-record-type is RECORD
(dno NUMBER,
name VARCHAR(20));
division-recdivision-record-type;
How can you retrieve an entire row of the division table using the division-rec variable?
a. SELECT*
INTO division-rec
FROM division
WHERE division no=10;
b. SELECT divisionno,dname,loc
INTO division-rec
FROM division
WHERE division no=10;
c. You can’t retrieve an entire row using the division-rec variable declared in the code.
d. SELECT*
INTO division-rec.dno,division-rec.name,division-rec
FROM division
WHERE division no=10;
8. Examine the following:
DECLARE.
CURSOR worker-cursor IS
SELECT wname,divisionno
FROM worker;
worker-rec worker-cursor %ROWTYPE
BEGIN
OPEN worker-cursor
LOOP
FETCH worker cursor
INTO worker-rec
EXIT WHEN worker-cursor NOT FOUND;
INSERT INTO temp-worker(name’dno)
VALUES(worker-rec.wname,worker-rec divisionno);
END LOOP;
CLOSE worker-cursor;
END;
Using a cursor FOR loop, which PL/SQL block is equivalent to the above code?
a. DECLARE
CURSOR work-cursor 1S
SELECT wname,divisionno
FROM work;
BEGIN
FOR work-rec IN work-cursor LOOP
INSERT INTO temp-work(name,dno)
VALUES (work-rec.wname,
work-re.divisionno);
END LOOP
END;
b. DECLARE
CURSOR work-cursor 1S
SELECT wname,divisionno
FROM work;
BEGIN
FOR work-rec IN work-cursor LOOP
OPEN work-cursor;
INSERT INTO temp-work(name,dno)
VALUES (work-rec.wname,
work-re.divisionno);
END LOOP
END;
c. DECLARE
CURSOR work-cursor 1S
SELECT wname,divsisionno
FROM work;
BEGIN
FOR work-rec IN work-cursor LOOP
OPEN work-cursor;
INSERT INTO temp-work(name,dno)
VALUES (work-rec.wname,
work-re.divisionno);
END LOOP
CLOSE work-cursor;
END;
d. The above code cannot be simulated with a LOOP.
9. An explicit cursor must be used ______.
a. When any DML or select statement is used in a PL/SQL block.
b. When a delete statement in a PL/SQL block deletes more than one row.
c. When a select statement in a PL/SQL block is more than one row.
d. When an update statement in a PL/SQL block has to modify more than one row.
10. Examine the following:
DECLARE
CURSOR query_cursor (v_salary) IS
SELECT LAST_NAME, SALARY, DIVISION_NO
FROM WORKER
WHERE SALARY>V_SALARY;
Why does this statement cause an error?
a. The parameter mode was not defined.
b. A WHERE clause is not allowed in a cursor statement.
c. The INTO clause is missing from the SELECT statement.
d. A scalar type was not specified for the parameter.