Sql unit 6 views and temporary tables
This presentation is the property of its rightful owner.
Sponsored Links
1 / 74

SQL Unit 6 Views and Temporary Tables PowerPoint PPT Presentation


  • 52 Views
  • Uploaded on
  • Presentation posted in: General

SQL Unit 6 Views and Temporary Tables. Kirk Scott. 6.1 Introduction 6.2 Enabling Database Content in MS Access 6.3 Temporary Tables 6.4 Views 6.5 Inline Views 6.6 Details. 6.1 Introduction. It is possible to write relatively complex SQL queries.

Download Presentation

SQL Unit 6 Views and Temporary Tables

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 6 views and temporary tables

SQL Unit 6Views and Temporary Tables

Kirk Scott


Sql unit 6 views and temporary tables

  • 6.1 Introduction

  • 6.2 Enabling Database Content in MS Access

  • 6.3 Temporary Tables

  • 6.4 Views

  • 6.5 Inline Views

  • 6.6 Details


6 1 introduction

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


Sql unit 6 views and temporary tables

  • In previous units some examples have come up where something can't be done in Access SQL.

  • For example, this query won't work:

  • SELECT COUNT(DISTINCT stickerprice)

  • FROM Car


Sql unit 6 views and temporary tables

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


Sql unit 6 views and temporary tables

  • This would be a plan of action:

  • 1. Create a new table which would just hold 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.


Sql unit 6 views and temporary tables

  • Step two, entering the results of the first query into a table, is obviously impractical as given.

  • If you're going to enter the results of the query into the new table by hand, you might as well just count them by hand.

  • However, as you will see, there is SQL syntax for creating a table in mid-stream, using the results of a query


6 2 enabling database content in ms access

6.2 Enabling Database Content in MS Access


Sql unit 6 views and temporary tables

  • Using SQL it will be possible to create a new table out of the results of a query

  • If you try to run a query in MS Access that creates a table you may not have success.

  • The query will obviously not run, and the only indication of what's wrong will consist of a temporary message that appears at the bottom of the Access window:

  • "The action or event has been blocked by Disabled Mode."


Sql unit 6 views and temporary tables

  • The overheads in this section explain how to deal with this situation

  • You will not be tested on this stuff

  • It is just presented in case you run into problems when doing homework

  • If you forget the steps shown below, all you have to do is go to Help in the menu and search for "Disabled Mode" for an explanation.


Sql unit 6 views and temporary tables

  • The screen shot on the next overhead shows the “blocked by Disabled Mode” message as it appears at the bottom of the Access screen


Sql unit 6 views and temporary tables

  • It is possible that in your installation of MS Access the so-called Message Bar already appears

  • If so, you can just breeze through the following steps

  • If the Message Bar doesn’t appear, then the first step in dealing with the “blocked by Disabled Mode” problem is to make sure that the Message Bar appears on the screen.

  • To show the Message Bar, go to the Database Tools tab and check the Message Bar box.

  • See the screen shot below and find the Tools/Message Bar check box.


Sql unit 6 views and temporary tables

  • The screen shot below shows the window with the Message bar showing

  • It has a Security Warning which you would see after you’d tried to run a query in Disabled Mode.


Sql unit 6 views and temporary tables

  • The Message bar includes an Options button.

  • Once you’ve got the Options button showing, the next step is to click on it.

  • When you do so, you’ll get a Security Alert box with an option allowing you to “Enable this content”

  • You should select that option.


Sql unit 6 views and temporary tables

  • After selecting the option, you should click OK.

  • After taking all of these steps, it should be possible to run the query—but not necessarily immediately.

  • After clicking OK in the Security Alert, it may be necessary to close the query and then try to run it again before it will work.


6 3 temporary tables

6.3 Temporary Tables

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

  • Such a table is known as a temporary table.

  • The table is not really temporary, but it is different from the tables that exist in the original database design


Sql unit 6 views and temporary tables

  • Because of the interruption of describing how to deal with disabled content, you may have forgotten why it is convenient to be able to create a table from query results.

  • The goal is to divide and conquer, obtaining a complex query result by working with two parts.

  • The first part consists of making the temporary table.

  • The second part consists of writing a query that uses the temporary table.


Sql unit 6 views and temporary tables

  • This is the syntax that doesn’t work in Access:

  • SELECT COUNT(DISTINCT stickerprice)

  • FROM Car

  • The goal is to get the desired results by writing two queries that are related by the fact that the first creates a temporary table and the second uses it.


Sql unit 6 views and temporary tables

  • 1. The following query accomplishes the first part.

  • It selects the distinct stickerprices from the Car table, creating the temporary table Distinctpricetable in the process.

  • SELECT DISTINCT stickerprice INTO Distinctpricetable

  • FROM Car


