Exercise: Temp table with update statements

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the Wise Owl course listed below!

Category ==> SQL  (115 exercises)
Topic ==> Temporary tables and table variables  (10 exercises)
Level ==> Average difficulty
Course ==> Advanced SQL
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

If you haven't already done so, run the stored procedure in the above folder to generate a database of training courses and delegates.

Create a query to show a list of all of the people in the database in first name order, showing for each:

  • Their id and name
  • The number of course places they have booked in total
  • The number of SQL course places they have booked; and
  • The number of Visio course places they have booked

The final output of the query should be something like this:

SQL exercise - Temporary tables and table variables (image 1)

The first few rows of the final table produced by the query

There are many ways you can do this, but here's a suggestion (which is the method used by the answer):

  • Create a temporary table with the columns shown above
  • Use an INSERT INTO statement to fill it with all of the rows from the tblPerson table (at this stage the last 3 columns will be null)
  • Use an UPDATE statement to set the value of the NumberPlaces column
  • Use another UPDATE statement to set the value of the NumberSql column
  • Finally, use yet another UPDATE statement to set the value of the NumberVisio column

This method is without doubt not the fastest-executing one you could choose - but it is nice and easy to read!

Optionally, save this query as People facts.sql, then close it down.

This page has 0 threads Add post