SQL Chapter 2-8

B. When used with the UNION operator, CORR causes PROC SQL to match the columns in table expressions by name and not by position. Columns that do not match by name are excluded from the result table.

PROC SQL with the UNION operator and the CORR keyword will overlay the same named columns and keep all others. a. True b. False

A. A subquery resides inside the clauses of another SELECT statement or subquery.

Select the best answer for each question. When you are finished, click Submit Quiz. What is a subquery? a. a SELECT statement that resides inside the clauses of another SELECT statement or another subquery b. a SUB statement that resides inside the clauses of another SELECT statement or another subquery c. a SELECT statement that resides inside a PROC SQL statement or another subquery d. a SUB statement that resides inside a PROC SQL statement or another subquery

B. If your data changes frequently and a PROC SQL view is referenced multiple times within the same program, then the view might return different data to different steps within the same program.

Suppose you have a large SAS program that accesses transactional data that changes several times throughout the different steps. The data for the different steps needs to be the same for each step. Should you use a view to obtain the data? a. Yes b. No

A. A view builds the data when the view is executed and prevents the user from accessing the source tables. The view will retrieve the most current data.

Suppose you need to use the most up-to-date data from multiple tables for a user that should not be able to alter the source tables. Should you use a view? a. Yes b. No

A A self-join, also known as a reflexive join, is a query in which a table is joined to itself.

A self-join requires reading data from different rows of the same table. a. True b. False

C. When a SELECT statement resides in a WHERE or HAVING clause, it is called a subquery.

A subquery can reside in which of the following clauses? a. SELECT or FROM b. SELECT or WHERE c. WHERE or HAVING d. WHERE or FROM

b. A subquery must return values only from a single column.

A subquery can return several rows of data, but can a subquery return values from multiple columns? a. Yes b. No

A. This inner join can create this result set. An inner join creates a Cartesian product first, so it returns all possible combinations of the rows with matching join key values.

Acme.Orders lists information for each order, including the employee ID of the salesperson. Some employee IDs might appear multiple times, and others not at all. Acme.Phones lists each employee ID twice, once for each type of phone number. Can the following join create a result set like the one shown below, which lists two rows for each order?

A. This inner join can create this result set. An inner join creates a Cartesian product first, so it returns all possible combinations of the rows with matching join key values. A self-join requires reading data from different rows of the same table.

Acme.Orders lists information for each order, including the employee ID of the salesperson. Some employee IDs might appear multiple times, and others not at all. Acme.Phones lists each employee ID twice, once for each type of phone number. Can the following join create a result set like the one shown below, which lists two rows for each order? a. Yes b. No

B. The SQL join does not require tables to be indexed or sorted. In theory, a Cartesian product is produced. From the result of the Cartesian product, the join conditions are evaluated and the rows can be joined on equal join conditions or on unequal (inequality) conditions.

An SQL join requires the tables to be indexed or sorted and can be performed only on an equality. a. Yes b. No

B The SQL join does not require tables to be indexed or sorted. In theory, a Cartesian product is produced. From the result of the Cartesian product, the join conditions are evaluated and the rows can be joined on equal join conditions or on unequal (inequality) conditions.

An SQL join requires the tables to be indexed or sorted and can be performed only on an equality. a. Yes b. No

B. No, an in-line view can be nested only in the FROM clause in the place of a table name. In addition, an in-line view can return values from one column or multiple columns.

An in-line view is nested in the WHERE clause. a. Yes b. No

B. The SELECT list in this query specifies both a non-summarized column and a summarized column. By default, SAS will remerge unless you either group by Order_Type or remove the non-summarized column. Specifying the NOREMERGE option prevents SAS from remerging, but then PROC SQL does not produce a report.

Before you run this query, which of the following changes can you make to prevent SAS from remerging and still produce a report? proc sql; select Order_Type, min(Quantity) label="Min Qty" from orion.order_fact; quit; a. Add an ORDER BY clause to sort the output rows by the values of Order_Type. b. Add a GROUP BY clause to group by Order_Type. c. Add an additional column to the SELECT list. d. Specify the PROC SQL NOREMERGE option.

