CPSC340A
Database Systems

Activity 3

Where

Sales Orders Database

  1. Give me the names of all vendors based in Ballard, Bellevue, and Redmond.

    +------------------------+----------+
    | VendName               | VendCity |
    +------------------------+----------+
    | Kona, Incorporated     | Redmond  |
    | Nikoma of America      | Ballard  |
    | Shinoman, Incorporated | Bellevue |
    +------------------------+----------+
    (3 rows)
  2. Show me an alphabetized list of products with a retail price of $125.00 or more.

    +----------------------------------+-------------+
    | ProductName                      | RetailPrice |
    +----------------------------------+-------------+
    | AeroFlo ATB Wheels               |      189.00 |
    | Cosmic Elite Road Warrior Wheels |      165.00 |
    | Cycle-Doc Pro Repair Stand       |      166.00 |
    | Eagle FS-3 Mountain Bike         |     1800.00 |
    | Eagle SA-120 Clipless Pedals     |      139.95 |
    | Glide-O-Matic Cycling Helmet     |      125.00 |
    | GT RTS-2 Mountain Bike           |     1650.00 |
    | King Cobra Helmet                |      139.00 |
    | Road Warrior Hitch Pack          |      175.00 |
    | Trek 9000 Mountain Bike          |     1200.00 |
    | Ultimate Export 2G Car Rack      |      180.00 |
    | Viscount CardioSport Sport Watch |      179.00 |
    | Viscount Mountain Bike           |      635.00 |
    +----------------------------------+-------------+
    (13 rows)
  3. Which vendors do we work with that don’t have a Web site?

    +------------------------+-------------+
    | VendName               | VendWebPage |
    +------------------------+-------------+
    | Big Sky Mountain Bikes | NULL        |
    | Dog Ear                | NULL        |
    | Lone Star Bike Supply  | NULL        |
    | Sun Sports Suppliers   | NULL        |
    +------------------------+-------------+
    (4 rows)

Entertainment Agency Database

  1. Let me see a list of all engagements that occurred during October 2012. (Hint: You need to solve this problem by testing for values in a range in the table that contain any values in another range—the first and last dates in October.)

    +------------------+---------------+------------+------------+
    | EngagementNumber | ContractPrice | StartDate  | EndDate    |
    +------------------+---------------+------------+------------+
    |               16 |       1550.00 | 2012-10-02 | 2012-10-06 |
    |               17 |        530.00 | 2012-09-29 | 2012-10-02 |
    |               19 |        365.00 | 2012-09-29 | 2012-10-05 |
    |               21 |       1490.00 | 2012-09-30 | 2012-10-03 |
    |               22 |        590.00 | 2012-09-30 | 2012-10-05 |
    |               24 |       1940.00 | 2012-10-01 | 2012-10-07 |
    |               26 |        950.00 | 2012-10-09 | 2012-10-14 |
    |               27 |       2210.00 | 2012-10-07 | 2012-10-12 |
    |               28 |       3800.00 | 2012-10-06 | 2012-10-15 |
    |               30 |        275.00 | 2012-10-06 | 2012-10-08 |
    |               31 |       2450.00 | 2012-10-07 | 2012-10-16 |
    |               32 |       1250.00 | 2012-10-07 | 2012-10-16 |
    |               34 |        680.00 | 2012-10-14 | 2012-10-20 |
    |               35 |        410.00 | 2012-10-14 | 2012-10-15 |
    |               36 |        710.00 | 2012-10-13 | 2012-10-23 |
    |               37 |       2675.00 | 2012-10-13 | 2012-10-19 |
    |               38 |       1850.00 | 2012-10-14 | 2012-10-18 |
    |               41 |        860.00 | 2012-10-20 | 2012-10-28 |
    |               42 |       2150.00 | 2012-10-20 | 2012-10-26 |
    |               43 |        140.00 | 2012-10-21 | 2012-10-21 |
    |               44 |       1925.00 | 2012-10-22 | 2012-10-26 |
    |               45 |        530.00 | 2012-10-21 | 2012-10-28 |
    |               46 |       1400.00 | 2012-10-28 | 2012-11-05 |
    +------------------+---------------+------------+------------+
    (23 rows)
  2. Show me any engagements in October 2012 that start between noon and 5 P.M.

    +------------------+------------+-----------+
    | EngagementNumber | StartDate  | StartTime |
    +------------------+------------+-----------+
    |               21 | 2012-09-30 | 12:00:00  |
    |               22 | 2012-09-30 | 12:00:00  |
    |               24 | 2012-10-01 | 12:00:00  |
    |               26 | 2012-10-09 | 17:00:00  |
    |               27 | 2012-10-07 | 12:00:00  |
    |               28 | 2012-10-06 | 17:00:00  |
    |               30 | 2012-10-06 | 17:00:00  |
    |               31 | 2012-10-07 | 16:00:00  |
    |               32 | 2012-10-07 | 13:00:00  |
    |               34 | 2012-10-14 | 16:00:00  |
    |               37 | 2012-10-13 | 14:00:00  |
    |               38 | 2012-10-14 | 14:00:00  |
    |               42 | 2012-10-20 | 17:00:00  |
    |               43 | 2012-10-21 | 14:00:00  |
    |               44 | 2012-10-22 | 14:00:00  |
    |               45 | 2012-10-21 | 14:00:00  |
    |               46 | 2012-10-28 | 15:00:00  |
    +------------------+------------+-----------+
    (17 rows)
  3. List all the engagements that start and end on the same day.

    +------------------+------------+------------+
    | EngagementNumber | StartDate  | EndDate    |
    +------------------+------------+------------+
    |               23 | 2012-09-30 | 2012-09-30 |
    |               43 | 2012-10-21 | 2012-10-21 |
    |               90 | 2013-01-08 | 2013-01-08 |
    |               97 | 2013-01-19 | 2013-01-19 |
    |              118 | 2013-02-18 | 2013-02-18 |
    +------------------+------------+------------+
    (5 rows)

