Studying the characteristics of SQL-related development tasks: An empirical study

6 - The Basics of Efficient SQL

chapter 7 - The basics of sql query code tuning

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.

University of Utah Health (UUH) is an academic medical center that achieved "committed to care excellence" in age-friendly care in 2021 and has a long-standing culture of quality improvement central to a learning health system. University of California San Francisco (UCSF) developed electronic health record (EHR) documentation metrics for inpatient assessment of the 4Ms (What Matters, Medication, Mentation, and Mobility) based on the Institute for Healthcare Improvement's recommended care practice for an Age-Friendly Healthcare System. In partnership with UCSF, we replicated the assessment and action EHR metrics with local adaptations for each of the 4Ms at UUH. The UCSF team shared 4Ms documentation metrics and Structured Query Language code used to assess 4Ms care at UCSF. At UUH, this code was adapted for a different relational database management system and local clinical context. We assessed 4Ms care, individual M, and composite measures of all 4Ms, for all patients aged 65 and older admitted to UU Hospital between January 1, 2019 and December 31, 2021. We conducted a clinical validation of individual patient cases to confirm accuracy of 4Ms queries. In the 3-year study period, 16,489 qualifying patients, mean age 74.2, were admitted to UU Hospital in a total of 25,070 admissions with mean length of stay of 6.08 days. We were able to replicate 14 of the 16 EHR metrics of individual 4Ms developed at UCSF and five composite measures. For the composite measure addressing completeness of 4Ms care, 50% of patient encounters had all 4Ms administered during their encounter. Indicators of the completeness of 4Ms care can be measured using EHR data to validate implementation of the 4Ms at multiple academic medical centers. Key lessons to support future scaled-up assessments include the importance of adapting EHR measures to local activities and involving expert data analysts.

A computational phenotype for difficult-to-treat asthma with corticosteroid overuse using electronic health care records.

: The purpose of IDA Memorandum Report M-459, An Oracle - Ada/SQL implementation, is to identify and describe a version of a software delivery, an Oracle - Ada/SQL (Structured Query Language) implementation, to the WIS Joint Program Management Office. The software system provides a Level 1 Ada/SQL implementation which interacts with the Oracle database management system (DBMS) . It provides the ability to define Ada/SQL Data Definition Language (DDL) and Ada/SQL Data Manipulation Language (DML) which will be converted to the appropriate SQL calls required by the Oracle DBMS. Ada/SQL is a binding between Ada and the database programming language SQL. The software was developed on a VAX 8600, using VAX/VMS version 4.6, and the DEC Ada compiler, version 1.4-33.

Common design patterns in application development mix implementation of business logic with application logic, user interface logic, or with data access logic. This leads to results that business logic is not well maintainable and reusable, the application code should be reimplemented each time the business logic has changed or the same business logic should be reimplemented for each application. This paper presents a new rule-based approach to Web-based IS development making possible separating the business logic from interface or application logic when creating complex Web-based software applications. The technology consists of 2 main parts: the application server Xstone for creating 3-layered systems and the RqlGandalf rule solver. The middleware server Xstone connects to Oracle, PostgreSQL databases and the Rql rule system. The Rql rule system is targeted for defining and using business logic rules. The Rql rule system is able to read data from a SQL database: it can call the application server and import/convert SQL-given data into its own knowledge base. The presented rule-based system development technology is implemented for the Linux platform as open source software.

This paper discusses a practical approach to the use of Commercial-Off-the-Shelf (COTS) tools to implement Automatic Test Equipment (ATE) and Unit Under Test (UUT) signal-based models to support both new Test Program Set (TPS) development and TPS rehost activities. This modeling technique also provides a standard to which existing and future ATEs and UUTs can be modeled and used for analysis and comparisons. The signal-based model is implemented via a Structured Query Language (SQL) Relational Data Base Management System (RDBMS). COTS Rapid Application Development (RAD) tools, such as Microsoft/sup (R)/ Visual Basic/sup (R)/ Microsoft Access/sup (R)/ and PowerBuilder/sup (R)/, are employed to implement interfaces to the SQL database model and to provide the necessary algorithms for data comparison, model reuse and other manipulations of the model data. After models are established for both the ATE and UUTs, ATU/UUT compatibility analysis can be performed to identify any potential incompatibilities. This information can be used to optimize the selection of a target ATE for a selected group of UUTs. It can also be used to identify any signal deficiencies in the target ATE that require ATE enhancements or Interface Test Adapter (ITA) signal conditioning, to ensure UUT compatibility.

The database and the database management system (DBMS) are two of the main components of any information system. Structured Query Language (SQL) is the most popular query language for retrieving data from the database, as well as for many other data management tasks. During system development and maintenance, software developers use a considerable amount of time to interpret compiler error messages. The quality of these error messages has been demonstrated to affect software development effectiveness, and correctly formulating queries and fixing them when needed is an important task for many software developers. In this study, we set out to investigate how participants (N=152) experienced the qualities of error messages of four popular DBMSs in terms of error message effectiveness, perceived usefulness for finding and fixing errors, and error recovery confidence. Our results show differences between the DBMSs by three of the four metrics, and indicate a discrepancy between objective effectiveness and subjective usefulness. The results suggest that although error messages have perceived differences in terms of usefulness for finding and fixing errors, these differences may not necessarily result in differences in query fixing success rates.