A. Although they do not remove the duplicate rows at the same point, they do all remove duplicate rows.

By default, do the EXCEPT, INTERSECT, and UNION set operators remove duplicate rows from the query results? a. Yes b. No

C. Because the columns in both tables correspond, no columns are removed. The ALL keyword instructs PROC SQL to retain all duplicate rows in the results.

How many rows will this query produce?

B. The EXCEPT set operator selects unique rows from the first query that are not found in the second query.

How many rows will this query produce? a. 1 b. 2 c. 5 d. 6

D. The OUTER UNION set operator selects all rows from both query results.

How many rows will this query produce? a. 1 b. 2 c. 5 d. 6

D. The UNION set operator returns unique rows from both queries.

How many rows will this query produce? a. 2 b. 3 c. 4 d. 5

A. The INTERSECT operator returns the unique rows that occur in both queries.

How many rows will this query produce? a. 1 b. 2 c. 5 d. 6

C. This complex join has more than one error. In the outer query's FROM clause, a parenthesis should precede the in-line view. An in-line view should end with a parenthesis and not a semicolon. Also, in-line views cannot contain an ORDER BY clause.

How many syntax errors does the following complex join have? a. no errors b. one error c. more than one error

C This query has more than one error. The SELECT clause and the ON clause use table aliases in qualified column names, but the FROM clause does not define the table aliases. In the FROM clause, a comma should not appear after the first table name. Also, the keywords in the FROM clause should be RIGHT JOIN.

How many syntax errors does the following join have? select r.Name, Course, Grant from school.registration, right outer join school.scholarships on r.Name=s.Name where Grant > 0; a. no errors b. one error c. more than one error

C. This query has more than one error. The SELECT clause and the ON clause use table aliases in qualified column names, but the FROM clause does not define the table aliases. In the FROM clause, a comma should not appear after the first table name. Also, the keywords in the FROM clause should be RIGHT JOIN.

How many syntax errors does the following join have? select r.Name, Course, Grant from school.registration, right outer join school.scholarships on r.Name=s.Name where Grant > 0; a. no errors b. one error c. more than one error

B. In a query's output, the columns appear in the order in which they are listed in the SELECT clause. Columns that are listed in other clauses, but not the SELECT clause, do not appear in output.

In orion.sales, the columns appear in this order: Employee_ID, First_Name, Last_Name, Gender, Salary, Job_Title, Country, Birth_Date, Hire_Date. When you run the following query, in what order do columns appear in the report? proc sql; select Last_Name, First_Name from orion.sales where Job_Title='Sales Rep. I'; quit; a. First_Name, Last_Name b. Last_Name, First_Name c. Last_Name, First_Name, Job_Title

B. COUNT(*) counts the total number of rows in a table or in a subset. However, when you specify a column as an argument for COUNT, the function counts the number of rows in the table or in a subset that have a nonmissing value for that column.

In this PROC SQL program, given the partial data, do the two SELECT statements generate the same results? a. Yes b. No

D. Because an alias was not provided, SAS assigns a unique name to the column.

In this code, what will be the name of the calculated column in the newly created table? proc sql; create table work.birth_months as select Employee_ID, Birth_Date, month(Birth_Date) from orion.employee_payroll; quit; a. Employee_ID b. Birth_Date c. Birth_Month d. SAS assigns a name such as _TEMA001

A. Like all PROC SQL options, the FEEDBACK option remains in effect until PROC SQL encounters a step boundary.

In this program, if you add another SELECT statement above the QUIT statement, will the second SELECT statement generate an expanded query in the SAS log? proc sql feedback; select * from acme.payroll where salary<750000; quit; a. Yes b. No

D Unlike most SAS summary functions, ANSI summary functions can take only a single argument. Also, because PROC SQL is ANSI standard, shortcuts to reference a range of variables in an argument list within a function are not acceptable in PROC SQL as they are in other SAS procedures.

