LATEST SUBMISSION GRADE 100% The data collected for an analysis project has just been cleaned. What are the next steps for a data analyst? Select all that apply. Correct. Verification and reporting are the next steps for a data
analyst after the data is cleaned. What is involved in seeing the big picture when verifying data cleaning? Select all that apply. Correct. To see the big picture when verifying data cleaning, consider the business problem, the goal, and the
data. Which of the following functions automatically remove extra spaces when cleaning data? Correct. TRIM automatically removes extra spaces when cleaning data. What tool can a data analyst use to figure out how many identical errors occur in a dataset? Correct. A data analyst can use COUNTA to figure out how many identical errors occur in a dataset. A WHEN statement considers one or more conditions and returns a value as soon as that condition is met. Correct. A CASE statement considers one or
more conditions and returns a value as soon as that condition is met. What is the process of tracking changes, additions, deletions, and errors during data cleaning? Correct. Documentation is the process of tracking changes, additions, deletions, and errors during data cleaning. At what point during the analysis process does a data analyst use a changelog? Correct. A data analyst uses a changelog while cleaning data. A data analyst commits a query to the repository as a new and improved query. Then, they specify the
changes they made and why they made them. This scenario is part of what process? Correct. Specifying the changes an analyst made and why they made them is part of creating a changelog. What is the first step in the verification process? Correct. The first step in the verification process is to compare cleaned data with the original, uncleaned dataset and compare it to what is there
now. Fill in the blank: A changelog contains a _____ list of modifications made to a project. Correct. A data analyst uses a changelog to access the information needed. A changelog is a file that contains a chronological list of modifications made to a project.Weekly challenge 4
Question 1
Question 2
Question 3
Question 4
Question 5
Question 6
Question 7
Question 8
EXTRA QUESTIONS
Question
Question
Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last condition evaluating to true overwrites what the first condition that evaluated to true did? For example:
SELECT CASE WHEN 1+1 = 2 THEN'YES' WHEN 1+1 = 3 THEN 'NO' WHEN 1+1 = 2 THEN 'NO' ENDThe results is "YES" even though the last when condition should make it evaluate to "NO". It seems that it exits once it finds the first TRUE condition. Can someone please confirm if this is the case.
Paul White♦
73.7k26 gold badges381 silver badges595 bronze badges
asked May 29, 2013 at 18:33
1
•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
Reference //docs.microsoft.com/sql/t-sql/language-elements/case-transact-sql
This is standard SQL behaviour:
A CASE expression evaluates to the first true condition.
If there is no true condition, it evaluates to the ELSE part.
If there is no true condition and no ELSE part, it evaluates to NULL.
Ian Kemp
3271 silver badge12 bronze badges
answered May 29, 2013 at 18:36
James JenkinsJames Jenkins
6,0586 gold badges38 silver badges77 bronze badges
1
SQL Server usually does short-circuit evaluation for CASE statements (SQLFiddle):
--Does not fail on the divide by zero. SELECT CASE WHEN 1/1 = 1 THEN 'Case 1' WHEN 2/0 = 1 THEN 'Case 2' END; --Fails on the divide by zero. SELECT CASE WHEN 1/1 = 99 THEN 'Case 1' WHEN 2/0 = 99 THEN 'Case 2' END;There are however several types of statements that as of SQL Server 2012 do not correctly short-circuit. See the link from ypercube in the comments.
Oracle always does short-circuit evaluation. See the 11.2 SQL Language Reference. Or compare the following (SQLFiddle):
--Does not fail on the divide by zero. SELECT CASE WHEN 1/1 = 1 THEN 'Case 1' WHEN 2/0 = 1 THEN 'Case 2' END FROM dual; --Fails on the divide by zero. SELECT CASE WHEN 1/1 = 99 THEN 'Case 1' WHEN 2/0 = 99 THEN 'Case 2' END FROM dual;This same test can't be done with MySQL because it returns NULL for division by zero. (SQL Fiddle)
answered May 29, 2013 at 19:07
Leigh RiffelLeigh Riffel
23.6k16 gold badges75 silver badges147 bronze badges
5
It appears that MS SQL Server uses a short-circuit evaluation also.
In the following test I have 3 tests. The first one is always true, the second one fails without referencing the table, and the third fails only when the data is taken into account.
In this particular run both rows are returned successfully. If I comment out the first WHEN, or
the first and second then I get failures.
answered May 29, 2013 at 20:07
Kenneth FisherKenneth Fisher
23.8k9 gold badges56 silver badges108 bronze badges
if the case statement used in the WHERE condition and the first case when statement involve evaluating column values from the table, and the first row in the table does not satisfy this condition, the case statement will go to next case when statement.
declare @tbl table(id int) insert into @tbl values(1) insert into @tbl values(2) insert into @tbl values(3) --Fails on the divide by zero. SELECT * FROM @tbl where CASE WHEN id = 2 THEN 1 -- first row in table will not satisfy the condition WHEN 2/0 = 1 THEN 1 ELSE 0 END =1 -- when filter the records to only who will staisfy the first case when condition, it will not fail on the divide by zero SELECT * FROM @tbl where ID=2 and -- first row in table will satisfy the condition CASE WHEN id = 2 THEN 1 WHEN 2/0 = 1 THEN 1 ELSE 0 END =1
answered Jan 24, 2015 at 11:11