SQL Unit 5 Views and Temporary Tables. Kirk Scott. 5.1 Introduction 5.2 Temporary Tables 5.3 Views 5.4 Inline Views 5.5 Details. 5.1 Introduction. It is possible to write relatively complex SQL queries. Queries can include more than one table, have multiple conditions, etc.
In previous units some examples have come up where something can\'t be done in Access SQL.
It is perfectly reasonable to want to count the number of distinct values of the stickerprice field in the Car table, but this syntax isn\'t supported.
1. The following query accomplishes the first two steps explained earlier.
If you are using Microsoft Access 2007 and you try to run a query like this, it is quite likely that you will get a security warning which reads "Certain content in the database has been disabled."
It is important to understand that even though the intermediate table created by this process, Distinctpricetable, is known as a temporary table, it is persistent.
The data that Distinctpricetable contains will be a snapshot based on the contents of the Car table at the time the query was run that created it.
Distinctpricequery is playing the same role here as Distinctpricetable did in the previous approach.
The query below selects the vin and all of the monetary fields from the Car table.
4. It is the SQL code that is saved under the name Carmoneyview, not the results of the query.
The results of this query on Carmoneyview will be different from what they were before the insertion of the new record into Car.
7. Now consider changing the saved query, Carmoneyview, so that its results would be ordered.
9. It may be convenient to rename the fields when defining a view. Let Carmoneyview be redefined in this way.
10. Then if you used the view in another query, you would have to use the new field names.
11. It is important to understand that you can use a view in a query in any way that a table can be used.
12. As noted earlier, if you change the base table, the next time you make use of a saved query on that table, any changes in the base table will be reflected.
The SQL syntax for inserting, updating, and deleting will be covered in full in Unit 10.
13. If you then ran the following query on the Car table, you would discover that the stickerprice for the car with vin = \'qqqqq\' is now 5000.
14. A final way of describing what is happening with saved queries is that any results generated by a saved query are completely transient.
It is worth repeating that this is the difference between temporary tables and views.
The point is that this query, although not very useful, could literally be written and run as shown.
An inline view is a kind of subquery or nested query, but it differs from other queries in a very important way.
2. The problem of counting the distinct salesprices gives a more useful example.
Here is a query that accomplishes the same thing that could be done with a query using the keyword AND:
Here is a query that accomplishes the same thing that could be done with a non-nested join:
In this query, the joining fields vin and v "cross" parenthesis boundaries.
For example, table or field aliases may be set off with square brackets or quotes, dot notation may be used in unexpected places, and so on.
It doesn\'t make sense to try to master this complex (and needless) notation, but you shouldn\'t be surprised when you run into it.