Total Pageviews

February 20, 2015

2/20/2015 11:26:00 PM
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)

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.