- Simple Queries (4)
- Setting criteria using WHERE (5)
- Calculations (7)
- Calculations using dates (4)
- Basic joins (8)
- More exotic joins (2)
- Aggregation and grouping (8)
- Views (5)
- Subqueries (5)
- Stored procedures (5)
- Variables (8)
- Parameters and return values (11)
- Testing conditions (1)
- Looping (3)
- Scalar functions (6)
- Transactions (5)
- Creating tables (5)
- Temporary tables and table variables (9)
- Table-valued functions (6)
- Derived tables and CTEs (13)
- Dynamic SQL (4)
- Pivots (2)
- Triggers (2)
- Archived (70)
SQL | Archived 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.
You can learn how to do this exercise on the relevant Wise Owl classroom training course (sadly for the moment only in the UK).
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:
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.