TRY IT YOURSELF

Put your grouping and aggregating skills to the test with these challenges:

$1.$ We saw that library visits have declined recently in most places. But what

is the pattern in library employment? All three library survey tables contain the column totstaff, which is the number of paid full$-$time equivalent

employees. Modify the code in Listings $9-13$ and $9-14$ to calculate the

percent change in the sum of the column over time, examining all states as

well as states with the most visitors. Watch out for negative values!

$2.$ The library survey tables contain a column called obereg, a two$-$digit

Bureau of Economic Analysis Code that classifies each library agency

according to a region of the United States, such as New England, Rocky

Mountains, and so on$.$ Just as we calculated the percent change in visits

grouped by state, do the same to group percent changes in visits by US

region using obereg. Consult the survey documentation to find the meaning

of each region code. For a bonus challenge, create a table with the obereg

code as the primary key and the region name as text, and join it to the

summary query to group by the region name rather than the code.

$3.$ Thinking back to the types of joins you learned in Chapter $7,$ which join

type will show you all the rows in all three tables, including those without

a match? Write such a query and add an IS NULL filter in a WHERE clause to

show agencies not included in one or more of the tables