Selected Single-Row Functions Group Functions
Group functions can be nested to a depth of ____.
2
Which of the following functions can be used to determine the date one month after a specified date?
ADD_MONTHS
If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.
ALL
Functions that return one result per group of rows are called ____ functions.
ALL OF THE ABOVE
Which of the following cannot be used with date columns?
AVG
The ____________________ function is used to combine two sets of character strings into one set.
CONCAT
The ____ function can be used to determine the number of rows containing a specified value.
COUNT
The ____ function can be used to determine the number of rows meeting a specific condition.
COUNT
Which of the following format elements will display the value of Friday in a specified date as a 6?
D
Which of the following functions can be used to specify a list of alternative actions that should be taken depending upon a stated value?
DECODE
Although the ____________________ table is rarely used in the industry, it can be valuable for someone learning how to work with functions or testing new functions.
DUAL
Which of the following format elements can be used to display the three-letter abbreviation for Wednesday?
DY
By default, the AVG function will include NULL values when calculating the average of the numeric values in a specified column.
FALSE
LOWER, UPPER, and INITCAP functions are all character manipulation functions.
FALSE
The GROUP BY clause can only be used when a SELECT statement includes a group function.
FALSE
The MAX function can only be used with numeric or date columns.
FALSE
The NEXT_DATE function is used to determine the next occurrence of a specific day of the week after a given date. _________________________
FALSE
The SOUND function is used to determine the phonetic representation of a character string. _________________________
FALSE
The VAR function is used to determine the variance contained within a set of data.
FALSE
The only group function that includes NULL values by default is the MIN function.
FALSE
When a SELECT statement includes WHERE, HAVING, and GROUP BY clauses, the GROUP BY clause is always evaluated first.
FALSE
You can determine the number of characters in a column by using the COUNT function. _________________________
FALSE
A(n) ____________________ is a predefined block of code that accepts one or more arguments and returns a single value as output.
FUNCTION
The ____ clause is used to indicate that groups should be created.
GROUP BY
The ____________________ clause is used to group data based upon a specified column or columns.
GROUP BY
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
HAVING
Which of the following format elements will display 1:00 p.m. as 13?
HH24
The ____________________ function removes a specific string of characters from the left side of a set of data values.
LTRIM
The ____________________ function is used to determine the largest value stored in a specified column.
MAX
The ____________________ function is used to determine the next occurrence of a specific day of the week after a given date.
NEXT_DAY
If the two values compared by the NULLIF function are equal, what value does the function return?
NULL
The ____ function can be used to include NULL values in a calculation.
NVL
Which of the following functions can be used to substitute another value for a NULL value during calculations?
NVL
When functions are nested, the ____ function is solved last.
OUTER
Contents of the ORDERS table nar002-1.jpg 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 COUNT(*) FROM orders WHERE shipdate IS NULL;
Contents of the BOOKS table nar001-1.jpg Based upon the contents of the BOOKS table, which of the following will determine the number of books provided by publisher 3?
SELECT COUNT(pubid) FROM books WHERE pubid = 3;
Contents of the CUSTOMERS table Note: The ST column name is truncated, this represents the STATE column. Based upon the contents of the CUSTOMERS table, which of the following will display the shipping location as: City, State Zip
SELECT INITCAP(CONCAT(city, CONCAT(', ', CONCAT(state, CONCAT(' ', zip))))) FROM customers;
Contents of the BOOKS table nar001-1.jpg 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 SUM(retail-cost) FROM books WHERE pubid = 4;
Contents of the ORDERS table nar002-1.jpg 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 customer#, COUNT(customer#) FROM orders GROUP BY customer#;
Contents of ORDERS table Note: Column names are truncated as follows: SH = SHIPSTATE and SHIPZ = SHIPZIP. Based upon the contents of the ORDERS table, which of the following SQL statements will display only those orders shipped to the zip code zone that begins with 323?
SELECT order# FROM orders WHERE SUBSTR(shipzip, 1, 3) = 323;
Contents of ORDERS table Note: Column names are truncated as follows: SH = SHIPSTATE and SHIPZ = SHIPZIP. Based upon the contents of the ORDERS table, which of the following SQL statements will display the ship date for order 1006 as April 2, 2002?
SELECT order#, TO_CHAR(shipdate, 'Month DD, YYYY') FROM orders;
Contents of ORDERS table Note: Column names are truncated as follows: SH = SHIPSTATE and SHIPZ = SHIPZIP. Based upon the contents of the ORDERS table, which of the following SQL statements will display the number of days between the date an order was placed and the date it was shipped?
SELECT order#, TO_CHAR(shipdate-orderdate, '99') FROM orders WHERE shipdate IS NOT NULL;
Contents of the BOOKS table nar001-1.jpg Based on the contents of the BOOKS table, which of the following is a valid SQL statement?
SELECT pubid, AVG(retail-cost) "Average Profit" FROM books GROUP BY pubid;
The ____________________ function returns the phonetic representation for a specified value.
SOUNDEX
The phonetic representation of a character string can be determined using the ____ function.
SOUNDEX
Which of the following format model elements can be used to spell out a number?
SP
Which of the following format argument elements will display the number of seconds past midnight?
SSSS
The ____ function calculates the standard deviation for a specific set of data.
STDDEV
The ____ function is based upon the concept of a normal distribution.
STDDEV
Which of the following keywords will return the value of the date according to the computer?
SYSDATE
If a SELECT statement includes SUBSTR(TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY'), -4, 4) in the SELECT clause, which of these functions will be executed first?
TO_CHAR
AVG, COUNT, and STDDEV are all considered group functions.
TRUE
Columns used to group data in the GROUP BY clause do not have to be listed in the SELECT clause.
TRUE
Group functions are also known as aggregate functions.
TRUE
The CONCAT function can be used to concatenate the data contained in two columns. _________________________
TRUE
The COUNT(*) function can be used to count the number of rows containing NULL and non-NULL values.
TRUE
The HAVING clause is used to restrict the groups returned by a query.
TRUE
The LPAD or RPAD function can be used to add a character to extend the current contents of a column to a specified width.
TRUE
The MIN function can be used with numeric, date, and character columns.
TRUE
The MIN function can be used with the DISTINCT or ALL keywords.
TRUE
The NULLIF function is often combined with the NVL2 function to display a descriptive status. _________________________
TRUE
The SUM function is used to calculate the total amount stored in a numeric field for a group of records.
TRUE
The WHERE clause filters the data before grouping, while the HAVING clause filters the groups after grouping is performed.
TRUE
The optional DISTINCT keyword instructs Oracle11g to include only unique numeric values in the calculation.
TRUE
When a function is used in a SELECT statement, it only affects how the data are displayed in the results.
TRUE
When group functions are nested, the inner function is always resolved first.
TRUE
Character functions can be used to change the case of characters or to manipulate characters.
TRUE
Which of the following functions will truncate a numeric value to a specific position?
TRUNC
Which of the following functions can be used to convert a character string to upper-case letters?
UPPER
The ____________________ clause can be used to restrict rows, but not groups.
WHERE
When a SELECT statement contains WHERE, GROUP BY, and HAVING clauses, the ____________________ clause is always evaluated first.
WHERE
Which of the following indicates the processing order for the indicated clauses?
WHERE, GROUP BY, HAVING
The MAX function can be used with which type of columns?
all of the above
The MIN function can be used with ____ columns.
all of the above
LOWER, UPPER, and INITCAP functions are all character manipulation functions.
false
Single-row functions return one row of results for each group or category of rows processed.
false
The COUNT(*) function is only used to count the number of records containing NULL values.
false
The INSTR function is used to return a portion of a character string.
false
The STDVAR function is used to determine the standard deviation of numeric data.
false
The YR element can be included in a format argument of the TO_CHAR function to spell out the year of the specified date. _________________________
false
When functions are nested, the outer function is solved or executed first.
false
If an individual column is listed in a SELECT clause, along with a group function, the column must also be included in a(n) ____________________ clause.
group by
Functions that return one result per group of rows processed are called ____________________ functions.
group or multiple-row or multiple row or aggregate
Which of the following represents the number of days that have passed since January 1, 4712 B.C.?
julian date
The ____________________ function converts character strings to lower-case letters.
lower
Which of the following functions can be used to convert a character string to lower-case letters?
lower
The ____ function is used to calculate the total amount stored in a numeric field.
sum
If both nonaggregated and aggregated columns are used in the SELECT clause, the GROUP BY clause must contain the nonaggregated columns.
true
The GROUP BY clause is used to group data.
true
The REPLACE function is used to substitute one character string for another character string in a set of data.
true
The ROUND function can be used to round numeric data to the left or right of the decimal point. _________________________
true
To add a series of asterisks (*) to a column with the intent of extending it to a specific length, you can use the LPAD or RPAD function. _________________________
true
What is the maximum number of columns or character strings that can be combined through a single CONCAT function?
two
Which of the following functions is used to determine the number of months between two date values?
MONTHS_BETWEEN
Contents of the CUSTOMERS table Note: The ST column name is truncated, this represents the STATE column. Which of the following will display the name of each customer in lower-case characters based upon the contents of the CUSTOMERS table?
SELECT LOWER(firstname) || ' ' || LOWER(lastname) FROM customers;
