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)
GROUP BY AVG(sal)
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,
ROUND(MONTHS-BETWEEN(shipdate,orderdate))
“Time Taken”
FROM ord;
b. SELECT custid, orderdate, shipdate
ROUND(DAYS-BETWEEN(shipdate,orderdate))/30.
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:
CREATE TABLE invoice
(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.
