Chapter 2: Performing Advanced Queries Using PROC SQL
Correct answer: d
To remove duplicate
values from PROC SQL output, you specify the DISTINCT keyword before
the column name in the SELECT clause.
Correct answer: d
To list rows that have
no data (that is, missing data), you can use either of these other
conditional operators: IS MISSING or IS NULL. The NOT EXISTS operator
is used specifically with a subquery, and resolves to true if the
subquery returns no values to the outer query.
Correct answer: b
When a WHERE clause
references a new column that was defined in the SELECT clause, the
WHERE clause must specify the keyword CALCULATED before the column
name.
Correct answer: c
To determine how PROC
SQL calculates and displays output from summary functions, consider
the key factors. This PROC SQL query has a GROUP BY clause, and it
does not specify any columns that are outside of summary functions.
Therefore, PROC SQL calculates and displays the summary function for
each group. There are 7 unique values of FlightNumber, but the HAVING
clause specifies only the flights that have an average number of boarded
passengers greater than 150. Because 4 of the 7 flight numbers meet
this condition, the output will contain 4 rows.
Correct answer: b
Your PROC SQL query
needs to use data from both tables. The outer query reads the name
and number of books checked out from Library.Circulation. The multiple-value
noncorrelated subquery selects the names of volunteers from Library.Volunteers
and passes these names back to the outer query. The outer query then
selects data for only the volunteers whose names match names returned
by the subquery. The subquery is indented under the outer query's
WHERE clause, is enclosed in parentheses, and does not require a semicolon
inside the closing parenthesis.
Correct answer: c
A noncorrelated subquery
is a nested query that executes independently of the outer query.
The outer query passes no values to the subquery.
Correct answer: a
The syntax in this PROC
SQL query is valid, so the first statement is false. The query contains
a correlated subquery, so the second statement is true. The VALIDATE
keyword is used after the PROC SQL statement, so the third statement
is true. And the last statement correctly indicates that the VALIDATE
keyword causes the SAS log to display a special message if the query
syntax is valid, or standard error messages if the syntax is not valid.
Correct answer: c
In this PROC SQL query,
the outer query uses the operator NOT EXISTS with a correlated subquery.
The outer query selects all rows from Charity.Donors whose names do
not appear in Charity.Current. In other words, this PROC SQL query
output lists all donors who did not make a contribution in the current
year.
Correct answer: c
The third statement
about data remerging is correct.
Correct answer: c
PROC SQL can execute
this query, but the query will not produce the results that you want.
If you omit the GROUP BY clause in a query that contains a HAVING
clause, then the HAVING clause and any summary functions treat the
entire table as one group. Without a GROUP BY clause, the HAVING clause
in this example calculates the average circulation for the table as
a whole (all books in the library), not for each group (each category
of books). The output contains either all the rows in the table (if
the average circulation for the entire table is less than 2500) or
none of the rows in the table (if the average circulation for the
entire table is greater than 2500).