Structured Query Language Queries Research Articles

Structured query language query join optimization by using rademacher averages and mapreduce algorithms

Query optimization involves identifying and implementing the most effective and efficient methods and strategies to enhance the performance of queries. This is achieved by intelligently utilizing system resources and considering various performance metrics. Table joining optimization involves optimizing the process of combining two or more tables within a database. Structured query language (SQL) optimization is the progress of utilizing SQL queries in the possible way to achieve fast and accurate database results. SQL optimization is critical to decreasing the no of queries in research description framework (RDF) and the time for processing a huge number of relatable data. In this paper, four new algorithms are proposed such as hash-join, sort-merge, rademacher averages and mapreduce for the progress of SQL query join optimization. The proposed model is evaluated and tested using waterloo sparql diversity test suite (WatDiv) and lehigh university benchmark (LUBM) benchmark datasets in terms of time execution. The results represented that the proposed method achieved an enhanced performance of less execution time for various queries such as Q3 of 5362, Q8 of 5921, Q9 of 5854 and Q10 of 5691 milliseconds. The proposed gives better performance than other existing methods like hybrid database-map reduction system (AQUA+) and join query processing (JQPro).

Rabies post-exposure prophylaxis in the emergency department

Study objectivesRabies is a zoonotic single-stranded RNA lyssavirus that can cause acute infections of the central nervous system (CNS) including encephalomyelitis, encephalitis, and meningoencephalitis that is progressively fatal. Rabies is more common in developing countries, but approximately 23,000 people in the United States (US) are estimated to have been exposed or to have received post exposure prophylaxis (PEP) yearly. Nebraska Medicine follows the Advisory Committee on Immunization Practices (ACIP) guidelines for the vaccination series, as well as the 20 units/kg administration of immunoglobulin (RIG).Nebraska Medicine Medical Center (NMC) and Bellevue Medical Center (BMC) treat the scheduling of the complete rabies vaccine series differently. At both campuses, patients receive their immunoglobulin and first vaccine in the Emergency Department (ED). At NMC, patients are scheduled to receive the remainder of their vaccination series at the outpatient infusion center by the ED pharmacist. At BMC, the subsequent vaccinations are given as “Nurse Only” return visits to the ED. The objective of this study was to compare patient compliance of two different processes for follow-up rabies vaccine series completion. This project's primary aim was to determine the rate of patient compliance for follow up rabies vaccine doses. The secondary aims of this project were to determine if there was a difference in patient follow-up compliance between the two campuses, patient specific factors that impact compliance, and potential cost savings if a dose rounding protocol for RIG was utilized. MethodsThis retrospective chart review was completed as a quality improvement project. Data from patients who had received either rabies immunoglobulin and/or a rabies vaccine, were >18 years of age, and were not admitted were collected for a 3-year period from July 1, 2019, to June 30, 2022. Data were abstracted from the patient's EMR (electronic medical records) using a SQL (Structured query language) query of pre-identified data elements. When unable to abstract with SQL query, data elements were manually abstracted. All data abstracted was collated and descriptive analysis performed. ResultsA total of 723 individual encounters were identified during the study period. After combining rabies series for each individual patient, 173 unique patients remained. After exclusions were applied, 143 patients were included: 104 patients from NMC, and 39 from BMC. For the primary outcome, appropriate completion between the two campuses was 78.3%. When comparing the two campuses, completion rates were higher at NMC (82% vs. 69%), although not statistically significant (p = 0.12). Appropriate completion of vaccine series was statistically significant for both payor and exposure type. Application of a dose rounding policy with those >45 kg, rounding to the nearest vial, as well as rounding down if at the midpoint interval, 56 fewer vials would have been used between the two campuses. This would have resulted in a potential cost savings of $57,928.64 over the study period.

A Naive approach: Translation of Natural Language to Structured Query Language

