Week 3

Welcome back!!

Querying the CAP Database on our postgres server:

  • with thanks to Alan Labouseur for permission to use his materials! :-)
  • CAP database creation script

Class exercise:
Use CAP to answer all of these query questions. Write only one query per question. Be certain to end each query with a semi-colon. Your final script should execute all of the queries in sequence from one command. Remember that CAP is one snapshot in time in the life of that database. The queries you write should return the correct answer for all time, not just for this snapshot.

  1. List the order number and total dollars of all orders.
  2. List the last name and home city of people whose prefix is “Dr.”.
  3. List id, name, and price of products with quantity more than 1007.
  4. List the first name and home city of people born in the 1950s.
  5. List the prefix and last name of people who are not “Mr.”.
  6. List all fields for products in neither Dallas nor Duluth that cost US$3 or more.
  7. List all fields for orders in March.
  8. List all fields for orders in February of US$20,000 or more.
  9. List all orders from the customer whose id is 007.
  10. List all orders from the customer whose id is 005.

Solution: cap-queries.sql (the queries we worked on together in class)

Reading assignment for next week:

  • zyBook Chapter 4: Structured Query Language (we'll take this one out of order and come back to Chapter 3 after)
  • → reading a zyBook works better when you do the activities (active reading)

HW for next week: