Sql unit 5 views and temporary tables
Download
1 / 53

SQL Unit 5 Views and Temporary Tables - PowerPoint PPT Presentation


  • 78 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Unit 5 Views and Temporary Tables' - meg


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql unit 5 views and temporary tables

SQL Unit 5Views and Temporary Tables

Kirk Scott



5 1 introduction
5.1 Introduction

  • It is possible to write relatively complex SQL queries.

  • Queries can include more than one table, have multiple conditions, etc.

  • When queries become complex, people don't just sit down and write them from beginning to end.

  • They break the problem into pieces, try to solve the pieces, and then put the solutions together into a whole.



  • 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.

  • This obviously has two parts, count, and distinct, and it is reasonable to think that you could get the solution by doing it in steps.

  • This would be a plan of action:


  • 1. Create a new table which would just hold distinct values of the stickerprice values.

  • 2. Write a query to find the distinct stickerprices and enter them into the table.

  • 3. Write a query that would count the number of stickerprices in the new table.



5 2 temporary tables
5.2 Temporary Tables distinct values of the

  • Fortunately there is syntax which makes it possible to create a table directly from query results.

  • This is known as a temporary table.

  • This makes it possible to accomplish the result explained above in two steps instead of three.



  • If explained earlier.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."

  • Next to the warning there should be a box containing the word "Options…".

  • If you want to run the query, click on the box and then select the "Enable this content" option.




  • The data that intermediate table created by this process, Distinctpricetable contains will be a snapshot based on the contents of the Car table at the time the query was run that created it.

  • Subsequent changes to the Car table will have no effect on the contents of the Distinctpricetable unless you run the query that created it again, destroying the original Distinctpricetable and replacing it with a new version based on whatever is currently in Car.


5 3 views
5.3 Views intermediate table created by this process,

  • 1. The topic of views will be introduced by solving the problem of the previous example in yet another way.

  • The following query creates a result table containing the distinct stickerprices of the Car table.

  • SELECT DISTINCT stickerprice

  • FROM Car

  • Let the query be saved under the name Distinctpricequery.



  • Distinctpricequery query. is playing the same role here as Distinctpricetable did in the previous approach.

  • It is important to understand that although the results are the same, the two approaches work differently.


  • 3. Views will now be explained in detail. query.

  • In SQL the term "view" is used to describe defining a query and saving that query for future use.

  • The SQL syntax, not surprisingly, starts with "CREATE VIEW".

  • Because Microsoft Access supports the naming and saving of queries directly, there is no need to use the SQL syntax.

  • A view is by definition a saved query.


  • The query below selects the query. vin and all of the monetary fields from the Car table.

  • Let the query be saved under the name Carmoneyview.

  • SELECT vin, stickerprice, dealercost

  • FROM Car





  • The results of this query on again.Carmoneyview will be different from what they were before the insertion of the new record into Car.

  • They will include a record with the values 'qqqqq' and 8000.00.

  • The results change because every time this query is run, the underlying saved query, Carmoneyview,

  • The results of that query will reflect any changes that have been made in the base table, Car.






  • 11. It is important to understand that you can use a view in a query in any way that a table can be used.

  • Here is a join query that uses the view Carmoneyview.

  • SELECT v, sp, salesprice

  • FROM Carmoneyview, Carsale

  • WHERE v = vin

  • Notice that the names of the fields in the joining condition don't have to be qualified with table names because vin was given the alias v in Carmoneyview.


  • 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.

  • This process also goes in the other direction.

  • If you insert, update, or delete records in the view, these changes go directly to the base table.

  • The base table name and the view name are equally valid handles for doing anything that it's possible to do in SQL, including changing the contents of a table.




  • 14. A final way of describing what is happening with saved queries is that any results generated by a saved query are completely transient.

  • The query is saved, but the results are not saved.

  • Every time a query is run which uses a saved query in the role of a table, the results of the subquery are generated again based on the values in the tables at run time.


  • It is worth repeating that this is the difference between temporary tables and views.

  • Temporary tables are misnamed.

  • They aren’t temporary, they’re persistent.

  • They are also static.

  • Once they’ve been created, changes to the tables that they were based on don’t affect the contents of the temporary table.


  • Views are saved queries. temporary tables and views.

  • What is saved is the SQL syntax, not the results of running it.

  • The saved syntax is persistent, but the results are not.

  • Every time the view is used, its results are materialized again.

  • This means that views are dynamic.

  • Changes to the tables that they were based on are reflected in the results every time a view is used.


5 4 inline views
5.4 Inline Views temporary tables and views.

  • 1. The term "inline view" refers to the idea that any query that makes use of a view could be rewritten with the view simply appearing as a parenthesized, nested subquery in the overall query.

  • As above, let this query be saved as Carmoneyview:

  • SELECT vin, stickerprice, dealercost

  • FROM Car



  • The query shown below is literally what is executed at run time.

  • It is also a perfectly legitimate query in its own right.

  • In other words, you can write one query to solve the problem rather than creating a view, saving it, and then writing another query based on it.

  • SELECT vin, stickerprice

  • FROM

  • (SELECT vin, stickerprice, dealercost

  • FROM Car)


  • The second, parenthesized part of the query is an inline view.

  • That view is materialized at run time.

  • In other words, just like with other nested queries, what happens is that the inner query inside the parentheses is run first, bringing its results into existence in real time, and then the outer query operates on those results to produce the final results.



  • An inline view is a kind of could literally be written and run as shown. subquery or nested query, but it differs from other queries in a very important way.

  • The inline view is playing the role of a table in the FROM clause of the outer query.

  • Even though the inline view is contained within parentheses, its fields are available for use in the outer query.


  • 2. The problem of counting the distinct could literally be written and run as shown. salesprices gives a more useful example.

  • As shown earlier, let this query be saved as Distinctpricequery:

  • SELECT DISTINCT stickerprice

  • FROM Car



  • This is literally what is executed at run time: could literally be written and run as shown.

  • SELECT COUNT(stickerprice)

  • FROM

  • (SELECT DISTINCT stickerprice

  • FROM Car)

  • The point is that this nested query could be used exactly as shown, without making use of an intermediate saved query.





  • In this query, the joining fields be done with a non-nested join:vin and v "cross" parenthesis boundaries.

  • It's convenient to give vin the alias v in the nested query.

  • This makes it possible to SELECT vin and write the joining condition in the WHERE clause as vin = v without having to qualify vin with the respective tables it appears in.



5 5 details
5.5 Details be done with a non-nested join:

  • Inline views are one of those cases where the Microsoft Access design view can become inconvenient.

  • In all of the examples in this set of notes, the simplest possible syntax was shown.

  • Unfortunately, if you save the query and then go back later to edit it, it will frequently look different from the way you typed it in.



  • SELECT square brackets or quotes, dot notation may be used in unexpected places, and so on. vin, stickerprice

  • FROM

  • (SELECT vin, stickerprice, dealercost

  • FROM Car)

  • SELECT vin, stickerprice

  • FROM (SELECT vin, stickerprice, dealercost

  • FROM Car) AS [%$##@_Alias];




The end
The End choice:


ad