meets Tuesday and Thursday from 0800-0915
office hours in Manning 112
Class Schedule
10 Jan | intro
15 Jan | clients
17 Jan | servers
22 Jan | networks
24 Jan | basics lab
29 Jan | structural layer
31 Jan | presentational layer
05 Feb | working with layers
07 Feb | behavior layer |
12 Feb | images & design
14 Feb | website lab
19 Feb | document markup
21 Feb | graphics
26 Feb | document markup lab
28 Feb | spreadsheets
05 Mar | formulas & functions
07 Mar | data display
19 Mar | database tools
21 Mar | spreadsheets lab
26 Mar | relational databases
28 Mar | tables
02 Apr | relationships
04 Apr | input & output
09 Apr |
SQL |
05.05 |
next session
11 Apr | complex queries
16 Apr | databases lab
18 Apr | presentation design
23 Apr | presentation delivery
25 Apr | presentation lab
30 Apr | 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
Create these queries using your Task 05.01 tables, as related in Task 05.02.
These do not have to be graded now.
Save each using the qryNN naming convention, with the actual question appended if you wish
For the queries in this task,
all sorts based on text are ascending
all sorts based on numbers are descending
Be sure to put a sort on every value that you use in a query, but pay attention to the order in which the query will sort the values.
A question, or a query, that you might hear asked
followed by some thoughts you should consider as you create the query
Could you get me a list of our publishers? And, oh yes, I need to know where they are located.
Show the names of all of the publishers, their states and/or their countries.
This query simply sorts three parts of the publisher table in the resulting dynaset, using the sort specified in the query.
We want to spread around our purchases.
If I tell you a state, can you tell me what publishers we deal with in that state?
This is a question about US publishers, so use a criteria on the country field to weed out any publishers who may not have a location in a US state and a parameter criteria on the state field to select the specific state
Show the names of all of the publishers who are in a certain state.
Display only the name, not the state or the country, because the parameter will be the state.
I wonder how many university-affiliated publishers we deal with.
Could you let me see a list of all of them, sorted by where they are?
Show name, city, state, and country fields in the publishers table for those publishers who have the word university as part of their name
I was just asked about that guy Prinz or Prince or Prens, but I can't remember how his name is spelled?
Since this request will occur again with other names,
could you make the query so that we can use it each time
we need to find any authors whose names start with the same letters?
Use a parameter query to show the names of all of those authors whose last names begin in the same fashion. This query can pull names that begin with any series of letters; they don't have to begin with PR.
We have books in different languages, but how many titles do we have in each language?
Let's start by finding out the books for which we have more than two copies,
but only the titles of the books that are in French.
We need to see the number of copies, the name of the book, and the name of the publisher.
Create a list of titles for which we have more than two copies in stock, but only show us those titles that are in French.
This is a touch more complex. You will need a single table for this one, but this time your criteria needs to return values that meet two different criteria
Are we spending too much money on cheap books?
We need a list of the titles (just the titles) of all the books whose retail price is less than $10,
but only those titles for which we possess more than 1 copy.
Sort the titles by the number we have in stock,
but make sure the titles are arranged alphabetically.
You need to query on a criteria that will not be displayed in the result - i.e., we don't need to see the price or the number of books
Oops, I got that wrong.
I meant to say that we want to know the titles of all the books designated as cheap
as well as the titles of all the books that we have more than one copy of,
whether they were cheap or expensive.
This time we need to see both the number and the titles, by number first and title second.
Show the names of all of those books that have a price below a certain amount OR that have the number of books in stock greater than a certain number. Show the titles and the number of books in stock.
This one is similar to query 06, but here you need to use two criteria with an OR operation. Pay attention to what needs to show in the resultant table.
My hearing is going
and sometimes I can't quite distinguish the difference in the pronunciation of words.
For example, I find it hard to differentiate between words
that begin with B from words that begin with P.
Thus, when someone asks about our books, I am not sure I understand which one they are asking for
so I need a list of both possibilities.
While you're at it, make it so that I can call up books that begin with either of any two letters.
Use a multi-part parameter query to do this one.
SQL | 05.05 | next session