In which of the following queries can you replace the SAS summary function MEAN with the ANSI summary function AVG and get the same results? a. select mean(Qtr1,Qtr2,Qtr3,Qtr4) as Mean_Annual from orion.employee_donations; b. select Employee_ID, mean(Qtr1,Qtr2,Qtr3,Qtr4) as Mean_Annual from orion.employee_donations; c. select mean(of Qtr1:) as Mean_Qtr1 from orion.employee_donations; d. sselect Qtr1, mean(Qtr1) as Mean_Qtr1 from orion.employee_donations;

b Assigning an alias to a column is optional.

Is an alias required for a column created in the SELECT clause? a. Yes b. No

A Using DISTINCT to select unique values of one or more columns conforms to the ANSI standard.

The DISTINCT keyword is used to select unique values of one or more columns. a. Yes b. No

A. The left join returns all rows from the left table, Widgets, and any matching data from the rows of the right table, Locations. The coalesce function returns the first non-missing value in the ID columns.

The Widgets table stores information about the most popular items sold in a hardware store. The Locations table stores information about the location of some of the items in the store. Which query produces the result set that is shown below?

B. To aggregate summary statistics by the values of the non-summarized column, Product_Group, the query must use a GROUP BY clause that specifies the Product_Group column. This report is grouped by only one column. Also, the GROUP BY clause cannot specify a summarized column, like Products.

The product_dim table contains 481 rows, one row for each product that Orion Star sells. All Orion Star products are assigned to one of 50 product groups. Which SELECT statement creates a report that shows the number of products in each product group? a. select Product_Group, count(*) as Products from orion.product_dim; b. select Product_Group, count(*) as Products from orion.product_dim group by Product_Group; c. select Product_Group, count(*) as Products from orion.product_dim group by Product_Group, Products;

A. To guarantee the order of the rows in the output, you must use an ORDER BY clause.

To guarantee the order of the rows in the output, must you use an ORDER BY clause? a. Yes b. No

D. The FROM clause indicates the dictionary table used as the source of the data. The WHERE clause indicates that the data is subset to only output the data for the memname or table called TABLES.

What does this query do? proc sql; select name from dictionary.tables where memname='TABLES'; quit; a. Outputs the table name for a subset of data that includes only the TABLES column from dictionary.tables. b. Outputs the column TABLES for a subset of data that includes only the name table from dictionary.tables. c. Outputs the column name for a subset of data that includes only the dictionary.tablestable from the TABLES table. d. Outputs the column name for a subset of data that includes only the TABLES table from dictionary.tables.

B. PROC SQL checks the syntax and displays information in the SAS log but does not execute the statements.

What happens if you specify the NOEXEC option and then submit this PROC SQL statement? update work.sales set Salary=Salary * 1.03 where Job_Title='Sales Rep. I'; a. Nothing happens. PROC SQL does not process the step at all. b. PROC SQL checks the syntax and displays information in the log but does not update the table. c. PROC SQL checks the syntax and displays information in the log. If there are no syntax errors, PROC SQL then updates the table. d. PROC SQL skips the syntax check and updates the table if it can.

A. When the expression includes a comparison operator such as > or =, the subquery must return only a single column value.

What results do you think you would get from the following query? a. The subquery succeeds and returns a single value. b. The query fails and an error message is printed in the log. c. The subquery succeeds and returns multiple values. d. The query fails and a warning message is printed in the log.

B. The CREATE TABLE statement creates a table that contains no rows and three columns. In order to add row values to this empty table, you would need to use a technique such as the PROC SQL with the INSERT INTO statement to add rows of data to the empty table.

What will be the result of executing this code? proc sql; create table airline.books (Name char length=12, Code num, Amount num format=dollar12.2); quit; a. a report listing the columns Name, Code, and Amount b. an empty table named airline.books that contains no rows of data and three columns c. a table named airline.books that compiles the view definition and stores the SELECT statement d. an empty view named airline.books that contains no rows of data

B To select unique combinations of the values of multiple columns, you place the DISTINCT keyword once in the SELECT clause before the entire column list.

