Pro Elec 4 Reviewer (Lab) Quiz PDF

How many rows appear PRO ELEC 4 in the preview result (or the Written column in the step metrics) after filtering REVIEWER (LAB) records of the Excel file for null values?...

How many rows appear PRO ELEC 4 in the preview result (or the Written column in the step metrics) after filtering REVIEWER (LAB) records of the Excel file for null values? Question 1 Answer a. 14 WK1-2 LAB SW1: b. 11 c. 13 Connection from Pentaho d. 15 to DB2: Which fields did you use to sort data to merge with the Date_Dim table? N/A -No Data of MC Your answer should include fields in the Excel file and columns in the Date_Dim WK3-4 LAB SW2: Upload table. the PDF SSExcelData Data Question 2 Answer a. CALMONTH Integration activity as b. Month c. CALYEAR instructed in the Tutorial: d. Year e. CALDAY f. Day N/A -No Data of MC How many rows appear WK3-4 LAB SW3 Upload in the preview result (or the Written column in the step metrics) after merging the PDF SSAccessData the Excel file with the Date_Dim table? Data Integration activity as Question 3 Answer instructed in the Tutorial: Answer: 11 N/A -No Data of MC Which field did you use to sort rows before the merge with the Trans_Type_Dim table? WK3-4 LAB Q1: Related Question 4 Answer to Lab Assignment 3 and a. TransTypeKey b. TransTypeCodeId 4: c. ItemMasterKey d. CustVendorKey How many rows appear Question 9 Answer in the preview result (or the Written column a. 7 in the step metrics) after merging b. 9 c. 10 with the Trans_Type_Dim table? d. 8 Question 5 Answer a. 11 Which field did you use to sort b. 13 c. 14 data before the merge with the d. 12 Branch_Plant_Dim table? Question 10 Answer Which field did you use to sort Options: BranchPlantDim data before the merge with the BranchPlantKey Cust_Vendor_Dim table? BranchPlantID Question 6 Answer BPName a. State b. CustVendorKey How many rows appear c. AddreBookId d. Trans_Type_Key in the preview result (or the Written column in the step metrics) after merging How many rows appear with the Branch_Plant_Dim table? in the preview result (or the Written column Question 11 Answer in the step metrics) after merging Answer: 10 with the Cust_Vendor_Dim table? Which field did you use to sort Question 7 Answer Answer: 10 data before the merge with the Currency_Dim table? Which field did you use to sort Question 12 Answer a. CurrencyDim data before the merge with the b. Currency_Id Item_Master_Dim table? c. Currency Question 8 Answer d. Currency_Name a. ItemMasterId b. ItemMasterKey How many rows appear c. ItemId in the preview result (or the Written column d. ItemMasterDim in the step metrics) after merging with the Currency_Dim table? How many rows appear Question 13 Answer in the preview result (or the Written column Options: in the step metrics) after merging 9 or 8 or 11 or 10 with the Item_Master_Dim table? The UnitCost calculated for the Which field(s) did you use to sort Inventory_Fact data to merge with the Date_Dim table? table for Customer 1 is Your answer should include fields in the Question 14 Answer Access table and columns in the Date_Dim a. 11.22 table. b. 3.32 Question 18 Answer c. 1.71 a. PurchaseMonth d. 33.11 b. PurchaseDay c. CALMONTH For this question, make sure the d. PurchaseYear e. CALDAY Quantity is used as the B (right) field in the f. CALYEAR Calculator step. The ExtCost calculated for the Inventory_Fact table for Customer 1 is How many rows appear Question 15 Answer in the preview result (or the Written column in the step metrics) after merging a. 120.30 b. 993.30 with the Date_Dim table? c. 1700.30 Question 19 Answer d. 110.00 Answer: 12 How many rows are inserted into Which field did you use to sort the Inventory_Fact table? data to merge with the Trans_Type_Dim Question 16 Answer table? a. 10 Question 20 Answer b. 8 a. TransTypeKey c. 9 b. CustVendorKey d. 11 c. ItemMasterKey d. TransTypeCodeId How many rows appear in the preview result (or the Written column How many rows appear in the step metrics) after filtering in the preview result (or the Written column rows of the Access table with null values? in the step metrics) after merging Question 17 Answer with the Trans_Type_Dim table? a. 11 Question 21 Answer b. 16 Answer: 12 c. 15 d. 14 Which field did you use to sort data to merge with the Cust_Vendor_Dim table? Question 22 Answer How many rows appear Options: in the preview result (or the Written column State in the step metrics) after CustVendorKey merging with Branch_Plant_Dim table? AddreBookID Question 27 Answer Trans_Type_Key Answer: 11 Which fields did you use to sort How many rows appear data to merge with the Currency_Dim table? in the preview result (or the Written column in the step metrics) after merging Question 28 Answer a. Currency with the Cust_Vendor_Dim table? b. Currency_Name Question 23 Answer c. Currency_ID Answer: 11 d. CurrencyDim Which field did you use to sort How many rows appear data to merge with the Item_Master_Dim in the preview result (or the Written column table? in the step metrics) after merging Question 24 Answer with the Currency_Dim table? a. ItemMasterDim Question 29 Answer b. ItemMasterId Answer: 9 or 10 or 8 or 11 c. ItemMasterKey d. ItemId The UnitCost you calculated for How many rows appear the Inventory_Fact table for Customer 2 is in the preview result (or the Written column Question 30 Answer in the step metrics) after merging a. 29.07 b. 22.22 with the Item_Master_Dim table? c. 1.82 Question 25 Answer d. 24.17 Options: 10 or 7 or 11 or 9 For this question, make sure the Quantity is used as the B (right) field in the Which field did you use to sort Calculator step. The ExtCost you data to merge with the Branch_Plant_Dim calculated for the Inventory_Fact table for table? Customer 2 is Question 26 Answer a. BranchPlantId b. BranchPlantKey Question 31 Answer c. BranchPlantDim Answer: 45.65 d. BPName How many rows are inserted into Question 4 Select one or more: the Inventory_Fact table? a. CalMonth b. AddrCatCode1 Question 32 Answer c. CalQuarter a. 8 d. No columns used b. 9 e. SecondItemId c. 10 d. 11 Query 1: Identify the columns appearing after the SELECT keyword. This question involves columns by themselves, not WK5-6 LAB Q2: Related aggregate function calculations. to SQL SubTotal Question 5 Select one or more: a. AddrCatCode1 Operators Assignment: b. CalMonth c. SecondItemId Query 1: Identify the tables to join in the d. CalQuarter SELECT statement solution. e. No columns used Question 1 Select one or more: a. inventory_fact Query 1: Identify the aggregate functions b. date_dim appearing after the SELECT keyword. c. cust_vendor_dim This question involves aggregate d. branch_plant_dim calculations, not grouping columns e. item_master_dim appearing by themselves after the SELECT keyword. Query 1: Identify the columns with single Question 6 Select one or more: table conditions in the WHERE clause of the a. SUM(ExtCost) SELECT statement. b. COUNT Question 2 Select one or more: c. SUM(UnitCost) a. CalYear d. No aggregate function calculations b. CalMonth e. SUM(Quantity) c. TransTypeKey d. TransDescription Query 1: How many rows appear in the result using the original data warehouse Query 1: Identify the subtotal operator(s) tables? in the GROUP BY clause. Question 7 Select one: Question 3 Select one: a. 78 a. CUBE b. 64 b. No subtotal operators c. ROLLUP c. 87 d. UNION d. 124 Query 1: Identify the columns inside the Query 2: Identify the tables to join in the subtotal operator used in the GROUP BY SELECT statement solution. clause. Question 8 Select one or more: a. item_cat_code1 e. Zip b. inventory_fact c. cust_vendor_dim d. date_dim Query 2: Identify the aggregate functions appearing after the SELECT keyword. e. trans_type_dim This question involves aggregate calculations, not grouping columns Query 2: Identify the columns with single appearing by themselves after the table conditions in the WHERE clause of SELECT keyword. the SELECT statement. Question 13 Select one or more: Question 9 Select one or more: a. MAX(UnitCost) a. CalYear b. SUM(ExtCost) b. TransTypeCodeId c. No aggregate function calculations c. UnitCost d. SUM(Quantity) d. TransTypeKey e. COUNT(*) Query 2: Identify the subtotal operator(s) Query 2: How many rows appear in the in the GROUP BY clause. result using the original data warehouse tables? Question 10 Select one: a. ROLLUP Question 14 Select one: b. No subtotal operators a. 315 c. GROUPING SETS b. 90 d. UNION c. 510 d. 305 Query 2: Identify the columns inside the subtotal operator used in the GROUP BY Query 3: Identify the columns with single clause. table conditions in the WHERE clause of the SELECT statement. Question 11 Select one or more: a. Zip Question 15 Select one: b. CalYear a. CarryingCost c. CustVendorKey b. CalYear d. Name c. ItemMasterKey e. CalQuarter ItemMasterKey d. TransTypeKey Query 2: Identify the columns appearing after the SELECT keyword. This question Query 3: Identify the subtotal operator(s) involves columns by themselves, not in the GROUP BY clause. aggregate function calculations. Question 16 Select one: Question 12 Select one or more: a. Partial CUBE a. Name b. No subtotal operators b. CalYear c. CUBE c. AddrCatCode1 d. ROLLUP d. CalQuarter Query 3: Identify the columns inside the subtotal operator used in the GROUP BY Question 21 Select one or more: clause. a. item_master_dim Question 17 Select one or more: b. branch_plant_dim a. BPName branch_plant_dim b. SecondItemId c. trans_type_dim c. CustVendorKey d. inventory_fact d. CompanyName e. company_dim e. DateKey Query 3: Identify the columns appearing Query 4: Identify the subtotal operator(s) after the SELECT keyword. This question in the GROUP BY clause. involves columns by themselves, not aggregate function calculations. Question 22 Select one: a. CUBE Question 18 Select one or more: b. Nested ROLLUP a. AddrCatCode1 c. GROUPING SETS b. CompanyName d. No subtotal operators c. SecondItemI d. DateKey e. BPName Query 4: Identify the columns inside the subtotal operator used in the GROUP BY Query 3: Identify the aggregate functions clause. appearing after the SELECT keyword. Question 23 Select one or more: This question involves aggregate a. BPName calculations, not grouping columns b. Quantity appearing by themselves after the c. CompanyName SELECT keyword. d. CompanyKey Question 19 Select one or more: e. TransDescription a. MAX(UnitCost) b. COUNT(*) c. SUM(ExtCost) Query 4: Identify the columns appearing d. SUM(Quantity) after the SELECT keyword. This question involves columns by themselves, not aggregate function calculations. Query 3: How many rows appear in the result using the original data warehouse Question 24 Select one or more: tables? a. BPName Question 20 Select one: b. CompanyId a. 26 c. ExtCost b. 46 d. TransDescription c. 215 e. CompanyName d. 322 Query 4: Identify the aggregate function(s) appearing after the SELECT keyword. Query 4: Identify the tables to join in the This question involves aggregate SELECT statement. calculations, not grouping columns appearing by themselves after the SELECT keyword. d. CUBE on all grouping columns Question 25 Select one or more: a. SUM(UnitCost) b. COUNT(*) Query 5: Identify the columns inside the c. SUM(ExtCost) subtotal operator used in the GROUP BY d. No aggregate function calculations clause. Question 30 Select one or more: a. CalQuarter Query 4: How many rows appear in the b. AddrCatCode1 result using the original data warehouse c. No columns used tables? d. SecondItemId Question 26 Select one: e. CalYear a. 296 b. 16 c. 131 Query 5: Identify the aggregate functions d. 163 appearing after the SELECT keyword. This question involves aggregate calculations, not grouping columns Query 5: Identify the tables to join in the appearing by themselves after the SELECT statement. SELECT keyword. Question 27 Select one or more: Question 31 Select one or more: a. item_master_dim a. COUNT(*) b. inventory_fact b. No aggregate function calculations c. cust_vendor_dim c. SUM(ExtCost) d. branch_plant_dim d. SUM(UnitCost) e. date_dim e. SUM(Quantity) Query 5: Identify the columns with single Query 5: How many rows appear in the table conditions in the WHERE clause of result using the original data warehouse the SELECT statement. tables? Question 28 Select one or more: Question 32 Select one: a. TransDescription a. 21 b. CalMonth b. 10 c. CalYear c. 220 d. TransTypeKey d. 211 Query 5: Identify the subtotal operator(s) in the GROUP BY clause. Query 6: Identify the aggregate functions Question 29 Select one: appearing after the SELECT keyword for a. Partial ROLLUP on some grouping all blocks. This question involves columns aggregate calculations, not grouping b. Partial CUBE columns appearing by themselves after c. ROLLUP on all grouping columns the SELECT keyword. Question 33 Select one or more: Query 6: In the query block to produce the a. SUM(UnitCost) subtotals for CalMonth, what column(s) b. COUNT(*) appear in the GROUP BY clause? c. SUM(Quantity) Question 38 Select one: d. SUM(ExtCost) a. No GROUP BY clause is used. b. CalMonth Query 6: How many SELECT blocks c. AddrCode1 appear in the statement? d. CalMonth, AddrCode1 Question 34 Select one: a. 5 b. 3 Query 7: Identify the aggregate functions appearing after the SELECT keyword for c. 4 all blocks. This question involves d. 2 aggregate calculations, not grouping columns appearing by themselves after Query 6: In the query block to produce the the SELECT keyword. grand totals, what expressions for the Question 39 Select one or more: grouping columns are used in the a. SUM(Quantity) SELECT clause? b. SUM(UnitCost) Question 35 Select one: c. SUM(ExtCost) a. CalMonth, NULL d. COUNT(*) b. NULL. AddrCode1 c. CalMonth, AddrCode1 Query 7: How many SELECT blocks d. NULL,NULL appear in the statement? Question 40 Select one: Query 6: In the query block to produce the a. 3 grand totals, what column(s) appear in the b. 4 GROUP BY clause? c. 5 Question 36 Select one: d. 2 a. No GROUP BY clause is used. b. AddrCode1 Query 7: In the query block to produce the c. CalMonth grand total, what expressions for the d. CalMonth, AddrCode1 grouping columns are used in the SELECT clause? Query 6: In the query block to produce Question 41 Select one: subtotals for AddrCatCode1, what a. CompanyName, BPName expressions for the grouping columns are b. NULL, BPName used in the SELECT clause? c. NULL, NULL Question 37 Select one: d. CompanyName, NULL a. NULL, AddrCode1 b. CalMonth, AddrCode1 Query 7: In the query block to produce the c. NULL, NULL grand total, what column(s) appear in the d. CalMonth, NULL GROUP BY clause? Question 42 Select one: Query 8: Identify the subtotal operator(s) a. BPName in the GROUP BY clause. b. CompanyName, BPName Question 47 Select one: CompanyName, BPName a. Partial ROLLUP on some grouping c. CompanyName columns b. CUBE using a grouping column and a d. No GROUP BY clause is used. composite column c. ROLLUP on all grouping columns Query 7: In the query block to produce d. Partial CUBE on some grouping subtotals for CompanyName, what expressions for the grouping columns are columns used in the SELECT clause? Query 8: Identify the columns inside the Question 43 Select one: subtotal operator used in the GROUP BY a. NULL, BPName clause. b. CompanyName, NULL Question 48 Select one or more: c. NULL, NULL a. Name d. CompanyName, BPName b. CalQuarter c. SecondItemId Query 7: In the query block to produce the subtotals for BPName, what column(s) d. CalYear appear in the GROUP BY clause? e. (CalYear, CalQuarter) Question 44 Select one: Query 8: Identify the columns appearing a. BPName after the SELECT keyword. This question b. No subtotals are produced for involves columns by themselves, not BPName so there is no query block. aggregate function calculations. c. CompanyName, BPName Question 49 Select one or more: d. CompanyName a. CalYear b. SecondItemId Query 8: Identify the tables to join in the c. CalQuarter SELECT statement solution. d. Name Question 45 Select one or more: e. AddrCatCode1 a. cust_vendor_dim b. branch_plant_dim c. date_dim Query 8: How many rows appear in the d. inventory_fact result using the original data warehouse e. item_master_dim tables? Question 50 Select one: a. 211 Query 8: Identify the columns with b. 220 conditions in the WHERE clause of the c. 130 SELECT statement. d. 189 Question 46 Select one or more: a. TransTypeKey b. CalMonth Query 9: Identify the tables to join in the c. CalYear SELECT statement solution. d. TransDescription Question 51 Select one or more: e. cust_vendor_dim a. item_master_dim b. cust_vendor_dim c. inventory_fact Query 10: Identify the subtotal operator(s) in the GROUP BY clause. d. date_dim e. branch_plant_dim Question 56 Select one or more: a. ROLLUP nested inside GROUPING SETS Query 9: Identify the subtotal operator(s) b. CUBE nested inside GROUPING in the GROUP BY clause. SETS Question 52 Select one: c. GROUPING SETS a. ROLLUP on all grouping columns d. Partial ROLLUP on some grouping b. CUBE using a composite column columns c. CUBE on all grouping columns d. Partial CUBE on some grouping columns Query 10: Identify the columns inside the subtotal operator used in the GROUP BY clause. Query 9: Identify the columns inside the Question 57 Select one or more: subtotal operator used in the GROUP BY a. Name inside GROUPING SETS clause. b. CalYear inside GROUPING SETS but Question 53 Select one or more: not nested in another subtotal operator a. CalYear c. CalMonth b. AddrCatCode1 d. Nested ROLLUP using CalYear, c. (CalYear, CalMonth) CalQuarter d. CalQuarter e. CalQuarter inside GROUPING SETS e. CalMonth but not nested in another subtotal operator Query 9: How many rows appear in the result using the original data warehouse Query 10: Identify the columns appearing tables? after the SELECT keyword. This question involves columns by themselves, not Question 54 Select one: aggregate function calculations. a. 90 b. 130 Question 58 Select one or more: c. 189 a. AddrCatCode1 d. 78 b. Name c. CalMonth d. CalQuarter Query 10: Identify the tables to join in the e. CalYear SELECT statement solution. Question 55 Select one or more: Query 10: Identify the aggregate functions a. inventory_fact appearing after the SELECT keyword. b. date_dim This question involves aggregate c. branch_plant_dim calculations, not grouping columns d. item_master_dim appearing by themselves after the rows than the cells in a data cube using the SELECT keyword. same two columns as dimensions. Question 59 Select one or more: c. Data cubes show subtotals while GROUP a. SUM(Quantity) BY results only contain the finest level of b. SUM(ExtCost) subtotals. c. No aggregate function calculations d. GROUP BY can show unlimited columns d. SUM(UnitCost) while data cubes are limited to showing only e. COUNT(*) rows and columns. In the partial CUBE operation, GROUP BY Query 10: How many rows appear in the CUBE(ItemBrand, CustZip), TimeYear, result using the original data warehouse identify subtotal groups generated. tables? Question 3 Select one or more: Question 60 Select one: a. (ItemBrand) a. 130 b. (TimeYear) b. 45 c. () c. 31 d. (ItemBrand, TimeYear) d. 78 e. (ItemBrand, CustZip, TimeYear) WK5-6 LAB Q3: SQL The order of columns in an SQL ROLLUP operation impacts the rows subtotals SubTotal Operators shown in the result. (Concept Quiz): Question 4 Select one: a. True b. False The SQL ROLLUP operator shows Question 1 Select one: Identify major lessons from the evaluation a. just the lowest level of subtotals. order of clauses in the SQL SELECT b. the complete set of subtotals. statement. c. selected set of subtotals. d. a partial set of subtotals appropriate for Question 5 Select one or more: columns related as a hierarchical dimension. a. It may be useful to use small tables to ensure that expected results of row operations occur before grouping. Identify differences between results in b. The HAVING clause is evaluated before SELECT statements containing the GROUP the GROUP BY clause. BY clause (but not subtotal operators) and c. Grouping only occurs one time in the data cube displays. evaluation of a SELECT statement. Question 2 Select one or more: d. Row operations are evaluated before a. Data cubes explicitly show missing values group operations. while GROUP BY results do not explicitly show missing values. b. A SELECT statement containing The SQL GROUPING SETS operator shows grouping of two columns cannot show more Question 6 Select one: a. just the lowest level of subtotals. b. a partial set of subtotals appropriate for In a GROUPING SETS specification, all sets columns related as a hierarchical dimension. of column combinations must be listed c. the complete set of subtotals. including the normal GROUP BY columns if d. a specified set of subtotals desired in the result. Question 10 Select one: a. False b. True In a GROUPING _ID function, GROUPING_ID(ItemBrand, CustZip, The SQL CUBE and ROLLUP operators are TimeYear), 0 is the value for subtotals for primitive operators, unable to be expressed which group of columns in a cube operation by other parts of the SELECT statement. (CUBE(ItemBrand, CustZip, TimeYear)). Question 11 Select one: Question 7 Select one: a. True a. (ItemBrand, CustZip, TimeYear) b. False b. (TimeYear, ItemBrand) c. () d. (TimeYear) WK5-6 LAB SW4: Subtotal Operators: Which statement is true about the CUBE and ROLLUP operators? Question 8 Select one: Which subtotal operator offers flexibility a. CUBE operator is appropriate for of use to the rigid syntax of other subtotal operators? independent dimensions and ROLLUP operator is appropriate for hierarchical Question 1 Answer dimensions wrong! a. Grouping Sets b. CUBE operator and ROLLUP operator b. Variation of CUBE are appropriate for independent dimensions c. Variation of ROLLUP only d. ROLLUP c. CUBE operator is appropriate for hierarchical dimensions and ROLLUP operator is appropriate for independent We have 3 columns: Program, YearLevel, dimensions and total number of students. To d. CUBE operator and ROLLUP operator generate subtotal of the per Program, per are appropriate for both independent YearLevel and the overall total, which operator shall we use? dimensions and hierarchical dimensions Question 2 Answer a. Combination of CUBE AND ROLLUP The SQL CUBE operator shows b. None of these choices Question 9 Select one: c. CUBE a. a partial set of subtotals appropriate for d. ROLLUP columns related as a hierarchical dimension. b. selected set of subtotals. c. the complete set of subtotals. Which of the following is the correct d. just the lowest level of subtotals. syntax: Question 3 Answer a. SELECT Program, YearLevel, the overall total, which operator shall we SUM(NumStudents) as use? Number_of_Students Question 5 Answer FROM cics a. none of these choices GROUP BY Program, YearLevel b. ROLLUP c. CUBE WHERE Program = 'CS' d. Combination of ROLLUP and CUBE HAVING SUM(NumStudents) > 100 ORDER BY YearLevel; b. SELECT Program, YearLevel, WK7-8 LAB Q4: Related SUM(NumStudents) as to Assignment Analytic Number_of_Students FROM cics Functions WHERE Program = 'CS' GROUP BY Program, YearLevel Query 1: Identify the table(s) to join in the SELECT statement. HAVING SUM(NumStudents) > 100 Question 1 Select one or more: ORDER BY YearLevel; a. item_master_dim c. all of these choices are correct b. cust_vendor_dim d. SELECT Program, YearLevel, c. date_dim SUM(NumStudents) as d. inventory_fact Number_of_Students e. branch_plant_dim FROM cics WHERE Program = 'CS' Query 1: Identify the column(s) with single table conditions in the WHERE clause of ORDER BY YearLevel the SELECT statement. A single table GROUP BY Program, YearLevel condition compares a column to a constant or another column in the same HAVING SUM(NumStudents) > 100; table. Question 2 Select one: How do you mimic the operations of a. CalYear CUBE and ROLL with the basic SQL DML b. TransDescription command with no SQL Subtotal c. TransTypeKey operators? d. CalMonth Question 4 Answer a. Use variation of Grouping Sets b. Use UNION Query 1: Identify the column(s) following c. Grouping Sets the PARTITION BY keywords in the analytic function specification. We have 3 columns: Program, YearLevel, Question 3 Select one: and total number of students. To a. Cust_Vendor_Dim.State generate subtotal of the per Program and b. Inventory_Fact.ExtCost c. The statement does not contain a Query 2: Identify the column(s) following PARTITON BY clause so no columns are the GROUP BY keywords. specified. Question 8 Select one or more: d. Cust_Vendor_Dim.Name a. Cust_Vendor_Dim.Name b. Cust_Vendor_Dim.State Query 1: Identify the column(s) following c. No GROUP BY clause is used. the GROUP BY keywords. d. Inventory_Fact.ExtCost Question 4 Select one: Query 2: How many rows appear in the a. No GROUP BY clause is used. result with CA as the value of b. Cust_Vendor_Dim.Name Cust_Vendor_Dim.State using the original c. Inventory_Fact.ExtCost data warehouse tables? d. Cust_Vendor_Dim.State Question 9 Select one: a. 2 Query 1: How many rows appear in the b. 1 result using the original data warehouse c. 3 tables? d. 20 Question 5 Select one: a. 78 Query 2: What is the value for sum of the external cost for the California (CA) b. 110 customer with rank of 2? You need to use c. 30 the original data warehouse tables to d. 20 answer this question. Question 10 Select one: Query 2: Identify the column(s) following a. 44471146 the PARTITION BY keywords in the b. 38657564 analytic function specification. c. 40287693 Question 6 Select one: d. 38687623 a. Cust_Vendor_Dim.Name b. The statement does not contain a Query 3: Identify the column(s) following PARTITON BY clause so no columns are the PARTITION BY keywords in the specified. analytic function specification. c. Inventory_Fact.ExtCost Question 11 Select one: d. Cust_Vendor_Dim.State a. Inventory_Fact.ExtCost b. Cust_Vendor_Dim.State Query 2: Identify the column(s) following c. Cust_Vendor_Dim.Name the ORDER BY keywords in the last line of d. The statement does not contain a the SELECT statement. PARTITON BY clause so no columns are specified. Question 7 Select one: a. Cust_Vendor_Dim.State Query 3: Identify the column(s) following b. Cust_Vendor_Dim.Name the GROUP BY keywords. c. Inventory_Fact.ExtCost d. No GROUP BY clause is used. Question 12 Select one: a. Inventory_Fact.ExtCost b. No GROUP BY clause is used. c. Cust_Vendor_Dim.State Query 4: Identify the column(s) with single d. Cust_Vendor_Dim.Name table condition(s) in the WHERE clause of the SELECT statement. A single table Query 3: What is the largest value for the condition compares a column to a RANK function in the result? Use the data constant or another column in the same warehouse tables in the mmannino table. schema to answer this question. Question 17 Select one: Question 13 Select one: a. CalMonth a. 10 b. TransDescription b. 20 c. TransTypeKey c. 21 d. CalYear d. 17 Query 4: Identify the column(s) following Query 3: What is the largest value for the the PARTITION BY keywords in the DENSE_RANK function in the result? You analytic function specification. need to use the original data warehouse Question 18 Select one: tables to answer this question. a. The statement does not contain a Question 14 Select one: PARTITON BY clause so no columns are a. 10 specified. b. 20 b. Date_Dim.CalMonth c. 21 c. Cust_Vendor_Dim.Zip d. 17 d. Date_Dim.CalYear Query 3: What is the Query 4: Identify the column(s) following Cust_Vendor_Dim.Name value in the first the GROUP BY keywords. result row in which the RANK and Question 19 Select one or more: DENSE_RANK values differ? You need to a. No GROUP BY clause is used. use the original data warehouse tables to b. Date_Dim.CalMonth answer this question. c. Date_Dim.CalYear Question 15 Select one: d. Cust_Vendor_Dim.Zip a. Customer 12 b. Customer 10 Query 4: What keywords appear in the c. Customer 11 SELECT statement to specify the window d. Customer 13 size? Question 20 Select one: Query 4: Identify the table(s) to join in the a. ROWS 1 PRECEDING 1 FOLLOWING SELECT statement. b. ROWS UNBOUNDED FOLLOWING Question 16 Select one or more: c. The statement does not contain a window a. branch_plant_dim size specification. b. date_dim d. ROWS UNBOUNDED PRECEDING c. cust_vendor_dim d. item_master_dim e. inventory_fact Query 4: How many rows appear in the result using the original data warehouse tables? Question 21 Select one: a. 2100025 a. 20 b. 1222457 b. 400 c. 1505696 c. 480 d. 10116456 d. 380 Query 6: Identify the table(s) to join in the Query 5: Identify the column(s) following SELECT statement solution. the ORDER BY keywords inside the Question 26 Select one or more: analytic function specification. a. date_dim Question 22 Select one or more: b. inventory_fact a. Date_Dim.CalMonth c. item_master_dim b. Date_Dim.CalYear d. cust_vendor_dim c. Cust_Vendor_Dim.Zip e. branch_plant_dim d. No ORDER BY clause is used. Query 6: Identify the column(s) with single Query 5: Identify the column(s) following table condition(s) in the WHERE clause of the PARTITION BY keywords in the the SELECT statement. A single table analytic function specification. condition compares a column to a constant or another column in the same Question 23 Select one or more: table. a. Date_Dim.CalMonth b. The statement does not contain a Question 27 Select one: PARTITON BY clause so no columns are a. TransTypeKey specified. b. CalMonth c. Date_Dim.CalYear c. TransDescription d. Cust_Vendor_Dim.Zip d. CalYear Query 6: Identify the column(s) following Query 5: What is the cumulative sum of the PARTITION BY keywords in the external cost for the combination of zip analytic function specification. code 02162 in year 2011 for month 6? Question 28 Select one: You need to use the original data a. Date_Dim.CalYear warehouse tables to answer this question. b. Date_Dim.CalMonth Question 24Select one: c. The statement does not contain a a. 8893999 PARTITON BY clause so no columns are b. 10346470 specified. c. 10116456 d. Cust_Vendor_Dim.Zip d. 21765928 Query 6: Identify the column(s) following Query 5: What is the sum (non- the GROUP BY keywords. cumulative) of external cost for the Question 29 Select one: combination of zip code 02162 in a. Date_Dim.CalYear year 2011 for month 6? You need to use b. Date_Dim.CalMonth the original data warehouse tables to c. SecondItemId answer this question. d. No GROUP BY clause is used. Question 25 Select one: Query 6: How many rows appear in the Query 7: What is the ratio-to-report value result using the original data warehouse in the result for the combination of 2011 tables? for CalYear and “Second Part 18” for SecondItemId? You need to use the Question 30 Select one: original data warehouse tables to answer a. 30 this question. b. 40 c. 20 Question 35 Select one: d. 10 a..0599893128 b..0662167168 Query 7: Identify the table(s) to join in the c..0644280166 SELECT statement solution. d..0838853228 Question 31 Select one or more: Query 8: Identify the column(s) following a. branch_plant_dim the PARTITION BY keywords in the b. date_dim analytic function specification. c. item_master_dim d. inventory_fact Question 36 Select one: e. cust_vendor_dim a. BPName b. The statement does not contain a Query 7: Identify the column(s) following PARTITON BY clause so no columns are the PARTITION BY keywords in the specified. analytic function specification. c. CarryingCost d. CompanyName Question 32 Select one: a. The statement does not contain a Query 8: Identify the column(s) following PARTITON BY clause so no columns are the GROUP BY keywords. specified. b. Date_Dim.CalMonth Question 37 Select one: c. Cust_Vendor_Dim.Zip a.Date_Dim.CalYear d. Date_Dim.CalYear b. No GROUP BY clause is used. c. SecondItemId Query 7: Identify the column(s) following d. Date_Dim.CalMonth the GROUP BY keywords. Query 8: Identify the column(s) following Question 33 Select one or more: the ORDER BY keywords inside the clause a. No GROUP BY clause is used. that specifies the RANK() analytic b. Date_Dim.CalYear function. c. Date_Dim.CalMonth d. SecondItemId Question 38 Select one: a. No ORDER BY clause is used. Query 7: How many rows appear in the b. BPName result using the original data warehouse c. CompanyName tables? d. CarryingCost Question 34 Select one: Query 8: How many rows appear in the a. 40 result using the original data warehouse b. 50 tables? c. 20 d. 30 Question 39 Select one: a. 30 Query 10: Identify the table(s) to join in b. 10 the SELECT statement solution. c. 20 Question 44 Select one or more: d. 40 a. branch_plant_dim b. cust_vendor_dim Query 9: The SELECT statement should c. inventory_fact contain a nested SELECT statement in the d. date_dim FROM clause. e. item_master_dim Question 40 Select one: a. False Query 10: Identify the column(s) with b. True single table conditions in the WHERE clause of the SELECT statement. A single Query 9: How many rows appear in the table condition compares a column to a result using the original data warehouse constant or another column in the same tables? table. Question 41 Select one: Question 45 Select one: a. 20 a. Cust_Vendor_Dim.State b. 10 b. Inventory_Fact.CustVendorKey c. 4 c. Inventory_Fact.ExtCost d. 5 d. Cust_Vendor_Dim.Name Query 9: What is the value of the Query 10: Identify the column(s) following CUME_DIST function column for the row the PARTITION BY keywords in the with a BPName value of “Branch Plant analytic function specification. 17”? You need to use the original data Question 46 Select one: warehouse tables to answer this question. a. Inventory_Fact.ExtCost Question 42 Select one: b. Cust_Vendor_Dim.State a..91 c. Cust_Vendor_Dim.Name b..21 d. The statement does not contain a c. 1 PARTITON BY clause so no columns are d..95 specified. Query 9: What is the value of the Query 10: Identify the column(s) following CarryingCost column for the row with a the GROUP BY keywords. BPName value of “Branch Plant 1”? You Question 47 Select one: need to use the original data warehouse a. Cust_Vendor_Dim.Name tables to answer this question. b. No GROUP BY clause is used. Question 43 Select one: c. Inventory_Fact.ExtCost a..23 d. Cust_Vendor_Dim.State b..19 c..95 d..21 Query 10: To eliminate duplicates in this statement, you should use the DISTINCT keyword. Question 48 Select one: a. True b. False Query 10: How many rows appear in the result using the original data warehouse What does 250 Cumulutative Sum tables? represent? Question 49 Select one: Question 2 Answer a. 1712 b. 1845 a. Relative OrderAmount of row 1 and 2 c. 1530 b. Cumulative OrderAmount ofr row 1,2, d. 54 and 3 c. Total of OrderAmount of row 1 and 2 d. Total of OrderAmount of row 1,2,3 WK7-8 LAB SW5: Match the correct answer: Analytic Functions: Question 3 Answer Window or range of rows for a window Match the correct answer function that includes the current row and the rows immediately preceding and Window function that calculates the following it, based on a specific ordering cumulative distribution of a value within a specified partition Answer: ROWS BETWEEN n PRECEDING AND n FOLLOWING Answer: CUME_DIST Window frame specification that includes all rows from the beginning of the partition Window function that calculates the (or result set) up to and including the relative rank of a value within a specified current row partition Answer: ROWS UNBOUNDED Answer: PERCENT_RANK PRECEDING Window function that calculates the ratio of a value to the total value within a specified partition Answer: RATIO_TO_REPORT The following command was executed: SELECT EmployeeID, Name, Salary, AVG(Salary) OVER (ORDER BY EmployeeID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS AvgSalary FROM Employees; And generated the following result: Question 4 Answer What does 5166.6667 represent? Question 4 Answer a. Total salary of rows 2-4 b. Total salary of rows 1-3 c. Average salary of rows 2-4 d. Average salary of rows 1-3 Select the correct answer. Question 5 Answer The ranks are not consecutive. Gaps are left in case of ties. Answer: RANK The ranks are consecutive without any gaps, even for tied rows. Answer: DENSE_RANK Each row is assigned a tile number indicating which group it belongs to Answer: NTILE