A database is a major source of information which plays an important role in our life. Information retrieval from the database requires formulating a querythatisunderstandablebythecomputerinordertoproducedesiredoutput. Generally, databases work with structured query language (SQL). But a naive user usually unfamiliar with the structured query language as well as structure of the table in the database. Hence, it becomes very difficult for the naïve-user to collect the desired information. This paper provides a solution to this problem and it enables users to retrieve information through natural language, such as English language. Being able to access information from the database by using natural language bridges the man-machine gap. Tokenization, lexical analysis, syntactic analysis, semantic analysis, and other complex stages are all involved in converting a natural language query into a SQL query. The purpose of this paper is to translate natural language queries into Structure Query Language queries, allowing non-technical people to get connected to databases and to gather the required information.

A mental model approach to teaching database querying skills with SQL and Alteryx

This paper describes an instructional approach and exercises to introduce a data analytics tool into an existing course that covers relational databases and structured query language (SQL). SQL and relational databases are traditional topics in many AIS classes and are frequently taught with established technologies such as Microsoft Access. With the onset of newer data analytics tools, educators are faced with the challenge of incorporating emerging technologies while still covering basic foundational concepts. In this paper, we position SQL as the mental model for learning the data preparation and data blending features of a popular analytics tool, Alteryx. We split the traditional SQL content from our course into four increments that increase in complexity. For each increment, we use a multi-step approach to first develop students’ mental model of the SQL query code and then apply the mental model to the new environment of Alteryx. For each increment, students solve the same set of problems in SQL and Alteryx, so that the understanding of SQL eases the transition to Alteryx, and the practice in Alteryx reinforces the understanding of SQL.

Converting Natural Language To SQL Queries Using LSTM

bstract: In today's world, not everyone is familiar with using Structured Query Language (SQL). This makes it hard for users to understand or create complex SQL queries. What we need is an improved application with a smarter interface that can bridge the gap between novice users and databases. Databases are great at managing data, but to understand their structure, users have to learn SQL. This poses a challenge for non-experts who aren't well-versed in SQL. What they need is a system that allows them to interact with databases in natural language. The system should be capable of understanding and responding to natural language commands. To achieve this objective, we utilize a range of end-to-end deep learning models, as well as probability models like conditional random field. The ambiguity in natural language makes it exceedingly difficult to determine the exact meaning of every word, therefore it is a difficult process to map individual keywords to the description of the schema and the contents of the underlying database. Accurate predictions can help us avoid unnecessary trouble. If we apply machine learning tools, we can skip the complicated process.

Data on 33 Years of Erroneous Usage of Rainfall Erosivity Equations

This paper describes the data gathered for a paper published in Earth-Science Reviews (DOI: 10.1016/j.earscirev.2023.104339) to address the problem of studies using incorrect equations to calculate rainfall erosivity (R factor), which can lead to issues related to land degradation, soil productivity loss, and biodiversity loss. The aim was to locate articles containing the incorrect equations and create a relational database that could be used to perform an in-depth analysis of the errors. Because the search target is an equation, it is impossible to directly query any literature database for the articles that contain the incorrect R equations. Therefore, a manual search of multiple databases was conducted. Subsequently, the literature search was broadened to identify the origin of the misuse of the R equations, and SQL (Structured Query Language) queries were formulated to understand why the errors continued to persist for a minimum of 33 years. The resulting entity-relationship-based Microsoft Access database was determined to be a valuable tool for performing in-depth analysis. It can be used to add incorrect studies and perform further analysis. It is suggested that further research should be conducted to determine the extent of the impact of these errors on soil erosion, ecosystems, and the environment.

SQL#: A Language for Maintainable and Debuggable Database Queries

