Chapter 6: Chapter 6: Creating And Managing Indexes Using Proc SQL
(167).webp "Chapter 6: Chapter 6: Creating And Managing Indexes Using Proc SQL - Quiz")
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);
Correct Answer
D. proc sql; create index empid on sasuser.staffmaster(empid);Explanation
The index specified above is based on one column, so it is a simple index. In the CREATE INDEX statement, you specify the index name after the keywords CREATE INDEX. You do not include a keyword to specify that this is a simple index. The name of the key column is specified in parentheses after the table name. The name of a simple index must be the same as the name of the key column.Rate this question:
-
- 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
Correct Answer
B. UNIQUEExplanation
To create a unique index, the UNIQUE keyword is added to the CREATE INDEX statement, between the keywords CREATE and INDEX.Rate this question:
-
- 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);
Correct Answer
D. proc sql; create index places on sasuser.flightdelays (flightnumber, destination);Explanation
A composite index is based on two or more columns. In the CREATE INDEX statement, you specify the index name after the keywords CREATE INDEX. You do not include a keyword to specify that this is a composite index. The names of the key columns are specified in parentheses after the table name. The name of a composite index cannot be the same as the name of any columns in the table.Rate this question:
-
- 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;
Correct Answer
A. options msglevel=i; proc sql; select * from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;Explanation
Specifying the option MSGLEVEL=I causes informational messages about index usage to be written to the SAS log.Rate this question:
-
- 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);
Correct Answer
C. proc sql; drop index flights from sasuser.marchflights;Explanation
The DROP INDEX statement drops one or more specified indexes from a table. You specify the name of each index to be dropped after the keywords DROP INDEX. The table name is specified after the keyword FROM. The type of index and the names of the indexed columns are not specified in the statement.Rate this question:
-
- 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
Correct Answer
B. DESCRIBE TABLEExplanation
The DESCRIBE TABLE statement lists all indexes for one or more tables that you specify, along with other information about the table(s).Rate this question:
-
- 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.
Correct Answer
A. It forces SAS to use the best available index to process the WHERE expression.Explanation
The IDXWHERE=YES data set option tells SAS to use the best available index, even if the index does not optimize performance.Rate this question:
-
- 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.
Correct Answer
C. Indexes can be created for numeric columns only.Explanation
Indexes can be created on either character or numeric columns.Rate this question:
-
- 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.
Correct Answer
D. The key column is specified only in a SELECT clause.Explanation
Using an index will optimize specific classes of PROC SQL queries. A query in which the key column is specified only in a SELECT clause is not one of these queries.Rate this question:
-
- 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.
Correct Answer
C. The option allows you to create and name an index on the table.Explanation
The IDXNAME= data set option directs PROC SQL to use an index that you specify. The specified index must exist and must be suitable by having at least its first or only column match the condition in the WHERE expression.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Feb 13, 2024Quiz Edited by
ProProfs Editorial Team -
Mar 12, 2013Quiz Created by
Moxleyv