Chapter 6: Chapter 6: Creating And Managing Indexes Using Proc SQL
Practice for SAS advance certification exam.
- 1.Which of the following will create an index on the column EmpID for the table Sasuser.Staffmaster?
- A.
proc sql; create simple index(empid) on sasuser.staffmaster;
- B.
proc sql; create empid index on sasuser.staffmaster(empid);
- C.
proc sql; create simple index on empid from sasuser.staffmaster;
- D.
proc sql; create index empid on sasuser.staffmaster(empid);
- 2.Which keyword must you add to your index definition in the CREATE INDEX statement to ensure that no duplicate values of the key column can exist?
- A.
KEY
- B.
UNIQUE
- C.
NODUPS
- D.
NODUPKEY
- 3.Which of the following will create a composite index for the table Sasuser.Flightdelays?(Sasuser.Flightdelays contains the following columns: Date, FlightNumber, Origin, Destination, DelayCategory, DestinationType, DayOfWeek, and Delay.)
- A.
proc sql; create index destination on sasuser.flightdelays(flightnumber, destination);
- B.
proc sql; create composite index places on sasuser.flightdelays (flightnumber, destination);
- C.
proc sql; create index on flightnumber,destination from sasuser.flightdelays (places);
- D.
proc sql; create index places on sasuser.flightdelays (flightnumber, destination);
- 4.Which of the following will write a message to the SAS log that shows whether PROC SQL has used an index?
- A.
options msglevel=i; proc sql; select * from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;
- B.
options index=yes; proc sql; select * from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;
- C.
proc sql; select * (idxwhere=yes) from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;
- D.
proc sql; select * (msglevel=i) from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;
- 5.Which of the following will drop (delete) an index from a table?
- A.
proc sql; drop composite index flights from sasuser.marchflights;
- B.
proc sql; delete index flights on sasuser.staffmaster(flightnumber, date);
- C.
proc sql; drop index flights from sasuser.marchflights;
- D.
proc sql; delete index on sasuser.marchflights(flightnumber, flightdate);
- 6.Which of the following statements will show you all the indexes that are defined for a table?
- A.
DESCRIBE INDEX
- B.
DESCRIBE TABLE
- C.
SELECT
- D.
IDXNAME
- 7.What is the purpose of specifying the data set option IDXWHERE=YES?
- A.
It forces SAS to use the best available index to process the WHERE expression.
- B.
It creates an index from the expression in the WHERE clause.
- C.
It writes messages about index usage to the SAS log.
- D.
It stops SAS from using any index.
- 8.Which of the following is false regarding the use of an index?
- A.
Equijoins can be performed without internal sorts.
- B.
Indexes provide fast access to a small subset of data.
- C.
Indexes can be created for numeric columns only.
- D.
Indexes can enforce uniqueness.
- 9.Using an index is not likely to optimize a PROC SQL query in which of the following situations?
- A.
The query contains an IN subquery that references the key column.
- B.
The key column is specified in a WHERE clause expression that contains a comparison operator, the TRIM or SUBSTR function, the CONTAINS operator, or the LIKE operator.
- C.
The query is an equijoin, and all the columns in the join expression are indexed in one of the tables being joined.
- D.
The key column is specified only in a SELECT clause.
- 10.Which of the following is false regarding the IDXNAME= data set option?
- A.
The specified index must exist.
- B.
The specified index must be suitable by having at least its first or only column match a condition in the WHERE expression.
- C.
The option allows you to create and name an index on the table.
- D.
The option directs SAS to use an index that you specify.
Back to top