By creating and developing SQL tables only then can we understand how to best use the memory available.
Introduction
This article will aim to address the core four steps required when maintaining SQL tables. We are talking about the four operations of; Create, Read, Update, and Delete, commonly referred to as CRUD. With CRUD, we are able to build SQL tables from any information that is currently available in the database or that we have added after the database was initially created.
SQLite was used to complete the analysis performed below. The examples that are being reviewed will look at creating a simple dataset containing soccer player details.
CREATE & READ
The operation of creating a table helps to create an empty shell. With an empty shell in place, the user is able to populate it with the appropriate data. Understanding the correct data formats is key to creating this empty table. There are many different variable formats available, with string and numeric making up the majority of variable values. Therefore, being able to correctly assign the right format for the job required does entail some planning.
Whereas the reading of a table can be thought of as viewing the data that is contained within the table. By taking a snapshot of the variables we are able to get a sense of the range of values that are present. Also, it provides developers with the opportunity to explore the information that can be gained by querying the data in different ways.
During the initial development of a table, there will most likely be some trial and error involved. Particularly, if the data that is due to be input into the table is not all made available during the planning phase. We will see with some examples how this unknown quality can be overcome.
Within Sql code 1.1, we are creating the player_details table with four different variables. By using the string format VARCHAR(), it allows us to provide a varying length of character values for each row in the dataset. One key point to highlight is that with an SQLite database the value contained within the parenthesis is ignored and no length restrictions are applied. The example details that follow help to let readers understand the concept behind assigning a value in the parenthesis. It is most common for this value to have an impact in many other SQL languages that have a more rigid data structure in place. The value contained within the first parenthesis (100), relates to 100 unique values being available for data entry to the variable.
If the value that we include for player_name only contained 4 characters for the name "Mark", then 96 empty characters would also be included. Therefore, this could be viewed as an inefficient use of memory as there are more empty values compared to populated values. However, if the maximum value for this variable was 3, and we had provided the value "Mark", the string would have been truncated to show "Mar", as not enough values were made available for use within the variable. It is this unknown quantity that we have to be aware of when developing character variables in a SQL table. By understanding the maximum length of all potential input values then we could efficiently assign an appropriate maximum value for the character variable.
As the first three variables in the table have a string format, each of them faces the same challenges mentioned above with regard to appropriately assigning enough space for the input values. The fourth variable relates to the only numeric variable within the table. In this instance, an integer format value (INT) has been provided as the variable type. As all values to be assigned will be whole numbers, with no player being assigned a half appearance, even if they only play half a match, an integer format makes the most sense. Should a decimal value be required for a numeric variable then the format to use would be float. By understanding the range of numeric values that will be used can help when assigning an efficient variable format.
When aiming to read the values from the created SQL table, we have attempted the SELECT statement. However, when this query was executed no results were returned.
We can review the output values displayed above by browsing through the tables available within the database. With the player_details table, the variable names are shown but no values are present to review. In the next sections, we will understand how to insert and delete rows of data from this new table.
INSERT
With the table in place, we are now able to insert values and begin to perform data analysis. When inserting values we always want to make sure that the correct values align with the right variable. This is where having the variable names in the same order as the initial table design really helps to avoid any confusion.
The values inserted using the SQL code above have been assigned to the correct variables. To better understand this point, we have highlighted the table that the data is being inserted into. The variables that are required to be populated have been provided in the following parenthesis. With the variable order aligning to the table design, the values parenthesis can be updated and inserted into the table.
After running the insert SQL query, we can perform a select statement to read the values in the table and see that everything has been added correctly. This short insert statement highlighted how a single row of values could be inserted into the table. Additionally, multiple rows of values could be provided and inserted using the same method. However, as the number of rows begins to increase there is the possibility for data entry error using this method. Therefore, performing a select statement to retrieve the data from other imported tables and inserting these into the table can help to reduce the likelihood of errors occurring.
UPDATE
With data now inserted into the table, there are occasions when the developer needs to adjust the values that have been assigned. We could start the create and insert process all over again. However, if the table has been creating using data that has since been deleted then this might not be a viable option. An alternative solution exists and this is using the update statement.
As we can see within the SQL code, an update statement has been used to adjust the team name from the original acronym to the long name format. We first start by requesting the update statement to be applied to the appropriate table. From here we use the set statement to highlight the variable that we wish to update and provide a corresponding value. In order to ensure that only the row value required to be updated is done so, we make use of the where filter statement to select the row. As there is only one value to update in this table it might not make sense to add this extra line of code. However, it is a great practice to get used to filtering the appropriate row values required. In addition to using the set statement on one variable, we could provide multiple variable names and update values if these were all required to be changed.
The output results above show that the changes have been updated in the table as expected. If there was a requirement to update the variable but maintain a record of the original variable value, an additional variable could be added to the table. Such a process of maintaining a record of table changes can be logged for future reference.
DELETE
Finally, after creating, reading, inserting, and updating values within the table and performing the appropriate data analysis we might have to delete values that are not required anymore. It can be a common practice to perform the data analysis to produce Management Information (MI) or Business Intelligence (BI) that is shared with business stakeholders and then have to delete data for memory purposes. We are able to maintain the process steps required to create the analysis with all of the SQL statements but may need to delete information to be able to perform other analyses.
Similar to the insert and update statements, with the delete statement we have to outline the table to perform the query on. Also, we have provided the where filter clause to ensure that only the data that needs to be deleted will be deleted. If the where clause was not assigned then additional data might be deleted.
The output table above shows that the data was successfully deleted from the table. When performing any delete statement we must be very careful that the operation being performed is required. Also, we might require an elevated level of authorization to perform this statement as the permanent deletion of data should always be treated with caution. With this example, we have outlined the statements that can be used to ensure that only certain data values are deleted.
Conclusion
Within this article, we have discovered the endless possibilities available to us as SQL developers when working with tables. We started with the process of creating an initial table that would be used to store future values in it. From here we showed how the table could have values inserted into the table and then read these values to see if they were inserted correctly. As with any table, some values may require adjustment and this is where the update statement was used to change the value of a variable. Lastly, we saw how after performing all of the data analysis we have the ability to delete the dataset. It is this deletion step that might be performed automatically by a database to conserve memory. However, we should always be cautious when permanently deleting any data if we have the ability to do so.
Thanks very much for reading
