Free Printable AIT 524 SQL Chapter 11

Worksheets

AIT 524 SQL Chapter 11

Total questions: 109

Worksheet time: 45mins

Name

Class

Date

Group functions return a group of results per row processed.

true

false

Group functions return one result per group of rows processed.

true

false

AVG, COUNT, and STDDEV are all considered group functions.

true

false

Group functions are also known as aggregate functions.

true

false

The only group function that includes NULL values by default is the MIN function.

true

false

The SUM function is used to calculate the total amount stored in a numeric field for a group of records.

true

false

By default, the SUM function assumes the ALL keyword and the result will include multiple occurrences of numeric values.

true

false

The optional UNIQUE keyword instructs Oracle 12c to include only unique numeric values in its calculation.

true

false

By default, the AVG function will include NULL values when calculating the average of the numeric values in a specified column.

true

false

10.

The COUNT function can be used to count NULL, as well as, non-NULL values.

true

false

11.

The COUNT(*) function is only used to count the number of records containing NULL values.

true

false

12.

The COUNT function can only be used for numeric columns.

true

false

13.

The MAX function can only be used with numeric or date columns.

true

false

14.

The MAX function can be used with character data.

true

false

15.

The DISTINCT keyword cannot be used with the MAX function.

true

false

16.

The MIN function is used to determine the smallest value in a specified column.

true

false

17.

The MIN function can be used with numeric, date, and character columns.

true

false

18.

The GROUP BY clause is used to group data.

true

false

19.

The GROUP BY clause can only be used when a SELECT statement includes a group function.

true

false

20.

If a group function is used in the WHERE clause, then any individual columns listed in the SELECT clause must also be listed in the GROUP BY clause.

true

false

21.

Columns used to group data in the GROUP BY clause must also be listed in the SELECT clause.

true

false

22.

Column aliases can be used in the GROUP BY clause.

true

false

23.

A SELECT statement cannot include both a GROUP BY and an ORDER BY clause.

true

false

24.

The HAVING clause is used to restrict the groups returned by a query.

true

false

25.

If both nonaggregated and aggregated columns are used in the SELECT clause, the GROUP BY clause must contain the nonaggregated columns.

true

false

26.

The WHERE clause specifies which groups will be displayed in the results.

true

false

27.

When the SELECT statement includes both WHERE and GROUP BY clauses, the GROUP BY clause is always evaluated first.

true

false

28.

Group functions can only be nested to a depth of two.

true

false

29.

The STDVAR function is used to determine the standard deviation of numeric data.

true

false

30.

The VARIANCE function is used to determine the variance in a group of numeric data.

true

false

31.

Pattern matching features available in a SELECT are targeted to analyze patterns or trends of a specific column of data across many rows.

true

false

32.

A grouping sets expression can perform multiple Group BY clauses in a single query.

true

false

33.

Single-row functions return one result per group of rows processed.

true

false

34.

SUM, AVG, and COUNT are all considered group functions.

true

false

35.

The only group function that includes NULL values is the NVL function.

true

false

36.

The TOTAL function is used to calculate the total value stored in a numeric field for a group of records.

true

false

37.

The COUNT function is used to total the values stored in numeric columns.

true

false

38.

The optional DISTINCT keyword instructs Oracle12c to include only unique numeric values in the calculation.

true

false

39.

The AVG function only includes non-NULL values in its calculations.

true

false

40.

The COUNT(*) function can be used to count the number of rows containing NULL and non-NULL values.

true

false

41.

By default, the COUNT function uses the DISTINCT keyword when counting the values contained in a column.

true

false

42.

The MAX functions returns the smallest value stored in the specified column.

true

false

43.

The MIN function can be used with the DISTINCT or ALL keywords.

true

false

44.

The ORDER BY clause is used to group data.

true

false

45.

If a group function is used in the SELECT clause, then any individual columns listed in the SELECT clause must also be listed in the ORDER BY clause.

true

false

46.

Columns used to group data in the GROUP BY clause do not have to be listed in the SELECT clause.

true

false

47.

Results returned from a SELECT statement that include a GROUP BY clause will present the results in descending order of the column(s) listed in the GROUP BY clause.

true

false

48.

The GROUP BY clause is used to restrict the groups returned by a query.

true

false

49.

The WHERE clause can contain group functions.

true

false

50.

The HAVING clause specifies which groups will be displayed in the results, while the WHERE clause restricts the records that are retrieved from the table for processing.

true

false

51.

The HAVING clause serves as the WHERE clause for aggregated data.

true

false

52.

The WHERE clause filters the data before grouping, while the HAVING clause filters the groups after grouping is performed.

true

false

53.

When a SELECT statement includes WHERE, HAVING, and GROUP BY clauses, the GROUP BY clause is always evaluated first.

true

false

54.

