When statement considers one or more conditions and returns a value as soon as that condition is met.

Weekly challenge 4

LATEST SUBMISSION GRADE 100%

Índice Show

Weekly challenge 4
EXTRA QUESTIONS
Which SQL tool considers one or more conditions then returns a value as soon as a condition is met 1 point case then else when?
Which clause is used in case statement for comparing the column values?
Which SQL function enables you to eliminate the extra spaces for consistency?

Question 1

The data collected for an analysis project has just been cleaned. What are the next steps for a data analyst? Select all that apply.

Reporting
Certification
Verification
Validation

Correct. Verification and reporting are the next steps for a data analyst after the data is cleaned.

Question 2

What is involved in seeing the big picture when verifying data cleaning? Select all that apply.

Consider the data.
Consider the reporting.
Consider the goal.
Consider the business problem.

Correct. To see the big picture when verifying data cleaning, consider the business problem, the goal, and the data.

Question 3

Which of the following functions automatically remove extra spaces when cleaning data?

TRIM
CLEAR
REMOVE
SNIP

Correct. TRIM automatically removes extra spaces when cleaning data.

Question 4

What tool can a data analyst use to figure out how many identical errors occur in a dataset?

CONFIRM
COUNT
COUNTA
CASE

Correct. A data analyst can use COUNTA to figure out how many identical errors occur in a dataset.

Question 5

A WHEN statement considers one or more conditions and returns a value as soon as that condition is met.

True
False

Correct. A CASE statement considers one or more conditions and returns a value as soon as that condition is met.

Question 6

What is the process of tracking changes, additions, deletions, and errors during data cleaning?

Observation
Cataloging
Documentation
Recording

Correct. Documentation is the process of tracking changes, additions, deletions, and errors during data cleaning.

Question 7

At what point during the analysis process does a data analyst use a changelog?

While reporting the data
While gathering the data
While cleaning the data
While visualizing the data

Correct. A data analyst uses a changelog while cleaning data.

Question 8

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?

Visualizing data
Creating a changelog
Reporting data
Communicating with stakeholders

Correct. Specifying the changes an analyst made and why they made them is part of creating a changelog.

EXTRA QUESTIONS

Question

What is the first step in the verification process?

Inform others of your data-cleaning effort.
Create a chronological list of modifications made to the data.
Determine the quality of the data.
Compare cleaned data with the original, uncleaned dataset and compare it to what is there now.

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.

Question

Fill in the blank: A changelog contains a _____ list of modifications made to a project.

chronological
synchronized
approximate
random

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.

🗂️ Page Index for this GitHub Wiki

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' 
    END

The 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

•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

Reference https://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

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

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.

CREATE TABLE casetest (test varchar(10))
GO
INSERT INTO casetest VALUES ('12345'),('abcdef')
GO

SELECT CASE WHEN LEN(test)>1 THEN test
        WHEN 1/0 = 1 THEN 'abc'
        WHEN CAST(test AS int) = 1 THEN 'def'
        END
FROM casetest
GO

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

Which SQL tool considers one or more conditions then returns a value as soon as a condition is met 1 point case then else when?

Correct. A CASE statement considers one or more conditions and returns a value as soon as that condition is met.

Which clause is used in case statement for comparing the column values?

CASE Syntax The names of specific columns or expressions are entered after the CASE keyword. The WHEN and THEN keywords define the logical conditions. After the WHEN clause, we see the condition or value to compare; the THEN determines the result if the condition is met.