50. Mrs. Jensen is president of her company. Four managers report to her, and all other employees report to the four managers. Examine the following:
SELECT worker.wname
FROM work worker
WHERE worker, workno not in
SELECT manager.mgr
FROM work manager;
The above statement returns no rows. Why?
a. All employees have a manager.
b. None of the employees have a manager.
c. A null value is returned from the subquery.
d. An operator is not allowed in subqueries.
51. Which of the following statements regarding column subqueries is true?
a. A pair wise comparison produces a cross product.
b. A non-pair wise comparison produces a cross product.
c. In a pair wise query, the values returned from the subquery are compared individually to the value in the outer query.
d. In a non-pair wise query, the values returned from the subquery are compared as a group to the values in the outer query.
52. You query a database with the following command:
SELECT dept_no, AVG (MONTHS_BETWEEN (SYSDATE, hire_date))
FROM worker
WHERE AVG (MONTHS_BETWEEN (SYSDATE, hire_date)) > 60
GROUP BY dept_no
ORDER BY AVG (MONTHS_BETWEEN (SYSDATE, hire_date) )
Where does the statement cause an error?
a. A SELECT clause cannot contain a group function.
b. A WHERE clause cannot be used to restrict groups.
c. An ORDER BY clause cannot contain a group function.
d. A group function cannot contain a single row function.
53. A group function ______.
a. Produces a group of results from each row.
b. Produces one result from each row of a table.
c. Produces one result from many rows per group.
d. Produces many results from many rows per group.
54. The worker table contains the following columns:
ID NUMBER(9) PK
LAST_NAME VARCHAR2(25) NN
DEPT_ID NUMBER(9)
Evaluate the following:
DEFINEid_3=93011
SELECT
FROM worker
WHERE id= (% id_3)
Which change should be made to the script so that it will execute?
a. Remove the ampersand.
b. Use the ACCEPT account.
c. Close the cursor.
d. No change is needed.
55. Evaluate the following:
SELECT w.id, (.15* w.salary) + (.25* w.bonus))
(w.sale_amount * (.15* w.commision_pct))
FROM worker w , sales
WHERE w.id = s.worker_id;
What would be the result of removing all parentheses from the calculation?
a. Results will be higher.
b. Results will be lower.
c. The results will be the same.
d. The statement will not execute.
56. Which of the following is not an SQL Plus command?
a. DESCRIBE
b. UPDATE
c. CHANGE
d. ACCEPT
57. When selecting data, projection ______.
a. Allows you to choose rows.
b. Allows you to choose columns.
c. Allows you to join tables together.
d. Allows you to add columns to a table.
58. The worker table contains the following columns:
ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
COMMISSION NUMBER(7,2)
You must display commission calculations for employees, and are given the following guidelines:
·
· +Display commission multiplied by 1.5.
· Exclude employees with zero commission.
· Display a zero for employees with null commission value.
Evaluate the following statement:
SELECT id, last_name, first_name, commission*1.5
FROM worker
WHERE commission <>0;
How many guidelines are met by the statement?
a. 3
b. 2
c. 1
d. The statement generates an error.
59. Click EXHIBIT and examine the trace instance chart for the worker table (exhibit not available). What SQL statement must be used to display each worker hire date from earliest to last?
a. SELECT hire_date
FROM worker;
b. SELECT hire_date
FROM worker
ORDER BY hire_date;
c. SELECT worker
FROM worker
ORDER BY hire_date;
d. SELECT hire_date
FROM worker
ORDER BY hire_date DESC;
60. The division table is structured as follows:
Name Null Type
DIVISIONNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Examine the following declaration:
DECLARE
TYPE division_table_type IS TABLE OF division &ROWTYPE
INDEX BY BINARYINTEGER
division_table division_table_type;
You need to assign LOC file in record 13 of value ‘Boston’. Which of the following statements should be used?
a. division_table.loc.13 := ‘Boston’;
b. division_table[13].loc := ‘Boston’;
c. division_table(13).loc := ‘Boston’;
d. division_table_type(13).loc := ‘Boston’;
