CIS-3010 Lab Assignment #4: PeakBaggers Queries

Due: Friday, February 17, 2023

Reading: Continue reading in Chapter 7 of the text. Join queries are described in Section 7.3.1.5. Be aware that the text mostly uses the "traditional" join syntax, although it does talk about the more modern syntax as well. You should try to practice with both syntax forms. The text discusses INSERT, DELETE, and UPDATE "queries" in Sections 7.3.2, 7.3.3, and 7.3.4.

In this lab assignment you will experiment with some queries against the PeakBaggers database you designed in the last assignment. Specifically, you will explore JOIN, INSERT, and UPDATE queries.

Populate your mountain table with information on some New England mountains, and information about some hypothetical climbers and their ascents. You may need to edit the script to provide appropriate attribute names depending on how you defined your schema. You don't need to submit any results for this part. Note that you may have done this part already in the previous lab.
Show a SELECT that displays the mountain names, state names (not abbreviations), and elevations. Display only the mountains in Vermont or New Hampshire that are over 3500 feet high. Use a JOIN to look up full state names based on state abbreviations. Use ORDER BY to sort the output in descending order of elevation (Washington in New Hampshire should be first on the list).
Show a SELECT that displays all the mountain names and locations (state, town) and the ascent dates done by Alice Atlas. Use a JOIN to look up mountain names based on mountain id values and a second JOIN to look up climber names based on climber id values. HINT: The join conditions just connect the tables. Use a WHERE clause to specify the climber's name.
Show the email addresses of all climbers who have climbed a mountain over 5000 feet, ordered by email address. The output should be just a list of email addresses with no other information. HINT: Use DISTINCT.
This query is a variation of the previous one. Include the name of the mountain in the output. Note: You should see multiple entries for cryptoqueen@example.com since, even with DISTINCT, different mountains create distinct (email, mountain) pairs. However, if Alice climbed the same mountain (at different times), DISTINCT would only show a single (email, mountain) pair representing that she climbed that mountain at least once.
Suppose Alice Atlas notes there is an error in the recorded value of her birthdate. It should be recorded as 2000-04-30. Show (and submit) an UPDATE statement that would fix the problem. In real life, this command would likely be issued by the backend of some website in response to Alice editing her profile information.

For this question, you can look up the appropriate record via Alice's name. If there were multiple Alice Atlas climbers, this would change the birthdate for all of them (probably not desirable). In real life, the application would have the climber's ID number as a result of the authentication process (when Alice logs into the site), and you would use that to uniquely modify her profile information.
Zachary Zephyr, with email address married@inahurricane.com, and with a birthdate of 2003-03-10, recently (February 2, 2023) climbed Mt. Coe in Maine. Show (and submit) the necessary INSERT statements to add this information to the database. Be sure you look up the mountain id and climber id information using sub-SELECTs in your INSERTs as appropriate (the provided ascent data shows how sub-SELECTs can be used).
You later find out that Zachary didn't really climb Mt. Coe after all. Show (and submit) the necessary DELETE statement to remove that bogus ascent from the ascent table. Use sub-SELECTs as appropriate to locate the proper record to delete (don't look up id numbers manually). You do not need to remove the record about Zachary himself, however.

Submit a text file with your various SQL statements. (20 points).