Summary

Summary of SQL Exercise: The Blunder

Overview

In this exercise from Hackeray's SQL aggregation section, the task involves calculating the difference in average salaries due to a key error. An employee, Samantha, was typing salaries without zeros due to a broken keyboard.

Task

Data Source: The exercise uses a table named employees, with columns: id, name, and salary.
Objective:
- Calculate the average of the actual salary values.
- Calculate the average of modified salary values with all zeros removed.
- Find the difference between these two averages and round it up to the nearest integer.

Steps

Calculate Actual Average:
- Use the AVG() function directly on the salary column.
Calculate Modified Average:
- Use the REPLACE() function to remove zeros from each salary value.
- Calculate the average of these modified values.
Compute the Difference:
- Subtract the modified average from the actual average.
- Apply the CEIL() function to round up the difference to the next integer.

Example SQL Code

SELECT
  CEIL(AVG(salary) - AVG(REPLACE(salary, '0', ''))) AS error_difference
FROM
  employees;

Conclusion

The exercise effectively illustrates how a simple data entry error can significantly impact numerical calculations and provides practice in using SQL functions for aggregation and string manipulation.

Video Transcript

Hey everybody, I'll be doing the Blunder, an exercise from the aggregation subsection of the section SQL in Hackeray. So we're given the stable employees has the columns id, name and salary. We'll be interested in the columns salary for the purposes of this exercise. And there's lots going on here, but actually it's a very simple exercise. So you have the salary values, right? Each row has a salary. If we add them up and divide by the number of salaries, you get the average. Now this person here, Samantha in the exercise, for some reason, she had a keyboard with a key zero broken. So whenever she typed numbers, it would type the numbers without the zeros. So we have to figure out what's the error. Like if we take the average of the original salaries as they are, and then we take the average of the salary values as they were typed without the zeros, and what's the difference? Okay? So to do this, we're first going to take the average normally, like we do the average of salary values. And then we have to take the average of the salary values with the zeros from each number removed. So we have to replace for each value of salary. If there's a zero, you take that out. And we're going to use the replace function to do that. And we take those two averages and we subtract them. And then we have to round up to the next integer. And then once you round up, you use the ceiling function, seal. So if you have like 1.5, you take ceiling of that or go to two, because that's the next integer up. So let's get started. I will use my SQL. So if you want to visualize the data, you can do select star from employees. Oops, I forgot from. And here's what the data looks like. We'll be interested in the third column here. Okay, you see this one has a zero, nine, zero, zero, nine. So for the first average, you're going to do as they are like this, sum them all up, okay, divide by the number of rows. Now for the second average, we're going to take this nine, zero, zero, nine, take out the zero zeros in between so it becomes 99. And then do that for every single row. For example, this other row, nine, nine, nine, followed by zero, remove the zero, only take 99.9 and then add them all up with the other ones and take this average. And then at the end, we just subtract from the original average with the zeros included. So let's get started here. Let's first do the one with the zeros already there. So it's just average of salary column. Okay, now what's the other average here? I'm going to do minus, okay, and this is going to be average of salary, but I need to remove the zeros. How can I do that? Well, we're going to use the replace function. So replace something, what do I want to replace from the salary column, comma, take any zeros that you find, replace that with nothing. So this is an empty string. It's a single quote, single quote without anything in between. So that will take care to remove all the zeros. So remember that nine, zero, zero, nine becomes just 99. And then once you get this average, and we got the other average, subtract them, but you had to round it up to the next integer. So you take this whole thing and you pass it to the seal ceil function. Okay, let's submit the code. All right, so this is the error, right? The difference between the actual and the miscalculated average.