Class Schedule
22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab
12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab
03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab
15 Oct | spreadsheets
17 Oct | formulas & functions
22 Oct | thoughts about data display
18 Oct | Fall Break
24 Oct | database tools
29 Oct | spreadsheets lab
31 Oct | relational databases
05 Nov | tables
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries |
05.06 |
next session
26 Nov | databases lab
21 Nov | Thanksgiving
28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
home & schedule | class blog | syllabus | contact | grades
Build some queries that merge data from several tables
and that mathematically manipulate the data in tables.
Hey, what states are our publishers in and how many publishers are in each state?
I don't need the names of the publishers; I just need the number in each state.
But I do need to see them sorted by the number in each state with the largest number first.
Now you start to add the use of functions to manipulate the results
The fire insurance people need to know how much our book stocks are worth.
We need a list of the replacement costs for each our books.
They don't need to know the individual cost or the number of copies we have,
but they do want to see the replacement costs from largest to smallest,
to include the titles of the books that will need replacing.
For each book, show:
More functions, but instead of using a function on the results of a select query, you now you have to use the Build tool to create a new value that doesn't exist in the table as it stands
What and how many books do we have on order
and how much of a break are we getting on the price?
I need to know how much of a price break we get on each book
and total savings we are getting on each order.
Show:
This will require
I need to rattle some publisher cages about slow deliveries.
Get me a list of the publishers as well as their cities and states (or countries).
Sort it by publishers because I also want to see how many of each title we have ordered from each one
and how much each order will cost if we have to buy it locally at full retail price.
This calls for values from all orders, whether already delivered or not. Show:
You will need the number of books ordered, but not in the results of the query
For no particular reason, of the books in our collection,
I would like to know what authors are published by what publishers
and how many titles each author has by that publisher.
Make it so I can do it by individual publisher.
Include in the query design:
OK, this is the last request.
Our investments have lost value and this has cut into our budget.
We need to know where we can save some money fast.
Get a list of all our outstanding orders, ones that we have sent to the publishers,
but that the publishers have not yet filled.
We need to see how much money we have allocated for these orders so we can cancel them if we have to.
I promise, this is the last one.
This query could be the source of a report, which would present a formatted paper copy of the result of this query.
For each distinct order that has not been received yet, show only the following fields:
This calls for a select across multiple tables, newly built fields of new data, application of functions, and use of the NULL criteria. This is a request for a list of things that have not yet happened, not a list of things that have happened already.