And below are the rest of the questions. BTW, I took myself out of being considered for candidacy for the role so I am not be considered for the position any longer.

1. Name three simple aggregate functions: _________, ________, ________

Answers (any three): Sum, Count, Avg, Min, Max, Stddev (variations: Stddev_XXXX), Variance (variations: Var_XXXX)

2. Table Spending has a foreign key reference defined to Customers. Which of the following are true statements?

a. No row can exist in Spending without a corresponding row in Customers with the same key value.

b. No row can exist in Customers without a corresponding row in Spending with the same key value.

c. No row can be deleted in Spending without first deleting corresponding row(s) in Customers.

d. No row can be deleted in Customers without first deleting corresponding row(s) in Spending.

e. There must be one or more rows in Customers with the same key value as each row in Spending.

f. There may be zero but no more than one row in Spending with the same key value as each row in Customers.

Answer: e

3. We must generate a list of customers who have spent $30,000 or more in the last 30 days. All the information needed is in a table that has a separate entry for every transaction made by each customer. Which, if any, of the following clauses must be part of the query?

a. group by cust_id

b. where sum(amt_spent) >= 30000

c. having sum(amt_spent) >= 30000

d. where transaction_date between now() and now() – 29

Answer: a, c, d

4. Give a brief explanation of the differences between the following statements.

a. drop table table_name;

b. delete table_name;

c. truncate table table_name;

Answer: A: removes the table and all contents from the database. B: removes the contents of the table but the table remains. C: same as B except it cannot be rolled back.

5. A query is generating a compile error. We’ve narrowed the problem to the following fragment. What could be wrong with it?

select coalesce( a.phone_number, b.birth_date) as Answer from …;

Answer: The second and any subsequent arguments to coalesce must be the same data type as the first argument.

6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)

a. where f > :p1

b. where f between :p1 and dateadd( day, :p2, :p1)

c. where dateadd( day, :p2 , f) > :p1

d. where dateadd( day, :p2, :p1) < f

e. where f not in( select …)

Answer: c

7. What is the difference between UNION and UNION ALL?

Answer: UNION performs a set merge where duplicate rows are discarded. UNION ALL concatenates all rows together with no regard to duplicates.

8. What are the two most significant differences between a primary key and a unique key?

Answer: 1) The primary key has the additional constraint of NOT NULL. 2) There can be only one primary key per table but there may be many unique keys.

9. Which use of subquery is correct and why?

a. where f in (select a from tab1)

b. where f = (select a from tab1)

Answer: a

10. What might be done to make the incorrect subquery above executable? Change the subquery, not the WHERE clause.

Answers: (any are fine)

a. where f = (select max(a) from tab1)

b. where f = (select min(a) from tab1)

c. where f = (select avg(a) from tab1)

d. Any change which makes the subquery return no more than one result.