Which SELECT statement creates a report that displays only the unique combinations of values of Customer_Type_ID and Country? a. select distinct Customer_Type_ID Country from orion.customer; b. select distinct Customer_Type_ID, Country from orion.customer; c. select distinct Customer_Type_ID, distinct Country from orion.customer; D.select Customer_Type_ID, Country from orion.customer where distinct Customer_Type_ID and Country;

C. The original query uses the IN operator in the WHERE clause because it returns multiple values. The use of the =ANY keyword returns equivalent results.

Which WHERE clause is equivalent to the following? where ID in (select employee_ID from orion.employee_addresses where city='San Diego') a. where ID = (select employee_ID from orion.employee_addresseswhere city='San Diego') b. where ID = all (select employee_ID from orion.employee_addresseswhere city='San Diego') c. where ID = any (select employee_ID from orion.employee_addresseswhere city='San Diego') d. where ID in (select employee_ID from orion.employee_addresseswhere city=San Diego)

D. The SAS keyword CALCULATED enables you to use the results of an expression in the same SELECT clause or in the WHERE clause.

Which WHERE clause would subset rows based on NewSalary, an alias created in the SELECT clause? a. where 75000<=Salary calculated <=100000 b. where NewSalary>=75000 and NewSalary<=100000 c. where 75000<= calculated Salary<=100000 d. where 75000<=calculated NewSalary<=100000

A. The query, as shown, creates a Cartesian product. To perform an inner join, you add a WHERE clause that specifies join conditions. You cannot use the ON clause unless you use the alternate syntax for an inner join. You use the BY statement in the DATA step, not in PROC SQL.

Which clause can you add to the following query so that it performs an inner join? select * from orion.customer, orion.order_fact; a. where customer.Customer_ID=order_fact.Customer_ID b. on customer.Customer_ID=order_fact.Customer_ID c. by Customer_ID d. None. This query already performs an inner join.

A. The query, as shown, creates a Cartesian product. To perform an inner join, you add a WHERE clause that specifies join conditions. You cannot use the ON clause unless you use the alternate syntax for an inner join. You use the BY statement in the DATA step, not in PROC SQL.

Which clause can you add to the following query so that it performs an inner join? select * from orion.customer, orion.order_fact; a. where customer.Customer_ID=order_fact.Customer_ID b. on customer.Customer_ID=order_fact.Customer_ID c. by Customer_ID d. None. This query already performs an inner join.

A. The columns that appear in the SELECT clause list must be separated by commas, not spaces.

Which clause in the following PROC SQL program is written incorrectly? proc sql; select Style Area Rooms from Realty. Houses where Area > 1000; quit; a. SELECT b. FROM c. WHERE

A. An = is not allowed in the expression in the SELECT clause when creating a new column. To assign a name that is referred to as an alias in PROC SQL, use the keyword AS followed by a valid SAS name, not the expression.

Which expression is valid in the SELECT clause? a. Salary *.01 as Increase b. Bonus=Salary*.03 c. Job_Level as Scan (Job_Title,1,' ') d. Salary*.03 Bonus

C. Use the embedded LIBNAME statement in the USING clause to make the view definition portable. If you use a one-level name for the source table in a view definition, the view and the source table must reside in the same location.

Which method would enable a view to be portable, that is, enabling it to function correctly if the underlying tables in the view are stored in different locations than the view? a. using a one-level name for the source table in the FROM clause in the view definition and storing the view in a different location as the source table b. using a two-level name for the source table in the FROM clause in the view definition and storing the view in the different location as the source table c. embedding the LIBNAME statement with a USING clause when creating the view

C. The DESCRIBE VIEW statement writes the stored query expression of a view to the log.

Which statement would you use to investigate the contents of a view named salesrepsstored in the orion library? a. dictate orion.salesreps; b. describe table orion.salesreps; c. describe view orion.salesreps; d. print view orion.salesreps;

B. When you need to use a comparison operator other than equal, put the case operator in the WHEN-THEN clause.

Which of the following CASE expressions is correct? a. case Age when 20<=Age<=29 then '20-29' b. case when 20<=Age<29 then '20-29'

E. All of this information can be stored as a text string in a macro variable.

