Total Pageviews

February 26, 2015

2/26/2015 08:24:00 PM
91.  Evaluate the following PL/SQL block:

BEGIN
FROM 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.




 
Related Posts Plugin for WordPress, Blogger...