School Scheduling Database

  1. Show me which staff members use a post office box as their address.

    +--------------+-------------+-------------------+
    | StfFirstName | StfLastname | StfStreetAddress  |
    +--------------+-------------+-------------------+
    | Alaina       | Hallmark    | Route 2, Box 203B |
    | Gary         | Hallmark    | Route 2, Box 203B |
    | Michael      | Hernandez   | PO Box 223311     |
    +--------------+-------------+-------------------+
    (3 rows)
  2. Can you show me which students live outside of the Pacific Northwest?

    +------------------+--------------+-----------------+-----------+
    | StudentName      | StudAreaCode | StudPhoneNumber | StudState |
    +------------------+--------------+-----------------+-----------+
    | Patterson, Kerry | 210          | 555-2706        | TX        |
    | Stadick, Betsy   | 760          | 555-2696        | CA        |
    | Thompson, Sarah  | 806          | 555-2626        | TX        |
    | Jones, Brannon   | 562          | 555-0399        | CA        |
    | Pundt, Steve     | 972          | 555-9938        | TX        |
    +------------------+--------------+-----------------+-----------+
    (5 rows)
  3. List all the subjects that have a subject code starting ‘MUS’.

    +------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
    | SubjectName                        | SubjectDescription                                                                                                                                                                       | SubjectCode |
    +------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
    | Music in the Western World         | An introduction to music. Features music from a global perspective with a focus on Western Music. Many musical examples, listening, videos on great musicians of the past.               | MUS 100     |
    | First Year Theory and Ear Training | Rudiments of music - notation, scales, intervals, and triads, rhythmic and melodic sight-reading and dictation. Studies of historical periods.                                           | MUS 101     |
    | Second Year Music Theory           | Continuation of MUS 101. Chromatic harmony, modulations and related modern concepts. Prereq: MUS 101 or instructor permission.                                                           | MUS 201     |
    | History of Jazz                    | Traces the roots of jazz in America from New Orleans, New York, Chicago, Kansas City, the Big Band Era, Be-Bop to modern jazz through films, lectures, recordings and live performances. | MUS 204     |
    +------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
    (4 rows)

