Total Pageviews

March 11, 2015

3/11/2015 07:36:00 AM

121.        You must retrieve worker details from the work table and process them in a PL/SQL block.  Which variable type must be created in the PL/SQL block to retrieve all rows and columns using a single SELECT statement from the work table?

a.     PL/SQL record.
b.     PL/SQL table of records.
c.     %ROWTYPE variable.
d.     PL/SQL table of scalars.

122.        Examine the following table:

ID NO    LAST_NAME      FIRST_NAME     SALARY               DEPT_NO
7              Brown                    Jerry                       30000                    255
6              Warner                   James                                                     233
4              West                       Dawn                     25000                    102
3              Chalmers               Mack                     50000                   
2              Landers                 Jillian                      32000                    145
5              Brunswick             Kate                                                       233
1              Lauder                   Susan                     55000                   
8              Ott                          Trixie                                                      145

You query the database using the following command:

SELECT dept_no,last_name,SUM(salary)   
FROM worker
WHERE salary<50000 p="">
GROUP BY dept_no
ORDER BY last_name;

Which clause causes an error?

a.     FROM employee
b.     WHERE salary<50000 p="">
c.     GROUP BY dept_no
d.     ORDER by last_name

123.        Which of the following will display the average salary of divisions 3 and 6, but only if the departments have an average salary of at least 3100?

a.     SELECT divisionno, AVG(sal)
FROM work
WHERE divisionno IN(3,6)
GROUP BY divisionno
HAVING AVG (sal)>=3100;
b.     SELECT divisionno, AVG(sal)
FROM work
WHERE divisionno IN (3,6)
AND AVG (sal)>=3100
GROUP BY divisionno;
c.     SELECT divisionno, AVG(sal)
FROM work
WHERE divisionno IN (3,6)
HAVING AVG(sal)>=3100
d.     SELECT divisionno, AVG(sal)
FROM work
GROUP BY divisionno
HAVNG AVG (sal)>=2000;
Divisionno IN (3,6);

125.        You need to create a report to display the ship date and order totals of your inventory table.  If an order has not been shipped, the report must indicate “not shipped.”  If a total is not available, the report must indicate “not available.”  In the inventory table, the ship date column has a data type of date and the total column has a data type of number.  Which of the following statements should be used to create the report?

a.     Select inventory, ship date “Not shipped”,
                Total “Not available”
        FROM order.
b.     Select inventory, To_CHar(ship date, ‘Not ship’)
                To-CHAR (total, ‘Not available’)
        FROM order;
c.     Select inventory, NVL (Ship date Not),
NVL (total, “Not available”)
        FROM order;
d.     Select inventory, NVL(TO_CHAR(shipdate) “Not
NVL (To char(total), ‘not available’)Shipped”)
        FROM order.

126.        You want to display data about all workers with the last name Randall, but are not sure what case last names are stored in.  What statement will be successful?

a.     Select last name, first name.
FROM work
WHERE last name=’randall’;
b.     Select last name, first name.
FROM work
WHERE last name=UPPER (‘randall’);
c.     Select last name, first name.
FROM work
WHERE UPPER(last name)=(‘randall’);
d.     Select last name, first name.
FROM work
WHERE LOWER(lastname)=(‘smith’);

127.        Your manager requests that you analyze the time taken between when orders are taken and when they are shipped.  You must create a report that displays the customer number, date of order, date shipped, and the number of months in whole numbers from the time the order is placed to the time the order is shipped.  Which statement meets these required results?

a.     SELECT custid, orderdate, shipdate,
                “Time Taken”
        FROM ord;
b.     SELECT custid, orderdate, shipdate
                FROM ord;
c.     SELECT custid, orderdate, shipdate,
                MONTHS-BETWEEN (shipdate,orderdate) “Time Taken”.
                FROM ord;
d.     SELECT custid, orderdate, shipdate
                ROUND OFF(shipdate-orderate) “Time Taken”
                FROM ord;

128.        The worker table has the following columns:

FIRST NAME                      VARCHAR2(20)
COMMISSION                    NUMBER(3,2)

Evaluate the following statement:

SELECT                                                first-name,commission
FROM                                    worker
WHERE                                                commission
                                                (SELECT commission
                                                FROM employee
                                                WHERE UPPER(first-name)=’Charles’)

Which of the following will cause this statement to fail?

a.     The first name values in the database are in lower case.
b.     There is no employee with the first name Charles.
c.     Charles has zero commission.
d.     Charles has null commission.

129.        You create the invoice table with the following command:

(purchase_no                       NUMBER(8)
CONSTRAINT     invoice-purchase-no-pk     PRIMARY KEY,
Customer_id                         NUMBER(8)
CONSTRAINT     invoice-customer-id-nk      NOT NULL);

Which index or indexes are created for the invoice table?

a.     No indexes are created.
b.     An index is created for each column.
c.     An index is created for the purchase_no column.
d.     An index is created for the customer_no column.

130.        How would a foreign key constraint be added on the division_no column in the worker table, referring to the ID column in the division table?

a.     Use the ALTER TABLE command with the ADD clause in the DETP table.
b.     Use the ALTER TABLE command with the ADD clause on the EMP table.
c.     Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
d.     This cannot be done.