Week 3 - SQL Reflections

What is an SQL view.  How is it similar to a table? In what ways is it different (think about primary keys,  insert, update, delete operations) ?

A view in SQL acts as a fixed and somewhat imaginary table that limits operations done on data and is mostly intended for database users who are not changing data but instead simply accessing it. A view is created with a select statement that will define what can be viewed from the view. Because a view is driven by a pre-defined separate query, it can encapsulate a query that will be reused elsewhere in more complex searches.

A few immediate differences between the two are the lack of direct primary and foreign keys when compared to a table. The table is holding the constraints and indexes while the view simply reveals what the underlying table has already established. As the name suggests, a view simply is a view and does not store data the way a table does. Because of these former two facts, insert/update/delete statements are generally not recommended or supported for views. A view does not contain the actual data used to create the view, thus making changes to the view can adversely impact underlying tables. 

We have completed our study of SQL for this course.  This is not to imply that we have studied everything in the language.  There are many specialized features such as calculating rolling averages, query of spatial data (data with latitude and longitude) coordinates, and more. But take a minute to think about how SQL compares to other programming languages such as Java.  What features are similar , and which are present in one language but not in the other?  For example,  Java has conditional if statements which are similar to SQL WHERE predicates,  the SELECT clause is similar to a RETURN statement in that it specifies what data or expression values are to be returned in the query result (although it is strange that a statement should specify the RETURN as the first part of a SELECT. 

Despite SQL being mostly a querying language and Java being an all-purpose language, they do share a few key similarities that I appreciate. While the clauses and keywords are almost entirely distinct from each other, the conditional logic of both languages is relatively common sense and commensurate. Returning a value in Java is not wholly unlike returning results from a query in SQL, both rely on a series of operations and responsible handling of data/values. Both languages use string and integer functions like substring and arithmetic operators, as well as similar keywords for data types. 

The aforementioned traits are about where the similarities end as SQL does not have control of code flow the way Java does--there are no for/while loops, no switch, and no if statements. SQL can perform operations on entire sets of data whereas Java tends to handle one component of the data (AKA object or element) at a time. Java inherently manages memory and can change data while returning the results whereas SQL queries run independently of each other and do not work together the way Java methods can. While SQL can store data and "functions" in the form of views and nested queries, Java can inherit/implement/extend other classes or interfaces in a much more transformable fashion than SQL.