OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
Review Questions
1. Which function should be used to assign rankings to rows, giving duplicate ranking for ties, and not skip any ranks after ties?
A. DENSE_RANK
B. SPARSE_RANK
C. RANK
D. ROWNUM
------
Both the RANK and DENSE_RANK functions will assign the same rankings to duplicate values, but the RANK function will skip rank values when it encounters duplicate values. The SPARSE_RANK function does not exist. The ROWNUM pseudo-column, if used in a view, can provide rankings, but would not give equal ranking to duplicate values or skip any rankings.
Ans: A
rank function: http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions95a.htm
2. Which statement will generate the most rows?
A.
select ORDER_MODE,SALES_REP_ID, sum(ORDER_TOTAL)
from oe.orders
group by ROLLUP (ORDER_MODE,SALES_REP_ID);
B.
select ORDER_MODE,SALES_REP_ID, sum(ORDER_TOTAL)
from oe.orders
group by CUBE (ORDER_MODE,SALES_REP_ID);
C.
select ORDER_MODE,SALES_REP_ID, sum(ORDER_TOTAL)
from oe.orders
group by ORDER_MODE,SALES_REP_ID;
D.
They will all generate the same number of rows.
-----
The CUBE modifier in the GROUP BY clause generates aggregates for all possible group combinations in the CUBE modifier, producing subtotals for each order mode, each sales rep ID, and a grand total. The ROLLUP modifier produces only subtotals for each order mode and a grand total. A GROUP BY without a CUBE or ROLLUP modifier does not produce any subtotals. If you try it with the Oracle sample schema, you will see that the CUBE option return 24 rows, the ROLL option return 14 rows, and the plain GROUP BY option return 11 rows.
Ans: B
3. Based on the output below, which GROUP BY clause was used?
DEPARTMENT_ID YEAR COUNT(*)
------------- ---- ----------
30 1999 1
30 1
50 1999 9
50 2000 4
50 13
60 1999 1
60 1
80 1999 5
80 2000 7
80 12
100 1999 1
100 1
1999 1
1
29
A. GROUP BY CUBE(department_id,to_char(hire_date,'YYYY'))
B. GROUP BY department_id,to_char(hire_date,'YYYY')
C. GROUP BY ROLLUP(department_id,to_char(hire_date,'YYYY'))
D. GROUP BY department_id,ROLLUP(,to_char(hire_date,'YYYY'))
----
Since there is no subtotal for year 1999 or 2000, the CUBE modifier could not have been used. Since there are subtotals for the departments, a ROLLUP modifier had to be used. Option D would not have generated the last row in the report, which provides a grand total across all department/year combinations.
Ans: C
---
4. Which of the following group functions can return a NULL?
A. MIN
B. MAX
C. VARIANCE
D. VAR_SAMP
---
MIN and MAX always return a numeric value. The only difference between the VARIANCE and VAR_SAMP functions is that the VAR_SAMP function will return a NULL if there is only one row in the aggregation, whereas VARIANCE will return a 0.
Ans: D
VAR_SAMP: http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions165a.htm#87171
5. Which of the functions below requires a GROUP BY clause in the SQL statement?
A. CUBE
B. GROUPING
C. GROUP_ID
D. All of the above
E. None of the above
----
Ans D. All of the above functions require a GROUP BY function to be used.
6. Which of the following functions is not an Oracle group function?
A. REGR_SXY
B. CORR
C. SKEW
D. COVAR_POP
E. All of the above functions are valid.
----
Ans: C. There is no SKEW function in Oracle9i.
7. What is the GROUPING function used for?
A. The GROUPING function is identical to the GROUP BY function, but executes faster.
B. The GROUPING function is used to eliminate NULL values prior to aggregation.
C. The GROUPING function identifies superaggregate rows.
D. The GROUPING function is deprecated in Oracle 9i and should not be used.
----
A GROUP BY clause, together with a CUBE or ROLLUP operator, is required for the GROUPING function. The GROUPING function was new to 8.1.6 and is still an important aggregate function that identifies superaggregate rows.
Ans:C
8. How will the results of the following two statements differ?
Statement 1:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city;
Statement 2:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city
GROUP BY state;
A. Statement 1 will fail because it is missing a GROUP BY clause.
B. Statement 2 will return one row, and statement 1 may return more than one row.
C. Statement 2 will display a longitude and latitude for each ZIP_STATE_CITY.
D. Statement 1 will display two values, and statement 2 will display two values for each state.
----
Ans D. Option B has the statement numbers transposed. This one was intended to be a trick question. You should read all the answers carefully; the exam may have trick questions like this one.
9. Which group functions would you use to compute the mean and median values for a set of data?
A. MEAN and MEDIAN
B. AVG and PERCENTILE_CONT
C. MEAN and PERCENTILE_DISC
D. AVG and MEDIAN
----
There is no MEAN or MEDIAN function. To obtain these values, use the AVG function to obtain mean and either PERCENTILE_CONT or PERCENTILE_DISC to obtain the median.
Ans: B
10. Using the SALES table described below you need to report the following:
- Gross, net, and earned revenue
- For the second and third quarters of 1999
- For sales in the states Illinois, California, and Texas (codes IL, CA, and TX)
----------------------------------------------------------------------------
Column Name state_code sales_date gross net earned
Key Type pk pk
NULLs/Unique NN NN NN NN NN
FK Table
Datatype VARCHAR2 DATE NUMBER NUMBER NUMBER
Length 2 11,2 11,2 11,2
-----------------------------------------------------------------------------
Will all the requirements be met with the following SQL statement?
SELECT state_code, SUM(ALL gross), SUM(net),
SUM(earned)
FROM sales_detail
WHERE TRUNC(sales_date,'Q') BETWEEN
TO_DATE('01-Apr-1999','DD-Mon-YYYY')
AND TO_DATE('01-Sep-1999','DD-Mon-YYYY')
AND state_cd IN ('IL','CA','TX')
GROUP BY state_code;
A. The statement meets all three requirements.
B. The statement meets two of the three requirements.
C. The statement meets one of the three requirements.
D. The statement meets none of the three requirements.
E. The statement will raise an exception.
-----
All requirements are met. The gross, net, and earned revenue requirements are satisfied with the SELECT clause. The second and third quarter sales requirement is satisfied with the first predicate of the WHERE clause—the sales date will be truncated to the first day of a quarter, thus 01-Apr-1999 or 01-Jul-1999 for the required quarters (which are both between 01-Apr-1999 and 01-Sep-1999). The state codes requirement is satisfied by the second predicate in the WHERE clause. This question is intentionally misleading, but so are some exam questions (and, unfortunately, some of the code in some shops).
Ans: A
11. Which assertion about the following queries is true?
SELECT COUNT(DISTINCT mgr), MAX(DISTINCT salary)
FROM emp;
SELECT COUNT(ALL mgr), MAX(ALL salary)
FROM emp;
A. They will always return the same numbers in columns 1 and 2.
B. They may return different numbers in column 1 but will always return the same number in column 2.
C. They may return different numbers in column 1 and may return different numbers in column 2.
D. They will always return the same number in column 1 but may return different numbers in column 2.
----
The first column in the first query is counting the distinct MGR values in the table. The first column in the second query is counting all MGR values in the table. If a manager appears twice, the first query will count her one time, but the second will count her twice. Both the first query and the second query are selecting the maximum salary value in the table.
Ans: B
12. Which line in the following statement will raise an exception?
1 SELECT department_id ,COUNT(*)
2 ,VAR_POP(DISTINCT salary)
3 ,VAR_POP(salary)
4 FROM hr.employees
5 GROUP BY department_id;
A. Line 1
B. Line 2
C. Line 3
D. Line 5
E. There is no error.
----
Ans B. The DISTINCT option is not valid for the VAR_POP function.
13. What will the following SQL statement return?
select min(cust_income_level)
keep (dense_rank last order by cust_credit_limit)
from sh.customers;
A. The smallest CUST_INCOME_LEVEL in the CUSTOMERS table
B. The smallest CUST_INCOME_LEVEL and the highest CUST_CREDIT_LIMIT in the CUSTOMERS table
C. The minimum CUST_INCOME_LEVEL for the maximum CUST_CREDIT_LIMIT
D. The missing comma will raise a syntax error.
----
There is no missing comma; the SELECT list contains a single expression. The KEEP or LAST function is a modifier for another group function. In this case, the MIN function is modified to return the minimum CUST_INCOME_LEVEL for those rows having the LAST, or highest, CUST_CREDIT_LIMIT.
Ans: C
14. How will the results of the following two statements differ?
Statement 1:
SELECT COUNT(*), SUM(salary)
FROM hr.employees;
Statement 2:
SELECT COUNT(salary), SUM(salary)
FROM hr.employees;
A. Statement 1 will return one row, and statement 2 may return more than one row.
B. Both statements will fail because they are missing a GROUP BY clause.
C. Both statements will return the same results.
D. Statement 2 may return a smaller COUNT value than statement 1.
The COUNT(*) will count all rows in the table. The COUNT(salary) will count only the number salary values that appear in the table. If there are any rows with a NULL salary, statement 2 will not count them.
Ans: D
15. How will the results of the following two statements differ?
Statement 1:
SELECT COUNT(cust_gender)
FROM sh.customers;
Statement 2:
SELECT regr_count(cust_marital_status,cust_gender)
FROM sh.customers;
A. Statement 2 may return a smaller COUNT value than statement 1.
B. Both statements will return the same results.
C. Statement 1 will return one row, and statement 2 may return more than one row.
D. Both statements will fail because they are missing a GROUP BY clause.
----
The COUNT(cust_gender) will count all rows in the table where CUST_GENDER is not NULL. The REGR_COUNT(cust_marital_status, cust_gender) will count all rows in the table where CUST_MARITAL_STATUS and CUST_GENDER are both not NULL.
Ans: A.
16. Which of the following is not a group function?
A. AVG()
B. COUNT()
C. LEAST()
D. STDDEV()
E. CORR()
----
Ans C. LEAST is a single-row function.
17. Why does the following SELECT statement fail?
SELECT colorname Colour, MAX(cost)
FROM itemdetail
WHERE UPPER(colorname) LIKE '%WHITE%'
GROUP BY colour
HAVING COUNT(*) > 20;
A. A GROUP BY clause cannot contain a column alias.
B. The condition COUNT(*) > 20 should be in the WHERE clause.
C. The GROUP BY clause must contain the group functions used in the SELECT list.
D. The HAVING clause can contain only the group functions used in the SELECT list.
----
A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. It cannot use column aliasing.
Ans: A
18. What will the following SQL statement return?
select max(prod_pack_size)
from sh.products
where min(prod_weight_class) = 5;
A. An exception will be raised.
B. The largest PROD_PACK_SIZE for rows containing PROD_WEIGHT_CLASS of 5 or higher
C. The largest PROD_PACK_SIZE for rows containing PROD_WEIGHT_CLASS of 5
D. The largest PROD_PACK_SIZE in the SH.PRODUCTS table
----
You cannot place a group function in the WHERE clause.
Ans: A.
19. Why will the following query raise an exception?
select dept_no, avg(distinct salary).
,count(job) job_count
from emp
where mgr like 'J%'
or abs(salary) > 10
having count(job) > 5
order by 2 desc;
A. The HAVING clause cannot contain a group function.
B. The GROUP BY clause is missing.
C. ABS() is not an Oracle function.
D. The query will not raise an exception.
--------
There is at least one column in the SELECT list that is not a constant or group function, so a GROUP BY clause is mandatory.
Ans: B
20. What will the GRP column in the following SQL return?
select sales_rep_id,sum(order_total)
,grouping(sales_rep_id) grp
from oe.orders
group by cube(sales_rep_id)
A. The query will raise an exception.
B. The GRP column will be a cumulative count of SALES_REP_ID.
C. The GRP column will be a cumulative sum of ORDER_TOTAL, grouped by SALES_REP_ID.
D. The GRP column will be a superaggregate identifier.
-----
Ans: D. The GROUPING function returns a 0 for ordinary rows and a 1 for superaggregate rows.
No comments:
Post a Comment