Quiz

Select the best answer for each question. After completing the quiz, check your answers using the answer key in the appendix.
  1. Which PROC SQL query removes duplicate values of MemberType from the query output, so that only the unique values are listed? 
    1. proc sql nodup;
   select membertype
      from certadv.frequentflyers;
quit;
    2. proc sql;
   select distinct(membertype) as MemberType
      from certadv.frequentflyers;
quit;
    3. proc sql;
   select unique membertype
      from certadv.frequentflyers
      group by membertype;
quit;
    4. proc sql;
   select distinct membertype
      from certadv.frequentflyers;
quit;
  2. Which of the following causes PROC SQL to list rows that have no data in the Address column? 
    1. WHERE address is missing
    2. WHERE address not exists
    3. WHERE address is null
    4. Both a and c.
  3. 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? 
    1. proc sql;
   select name, spent, 120-spent as calculated Balance
      from certadv.absences
      where balance <= 0;
quit;
    2. proc sql;
   select name, spent, 120-spent as Balance
      from certadv.absences
      where calculated balance <= 0;
quit;
    3. proc sql;
   select name, spent, 120-spent as Balance
      from certadv.absences
      where balance <= 0;
quit;
    4. proc sql;
   select name, spent, 120-spent as calculated Balance
      from certadv.absences
      where calculated balance <= 0;
quit;
  4. 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?
    1. 150
    2. 7
    3. 4
    4. 1
  5. 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? 
    1. proc sql;
   select name, checkedout
      from certadv.circulation
      where * in
         (select *
            from certadv.volunteers);
quit;
    2. proc sql;
   select name, checkedout
      from certadv.circulation
      where name in
         (select name
            from certadv.volunteers);
quit;
    3. proc sql;
   select name
      from certadv.volunteers
      where name, checkedout in
         (select name, checkedout
            from certadv.circulation);
quit;
    4. proc sql;
   select name, checkedout
      from certadv.circulation
      where name in
         (select name
            from certadv.volunteers);
quit;
  6. By definition, a noncorrelated subquery is a nested query that does which of the following?
    1. returns a single value to the outer query
    2. contains at least one summary function
    3. executes independently of the outer query
    4. requires only a single value to be passed to it by the outer query
  7. 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;
    1. The query syntax is not valid.
    2. The outer query must pass values to the subquery before the subquery can return values to the outer query.
    3. PROC SQL will not execute this query when it is submitted.
    4. After the query is submitted, the SAS log indicates whether the query has valid syntax.
  8. 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?
    1. all donors whose rows do not contain any missing values
    2. all donors who made a contribution in the current year
    3. all donors who did not make a contribution in the current year
    4. all donors whose current year's donation in Certadv.Current has not yet been added to Total in Certadv.Donors
  9. Which statement about data remerging is true?
    1. When PROC SQL remerges data, it combines data from two tables.
    2. By using data remerging, PROC SQL can avoid making two passes through the data.
    3. When PROC SQL remerges data, it displays a related message in the SAS log.
    4. PROC SQL does not attempt to remerge data unless a subquery is used.
  10. 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;
    1. No. This query will not run because a HAVING clause cannot contain a summary function.
    2. No. This query will not run because the HAVING clause must include the CALCULATED keyword before the summary function.
    3. No. Because there is no GROUP BY clause, the HAVING clause treats the entire table as one group.
    4. Yes.
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.76.135