Structured Query Language (SQL) is the dominant language for managing relational databases. However, complex SQL queries are hard to write and maintain because of the intricate inter-table and inter-column relations. To this end, we propose a novel query language called SQL#, which allows programmers to construct complex queries module by module and explicitly specify the relations between different modules according to the logical steps of constructing queries. Besides, we design a SQL#-based system, aiming to facilitate the maintenance of SQL# queries. Specifically, the system renders a SQL# program into a hierarchical graph, which could help programmers understand the high-level structures of SQL# programs and the intricate relations between different components within SQL# programs. In addition, the system can ease the generation of the intermediate tables that correspond to the logical steps of constructing queries, which could help programmers debug complex SQL# queries. Notably, the design of SQL# makes it easy for the system to generate the hierarchical graph and the intermediate tables. Controlled experiments suggest that the SQL#-based system reduces the durations of writing and understanding database queries by 79% and 39%, respectively, compared to raw SQL code.

SQL: An Android Mobile Application for Effective Teaching of Basic Structured Query Language

Abstract: Structured Query Language (SQL) is a important query language for storing, manipulating and retrieving the data from database. So every individual needs the skill to access the data and analyze it. Since SQL is used while designing most of the softwares in the industry, students should have in-depth knowledge of firing the queries on the database. So the mobile app for the SQL is developed. This app contains SQL material, practice part to run the query, test, question bank and videos related to SQL topics. In this study, step by step process is given to access each part of this mobile app. The research question is “Whether an android mobile app for the course SQL is useful and helps students to understand this language by going through the notes & PPTs and practicing the SQL query through the practice section given in app?” Also the feedback is given to know the perception of students about this app. Keywords: SQL, Android Mobile Application, Quiz, Likert’s scale, Feedback

SQL Optimization in Oracle using Hybrid Genetic and Ant Colony Algorithm

In this paper, the input user Structured Query Language (SQL) query is converted into an optimized SQL query using a hybrid algorithm. The main aim is to reduce query execution time using PHP language and oracle database. These performance has been evaluated using different performance metrics: Cost of individuals, Query execution time. The hybrid algorithm method combines the evolutionary effect of the Genetic Algorithm (GA) and the cooperative effect of Ant Colony Optimization (ACO). A GA with a great global converging rate aims to produce an initial optimum for allocating initial pheromones of ACO. An ACO with great parallelism and effective feedback is then served to obtain the optimal solution. A fused algorithm of a GA and ACO to solve SQL optimization problems is an innovative solution that presents a clear methodological contribution to the optimization algorithm. In the simulation result, we found the algorithm of a GA and ACO to solve SQL optimization problems in Oracle. It is an innovative solution that presents a clear methodological contribution to the optimization algorithm.

Gamification Enhances Learning Experience of STEM Courses

Abstract: This work-in-progress is about teaching the students multi-tables in Structured Query Language (SQL) using an interactive application. There are various readily available text books, online courses and educational online forums. Although these resources may provide effective learning, they commonly miss the interactivity component, which can add an element of engagement and possibly fun user experience while learning. This proposed work focuses on the interactive and fun aspects in learning the concepts and principles of SQL queries. The project currently includes a high-fidelity prototype implementation. The aim of the prototype is to provide students with information on SQL related concepts through interactive elements that aim to make the learning experience more engaging, fun and appealing. A star based rewarding mechanism was implemented for increasing motivation. Daily-life based metaphors were included in the modules for making it easier for the users to understand the concepts and making the learning experience more enjoyable. For testing and comparison purposes, a traditional paper-based teaching method was also created with the same content. Informal user study sessions were held in a classroom setting with 16 users. Results indicated that the interactive prototype implementation provided more effective learning and a more fun user experience than the traditional method.

Empirical analysis of the impact of queries on watermarked relational databases

Robust relational database watermarking techniques aim to preserve the watermark in the protected assets despite daily updates and malicious attacks. Nevertheless, most techniques do not analyze the root of operations that can degrade the embedded watermark signal, being that the case of Structured Query Language (SQL) queries for relational data.In this paper, we perform an empirical analysis of the role played by different types of SQL queries in the persistence and degradation of a watermark signal in a relational data set. According to the type of operation, we present a set of alternatives for the watermark detection process, contributing to the extraction of an ownership copyright signals threshold. Moreover, our proposal allows data owners to consider variations for the selected watermarking technique, according to the suspected malicious operation performed in the copy of available data. Finally, we give evidence of the importance of some features to be considered when selecting a watermark source, making possible the resilience against update operations that affect a high amount of protected data and the enhancement of a partially detected watermark.