Bowling League Database

  1. Give me a list of the tournaments held during September 2012.

    +-------------+-------------------+
    | TourneyDate | TourneyLocation   |
    +-------------+-------------------+
    | 2012-09-04  | Red Rooster Lanes |
    | 2012-09-11  | Thunderbird Lanes |
    | 2012-09-18  | Bolero Lanes      |
    | 2012-09-25  | Imperial Lanes    |
    +-------------+-------------------+
    (4 rows)
  2. What are the tournament schedules for Bolero, Red Rooster, and Thunderbird Lanes?

    +-------------------+-------------+
    | TourneyLocation   | TourneyDate |
    +-------------------+-------------+
    | Bolero Lanes      | 2012-09-18  |
    | Bolero Lanes      | 2012-11-06  |
    | Bolero Lanes      | 2013-07-26  |
    | Red Rooster Lanes | 2012-09-04  |
    | Red Rooster Lanes | 2012-10-23  |
    | Red Rooster Lanes | 2013-07-12  |
    | Thunderbird Lanes | 2012-09-11  |
    | Thunderbird Lanes | 2012-10-30  |
    | Thunderbird Lanes | 2013-07-19  |
    +-------------------+-------------+
    (9 rows)
  3. List the bowlers who live on the Eastside (you know—Bellevue, Bothell, Duvall, Redmond, and Woodinville) and who are on teams 5, 6, 7, or 8. (Hint: Use IN for the city list and BETWEEN for the team numbers.)

    +-----------------+----------------+-------------+--------+
    | BowlerFirstName | BowlerLastName | BowlerCity  | TeamID |
    +-----------------+----------------+-------------+--------+
    | Sarah           | Thompson       | Duvall      |      6 |
    | Mary            | Thompson       | Duvall      |      7 |
    | William         | Thompson       | Duvall      |      7 |
    | John            | Viescas        | Redmond     |      5 |
    | Suzanne         | Viescas        | Redmond     |      5 |
    | Caleb           | Viescas        | Redmond     |      6 |
    | Michael         | Viescas        | Redmond     |      7 |
    | Alaina          | Hallmark       | Woodinville |      6 |
    | Bailey          | Hallmark       | Woodinville |      8 |
    +-----------------+----------------+-------------+--------+
    (9 rows)

