Joe's CSUMB Computer Science Blog

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 is a saved select query and it is not its own table in the database. Table views allow users to view specific columns of data without being exposed to unnecessary information. Views also allow for complex queries to be saved. Issues can arise when primary key columns (if they exist) are not present in the view. If a user tries to insert a value directly into this kind of view, it would fail because values would not be allowed to be inserted into original tables with null primary keys. Similar issues can also occur when a user attempts to delete rows from a view that contain primary keys from other 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 ...

 SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).    Most of the time the join will use equality between a primary and foreign key.   Think of an example where joining on something other than keys would be needed.  Write the query both as an English sentence and in SQL.  If you can't think of your own example, search the textbook or internet for an example An example of joining 2 tables without a common column or matching key is using a cross join. A cross join creates a new table from all possible combinations of 2 existing tables. This may be practical for pricing computers based on its model and size of its screen. The query below would create a record that represents a possible combination of computer model and screen size  along with its given price. SELECT model, screen_size, computer.price + screen.price FROM computer CROSS JOIN screen; What is your opinion of SQL as a language?  Do you thin...

Relational database tables and spreadsheets look similar with both having rows and columns.  What are some important differences between the two? Functions within a relational database can ensure partially handled transactions do not cause inconsistencies in the database. Databases also have the ability to enforce relational and business rules that may be difficult to incorporate with excel.  With excel, a user could input records that violate these rules and break referential integrity. Also, spreadsheets in excel can not relate data to other spreadsheets like how  database tables can relate data with other tables with the use of foreign keys. Installing and configuration a database and learning how to use it is more complicated that just reading and writing data to a file.  What are some important reasons that makes a database a useful investment of time?  The features of a database prevent errors in database operations from causing inconsistencies in data. Th...