Companies develop their software in versions and iterations. Ensuring the security of each additional version using code review is costly and time consuming. This paper investigates automated tracing of the impacts of code changes on the security of a given software. To this end, we use call graphs to model the software code, and security assurance cases to model the security requirements of the software. Then we relate assurance case elements to code through the entry point methods of the software, creating a map of monitored security functions. This mapping allows to evaluate the security requirements that are affected by code changes. The approach is implemented in a set of tools and evaluated using three open-source ERP/E-commerce software applications. The limited evaluation showed that the approach is effective in identifying the impacts of code changes on the security of the software. The approach promises to considerably reduce the security assessment time of the subsequent releases and iterations of software, keeping the initial security state throughout the software lifetime.

Early prediction for merged vs abandoned code changes in modern code reviews

Chapter 9 - Software engineering

Database-backed applications form the backbone of modern software, yet their complexity poses significant challenges for static analysis. These applications involve intricate interactions among application code, diverse database frameworks such as JDBC, Hibernate, and Spring Data JPA, and languages like Java and SQL. In this paper, we introduce DBridge, the first pointer analysis specifically designed for Java database-backed applications, capable of statically constructing comprehensive Java-to-database value flows. DBridge unifies application code analysis, database access specification modeling, SQL analysis, and database abstraction within a single pointer analysis framework, capturing interactions across a wide range of database access APIs and frameworks. Additionally, we present DB-Micro, a new micro-benchmark suite with 824 test cases crafted to systematically evaluate static analysis for database-backed applications. Experiments on DB-Micro and large, complex, real-world applications demonstrate DBridge's effectiveness, achieving high recall and precision in building Java-to-database value flows efficiently and outperforming state-of-the-art tools in SQL statement identification. To further validate DBridge's utility, we develop three client analyses for security and program understanding. Evaluation on these real-world applications reveals 30 Stored XSS attack vulnerabilities and 3 horizontal broken access control vulnerabilities, all previously undiscovered and real, as well as a high detection rate in impact analysis for schema changes. By open-sourcing DBridge (14K LoC) and DB-Micro (22K LoC), we seek to help advance static analysis for modern database-backed applications in the future.

Information retrieval system is a system that is widely used to retrieve information. This research will discuss how the system finds back the information stored in database tables. Tables in the database are arranged to store all forms of data entered by database users so that later the stored data can be used again. Re-accessing the database's information must go through a mechanism known as a database management system (DBMS). One of the most widely used DBMS is MySQL. By using a DBMS, information and data can be manipulated according to user needs. Data manipulation in the database is done in a special language, namely SQL (Structure Query Language). Mastery of SQL commands is an obligation for database users so that the manipulated data can produce the required information. However, many database users still do not understand how the actual SQL command syntax manages and manipulates data into information. This is, of course, very risky if the solution is not immediately sought because it will hinder the process of retrieving information from the data stored in the database. For this problem to be resolved, it is necessary to design a system that can help database users translate their wishes into SQL command syntax. This paper will discuss how a command in Indonesian can be translated into SQL command syntax. The method used to solve this research problem is rule-based. There are two stages in the main process: the pre-processing stage, which consists of a word tokenization process, and a translation stage, including a keyword grouping process. This keyword grouping process consists of the keyword group analysis phase, table and column analysis, identification of SQL commands, and mapping of SQL commands. From all stages that have been passed and testing of 7 scenarios with ten (10) commands for each scenario, the accuracy is 81.42%. The inaccuracy in the testing process is more a problem of displaying data from two or more tables, for example, using the join table command. This problem can be addressed by adding new rules for the use of table joins.

In the realm of data analytics, Power BI and SQL serve as essential tools for businesses aiming to derive actionable insights from their data. However, as datasets grow in complexity and volume, the efficiency of queries and the speed of data loading become critical factors affecting overall performance. This paper explores advanced performance tuning techniques for both Power BI and SQL, focusing on enhancing query efficiency and minimizing data load times. We investigate key strategies, including the optimization of data models, effective use of DAX (Data Analysis Expressions) functions, and the implementation of indexing and partitioning in SQL databases. Additionally, we analyze the impact of data source configurations and the importance of using best practices in report design to improve user experience. By conducting empirical tests and case studies, we illustrate how these tuning methods can lead to significant improvements in report rendering times and user interactions. Ultimately, our findings emphasize the need for a holistic approach to performance tuning, combining technical optimizations with best practices in data management. This research provides valuable insights for data professionals seeking to enhance the efficiency of their Power BI reports and SQL queries, ensuring that organizations can make data-driven decisions swiftly and effectively. The outcomes of this study not only contribute to the field of business intelligence but also pave the way for future research on optimizing analytics platforms.