Select the best answer
for each question. After completing the quiz, check your answers using
the answer key in the appendix.
-
Which PROC SQL query
removes duplicate values of MemberType from the query output, so that
only the unique values are listed?
-
proc sql nodup;
select membertype
from certadv.frequentflyers;
quit;
-
proc sql;
select distinct(membertype) as MemberType
from certadv.frequentflyers;
quit;
-
proc sql;
select unique membertype
from certadv.frequentflyers
group by membertype;
quit;
-
proc sql;
select distinct membertype
from certadv.frequentflyers;
quit;
-
Which of the following
causes PROC SQL to list rows that have no data in the Address column?
-
You are creating a PROC
SQL query to list all employees who have spent (or overspent) their
allotted 120 hours of vacation for the current year. The hours that
each employee used are stored in the existing column Spent. Your query
defines a new column, Balance, to calculate each employee's balance
of vacation hours.
Which query produces
the report that you want?
-
proc sql;
select name, spent, 120-spent as calculated Balance
from certadv.absences
where balance <= 0;
quit;
-
proc sql;
select name, spent, 120-spent as Balance
from certadv.absences
where calculated balance <= 0;
quit;
-
proc sql;
select name, spent, 120-spent as Balance
from certadv.absences
where balance <= 0;
quit;
-
proc sql;
select name, spent, 120-spent as calculated Balance
from certadv.absences
where calculated balance <= 0;
quit;
-
Consider this PROC SQL
query:
proc sql;
select flightnumber,
count(*) as Flights,
avg(boarded)
label="Average Boarded"
format=3.
from certadv.internationalflights
group by flightnumber
having avg(boarded) > 150;
quit;
The table Certadv.Internationalflights contains
201 rows, 7 unique values of FlightNumber, 115 unique values of Boarded,
and 4 different flight numbers that have an average value of Boarded
that is greater than
150
. How many
rows of output will the query generate?
-
You are writing a PROC
SQL query to display the names of all library cardholders who work
as volunteers for the library, and the number of books that each volunteer
currently has checked out. Use one or both of the following tables:
-
Certadv.Circulation lists the name
and contact information for all library cardholders, and the number
of books that each cardholder currently has checked out.
-
Certadv.Volunteers lists the name
and contact information for all library volunteers.
Assume that the values of Name are unique in both
tables.
Which of the following
PROC SQL queries will produce your report?
-
proc sql;
select name, checkedout
from certadv.circulation
where * in
(select *
from certadv.volunteers);
quit;
-
proc sql;
select name, checkedout
from certadv.circulation
where name in
(select name
from certadv.volunteers);
quit;
-
proc sql;
select name
from certadv.volunteers
where name, checkedout in
(select name, checkedout
from certadv.circulation);
quit;
-
proc sql;
select name, checkedout
from certadv.circulation
where name in
(select name
from certadv.volunteers);
quit;
-
By definition, a noncorrelated
subquery is a nested query that does which of the following?
-
returns a single value
to the outer query
-
contains at least one
summary function
-
executes independently
of the outer query
-
requires only a single
value to be passed to it by the outer query
-
Which statement about
the following PROC SQL query is false?
proc sql;
validate
select name label='Country',
rate label='Literacy Rate'
from certadv.literacy
where 'Asia' =
(select continent
from certadv.continents
where literacy.name =
continents.country)
order by 2;
quit;
-
The query syntax is
not valid.
-
The outer query must
pass values to the subquery before the subquery can return values
to the outer query.
-
PROC SQL will not execute
this query when it is submitted.
-
After the query is submitted,
the SAS log indicates whether the query has valid syntax.
-
Consider the following
PROC SQL query:
proc sql;
select lastname, firstname, total, since
from certadv.donors
where not exists
(select lastname
from certadv.current
where donors.lastname =
current.lastname);
quit;
The query references
two tables:
-
Certadv.Donors lists name and contact
information for all donors who have made contributions since the charity
was founded. The table also contains these two columns: Total, which
shows the total dollars given by each donor, and Since, which stores
the first year in which each donor gave money.
-
Certadv.Current lists the names
of all donors who have made contributions in the current year, and
the total dollars each has given this year (YearTotal).
Assume that the values
of LastName are unique in both tables.
What will the output
of this query display?
-
all donors whose rows
do not contain any missing values
-
all donors who made
a contribution in the current year
-
all donors who did not
make a contribution in the current year
-
all donors whose current
year's donation in Certadv.Current has not yet been added to
Total in Certadv.Donors
-
Which statement about
data remerging is true?
-
When PROC SQL remerges
data, it combines data from two tables.
-
By using data remerging,
PROC SQL can avoid making two passes through the data.
-
When PROC SQL remerges
data, it displays a related message in the SAS log.
-
PROC SQL does not attempt
to remerge data unless a subquery is used.
-
A public library has
several categories of books. Each book in the library is assigned
to only one category. The table Certadv.Inventory contains one row
for each book in the library. The Checkouts column indicates the number
of times that each book has been checked out.
You want to display
only the categories that have an average circulation (number of checkouts)
that is less than 2500. Does the following PROC SQL query produce
the results that you want?
proc sql;
title 'Categories with Average Circulation';
title2 'Less than 2500';
select category, avg(checkouts) as AvgCheckouts
from certadv.inventory
having avg(checkouts) < 2500
order by 1;
quit;
-
No. This query will
not run because a HAVING clause cannot contain a summary function.
-
No. This query will
not run because the HAVING clause must include the CALCULATED keyword
before the summary function.
-
No. Because there is
no GROUP BY clause, the HAVING clause treats the entire table as one
group.
-