SQL LANGUAGE 9
Running Head: SQL LANGUAGE 1
SQL Language
Wendy Fernandez
Grand Canyon University
HIM – 650
August 2021
SQL Language
Numerous programming languages have been used in computing environments to provide the ideal framework for designing reliable software solutions. Developers use various programming languages to create their desired solutions. These applications rely on a database that supports data capturing, processing and storage. This section outlines the role of SQL in database development, outlining some of the vital queries and statements for facilitating data manipulation. This project is based on the exercise and offers a framework for approaching database creation from a technical dimension.
Numerous database systems have been developed over the years, the ease of access and manipulation has influenced their effectiveness and connection with the underlying users. Relational database systems are commonly used in various environments to foster better outcomes concerning data storage and manipulation efficiency. SQL is a commonly used language in accessing and interacting with relational databases (Kim, So, Han & Lee, 2020). This language was initially developed and maintained by the IBM research laboratory located in San Jose. The language was first developed and used in the ’70s to facilitate relational database access and manipulation.
Since its development, companies and developers have used it to create and manipulate database systems, promoting efficiency in storage and records processing. The primary purpose of the SQL language is to provide a reliable framework for developing and manipulating relational databases. Some of the primary tasks performed using this language include creating databases and tables, updating them and manipulating data. Data manipulation is achieved through various operations and commands like inserting, deleting and updating records (Uma, Sneha, Sneha, Bhuvana & Bharathi, 2019). Likewise, data definition is accomplished through commands like create. This section outlines some of the primary commands and statements used in completing the assigned tasks.
The first step was data definition, manipulation and initial population. This task was accomplished through the Create Table and Insert statements. After creating the table, the next step was to add a column named HireDate and update the first name for provider 123456 to Ben. These commands produced the results shown below. These commands are used in the data definition. The first three steps allowed the process to create a table named Provider add the hire date column, update and populate various details that best match the selected parties, as shown in the figure below.
Figure 1. Date definition, column update and table population.
In step 4, the primary tasks were to perform run two queries: selecting the last name and hire date from the table and using the distinct command. For example, the last name and hire date command are as shown below.
Figure 2. LastName and HireDate query.
The second query was executed through the DISTINCT command. This command returns different values in a given table or database, as shown in the figure below.
Figure 3. DISTINCT query results.
Queries 3, 4, 5 and 6 were executed on the provider table, focusing on fetching data according to the desired parameters. The primary queries focused on returning details about the providers based on the WHERE command. The charts below show the results of executing the provided queries.
Figure 4. Utilizing the WHERE command.
In step 5, the primary focus was to add new records populating the providers with their respective salaries, as shown below.
Figure 5. Updating provider salaries.
The following queries focused on performing arithmetic on the data stored in the database. These tasks involved average, sum and determining the minimum and maximum salaries as shown in the figure below.
Figure 6. Column Functions.
The COUNT queries are used to return the total rows that match the passed arguments. The figure below shows the count for the total rows, hire date and distinct hire date.
Figure 7. COUNT function.
The next step was in aggregation functions. First, the count function was used to group records from the table, as shown below.
Figure 8. Adding a new row.
The following task was to create another table named Patient, where several operations were performed. The Patient table is as shown below.
Figure 9. Patient table.
The multiple operations involved combining different SQL commands like WHERE and JOIN to return records from multiple tables, as shown below.
Figure 10. Multi-table operations.
The last operation was to add a new column in any table named Delete_Date, as shown below.
Figure 11. Delete_Date column addition.
It is essential to note that this column offers a reliable framework for deleting multiple records simultaneously. Such a trend is vital in large databases where multiple records can be eliminated without executing numerous queries (Brunner & Stockinger, 2021). One of the primary drawbacks associated with the new column is that it is prone to accidental deletion. The delete operation is irreversible and may lead to adverse effects on the target systems. Deleting by row is desired because, while tedious, it ensures that only the required and selected data is eliminated.
References
Brunner, U., & Stockinger, K. (2021, April). Valuenet: A natural language-to-sql system that learns from database information. In 2021 IEEE 37th International Conference on Data Engineering (ICDE) (pp. 2177-2182). IEEE.
Kim, H., So, B. H., Han, W. S., & Lee, H. (2020). Natural language to SQL: Where are we today?. Proceedings of the VLDB Endowment, 13(10), 1737-1750.
Uma, M., Sneha, V., Sneha, G., Bhuvana, J., & Bharathi, B. (2019, February). Formation of SQL from natural language query using NLP. In 2019 International Conference on Computational Intelligence in Data Science (ICCIDS) (pp. 1-5). IEEE.