Sql unit 6 views and temporary tables

  • 2. The following query accomplishes the second part.

  • It selects the count of the stickerprices in the temporary table, Distinctpricetable.

  • SELECT COUNT(stickerprice)

  • FROM Distinctpricetable


Sql unit 6 views and temporary tables

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

  • It will show up in Access under the table tab as a real live table containing data.

  • It will contain a snapshot based on the contents of the Car table at the time the query was run that created it.


Sql unit 6 views and temporary tables

  • Once Distinctpricetable has been created, subsequent changes to the Car table will have no effect its contents

  • On the other hand, if you run the query that creates Distinctpricetable again, you will wipe out whatever it previously contained

  • If changes to the Car table have been made in the meantime, then the new version of Distinctpricetable will reflect them


6 4 views

6.4 Views

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

  • The following query generates results containing the distinct stickerprices of the Car table

  • Notice that the results of the query are not saved as a temporary table.

  • SELECT DISTINCT stickerprice

  • FROM Car

  • Let the query itself be saved under the name Distinctpricequery.


Sql unit 6 views and temporary tables

  • 2. The saved query can play the role of a table in another query.

  • This query counts the number of stickerprices in the results of the saved query.

  • SELECT COUNT(stickerprice)

  • FROM Distinctpricequery


Sql unit 6 views and temporary tables

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

  • It appears after FROM, and is the thing that this query selects results from

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


Sql unit 6 views and temporary tables

  • 3. Views will now be explained in detail.

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


Sql unit 6 views and temporary tables

  • 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 idea will be illustrated with more examples.


Sql unit 6 views and temporary tables

  • The query below selects the 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


Sql unit 6 views and temporary tables

  • 4. It is the SQL code that is saved under the name Carmoneyview, not the results of the query.

  • This saved query can be used just like a table in another query.

  • SELECT vin, stickerprice

  • FROM Carmoneyview


Sql unit 6 views and temporary tables

  • 5. To emphasize the point that what you’re using is a saved query (not a temporary table), suppose that the query above was run.

  • Then the following new record is entered into the base table, Car.

  • 'qqqqq', 'Chevrolet', 'Impala', 2002, 8000.00, 7500


Sql unit 6 views and temporary tables

  • 6. Then suppose the same query which selects from the saved query is run again.

  • SELECT vin, stickerprice

  • FROM Carmoneyview

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


Sql unit 6 views and temporary tables

  • The results of the query 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, is run again

  • The results of the Carmoneyviewquery will always reflect the current state of the Car table, including any changes that have been made in it since the last time the query was run.


Sql unit 6 views and temporary tables

  • 7. Now consider changing the saved query, Carmoneyview, so that its results would be ordered.

  • SELECT vin, stickerprice, dealercost

  • FROM Car

  • ORDER BY stickerprice


Sql unit 6 views and temporary tables

  • 8. Then if you ran this query again, which uses the saved query like a table, its results would also be ordered.

  • SELECT vin, stickerprice

  • FROM Carmoneyview


Sql unit 6 views and temporary tables

  • 9. It may be convenient to rename the fields when defining a view.

  • Let Carmoneyview be redefined in this way using column aliases.

  • SELECT vin AS v, stickerprice AS sp, dealercost AS dc

  • FROM Car


Sql unit 6 views and temporary tables

  • 10. Then if you used the view in another query, you would have to use the new field names.

  • SELECT v, sp

  • FROM Carmoneyview


Sql unit 6 views and temporary tables

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


Sql unit 6 views and temporary tables

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


Sql unit 6 views and temporary tables

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

  • The SQL syntax for inserting, updating, and deleting will be covered in full in Unit 10.

  • However, a small example will be given here that uses that syntax and illustrates the power of a view as a handle on a table


Sql unit 6 views and temporary tables

  • This example has the effect of finding a specified record using the view and then changing one of the field values for it:

  • UPDATE Carmoneyview

  • SET sp = 5000

  • WHERE v = 'qqqqq'


Sql unit 6 views and temporary tables

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

  • SELECT vin, stickerprice

  • FROM Car

  • WHERE vin = 'qqqqq'


Sql unit 6 views and temporary tables

  • A view is always based on one or more real tables.

  • If you make changes to the fields selected in the view, the changes are actually made in the table that the view is based on.

  • The name of the view is just as valid a handle for referring to the base table as the name of the table itself is.


Sql unit 6 views and temporary tables

  • A student asked once whether this functionality of a view depends on its containing the primary key value

  • In theory it doesn’t, and in practice, in Access, it doesn’t

  • An example emphasizing this idea follows


