SQL stands for “Structured Query Language”, and is a method
of communication with a database1. According to ANSI, SQL is the
typical method of communicating with relational database management systems. A
relational database management system, or RDBMS, is a database which allows you
to create and maintain information in a table format, where rows, columns, or
entries can have some relation or dependency on one another. Some more
well-known RDBMS are Oracle, IBM’s DB2, and Microsoft’s SQL server2.
First about the reason for SQL; originally, DBMS were used
primarily by programmers in order to maintain and create data. In order to
create a system which output some answer (for example, a total revenue or
production capacity), a programmer would have to develop new software that
often was not worth the trouble.
SQL is one of many “query languages” developed to solve this
problem. When a user is requesting something from a database, this is called a “query”.
As such, query languages were developed in order to allow the user to
efficiently request from the database in a customized manner. For SQL, database
management systems follow a particular process at the creation of a query. The system
must first break up the input statement, checking syntax, then validates the
statement, ensuring all relevant tables or entries exist. The system then
creates an access plan to retrieve the data, and optimizes this plan. Once
optimized, the system performs the access plan to retrieve the answer for the
request3.
This process is used on the data retrieval side of SQL. As
mentioned before, however, SQL is also used for the creation and updating of
databases. SQL has standard commands, such as “Select”, “Insert”, “Update”, “Delete”,
“Create”, and “Drop” that allow the user management for most purposes when
working with the database.
“Embedded SQL” is the first technique for sending SQL
statements to a DBMS. Here, SQL is used as a sublanguage, hosted by some other programming
language which contains variables or statements3. Furthermore,
embedded SQL can be broken up into static and dynamic SQL. Static SQL queries
consist of requests that do not change when the database is accessed4.
Conversely, dynamic SQL queries are a flexible form that allow for queries
where data access cannot be pre-determined. For example, if the database is
located elsewhere, then although the request can be entered, there is no way of
validating the statement, as mentioned in the process before5.
References
5 - https://msdn.microsoft.com/en-us/library/ms709342(v=vs.85).aspx
Comment to Dianna Vogel
Nice part about the importance of SQL. As mentioned, SQL is
relatively old, and compared to other programming languages, has been around
for quite some time. That being said, I don't see it disappearing any time
soon. SQL is an efficient and logical method for working with databases. Even
with newer programs, optimal languages for this form of work will likely always
have some basis from SQL; it's probably not going away unless databases do,
which certainly won't happen.
Comment to Bryan Cummings
I liked that you related this back to BIM. Take Revit for
example (since I know it fairly well). The amount of libraries and commands
that are likely based of something like this, or very similar, is huge. You
mentioned how most systems have their own proprietary languages built in to use
with SQL; I also discussed this a bit in my post, where systems use embedded
SQL.
Alex,
ReplyDeleteI like how you went into depth on how the queries work. The syntax is obviously important when coding a command for a database. If one comma, period, or letter is out of place the correct result will not come out of the query. This can make the process of querying complex and confusing to many people, which is why the user interface has been updated and reformatted in the programs built off of SQL. While these newer programs may be more user friendly, the importance of SQL has not faded. Without this initial language databases wouldn’t have made the progress they have made today.
Alex,
ReplyDeleteI have read Dianna's post as well in regards to the same topic and found it very interesting. I like how you elaborated on some of the information and also brought up some of the issues that were part of creating this syntax. I think that when studying a topic it is important to know some of the difficulties that were experienced in the creation.
Alex, great detailed summary on the workings of SQL. If I can piggy-back on Dianna’s comment, her statement referencing the necessity that the syntax be correct is crucial to notice. We all in this class (most of us, probably) have had experience writing a code of some sort in our university careers, most likely matlab. In matlab and similar software, if there is an error in our syntax, it will respond with something to the extent of “error on line 12, argument undefined.” Telling you where the error is and the most likely way to fix it. SQL and other types of base codes do not offer that luxury, and as stated, is one of the reasons newer additions have been made to the base SQL at this point
ReplyDelete