3) ABC company is about to give each staff a $500 increase in monthly salary. You want to
"preview" the result from your database table without making any modification. How do you do that?

A. You cannot do this. Actual update will take place.
B. Give the arithmetic expression to invoke salary increment in the select clause
C. Give the arithmetic expression to invoke salary increment in the where clause
D. Give the arithmetic expression to invoke salary increment in the from clause
E. Give the arithmetic expression to invoke salary increment with an update clause

8) For which of the following would you use the ALTER TABLE...MODIFY option?

A. Add a column to the table.B. Disable a table constraint.C. Drop a table column.

9) What is a characteristic of only PL/SQL?

A. Accepts input of variables. B. Allows shutdown of the database. C. Allows use of
exception handling routines based on error numbers. D. None of the above.

10) You query the database with this command:

SELECT atomic_weight FROM chart_n
WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight
BETWEEN (25 AND 75) Which of the following values could the statement retrieve ?

A. 51 B. 95 C. 30 D. 75

12) You query the database with this command:
SELECT isotope, group_id,mass_no,
DISTINCT(atomic_weight) FROM chart_n; What values are displayed?

A) Distinct combinations of isotope, group_id, mass_no, and atomic_weight.
B) isotope and distinct combinations of group_id, mass_no, and atomic_weight.
C) isotope, group_id, mass_no, and distinct values of atomic_weight.
D) No values will be displayed because the statement will fail.

13) Evaluate this procedure: CREATE OR REPLACE FUNCTION found_isotope (v_energy_line
IN BOOLEAN, v_proper_ratio IN BOOLEAN) RETURN NUMBER IS Ret_val NUMBER;
BEGIN
IF(v_energy_line AND v_proper_ratio) THEN ret_val:=1; ELSIF NOT (v_energy_line AND
v_proper_ratio) THEN ret_val:=2;
ELSIF (v_energy_line AND v_proper_ratio) IS NULL THEN
ret_val:=-1; END
IF; RETURN ret_val; END; If v_energy_line equals TRUE, and v_proper_ratio
equals NULL, which value is assigned to ret_val?

16) Which of the following is a purpose of the user-defined constraint?

A. To enforce not-null restrictions B. To enforce referential integrity
C. To enforce business rules
D. To take action based on insertions, updates, or deletions in the base table

17) The PL/SQL executable section contains which type of statements ?

A. PL/SQL and SQL statements to manipulate data in the database
B. The procedure or function name and input/output variable definitions
C. The definition of program variables, constants, exceptions, and cursors
D. Statements to deal with error handling

18) Which of the following is executed automatically?

A. Anonymous PL/SQL block B. Function C. Procedure D. Trigger

19) Evaluate this command: CREATE FORCE VIEW isotope_groups AS SELECT element,
group_id,count(*) isotopes FROM chart_n WHERE atomic_weight>50 GROUP BY element,group_id
ORDER BY atomic_weight;
Which clause will cause an error?

A. AS SELECT isotope, group_id B. FROM chart_n C. WHERE atomic_weight>50
D. ORDER BY atomic_weight; -- cannot use order by in view, order by use when select view

20) You attempt to create a view with this command: CREATE VIEW parts_view AS SELECT
id_number, description, sum(quantity) FROM inventory WHERE id_number = 1234 GROUP
BY id_number; Which clause causes an error?

24) You need to produce a report of all your products and their standard list prices. The
products are stored in the PRODUCT table, and the prices are stored in the PRICE table. Some products are new or discontinued and do not have a price in the PRICE table, but they should still be included on the report with a price of 0.00. Which of the following is the correct WHERE clause to complete this SQL command and meet these objectives?

If you were rewriting this block of PL/SQL, which of the following types
of loops would you use if you wanted to reduce the amount of code by
utilizing features of the loop that handle mundane aspects of processing
automatically?

A. A multiple-row subquery returns one row. B. A multiple-column subquery returns one
column.
C. A single-row subquery returns more than one row. D. A multiple-row query uses a single-
row subquery.
E. A single-row quey uses a multiple-row subquery that returns only one row.

34) Review this SQL statement:

SELECT department "Departments", MAX(salary) "Top Salaries"
FROM employee
WHERE department IN (200,300,400)
GROUP BY Departments
HAVING MAX(salary) > 60000;

This statement fails when executed. Which change will correct the problem ?

A. Remove the group function from the HAVING clause.
B. Add the condition "MAX(salary) > 60000" to the WHERE clause.
C. Replace the column alias in the GROUP BY clause with the column name.
D. Add the group function(s) used in the SELCT list to the GROUP BY clause.