Sql unit 6 views and temporary tables

  • The following could be saved as MyView:

  • SELECT stickerprice AS sp, dealercost AS dc

  • FROM Car


Sql unit 6 views and temporary tables

  • This query updates all of the relevant rows of the Car table even though the view doesn’t contain the primary key of the Car table:

  • UPDATE MyView

  • SET sp = 5000

  • WHERE dc = 16500


Sql unit 6 views and temporary tables

  • 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 saved subquery are generated again at run time based on the values in the table or tables contained in the saved query’s definition.


Sql unit 6 views and temporary tables

  • Temporary tables and views can both be used to divide and conquer a problem

  • In a sense, their uses in this regard are similar

  • However, there is a big difference between temporary tables and views.


Sql unit 6 views and temporary tables

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


Sql unit 6 views and temporary tables

  • Views are saved queries.

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

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


Sql unit 6 views and temporary tables

  • Every time the view is used, its results are generated or 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.


6 5 inline views

6.5 Inline 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


Sql unit 6 views and temporary tables

  • Then, as shown, it's possible to write this query:

  • SELECT vin, stickerprice

  • FROM Carmoneyview


Sql unit 6 views and temporary tables

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

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

  • SELECT vin, stickerprice

  • FROM

  • (SELECT vin, stickerprice, dealercost

  • FROM Car)


Sql unit 6 views and temporary tables

  • At run time, 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.

  • The inner, parenthesized part of the query is a subquery

  • This subquery is an inline view.


Sql unit 6 views and temporary tables

  • So when solving a problem by dividing and conquering, the first part of the solution doesn’t have to be saved as a separate query or view

  • All you have to do is remember it, and write the second part of the solution using it

  • The example query isn’t particularly useful, but it could literally be written and run as shown.


Sql unit 6 views and temporary tables

  • The inline view has another important characteristic that stems from the way views work.

  • The inline view, the complete, parenthesized subquery, 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.


Sql unit 6 views and temporary tables

  • 2. The problem of counting the distinct salesprices gives a more useful example.

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

  • SELECT DISTINCT stickerprice

  • FROM Car


Sql unit 6 views and temporary tables

  • Then it's possible to write this query:

  • SELECT COUNT(stickerprice)

  • FROM Distinctpricequery


Sql unit 6 views and temporary tables

  • This is literally what is executed at run time:

  • SELECT COUNT(stickerprice)

  • FROM

  • (SELECT DISTINCT stickerprice

  • FROM Car)

  • The outer query is free to make use of the field stickerprice, which is defined in the Car table and selected in the results of the inline view


Sql unit 6 views and temporary tables

  • 3. Nested queries in general are an important topic.

  • Here are a few more examples.

  • You would probably not write these queries using inline views, but they do illustrate in greater detail how inline views work.


Sql unit 6 views and temporary tables

  • Here is a query that accomplishes the same thing that could be done with a query using the keyword AND:

  • SELECT vin, stickerprice

  • FROM

  • (SELECT vin, stickerprice, dealercost

  • FROM Car

  • WHERE dealercost < 10000)

  • WHERE stickerprice > 10000


Sql unit 6 views and temporary tables

  • Here is a query that accomplishes the same thing that could be done with a non-nested join:

  • SELECT vin, stickerprice, salesprice

  • FROM Carsale,

  • (SELECT vin AS v, stickerprice, dealercost

  • FROM Car)

  • WHERE vin = v


Sql unit 6 views and temporary tables

  • In this query, the joining fields vin and v "cross" parenthesis boundaries.

  • Fields defined in the inner query can be used in the outer query.

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


Sql unit 6 views and temporary tables

  • It would also be possible to write the query this way:

  • SELECT Car.vin, stickerprice, salesprice

  • FROM Carsale,

  • (SELECT vin, stickerprice, dealercost

  • FROM Car)

  • WHERE Carsale.vin = Car.vin


6 6 details

6.6 Details

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


Sql unit 6 views and temporary tables

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

  • The original inline view query is shown below, followed by what you might see if you saved it and opened it again in the SQL view.


Sql unit 6 views and temporary tables

  • SELECT vin, stickerprice

  • FROM

  • (SELECT vin, stickerprice, dealercost

  • FROM Car)

  • SELECT vin, stickerprice

  • FROM (SELECT vin, stickerprice, dealercost

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


Sql unit 6 views and temporary tables

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

  • Unfortunately, it’s potentially unavoidable.


Sql unit 6 views and temporary tables

  • You may write an inline query and find that it doesn't produce the desired results.

  • If you saved it and reopen it to fix it, you will have a choice:

  • Try to deal with Access’s crazy changes to what you wrote, or try to write the correct query again from scratch.


The end

The End


  • Login