Recipes Database

  1. List all recipes that are main courses (recipe class is 1) and that have notes.

    +-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | RecipeTitle                                           | Preparation                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | Notes                                                                                                                                                                                                                                                                                                          |
    +-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Pollo Picoso                                          | Wash chicken pieces thoroughly in cold water. Pat dry and set aside.
    Mince garlic and then mix it with the salt, pepper, and cayenne. Make sure the mixture is combined as thoroughly as possible.
    Coat each chicken piece (to taste) with the mixture.
    Place pieces in the broiler pan and cook for 15 minutes. Turn pieces and cook for another 15 minutes. Turn pieces once more and cook for 35 - 40 minutes.
    When the chicken is cooked, remove from stove and let it stand for 10 minutes.                                                                                                                                                                                                                                                                                                                                                                                              | Pre-heat the oven to 400 degrees.
    Cover the bottom of a broiler pan with a sheet of aluminum foil and then pour in about 1/2 an inch of water. The water will keep the chicken grease drippings from splattering throughout the inside of the stove and causing smoke. The foil makes the pan easier to clean. |
    | Huachinango Veracruzana (Red Snapper, Veracruz style) | Heat one ounce of olive oil in a 1.5 quart saucepan.  Slice the onion and sautee lightly in the olive oil.
    Drain the canned tomatoes (you can use peeled fresh tomatoes if you like) and puree in a blender.
    To the pureed tomatoes, add all the spices, thinly sliced Jalapenos, and green olives.  Pour the tomato and spice mixture into the saucepan with the onions and simmer on a very low heat covered for 30 minutes.
    While the sauce is cooking, peel and boil the potatoes.
    Just before the potatoes are done, heat the remaining olive oil in a large frypan.  Wash and lightly dust the fish pieces in a mixture of flour, salt, and pepper.  Fry the fish, turning once, until just done.
    Place the cooked fish in a large platter.  Surround the fish with the boiled potatoes.  Pour the sauce over the fish, sprinkle with chopped parsley, and serve immediately.
    Serves 6. | You can substitute any firm white fish filets for the Red Snapper.  If you use salted canned tomatoes, reduce the salt in the sauce by half.  Adjust the amount of the Jalapenos in the sauce to suit your taste for spicy food!                                                                               |
    | Tourtière (French-Canadian Pork Pie)                  | Brown ground pork and chopped onion, stirring and breaking up pork.  Add spices and salt and pepper.  Simmer, uncovered, for 45 minutes, stirring occasionally.  Preheat oven to 375 degrees.  Prepare pie dough for 2-crust pie.  Line 9" pie plate with half of the rolled-out dough.  Drain pork and stir bread crumbs into pork.  Taste and add more salt and pepper if desired.  Fill pie with pork mixture and top with second half of the rolled-out dough.  Crimp edges of pie and slit the top in several places.  Bake in 375-degree oven for one hour, covering edges of pie crust with foil if necessary.  Serve hot or cold.                                                                                                                                                                                                                                                     | Be sure to use fresh ground pork, not sausage.  Can be made with half ground pork and half ground beef, if desired.                                                                                                                                                                                            |
    | Salmon Filets in Parchment Paper                      | Julienne carrots, leeks, and bell peppers and steam for several minutes.  Drain and rinse vegetables in ice water and set aside.  Preheat oven to 400 degrees.  Butter 4 large rounds of parchment paper.  Distribute half of vegetables on one side of each round of parchment.  Place a salmon filet on the vegetables on each round.  Top the filets with the rest of the vegetables.  Combine white wine and lemon juice and spoon 1 tablespoon on each filet.  Pour melted butter on filets.  Place a thinly-sliced lemon round on each.  Salt and pepper very lightly.  Fold over parchment paper into half circles and roll and crimp edges tightly.  Bake packets at 400 degrees for 10-15 minutes, depending on thickness of the filets.                                                                                                                                             | Serve the salmon in the parchment packets. A salad and boiled new potatoes, buttered and sprinkled with fresh parsley, are the perfect complements.                                                                                                                                                            |
    +-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    (4 rows)
  2. Display the first five recipes. (Hint: Use BETWEEN on the primary key of the table.)

    +----------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | RecipeID | RecipeTitle        | Preparation                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | Notes                                                                                                                                                                               |
    +----------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    |        1 | Irish Stew         | Cut the beef into 1" chunks
    Braise the meat
    Add water and Guinness
    Chop onions, potatoes, and carrots into 1/2" chunks.
    Add to stew.
    Simmer until vegetables are done.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | NULL                                                                                                                                                                                |
    |        2 | Salsa Buena        | Coarsely dice the jalapenos .
    Mix all ingredients thoroughly in a bowl and let stand in the refrigerator for about an hour.
    Serve with your favorite corn chips.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | NULL                                                                                                                                                                                |
    |        3 | Machos Nachos      | Slice the jalapenos crosswise (in circles) and set aside.
    Grate the cheddar cheese and set aside.
    Dice the onion and set aside.
    Spread the tortilla chips on a large microwavable dish.
    Top the chips with the grated cheese, diced onion, and jalapenos.
    Place the dish in the micowave and cook until the cheese just melts, about 3-4 minutes on high.
    When the cheese has melted, remove the dish and top with the black olives.                                                                                                                                                                                                                                                                                                                    | You can add a half a cup of diced tomatoes to this dish if you like. You can either add them prior to cooking in the microwave or afterwards, just before you add the black olives. |
    |        4 | Garlic Green Beans | Snap off and discard the ends of the beans, then rinse them in cold water.
    Mince the two cloves of garlic.
    Heat the oil in a frying pan on medium high heat.
    When the oil is hot, add the green beans and garlic.
    Stir contiuously for about four minutes.
    Place the beans on a serving dish when they're done and sprinkle silvered almonds on the top.                                                                                                                                                                                                                                                                                                                                                                                                | Be sure not to burn the oil. Watch it carefully while it's heating.                                                                                                                 |
    |        5 | Fettuccini Alfredo | Fill a large pot two thirds full with water. Add one tablespoon each of salt and vegetable oil. Bring to a rollling boil.
    Reduce heat, add pasta, and stir briefly. Cook until pasta is al dente.
    Just before the pasta is ready (about five minutes), melt the butter in a frying pan on low heat.
    After the butter has melted, add the heavy cream to the pan. Increase the heat to medium and stir until the mixture is slightly thickened. Remove the pan from the heat once the mixture is ready.
    Drain the fettuccini when it's done and add it to the mixture in the frying pan.
    Mix three ounces of the cheese the the fettuccini and toss the entire mixture.
    Add another three ounces of cheese and the white pepper, and toss again lightly. | NULL                                                                                                                                                                                |
    +----------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    (5 rows)