Oracle SQL : Plsql Questions Part7

91.  Evaluate the following PL/SQL block:

BEGIN

For  I IN 1 . . 5 LOOP

IF i=1 THEN NULL;

   ELSIF i=3 THEN COMMIT;

   ELSIF i=5 THEN ROLLBACK;

   ELSE
 INSERT INTO calculate(results);

VALUES(i);

END IF;

END LOOP

COMMIT;

END;

How many values will be permanently inserted into the calculate table?

a.     0

b.     1

c.     2

d.     3

e.     4

f.     5

92.  Which of the following scripts could be used to query the data dictionary to view only the names of the primary key constraints using a substitution parameter for the table name?

a.     ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)

SELECT constraint_name

FROM user_constraint

WHERE table_name=upper(‘&table’)   AND constraint_type= ‘P’;

b.     ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)

SELECT constraint_name

FROM user_constraint

WHERE table_name=upper(‘&table’)   AND constraint_type= ‘PRIMARY’;

c.     ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)

SELECT constraint_name,constraint_type

FROM user_constraint

WHERE table_name=upper(‘&table’);

d.     ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)

SELECT constraint_name

FROM user_cons_columns

WHERE table_name=upper(‘&table’)   AND constraint_type= ‘P’;

93.  Match the Constraint Name to its appropriate Definition:

Constraint Name                   Definition                                                                                                                      

CHECK                                    The column must contain a value in each row.

NOT NULL                              Each value must be different in a column.

UNIQUE                                  The value must be unique and present.

PRIMARY KEY                     Defines a condition that each row must satisfy.

FOREIGN KEY                      Establishes a relationship between columns.

94.  What statement would be used to add a primary key constraint to the patient table using the id_number column, immediately enabling the constraint?

a.     This cannot be done.

b.     ALTER TABLE patient

MODIFY(id_number CONSTRAINT pat_id_pk PRIMARY KEY);

c.     ALTER TABLE patient

ADD (id_number CONSTRAINT pat_id_pk PRIMARY KEY);

d.     ALTER TABLE patient

ADD CONSTRAINT pat_id_pk PRIMARY KEY(id_number);

95.  You attempt to create the salary table with this command:

CREATE TABLE TENURE.

(worker_id NUMBER(9)

CONSTRAINT tenure_pk PRIMARY KEY,

1995_salary NUMBER(8,2),

NUMBER manager_name VARCHAR2(25)

CONSTRAINT mgr_name_nn NOT NULL,

$ salary_96 NUMBER(8,2));

Which two lines of the statement will return errors?

a.     1

b.     2

c.     3

d.     4

e.     5

f.     6

g.     7

96.  Which SELECT statement displays the Order ID, Product ID, and quantity of items in the merchandise table that matches both the Product ID and quantity of an item, order(20)?

a.     SELECT ordeid,prodid,qty

FROM merchandise

WHER (prodid,qty) IN

                        (SELECT prodid,qty

                        FROM merchandise

                        WHERE ordid=20)

b.     SELECT ordeid,prodid,qty

FROM merchandise

WHERE (prodid,qty) =

                        (SELECT prodid,qty

                        FROM merchandise

                        WHERE ordid=20);

ANDordid<>20;

c.     SELECT ordeid,prodid,qty

FROM merchandise

WHERE (prodid,qty) IN

                        (SELECT ordid,prodid,qty

                        FROM item

                        WHERE ordid=20);

AND ordid<>20;

d.     SELECT ordeid,prodid,qty

FROM merchandise

WHERE (prodid,qty) IN

                        (SELECT prodid,qty

                        FROM merchandise

                        WHERE ordid=20);

AND ordid<>20;

97.  Which of the following SELECT statements displays all workers without a subordinate?

a.     SELECT

w.wname

FROM work w

WHERE w.mgr IS NOT NULL;

b.     SELECT w.wname

FROM work w

WHERE w.workno NOT IN (select m.mgr

FROM work w

WHER m.mgr IS NOT NULL);

c.     SELECT w.wname

FROM work w

WHERE w.workno IN (select m.mgr

FROM work m);

d.     SELECT w.wname

FROM work w

WHERE w.workno NOT IN (select m.mgr

FROM work m);

98.  Examine the following cursor statement:

DECLARE

CURSOR query_cursor(v_salary)IS

SELECT last_name,salary_divison_no

FROM worker

WHERE SALARY>v_salary;

Why does this statement cause an error?

a.     The INTO clause is missing.

b.     A WHERE clause cannot be used in a cursor statement.

c.     A scalar data type was not specified by the parameter.

d.     The parameter mode is not defined in the statement.

99.  Examine the structure of the EMP table:

EMP TABLE

NAME                                   NULL?                   TYPE

EMP NUMBER                   NOT NULL           NUMBER(4)

VARCHAR2                                                        NUMBER(10)

JOB                                        VARCHAR2        NUMBER(2,9)

MGR                                                                      NUMBER(4)

HIREDATE                                                          DATE

SALARY                                                               NUMBER(7,2)

COMM                                                                  NUMBER(7,2)

DEPT NO                              NOT NULL           NUMBER(2)

TAX TABLE

NAME                                   NULL?                   TYPE

TAX GRADE                                                        NUMBER

LOWSAL                                                              NUMBER

HIGHSAL                                                             NUMBER

You must create a report that displays employee details along with the tax category of each employee.  The tax category is determined by comparing the salary of the employee from the emp table to the upper and lower salary values in the tax table.  Which of the following SELECT statements will perform the necessary comparisons?

a.     SELECT e.name,e.salary,e.tax grade

FROM emp e,tax t

WHERE e.salary between t.lowsal and t.highsal;

b.     SELECT e.name,e.salary,e.tax grade

FROM emp e,tax t

WHERE e.salary>=t.lowsal and <= t.highsal;

c.     SELECT e.name,e.salary,e.tax grade

FROM emp e,tax t

WHERE e.salary in t.lowsal and t.highsal.

d.     SELECT e.name,e.salary,e.tax grade

FROM emp e,tax t

WHERE e.salary<=t.lowsal and >= t.highsal;

100.        Examine the structure of the product and part tables:

PRODUCT

Id PK                      Name

PART

Id PK                      name                      Product_id                            cost

You issue the following statement:

SELECT                pt.name

FROM part pt,product.printer

WHERE pt.product_id(+)=pr.id;

What will occur?

a.     A list of product names will be displayed.

b.     A list of products is displayed for parts that have products assigned.

c.     An error will be generated.*

d.     A list of all products is displayed for products with parts.

Answers:

1)

b. 1

2)

d. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)SELECT constraint_nameFROM user_cons_columnsWHERE table_name=upper(‘&table’) AND constraint_type= ‘P’;

4)

d(d. ALTER TABLE patientADD CONSTRAINT pat_id_pk PRIMARY KEY(id_number);


5)d. 4  e. 5

6)d 

SELECT ordeid,prodid,qtyFROM merchandiseWHERE (prodid,qty) IN (SELECT prodid,qty FROM merchandise WHERE ordid=20);AND ordid<>20;


7)

b. SELECT w.wnameFROM work wWHERE w.workno NOT IN (select m.mgrFROM work wWHER m.mgr IS NOT NULL);

8)c. A scalar data type was not specified by the parameter.

9)c. SELECT e.name,e.salary,e.tax gradeFROM emp e,tax tWHERE e.salary in t.lowsal and t.highsal.

10)c. An error will be generated.*

Click for Answers