Week 3

 Week 3 (5/14-5/20)

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

In a database, a view is a virtual table based on the result set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.  You can join multiple tables in a view and use the View to present the data as if the data were coming from a single table.

Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

However, views are different from tables in several ways. A view does not store data itself; it only shows data from the underlying tables. Views do not have primary keys or indexes like regular tables do. Also, you cannot insert, update, or delete data through a view unless it is a simple view based on a single table without aggregates or joins. Even when updates are allowed, the changes affect the base table(s), not the view itself.

MySQL views/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. 

In my opinion, Java and SQL are very different; one is a programming language, and the other is a query language, both are different types of language. In other words, SQL is a language where you declare what you want, for example, to retrieve data; meanwhile, in Java, we have to specify how to do something step by step. However, they share a few features, like the use of arithmetic and logical operations, the WHERE in SQL, and the IF in Java support conditional logic, and the return statement in Java mimics the SELECT statement in SQL to define the output. Other differences include the loops (for, while), which only Java implements, Java uses classes and OOP, while SQL uses tables, rows, and columns. Also, error handling and syntax are handled differently, in SQL with sets and relations, and Java handles individual objects.