When group functions are nested, the inner function is always resolved first.

true

false

55.

Group functions can be nested inside single-row functions.

true

false

56.

The STDDEV function calculates the standard deviation for the data contained in a specified field.

true

false

57.

The VAR function is used to determine the variance contained within a set of data.

true

false

58.

Which of the following is not considered a group function?

TRUNC

MIN

COUNT

SUM

59.

Functions that return one result per group of rows are called ____ functions.

group

multiple-row

aggregate

all apply

60.

The default keyword for group functions is ____.

ALL

DISTINCT

INCLUDENULLS

NONULLS

61.

The SUM function can only be used with ____ data.

numeric

character

date

alphanumeric

62.

The ____ function is used to calculate the total amount stored in a numeric field.

TOTAL

SUM

AVERAGE

ADD

63.

If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed.

UNIQUE

ALL

RANDOM

none apply

64.

The ____ function calculates the average of the numeric values in a specified column.

AVG

AVERAGE

MEAN

65.

The ____ function can be used to determine the number of rows containing a specified value.

TOTAL

SUM

COUNT

ADD

66.

The ____ function can be used to determine the number of rows containing a specified value.

ALL

UNIQUE

GROUP

none apply

67.

The AVG function can be used with ____ values.

numeric

character

date

all apply

68.

Which of the following cannot be used with date columns?

MIN

MAX

AVG

all apply

69.

The ____ function can be used to include NULL values in a calculation.

AVG

SUM

NULL

NVL

70.

The ____ function can be used to determine the number of rows meeting a specific condition.

ADD

SUM

COUNT

TOTAL

71.

Which of the following is a correct statement?

An asterisk can be used as the argument for the COUNT function to include NULL values in the results.

The NULL keyword can be included in the argument of the COUNT function to include NULL values in the results.

The ALL keyword can be included in the argument of the COUNT function to include NULL values in the results.

The INCLUDE NULLS keywords can be included in the argument for the COUNT function to include NULL values in the results.

72.

The ____ function can be used to determine the largest value stored in a specified column.

MAXIMUM

MAX

LARGE

LARGEST

73.

The MAX function can be used with which type of columns?

numeric

date

character

all apply

74.

The ____ function returns the smallest value in a specified column.

LOW

LOWEST

MIN

MINIMUM

75.

The MIN function can be used with ____ columns.

numeric

character

date

all apply

76.

The ____ clause is used to indicate that groups should be created.

ORDER BY

GROUP BY

AGGREGATED

GROUPING

77.

Which of the following is a valid statement?

The ORDER BY clause cannot be used in a SELECT statement containing a GROUP BY clause.

Data returned from a GROUP BY clause will automatically be sorted in descending order.

Column aliases cannot be used in a GROUP BY clause.

Columns referenced in the GROUP BY clause must also be contained in the SELECT clause.

78.

If a group function is used in the SELECT clause, any ____ listed in the SELECT clause must also be listed in the GROUP BY clause.

aggregate function

single-row function

individual column

all apply

79.

The ____ clause is used to restrict the groups returned by a query.

FROM

WHERE

HAVING

GROUP BY

80.

Which of the following lines of the SQL statement contains an error? 1 SELECT title, MAX(retail) 2 FROM books 3 WHERE retail > 30 4 AND pubid = 1 5 GROUP BY retail

line 2

line 3

line 4

line 5

81.

A(n) ____ clause cannot include a group function.

a. WHERE

b. HAVING

c. SELECT

both a and c

82.

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed first.

HAVING

GROUP BY

WHERE

ORDER BY

83.

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.

HAVING

GROUP BY

WHERE

SELECT

84.

Which of the following indicates the processing order for the indicated clauses?

WHERE, HAVING, GROUP BY

GROUP BY, HAVING, WHERE

WHERE, GROUP BY, HAVING

HAVING, WHERE, GROUP BY

85.

If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement.

GROUP BY

WHERE

HAVING

ORDER BY

86.

Which of the following is a valid SQL statement?

SELECT SUM(quantity*retail) "Total Sales"

FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN WHERE orderdate = '02-APR-03';

SELECT SUM(quantity*retail) "Total Sales"

FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN HAVING orderdate = '02-APR-03';

SELECT customer#, SUM(quantity*retail) "Total Sales"

FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN HAVING orderdate = '02-APR-03';

SELECT customer#, SUM(quantity*retail) "Total Sales"

FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN HAVING orderdate = '02-APR-03' GROUP BY customer#;

87.

Group functions can be nested to a depth of ____.

255

88.

A group function can be nested inside a(n)____.

a. group function

b. single-row function

c. order function

both a and b

89.

Which of the following is a valid statement?

A single-row function can be nested inside a group function.

Group functions can be nested inside other group functions.

Group functions can be nested inside single-row functions.

all apply

