Review Questions – Chapter 4
|
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
|
|||||
|
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.
|
|||||
|
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'))
|
|||||
|
4.
|
Which of the following group
functions can return a NULL?
A. MIN
B. MAX
C. VARIANCE
D. VAR_SAMP
|
|||||
|
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
|
|||||
|
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.
|
|||||
|
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.
|
|||||
|
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.
|
|||||
|
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
|
|||||
|
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.
|
||||||
|
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.
|
|||||
|
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.
|
|||||
|
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.
|
|||||
|
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.
|
|||||
|
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.
|
|||||
|
16.
|
Which of the following is not a
group function?
A. AVG()
B. COUNT()
C. LEAST()
D. STDDEV()
E. CORR()
|
|||||
|
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.
|
|||||
|
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
|
|||||
|
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.
|
|||||
|
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.
|
|||||
Answers
|
1.
|
A. 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.
|
|
2.
|
B. 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.
|
|
3.
|
C. 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.
|
|
4.
|
D. 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.
|
|
5.
|
D. All of the above functions
require a GROUP BY function to be used.
|
|
6.
|
C. There is no SKEW function in
Oracle9i.
|
|
7.
|
C. 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.
|
|
8.
|
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.
|
B. 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.
|
|
10.
|
A. 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).
|
|
11.
|
B. 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.
|
|
12.
|
B. The DISTINCT option is not valid
for the VAR_POP function.
|
|
13.
|
C. 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.
|
|
14.
|
D. 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.
|
|
15.
|
A. 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.
|
|
16.
|
C. LEAST is a single-row function.
|
|
17.
|
A. A GROUP BY clause must contain
the column or expressions on which to perform the grouping operation. It
cannot use column aliasing.
|
|
18.
|
A. You cannot place a group
function in the WHERE clause.
|
|
19.
|
B. 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.
|
|
20.
|
D. The GROUPING function returns a
0 for ordinary rows and a 1 for superaggregate rows.
|
No comments:
Post a Comment