AI Model to Generate SQL Queries from Natural Language Instructions through Voice

Data plays the most important role in the development of industries, small businesses. Even world leaders need the data to make analyses and make better policies for people. In almost every field where the work process is digitized need to store data and then retrieve it. According to statistics most of the data is stored in the relational database and for manipulations of the data, Structured Query Language(SQL) is commonly used. So for handling databases a person need to have specialized knowledge regarding the queries and had to remember the syntax of many complex queries. So to enhance data manipulation using SQL and to efficiently get the required query, the paper proposes a method for the generation of SQL query from natural language input, spoken(audio input) by the user. The model is constructed on NLP (Natural Language Processing) and Neural Networks (Deep Learning) technologies. Long Short Term Memory(LSTM) Model is used for predicting queries and is trained on the dataset with natural language as input and returns outline skeletal structure of the query as output. Then the output will be processed and the final query will be displayed to the user. The project also aims to benefit the people who are suffering from Repetitive Stress Injury (RSI), causing pain in the finger joints, which has been attributed to work requiring a long period of typing and also to those who are not familiar with SQL queries. As this system will readily provide the required query.

An Approach to Aid Decision-Making by Solving Complex Optimization Problems Using SQL Queries

In combinatorial optimization, the more complex a problem is, the more challenging it becomes, usually causing most research to focus on creating solvers for larger cases. However, real-life situations also contain small-sized instances that deserve a researcher’s attention. For example, within a web development context, a developer might face small combinatorial optimization cases that fall in the following situations to solve them: (1) the development of an ad hoc specialized strategy is not justified; (2) the developer could lack the time, or skills, to create the solution; (3) the efficiency of naive brute force strategies might be compromised due to the programming paradigm use. Similar situations in this context, combined with a recent increasing interest in optimization information from databases, open a research area to develop easy-to-implement strategies that compete with those naive approaches and do not require specialized knowledge. Therefore, this work revises Structured Query Language (SQL) approaches and proposes new methods to tackle combinatorial optimization problems such as the Portfolio Selection Problem, Maximum Clique Problem, and Graph Coloring Problem. The performance of the resulting queries is compared against naive approaches; its potential to extend to other optimization problems is studied. The presented examples demonstrate the simplicity and versatility of using a SQL approach to solve small optimization problem instances.

Applying Text Mining and Natural Language Processing to Electronic Medical Records for extracting and transforming texts into structured data

The recording of patients' data in electronic patient records (EPRs) by healthcare providers is usually performed in free text fields, allowing different ways of describing that type of information (e.g., abbreviation, terminology, etc.). In scenarios like that, retrieving data from such source (text) by using SQL (Structured Query Language) queries becomes an unfeasible issue. Based on this fact, we present in this paper a tool for extracting comprehensible and standardized patients' data from unstructured data which applies Text Mining and Natural Language Processing techniques. Our main goal is to carry out an automatic process of extracting, clearing and structuring data obtained from EPRs belonging to pregnant patients from the Januario Cicco maternity hospital located in Natal - Brazil. 3,000 EPRs written in Portuguese from 2016 e 2020 were used in our comparison analysis between data manually retrieved by health professionals (e.g., doctors and nurses) and data retrieved by our tool. Moreover, we applied the Kruskal-Wallis statistical test in order to statically evaluate the obtained results between manual and automatic processes. Finally, the statistical results have showed that there was no statistical difference between the retrieval processes. In this sense, the final results were considerably promising.

Bayes Lines Tool (BLT): a SQL-script for analyzing diagnostic test results with an application to SARS-CoV-2-testing

