Question

Does the data contain errors? If so, write queries in SQL to find these errors and propose a way to address the issues

Theres a change of weight error (end weight-start weight) calculated wrong

and a logical error

Answer 1

Yes, Of Course the data contain the errors. I think the errors may be:

1. Gauge min and max values: For continuous variables, checking the minimum and maximum values for each column can give you a quick idea of whether your values are falling within the correct range.

To Fix this issue using sql queries: create a measure using ALL() function to calculate the max value, ALL() function ignores all slicers and visual/page/report filters.

1. Look for missings: The easiest way to find missings is to perform a count, if you have this function available. If not, there are other ways to find missing values. Try sorting your columns (both ‘ascending’ and ‘descending’) to see if any missing values exist in your columns, or filtering your dataset such that you’re only looking at records with a missing value. While sometimes missing values are inevitably due to chance, it’s worth double-checking to see if there might be an underlying reason for missingness, and address them as best you can.

To Fix this issue using sql queries: By default, SQLite does not display NULL values in its output. The .nullvalue command causes SQLite to display the value you specify for NULLs. We will use the value -null- to make the NULLs easier to see

SELECT * FROM Visited;

SELECT * FROM Visited WHERE dated IS NULL;

1. Check the values of categorical variables: Depending on your methodology and the number of people contributing to a database, there can be lots of room for error when entering data. One quick way to find these is to pull up all of the different categories that a categorical variable can take on.

To Fix this issue using sql queries: The correlated subquery causes the ERROR: because it has multiple rows:

, CASE

    WHEN a.Group IN (' ')

    THEN (select first(b.group) from mydata as b where first(a.Group_no) = first(b.Group_no))

  END AS group_desc2

Change the sub-query to one that returns a single row. Something like:

, case

    when not missing(group) then group

    else (select min(group) from have as inner where inner.group_no = outer.group_no)

  end

  as group

1. Look at the ‘incidence rate’ of binary variables: If we think of a true binary variable as one made up of 1’s and 0’s, looking at its mean (or the incidence rate) will tell you the proportion of 1’s you have in your dataset. It’s worth double-checking this to make sure that your binary is set up correctly. One common mistake I’ve seen is to have 1’s and nulls, rather than 1’s and 0’s. This becomes easy to spot because the “rate” of the binary variable will be equal to 1. The proportion of 1’s you get should make sense for the behavior you are trying to flag within your dataset.

Fix weight error which was calculated wrong:

For Example..Weight Decimal (5,2) means the total number of digits cannot exceed 5 and 2 digits can be placed to the right of the decimal. However, the value 1000.45 in the second line of code above exceeds the specified range of (5, 2) since it means 6 digits in total and throws an overflow error .

Fix using

ALTER TABLE Table_name ALTER COLUMN Weight decimal (6,2)

About logical error :

Sometimes, a subcondition is inconsistent, but the entire condition is consistent (e.g., because of a disjunction). Of course, also the opposite can happen: Sub Conditions that are tautologies.Both kinds of unnecessary complications indicate logical misconceptions and it is quite likely that the query will not behave as expected.Furthermore, implied sub conditions are unnecessary complications. In certain circumstances,implied sub conditions can help the optimizer to find a better execution plan, but then they should be clearly marked as an optimizer hint. In exams, it happens quite often that students adda condition, such as “A IS NOT NULL '' that is already enforced as a constraint.There are different possible formalizations of the requirement for “no unnecessary logicalcomplications”. A quite strict version is that whenever in the DNF of the query condition, asub condition is replaced by “true” or “false”, the result is not equivalent to the original condition.