In week 3 of our Improving the Daily Life of a Database Developer blog series, we detail how to create high quality SQL code that is easy to edit. If you missed it, you can read the previous post on Navigating the Database.
Creating, editing, and running SQL is at the heart of almost all database development and administration tasks. Even with variations across databases, accurate SQL requires proper syntax that the database can execute efficiently and reliable formatting that makes the code easy to follow and edit. Sophisticated database development tools include not only a SQL editor interface for editing SQL statements and queries but also a GUI-based, drag-and-drop, visual query interface.
Commands and syntax vary among procedural layers, or dialects, of SQL. To address development challenges that users may encounter in working with multiple DBMSes, the ideal SQL code editor abstracts those variations and includes added functions helpful in quickly creating efficient code from a single UI:
- Autocompletion through a pop-up list of database objects in the selected database
- Separate tabs in the same UI for editing SQL and scripts in multiple databases/platforms simultaneously
- Efficient, consistent formatting of keywords, text alignment, indentation, operators, and punctuation marks
- Abbreviations for inserting user-defined text, from a few words to entire blocks of code
- Automation of frequently used statements such as SELECT, DELETE, and INSERT
- Immediate execution of queries, with the results displayed in the same window
A GUI-based, drag-and-drop, visual query builder may be better suited to the needs of citizen developers, line-of-business users who are less technical but who still need to construct and run queries to perform their job. When it is included in the same tool as the SQL editor, so much the better. This offers a familiar starting point for some users working with SQL and queries, and it is often found in a database developer IDE.
By dragging and dropping tables, views, and columns into a workspace, users unfamiliar with creating SQL from scratch (or with the differences in syntax among dialects of SQL) can use the GUI-based tool to generate SQL statements for basic functions such as:
- Selecting columns for output
- Adding JOINs and UNIONs
- Filtering, grouping, and sorting records with WHERE, GROUP BY, HAVING, and ORDER BY clauses
- Displaying results
- Saving queries for subsequent reuse and sharing
The IDE approach offers a uniform, consistent UI for the most common dialects of SQL and meets the needs of novice users as well as experts. Even on an unfamiliar DBMS, database developers can focus on the data sets they are trying to extract, rather than on the syntax, punctuation, and formatting of SQL statements.
For more information, visit https://www.aquafold.com/aquadatastudio or click here to download the full whitepaper.