The performance of diagnostic tests crucially depends on the disease prevalence, test sensitivity, and test specificity. However, these quantities are often not well known when tests are performed outside defined routine lab procedures which make the rating of the test results somewhat problematic. A current example is the mass testing taking place within the context of the world-wide SARS-CoV-2 crisis. Here, for the first time in history, laboratory test results have a dramatic impact on political decisions. Therefore, transparent, comprehensible, and reliable data is mandatory. It is in the nature of wet lab tests that their quality and outcome are influenced by multiple factors reducing their performance by handling procedures, underlying test protocols, and analytical reagents. These limitations in sensitivity and specificity have to be taken into account when calculating the real test results. As a resolution method, we have developed a Bayesian calculator, the Bayes Lines Tool (BLT), for analyzing disease prevalence, test sensitivity, test specificity, and, therefore, true positive, false positive, true negative, and false negative numbers from official test outcome reports. The calculator performs a simple SQL (Structured Query Language) query and can easily be implemented on any system supporting SQL. We provide an example of influenza test results from California, USA, as well as two examples of SARS-CoV-2 test results from official government reports from The Netherlands and Germany-Bavaria, to illustrate the possible parameter space of prevalence, sensitivity, and specificity consistent with the observed data. Finally, we discuss this tool’s multiple applications, including its putative importance for informing policy decisions.

Use of automatic SQL generation interface to enhance transparency and validity of health-data analysis

Analysis of health data typically requires development of queries using structured query language (SQL) by a data-analyst. As the SQL queries are manually created, they are prone to errors. In addition, accurate implementation of the queries depends on effective communication with clinical experts, that further makes the analysis error prone. As a potential resolution, we explore an alternative approach wherein a graphical interface that automatically generates the SQL queries is used to perform the analysis. The latter allows clinical experts to directly perform complex queries on the data, despite their unfamiliarity with SQL syntax. The interface provides an intuitive understanding of the query logic which makes the analysis transparent and comprehensible to the clinical study-staff, thereby enhancing the transparency and validity of the analysis. This study demonstrates the feasibility of using a user-friendly interface that automatically generate SQL for analysis of health data. It outlines challenges that will be useful for designing user-friendly tools to improve transparency and reproducibility of data analysis.

Neural Network Assisted Pathology Case Identification

BackgroundTraditionally, cases for cohort selection and quality assurance purposes are identified through structured query language (SQL) searches matching specific keywords. Recently, several neural network-based natural language processing (NLP) pipelines have emerged as an accurate alternative/complementary method for case retrieval. MethodsThe diagnosis section of 1000 pathology reports with the terms “colon” and “carcinoma” were retrieved from our laboratory information system through a SQL query. Each of the reports were labeled as either positive or negative, where cases are considered positive if the case was a primary adenocarcinoma of the colon. Negative cases comprised adenocarcinoma from other sites, metastatic adenocarcinomas, benign conditions, rectal cancers, and other cases that do not fit in the primary colonic adenocarcinoma category. The 1000 cases were randomly separated into training, validation, and holdout sets. A convolutional neural network (CNN) model built using Keras (a neural network library) was trained to identify positive cases, and the model was applied to the holdout set to predict the category for each case. ResultsThe CNN model classified 141 out of 149 primary colonic adenocarcinoma cases, and 43 out of 51 negative cases correctly, achieving an accuracy of 92% and area under the ROC curve (AUC) of 0.957. ConclusionTrained convolutional neural network models by itself, or as an adjunct to keyword and pattern-based text extraction methods may be used to search for pathology cases of interest with high accuracy.

XBLQPS: An Extended Bengali Language Query Processing System for e-Healthcare Domain