Which of the following can be stored in a macro variable? a. 9.2 b. 10:32 c. 1352050 d. Soccerballs e. all of the above

B. A view does not contain actual values. It contains instructions on how to retrieve the values upon execution of the compiled view instructions.

Which of the following is not a characteristic of a PROC SQL view? a. is a query expression that is given a name and stored for later use b. contains actual values and retrieves values upon execution c. can be created from a simple or complex query expression d. can be derived from one or more tables or views

B. The operator in the HAVING clause in the outer query specifies how many values it expects from the inner query.

Which of the following items controls whether the outer query expects a list of values or a single value from the subquery? a. the operator in the WHERE clause in the inner query b. the operator in the HAVING clause in the outer query c. the operator closest to the PROC SQL keywords d. the operator in the WHERE statement in the inner query e. the operator in the HAVING statement in the outer query

A. When you reference a macro variable, you must precede the macro variable name with an ampersand (&). You can reference macro variables anywhere in SAS code. When you reference a macro variable within a text string, you must enclose the text string within double quotation marks. The macro processor will not resolve macro variable references that appear within single quotation marks.

Which of the following lines of code correctly reference a macro variable? a. title "The Sales of the &Country Offices"; b. where salary > 'avgSal'; c. title 'The Sales of the &Country Offices';

A The LABEL= column modifier is not ANSI standard. It is a SAS enhancement to specify labels. You can use the ANSI column modifier, the LABEL= column modifier, or both within the same SELECT clause.

Which of the following options uses an ANSI standard column modifier to create a label that is displayed as the heading for a column in the output? a. 'Employee Number' b. Employee Number c. Label='Employee ID' d. Header=Employee Reference Number

D The NONUMBER/NUMBER options control whether the SELECT statement includes a column that displays row numbers as the first column of query output in the Output window or HTML output.

Which of the following options would you use to change the option that numbers the rows in query output? a. OUTOBS b. PRINT c. DOUBLE d. NONUMBER

B TITLE and FOOTNOTE statements must precede the SELECT clause, because in the SQL procedure the SELECT clause runs immediately. They can be placed before or after the PROC SQL statement.

Which of the following programs position the TITLE statement correctly? a. proc sql; select Employee_ID from orion.order_fact; quit; title 'Manager Report'; b. proc sql; title 'Manager Report'; select Employee_ID from orion.order_fact; quit; c. proc sql; select Employee_ID from orion.order_fact; title 'Manager Report'; quit;

A. The query that generates a Cartesian product includes both matching rows and non-matching rows in the result set. The inner join produces a result set that contains only matching rows.

Which of the following queries generates result sets that include both matching and non-matching rows? a. select phones.Employee_ID, Employee_Name, Phone_Number from company.phones, company.address; b. select phones.Employee_ID, Employee_Name, Phone_Number from company.phones, company.address where phones.Employee_ID = address.Employee_ID; c. select phones.Employee_ID, Employee_Name, Phone_Number from company.phones inner join company.address on phones.Employee_ID = address.Employee_ID;

B. The SYMBOLGEN system option writes the results of resolving macro variable references to the SAS log.

Which of the following statements enable you to view the values of the macro variables without using them in a report? a. the SYMBOLLOG system option b. the SYMBOLGEN system option c. the NOFEEDBACK PROC SQL option d. the LABEL= option

B A subquery has to be enclosed in parentheses.

Which of the following statements identifies the syntax error in this query? proc sql; select Lastname, Firstname from charity.donors where Donation>1000 and Lastname not in select Lastname from charity.current; quit; a. The subquery requires its own semicolon. b. Parentheses are required around the subquery. c. The subquery must reside inside a HAVING clause. d. The subquery must reference the same table as the outer query.

A. At initialization, SAS creates special Read-Only dictionary tables and views that contain data or metadata about each SAS session or batch job. You can access the tables using PROC SQL or you can use traditional SAS programming to access the view.

Which of the following statements is not accurate regarding dictionary tables and views? a. You can use the information only from dictionary tables and views using traditional SAS programming. b. They are created when SAS initializes the session. c. They are Read-Only tables and views. d. They contain data or metadata about the SAS session.

