Chapter 4: Combining Tables Vertically Using Proc SQL
Explore the intricacies of combining tables vertically in PROC SQL with this focused quiz. Topics include UNION, EXCEPT, INTERSECT operations, and understanding column overlay in SQL queries. Essential for learners aiming to master advanced SQL techniques.
- 1.
Which statement is false with respect to a set operation that uses the EXCEPT, UNION, or INTERSECT set operator without a keyword?
- A.
Column names in the result set are determined by the first table.
- B.
To be overlaid, columns must be of the same data type.
- C.
To be overlaid, columns must have the same name.
- D.
By default, only unique rows are displayed in the result set.
Correct Answer
C. To be overlaid, columns must have the same name.Explanation
In set operations that use the operator EXCEPT, INTERSECT, or UNION, and no keyword, columns are overlaid based on their position in the SELECT clause. It does not matter whether the overlaid columns have the same name. When columns are overlaid, the column name is taken from the first table that is specified in the SELECT clause.Rate this question:
-
- 2.
The keyword ALL cannot be used with which of the following set operators?
- A.
EXCEPT
- B.
INTERSECT
- C.
UNION
- D.
OUTER UNION
Correct Answer
D. OUTER UNIONExplanation
By default, when processing a set operation that contains the EXCEPT, INTERSECT, and UNION set operators, PROC SQL makes an extra pass through the data to eliminate duplicate rows. The keyword ALL is used to suppress that additional pass through the tables, allowing duplicate rows to appear in the result set. Because the OUTER UNION set operator displays all rows, the keyword ALL is invalid and cannot be used with OUTER UNION.Rate this question:
-
- 3.
Which PROC SQL step combines the tables Summer and Winter to produce the output displayed below?
- A.
proc sql; select * from summer intersect all select * from winter;
- B.
proc sql; select * from summer outer union select * from winter;
- C.
proc sql; select * from summer union corr select * from winter;
- D.
proc sql; select * from summer union select * from winter;
Correct Answer
D. proc sql; select * from summer union select * from winter;Explanation
The output shown above contains all rows that are unique in the combined set of rows from both tables, and the columns have been overlaid by position. This output is generated by a set operation that uses the set operator UNION without keywords.Rate this question:
-
- 4.
Which PROC SQL step combines tables but does not overlay any columns?
- A.
proc sql; select * from groupa outer union select * from groupb;
- B.
proc sql; select * from groupa as a outer union corr select * from groupb as b;
- C.
proc sql; select coalesce(a.obs, b.obs) label='Obs', med, duration from groupa as a full join groupb as b on a.obs=b.obs;
- D.
proc sql; select * from groupa as a intersect select * from groupb as b;
Correct Answer
A. proc sql; select * from groupa outer union select * from groupb;Explanation
The PROC SQL set operation that uses the set operator OUTER UNION without a keyword is the only code shown that does not overlay any columns in output.Rate this question:
-
- 5.
Which statement is false regarding the keyword CORRESPONDING?
- A.
It cannot be used with the keyword ALL.
- B.
It overlays columns by name, not by position.
- C.
When used in EXCEPT, INTERSECT, and UNION set operations, it removes any columns not found in both tables.
- D.
When used in OUTER UNION set operations, it causes same-named columns to be overlaid.
Correct Answer
A. It cannot be used with the keyword ALL.Explanation
The keyword CORRESPONDING (CORR) can be used alone or together with the keyword ALL.Rate this question:
-
- 6.
Which PROC SQL step generates the following output from the tables Dogs and Pets?
- A.
proc sql; select name, price from pets except all select * from dogs;
- B.
proc sql; select name, price from pets except select * from dogs;
- C.
proc sql; select name, price from pets except corr all select * from dogs;
- D.
proc sql; select * from dogs except corr select name, price from pets;
Correct Answer
B. proc sql; select name, price from pets except select * from dogs;Explanation
This PROC SQL output includes all rows from the table Pets that do not appear in the table Dogs. No duplicates are displayed. A PROC SQL set operation that contains the set operator EXCEPT without keywords produces these results.Rate this question:
-
- 7.
The PROG1 and PROG2 tables list students who took the PROG1 and PROG2 courses, respectively. Which PROC SQL step will give you the names of the students who took only the PROG1 class?
- A.
proc sql; select fname, lname from prog1 intersect select fname, lname from prog2;
- B.
proc sql; select fname, lname from prog1 except all select fname, lname from prog2;
- C.
proc sql; select * from prog2 intersect corr select * from prog1;
- D.
proc sql; select * from prog2 union select * from prog1;
Correct Answer
B. proc sql; select fname, lname from prog1 except all select fname, lname from prog2;Explanation
The set operator EXCEPT returns all the rows in the first table that do not appear in the second table. The keyword ALL suppresses the extra pass that PROC SQL makes through the data to eliminate duplicate rows. The EXCEPT operator when used alone will also produce the output specified in the question.Rate this question:
-
- 8.
Which PROC SQL step will return the names of all the students who took PROG1, PROG2, or both classes?
- A.
proc sql; select fname, lname from prog1 intersect select fname, lname from prog2;
- B.
proc sql; select fname, lname from prog1 outer union corr select fname, lname from prog2;
- C.
roc sql; select fname, lname from prog1 union select fname, lname from prog2;
- D.
proc sql; select fname, lname from prog1 except corr select fname, lname from prog2;
Correct Answer
C. roc sql; select fname, lname from prog1 union select fname, lname from prog2;Explanation
The set operator UNION returns all rows that are unique in the combined set of rows from both tables.Rate this question:
-
- 9.
Which PROC SQL step will return the names of all the students who took both the PROG1 and PROG2 classes?
- A.
proc sql; select fname, lname from prog1 union select fname, lname from prog2;
- B.
proc sql; select fname, lname from prog1 except corr select fname, lname from prog2;
- C.
proc sql; select fname, lname from prog1 intersect all select fname, lname from prog2;
- D.
proc sql; select fname, lname from prog1 union corr select fname, lname from prog2;
Correct Answer
C. proc sql; select fname, lname from prog1 intersect all select fname, lname from prog2;Explanation
The set operator INTERSECT returns all rows that are common to both tables. Specifying the keyword ALL suppresses PROC SQL's additional pass through the data to eliminate duplicate rows.Rate this question:
-
- 10.
Which PROC SQL step will generate the same results as the following DATA step?
- A.
proc sql; select fname, lname from prog1 outer union corr select fname, lname from prog2 order by lname;
- B.
proc sql; select fname, lname from prog1 union select fname, lname from prog2 order by lname;
- C.
proc sql; select fname, lname from prog2 outer union select fname, lname from prog1 order by lname;
- D.
proc sql; select fname, lname from prog2 union corr select fname, lname from prog1 order by lname;
Correct Answer
A. proc sql; select fname, lname from prog1 outer union corr select fname, lname from prog2 order by lname;Explanation
The DATA step returns all rows from the first table along with all rows from the second table, maintaining the order specified in the BY statement. Same-named columns are overlaid by default. The set operator OUTER UNION returns all rows from both tables. The CORR keyword causes same-named columns to be overlaid. The ORDER BY clause causes the result rows to be ordered by values of the specified column (LName).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
-
Mar 22, 2023Quiz Edited by
ProProfs Editorial Team -
Mar 08, 2013Quiz Created by
Moxleyv
Back to top