Week 3 - Study of SQL Completion

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) ?

An SQL view is a virtual table or a temporary table where the contents such as its rows and columns are defined by a SELECT query. Similar to a table the view contains rows and columns, and the contents of the view are from real tables within a database. In a sense an SQL view is a query that acts as a temporary regular table. As said previously the view acts as a table and you can query from it like a regular table. Although the nuance of it is that they don't store the data but rather present results from the SELECT query of an already existing database. View tables don't really have primary keys but they do have uniqueness in columns, views cannot have inserted data directly compared to normal tables, updating requires reference to one base table without aggregations and groupings, and deleting needs requirements of what rows in the base table to delete. The positive notes of a view is that they protect sensitive data by excluding access to sensitive columns in a normal table by displaying other columns and complex select statements can be saved as a view. Overall they act similarly as a normal table but without the data storage and data manipulation.

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. 

As we delve through both the SQL language in this course and the intricacies of understanding how database queries work through the Java labs there are some underlying similarities between the two languages. In one case Java conditional if statements are quite similar to the WHERE predicates. Both of these similar cases act as a way to define conditions and filter control. Another similar case is the way the SQL statements operate in the same way methods are in Java, where they act as procedural and are reusable such as creating certain SELECT statements for the condition of say a WHERE. Both languages utilize the same data types, order of precedence for their operations and the use of specific keywords for declaration. In SQL you can define a column name with the AS statement similar to Java's declaration of a field/variable. Another case is the way both languages handle strings by retrieving the sub-string, length, and other operations. Finally I would like to note the way they both manipulate data, in the java labs we utilize certain data structures to mimic the ways SQL manipulates their data. In the case of the SELECT statements compared to the RETURN statements of java, SQL acts as a more declarative language whereas java requires step by step instructions. Overall they may seem to have similarities but they function very differently as SQL works best to manage relational databases and its queries while java is more for general purpose programming to write a variety of software and not only involve databases.