B. The RESET statement enables you to add, drop, or change the options in the PROC SQL step without restarting the procedure.

Which of the following would you do to add, drop, or change the options in the same PROC SQL step? a. Restart the step. b. Use a RESET statement. c. Use a CHANGE statement. d. Use a RESTART statement.

B. The DESCRIBE TABLE statement is used to write the column details of the table, dictionary.columns.

Which of the programs writes the column details of dictionary.columns to the SAS log? a. proc sql; describe table dictionary.dictionaries; quit; b. proc sql; describe table dictionary.columns; quit; c. proc sql; describe view dictionary.columns; quit;

A. The INTO clause creates one macro variable and assigns the value from the first column of the first row of the query result.

Which of these queries would place one value from the first row returned by the SQL query into a macro variable? a. proc sql noprint; select avg(Salary) into :MeanSalary from store.employee; quit; b. proc sql noprint; select avg(Salary), Date into :MeanSalary, :DueDate from store.employee; quit; c. proc sql noprint; select No, Street, City, State, Zip, Country into :Address1-:Address6 from store.employee; quit;

D. To display an expanded query, which contains a list of qualified column names, in the SAS log, you add the FEEDBACK option to the PROC SQL statement above the SELECT statement.

Which program correctly displays a list of qualified column names in the SAS log? a. proc sql; describe table acme.staff; quit; b.proc sql; describe acme.staff; quit; C.proc sql noexec; select * from acme.staff; quit; D.proc sql feedback; select * from acme.staff; quit;

B You can specify columns created by summary functions in the HAVING clause, with or without the CALCULATED keyword. However, you cannot specify columns created by summary functions in the GROUP BY clause. In the WHERE clause, you cannot specify summary functions that have only a single argument.

Which query can successfully create a report? Assume that all referenced columns appear in the underlying table. a. select Dependents,avg(Salary) as AvgSalary from orion.employee_payroll group by calculated AvgSalary; b. select Dependents,avg(Salary) as AvgSalary from orion.employee_payroll group by Dependents having AvgSalary >40; c. select Dependents, month(Employee_Hire_Date) as HireMonth, avg(Salary) as AvgSalary from orion.employee_payroll where calculated AvgSalary group by Dependents, HireMonth; d. select Dependents,avg(Salary) as AvgSalary from orion.employee_payroll where AvgSalary >40 group by Dependents;

C The full join with the COALESCE function creates this result set. To overlay two columns, you must specify the two columns as arguments in the COALESCE function.

Which query joins the tables Group1 and Group2 and overlays the Tag columns to produce the result set shown below?

C. The full join with the COALESCE function creates this result set. To overlay two columns, you must specify the two columns as arguments in the COALESCE function.

Which query joins the tables Group1 and Group2 and overlays the Tag columns to produce the result set shown below?

C The SELECT clause qualifies the Prod_ID column name, so the result set has only one Prod_IDcolumn. This is an inner join, so the first WHERE expression selects only the rows that have matching values of Prod_ID. The second WHERE expression further subsets the rows so that the result table contains only the one row in which the value of Color is Blue.

Which result set does the following join create? a. Result Set 1 b. Result Set 2 c. Result Set 3

C. The SELECT clause qualifies the Prod_ID column name, so the result set has only one Prod_IDcolumn. This is an inner join, so the first WHERE expression selects only the rows that have matching values of Prod_ID. The second WHERE expression further subsets the rows so that the result table contains only the one row in which the value of Color is Blue.

Which result set does the following join create? a. Result Set 1 b. Result Set 2 c. Result Set 3

C. The CREATE TABLE statement does not create a stored query expression that does not include data.

Which statement is not correct regarding the CREATE TABLE statement? a. creates a new table from an existing table or set of tables b. populates the table with data from the result of the query c. creates a stored query expression that does not include the data d. creates a table from a simple query expression e. creates a table from a complex query expression

B. The INOBS= option is a PROC SQL option, not a system option, so you specify it in the PROC SQL statement. The INOBS= option restricts the number of rows that PROC SQL reads from all tables specified in the FROM clause. If a PROC SQL step produces output without the INOBS= option, it does not necessarily produce output when you add the INOBS= option.