90.

The ____ function calculates the standard deviation for a specific set of data.

STDDEVIATION

STD(haha)

STDDEV

STANDARDDEV

91.

If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.

UNIQUE

ALL

NONULLS

none apply

92.

The STDDEV function can be used with ____ columns.

numeric

character

date

all apply

93.

The ____ function is based upon the concept of a normal distribution.

MIN

MAX

SUM

STDDEV

94.

The ____ function is used to determine how widely data are spread out within a group.

STDDEVIATION

STANDARDDEV

VARIANCE

VAR

95.

If the DISTINCT keyword is not included in the VARIANCE function, the ____ keyword will be assumed.

UNIQUE

ALL

NULLS ONLY

NONULLS

96.

The VARIANCE function can be used with ____ columns.

alphanumeric

numeric

date

none apply

97.

Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?

SELECT COUNT(DISTINCT pubid) FROM books;

SELECT DISTINCT COUNT(pubid) FROM books;

SELECT DISTINCT (COUNT(pubid)) FROM books;

SELECT (DISTINCT COUNT(pubid)) FROM books;

98.

Based on the contents of the BOOKS table, which of the following is a valid SQL statement?

SELECT pubid, AVERAGE(retail-cost) "Average Profit"

FROM books;

SELECT pubid, AVERAGE(retail-cost) "Average Profit"

FROM books

GROUP BY pubid;

SELECT pubid, AVG(retail-cost) "Average Profit"

FROM books

GROUP BY pubid;

SELECT pubid, AVG(retail-cost) "Average Profit"

FROM books

HAVING retail-cost > 25;

99.

Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by books provided by publisher 4?

SELECT TOTAL(retail-cost) FROM books

WHERE pubid = 4;

SELECT TOTAL(retail-cost) FROM books

GROUP BY pubid;

SELECT SUM(retail-cost) FROM books

WHERE pubid = 4;

SELECT SUM(retail-cost) FROM books

GROUP BY pubid = 4;

100.

Based upon the contents of the BOOKS table, which of the following will determine the number of books provided by publisher 3?

SELECT SUM(isbn)

FROM books

WHERE pubid = 3;

SELECT TOTAL(*)

FROM books

WHERE pubid = 3;

SELECT COUNT(pubid)

FROM books

WHERE pubid = 3;

none apply

101.

Based upon the contents of the BOOKS table, which of the following will display the retail price of the most expensive book provided by publisher 3?

SELECT MAX(retail)

FROM books

GROUP BY pubid;

SELECT MAXIMUM(retail)

FROM books

WHERE pubid = 3;

SELECT MAX(retail)

FROM books

WHERE pubid = 3;

SELECT MAXIMUM(retail)

FROM books

HAVING pubid = 3;

102.

Based on the contents of the BOOKS table, which of the following will display the date of the book with the earliest publication date?

SELECT MIN(pubdate)

FROM books;

SELECT title

FROM books

WHERE pubdate = MIN(pubdate);

SELECT title

FROM books

WHERE pubdate = MINIMUM(pubdate);

SELECT MINIMUM(pubdate)

FROM books;

103.

Which of the following can be used with character data?

MIN

MAX

COUNT

all apply

104.

Which of the following can be used with date columns?

MIN

MAX

COUNT

all apply

105.

Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?

SELECT order#, COUNT(shipdate)

FROM orders

WHERE shipdate IS NULL;

SELECT order#, COUNT(shipdate)

FROM orders

WHERE shipdate IS NULL

GROUP BY order#;

SELECT COUNT(shipdate)

FROM orders

WHERE shipdate IS NULL;

SELECT COUNT(*)

FROM orders

WHERE shipdate IS NULL;

106.

Based on the contents of the ORDERS table, which of the following SELECT statements will determine the number of orders placed by each customer?

SELECT COUNT(DISTINCT(customer#))

FROM orders;

SELECT COUNT(*)

FROM orders;

SELECT customer#, COUNT(customer#)

FROM orders

GROUP BY customer#;

none apply

107.

Based upon the contents of the ORDERS table, which of the following will display how many orders were shipped to each state?

SELECT shipstate,COUNT(*)

FROM orders;

SELECT shipstate,COUNT(customer#)

FROM orders;

SELECT shipstate,COUNT(*)

FROM orders

HAVING COUNT(*) >0

SELECT shipstate,COUNT(*)

FROM orders

GROUP BY shipstate;

108.

Which of the following is not a GROUP BY extension?

ROLLUP

CUBE

OLAP

all apply

109.

What clause offers many options to identify measures and patterns to analyze in a query?

ANALYZE

MATCH_RECOGNIZE

PATTERN_MATCH

MATCH_TREND

Reset

AIT 524 SQL Chapter 11

Total questions: 109

Worksheet time: 45mins

Similar Resources on Wayground