Top 5 SQL Mistakes You Must Avoid!

Key Takeaways

these are five common sequel mistakes that nobody's talking about so without wasting any time let's start the tutorial mistake number one incorrect use of null null represents the absence of a value or unknown data in a database it is not the same as empty string or zero in simple terms it means that the value is missing or undefined for example I have a table named example uncore table with four rows of data as you can see there are a lot of null values in this data let's explore some common SQL mistakes and see how we can avoid them first checking for null values we usually use the equal to operator to check for null values like this if we run this query no rules will be returned let's execute as you can see nothing has been returned does this means we don't have any null values in the discount column no to check for null values we should use the is null logical operator instead of equal to operator like this this simply replace the equal to sign with the isal operator that's it let's execute this query and now you can see we have two rowes where the value for the discount column is null handling null in aggregate function as mentioned earlier null is neither an empty string nor zero so we need to handle it carefully with aggregate functions so for example consider this query the result shows that the sum of sales uncore amount is 450 while the average of commission is 7.5 this result is inaccurate because we did not handle null values properly here for instance there are four entries and among those there are two entries for commission 10 and 15 if we calculate the average it should be 3.75 but here it is showing 7.5 this happens because null entries are ignored in the calculation therefore we need to be careful with how we handle null values here is how to deal with null values while using aggregate functions in this query the co Zac function return the first nonn null value which will be the value stored in the column or zero if the value is null let's execute this query and now you can see we got the correct answer similarly there are many other examples comment if you want me to do a separate video on how to handle null properly mean why let's jump to the next mistake mistake number two incorrect use of distinct keyword we use the distinct keyword to find the unique values stored in a column but sometimes it yields inaccurate results let's take an example here I have a table named 01 with data for five employees among these five employees the name John appears twice however both entries are unique in their own way Joan has two job titles tester and developer if we use distinct on the first name we won't be able to uncover this detail so a proper investigation is required before using distinct keyword first find out which entries are duplicates using the group by Clause this will show the duplicate entries next examine which other columns the duplicate entries depend on in our case it's the job uncore title let's check the job titles and see how many job titles are mapped against each first name when we execute this we see that the name John is associated with two job titles this way we get the distinct first names and clearer picture of why the names is repeating it helps determine if it's really a duplicate or if it's the same JN with different job titles or possibly two different J using distinct can be slow for large data sets because it needs to sort and remove duplicates on the other hand Group by can be more efficient if you need to aggregate data and it's also clearer in terms of intent overusing distinct is a common SQL mistake that can mask underlying data issues and lead to inefficient queries you can write more efficient and meaningful SQL queries by understanding the root cause of duplicates and using group bu for aggregation or data cleanup let's move on to the next mistake mistake number three not handling errors suppose we are updating data into a table let's see if the first update statement succeeds but the second update statement fails the data is left in an inconsistent State there is no mechanism to roll back the changes if an error occurs the therefore we should handle error properly here if any error occurs now it will be handled and transaction will be rolled back to the save point this is a dummy structure of error handling I have done a video on error handling in Oracle database you can find its Link in the description or in the I button on your screen mistake number four failing to normalize data here we have a table definition can you guess what's wrong here this is a denormalized table it has employee related attributes as well as Department related attributes when creating this table the first problem is redundancy the department uncore name is repeated for every employee in the same Department this redundency can lead to inconsistent data and will consume a lot of storage the second problem is update anomalies for example if your table has millions of entries and your company decides to change the department name from HR to Human Resources this update requires modifying a huge number of rules which consumes resources and is error PR next are insertion anomalies for example if your company decides to add a new Department named AI but hasn't hired anyone for it yet you cannot insert that department data into this table to create a new Department you need data for other columns first for example to add the department name AI to to this table you need to enter an employee ID because it's a primary key but since no one has been hired yet you cannot add an employee ID similarly there are deletion anomalies Suppose there were five employees in the HR department and four have already left the company if the fifth one also decides to leave and you delete their record the data for the HR department will also be deleted losing the department record along with the employee records the solution is to normalize the table simply create two tables one for employee data and another for Department data if you want a dedicated tutorial on normalization comment and let me know this is one of the most common mistakes people make while it might seem convenient it can lead to inefficiency and other issues especially in large systems retrieving all columns can be inefficient especially if the table has many columns and only a few are needed if the table has many columns you might be transferring a lot of unnecessary data which can slow down the query that database needs to read more data from disk increasing input output operations the best approach is to retrieve only the necessary data and use the wear Clause to filter the data you need this will decrease the the load on the database by reducing the amount of data feted from the disk the benefits of not using select estri are it immediately clear which columns are being used second only the needed data is retrieved improving performance these are five common SQL mistakes people make in this tutorial I have not touched on the topic of indexes because that requires an entire video I will cover that soon that's it for this tutorial make sure sure to subscribe to catch the next interesting topic thanks for watching this is Manish from rebellionrider.com