Which statement is true about the INOBS= option? a. You specify the INOBS= option in the OPTIONS statement. b. The INOBS= option restricts the number of rows that PROC SQL reads from the table (or tables). c. The INOBS= option restricts the number of rows that PROC SQL writes to output. d. If PROC SQL step produces output without the INOBS= option, it also produces output when you add the INOBS= option.

B. Not using the keyword ALL is generally less efficient because it requires PROC SQL to make an extra pass through the data to eliminate duplicate rows.

Which statement is true about using the keyword ALL in a set operation? a. Generally, using the keyword ALL is less efficient because it requires an extra pass through the data. b. Generally, using the keyword ALL is more efficient because it avoids an extra pass through the data. c. PROC SQL does not allow duplicate rows to remain eligible for processing. d. The keyword ALL is not an implied part of the OUTER UNION set operator and is not necessary.

A. When the CORRESPONDING keyword is used with EXCEPT and INTERSECT, it instructs PROC SQL to align columns that the query results have in common. The OUTER UNION CORR aligns by like column names in both tables and includes columns that do not have the same name in both tables.

Which statement is true about using the keyword CORR in a set operation? a. When used with the EXCEPT operator, the CORR keyword instructs PROC SQL to position by same-name columns and eliminate columns that do not have the same name in both tables from the final results. b. When used with the INTERSECT operator, the CORR keyword instructs PROC SQL to overlay columns by position in the final results. c. When used with the OUTER UNION operator, the CORR keyword instructs PROC SQL to eliminate columns that do not have the same name in both tables from the final results.

b. A noncorrelated subquery can be extracted and run independently of the outer query.

Which statement is true regarding a noncorrelated subquery? a. The subquery is known as a virtual table. b. The subquery can run as a stand-alone query. c. The outer query passes a value to the inner query before it can execute. d. The subquery must be enclosed in brackets.

C. To write a self-join, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Which syntax is appropriate for a self-join? a. select ID, Name, mgr.ID, mgr.Name from employees as emp, organization as org where emp.ID = org.ID; b. select ID, Name, mgr.ID, mgr.Name from employees and emp or mgr, organization as org where emp.ID = org.ID and emplID =mgr.ID; c. select emp.ID, emp.Name, mgr.ID, mgr.Name from employees as emp, organization as org, employees as mgr where emp.ID = org.ID and org.Manager_Name=mgr.ID;

C To write a self-join, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Which syntax is appropriate for a self-join? a.select ID, Name, mgr.ID, mgr.Name from employees as emp, organization as org where emp.ID = org.ID; b. select ID, Name, mgr.ID, mgr.Name from employees and emp or mgr, organization as org where emp.ID = org.ID and emplID =mgr.ID; c. select emp.ID, emp.Name, mgr.ID, mgr.Name from employees as emp, organization as org, employees as mgr where emp.ID = org.ID and org.Manager_Name=mgr.ID;

D Descending order specifies to display values from the largest to the smallest numeric or character value. PROC SQL treats null or missing values as the smallest possible value.

Which value would be displayed first in the output based on this code if the values returned by the YEAR function are . (missing), 2010, 2011, and 2012? proc sql; select Employee_ID, Employee_Term_Date format=date9., year(Employee_Term_Date) as year from orion.employee_payroll where Employee_Gender='M' and Marital_Status='S' order by year desc; quit; a. . (missing) b. 2010 c. 2011 d. 2012

b. No. Because the tables had no duplicate rows and the columns are named the same, the keywords cause no changes.

Will adding the CORR and ALL keywords to the EXCEPT operator change the number of rows that the query produces? a. Yes b. No

B. The CREATE VIEW statement stores the SELECT statement. It does not create a report. PROC SQL builds the data each time the view is referenced rather than when the view is created.

Will this code generate a report? proc sql; create view work.birth_months as select Employee_ID, Birth_Date, month(Birth_Date) from orion.employee_payroll; quit; a. Yes b. No