The digital India program encourages Indian citizens to become conversant with e-services which are primarily English language-based services. However, the vast majority of the Indian population is comfortable with vernacular languages like Bengali, Assamese, Hindi, etc. The rural villagers are not able to interact with the Relational Database Management system in their native language. Therefore, create a system that produces SQL queries from natural language queries in Bengali, containing ambiguous words. This paper proposes a Bengali Query Processor named Extended Bengali language Query Processing System (XBLQPS) to handle queries containing ambiguous words posted to a Healthcare Information database in the electronic domain. The Healthcare Information database contains doctor, hospital and department details in the Bengali language. The proposed system provides support for the Bengali-speaking Indian rural population to efficiently fetch required information from the database. The proposed system extracts the Bengali root word by removing the inflectional part and categorizing them to a specific part of speech (POS) using modified Bengali WordNet. The proposed system uses manually annotated parts of speech detection of a word based on Bengali WordNet. Patterns of noun phrases are generated to detect the correct noun phrase as well as entity and attribute(s). Entity and attributes are used to prepare the semantic table which is utilized to create the Structured Query Language (SQL). The simplified LESK method is utilized to resolve ambiguous Bengali phrases in this query processing system. The accuracy, precision, recall and F1 score of the system is measured as 70%, 74%, 73%, and 73% respectively.

A Natural Language Interface to Relational Databases Using an Online Analytic Processing Hypercube

Structured Query Language (SQL) is commonly used in Relational Database Management Systems (RDBMS) and is currently one of the most popular data definition and manipulation languages. Its core functionality is implemented, with only some minor variations, throughout all RDBMS products. It is an effective tool in the process of managing and querying data in relational databases. This paper describes a method to effectively automate the conversion of a data query from a Natural Language Query (NLQ) to Structured Query Language (SQL) with Online Analytical Processing (OLAP) cube data warehouse objects. To obtain or manipulate the data from relational databases, the user must be familiar with SQL and must also write an appropriate and valid SQL statement. However, users who are not familiar with SQL are unable to obtain relevant data through relational databases. To address this, we propose a Natural Language Processing (NLP) model to convert an NLQ into an SQL query. This allows novice users to obtain the required data without having to know any complicated SQL details. The model is also capable of handling complex queries using the OLAP cube technique, which allows data to be pre-calculated and stored in a multi-dimensional and ready-to-use format. A multi-dimensional cube (hypercube) is used to connect with the NLP interface, thereby eliminating long-running data queries and enabling self-service business intelligence. The study demonstrated how the use of hypercube technology helps to increase the system response speed and the ability to process very complex query sentences. The system achieved impressive performance in terms of NLP and the accuracy of generating different query sentences. Using OLAP hypercube technology, the study achieved distinguished results compared to previous studies in terms of the speed of the response of the model to NLQ analysis, the generation of complex SQL statements, and the dynamic display of the results. As a plan for future work, it is recommended to use infinite-dimension (n-D) cubes instead of 4-D cubes to enable ingesting as much data as possible in a single object and to facilitate the execution of query statements that may be too complex in query interfaces running in a data warehouse. The study demonstrated how the use of hypercube technology helps to increase system response speed and process very complex query sentences.

IMPROVING THE AUTOMATIC PRETANSLATOR OF SQL-QUERIES TO A REGULAR PLAN

We have created an automatic pretranslator prototype and proposing a new approach to improve the automatic pretranslator efficiency by parsing the original SQL (Structured Query Language) query and building a regular tree. The new approach is to create custom handlers for specific SQL constructs. The resulting handlers, instead of omitting some SQL constructs at the sorting and aggregation stage, allow them to be processed at the join stage. Joining operations, in contrast to sorting and aggregation operations, are performed in parallel on several nodes of the computing cluster, which significantly increases the productivity of processing a single query. You can additionally improve performance by reducing the amount of data transferred, due to a more accurate selection of the dimensions of DECIMAL fields and lowering the projection operations to the level of sorting and aggregation if lifting them increases the number of intermediate relation fields. A regular tree for a single request can be built in various ways. Finding the most effective regular plan pretranslator requires constructing many plans. A plan is selected from the set in which join operations are performed with the order of intermediate relations with sizes from smallest to largest. The experiments carried out have shown that the execution time of a stream of requests pretranslated by the pretranslator is not more than the execution time of a similar stream, pretranslated manually, which proves its effectiveness.