Sales Orders Database
What if we adjusted each product price by reducing it 5 percent?
+---------------+----------------+-----------+ | ProductNumber | WholesalePrice | NewPrice | +---------------+----------------+-----------+ | 1 | 804.00 | 763.8000 | | 1 | 854.22 | 811.5090 | | 2 | 1269.00 | 1205.5500 | | 2 | 1477.81 | 1403.9195 | | 3 | 54.19 | 51.4805 | | 3 | 57.27 | 54.4065 | | 4 | 44.22 | 42.0090 | | 4 | 41.62 | 39.5390 | | 5 | 5.38 | 5.1110 | | 5 | 5.87 | 5.5765 | | 6 | 403.22 | 383.0590 | | 6 | 448.73 | 426.2935 | | 7 | 31.12 | 29.5640 | | 8 | 39.32 | 37.3540 | | 8 | 37.88 | 35.9860 | | 9 | 21.53 | 20.4535 | | 10 | 22.86 | 21.7170 | | 11 | 1076.62 | 1022.7890 | | 11 | 1178.65 | 1119.7175 | | 12 | 14.51 | 13.7845 | . . . (90 rows)Show me a list of orders made by each customer in descending date order. (Hint: You might need to order by more than one column for the information to display properly.)
+------------+------------+-------------+ | CustomerID | OrderDate | OrderNumber | +------------+------------+-------------+ | 1001 | 2013-02-27 | 931 | | 1001 | 2013-02-21 | 898 | | 1001 | 2013-02-20 | 887 | | 1001 | 2013-02-14 | 855 | | 1001 | 2013-02-13 | 851 | | 1001 | 2013-02-12 | 840 | | 1001 | 2013-02-08 | 817 | | 1001 | 2013-01-23 | 732 | | 1001 | 2013-01-21 | 723 | | 1001 | 2013-01-17 | 701 | | 1001 | 2013-01-10 | 657 | | 1001 | 2013-01-09 | 649 | | 1001 | 2013-01-07 | 632 | | 1001 | 2013-01-02 | 608 | | 1001 | 2012-12-31 | 596 | | 1001 | 2012-12-29 | 587 | | 1001 | 2012-12-25 | 567 | | 1001 | 2012-12-24 | 564 | | 1001 | 2012-12-11 | 501 | | 1001 | 2012-12-06 | 475 | | 1001 | 2012-11-30 | 452 | | 1001 | 2012-11-29 | 443 | | 1001 | 2012-11-29 | 448 | . . . (944 rows)Compile a complete list of vendor names and addresses in vendor name order.
+------------------------+---------------------------------------------+-----------------+ | VendName | VendCompleteAddress | VendPhoneNumber | +------------------------+---------------------------------------------+-----------------+ | Armadillo Brand | 12330 Side Road Lane, Dallas TX 75137 | (214) 444-9876 | | Big Sky Mountain Bikes | Glacier Bay South, Anchorage AK 99209 | (907) 222-1234 | | Dog Ear | 575 Madison Ave., New York NY 10003 | (212) 888-9876 | | Kona, Incorporated | PO Box 10429, Redmond WA 98052 | (425) 333-1234 | | Lone Star Bike Supply | 7402 Kingman Drive, El Paso TX 79915 | (915) 666-9876 | | Nikoma of America | 88 Old North Road Ave, Ballard WA 91324 | (206) 666-1234 | | ProFormance | 29 N. Quail St., Albany NY 12012 | (518) 444-1234 | | Shinoman, Incorporated | 3042 19th Avenue South, Bellevue WA 98001 | (425) 888-1234 | | Sun Sports Suppliers | PO Box 8082, Santa Monica CA 91003 | (310) 777-9876 | | Viscount | 1911 Commerce Way, St. Louis MO 63127 | (314) 777-1234 | +------------------------+---------------------------------------------+-----------------+ (10 rows)
Entertainment Agency Database
Give me the names of all our customers by city. (Hint: You’ll have to use an ORDER BY clause on one of the columns.)
+----------+---------------------+ | City | Customer | +----------+---------------------+ | Auburn | Hallmark, Elizabeth | | Bellevue | Bonnicksen, Joyce | | Bellevue | Keyser, Liz | | Bellevue | Pundt, Estella | | Bellevue | Rosales, Mark | | Bellevue | Thompson, Sarah | | Kirkland | Brehm, Peter | | Kirkland | Ehrlich, Zachary | | Kirkland | Gehring, Darren | | Redmond | McCrae, Dean | | Redmond | Patterson, Kerry | | Seattle | Hartwig, Doris | | Seattle | Viescas, Carol | | Tacoma | Berg, Matt | | Tacoma | Waldal, Deb | +----------+---------------------+ (15 rows)List all entertainers and their Web sites.
+--------------------------+---------------------------------+ | Entertainer | Drop_By | +--------------------------+---------------------------------+ | Carol Peacock Trio | Web site: www.cptrio.com | | Topazz | Web site: www.topazz.com | | JV & the Deep Six | Web site: www.jvd6.com | | Jim Glynn | NULL | | Jazz Persuasion | Web site: www.jazzper.com | | Modern Dance | Web site: www.moderndance.com | | Coldwater Cattle Company | Web site: www.coldwatercows.com | | Country Feeling | NULL | | Katherine Ehrlich | NULL | | Saturday Revue | Web site: www.satrevue.com | | Julia Schnebly | NULL | | Susan McLain | Web site: www.greensleeves.com | | Caroline Coie Cuartet | NULL | +--------------------------+---------------------------------+ (13 rows)Show the date of each agent’s first six-month performance review. (Hint: You’ll need to use date arithmetic to answer this request.)
+-------------------+------------+-------------+ | Agent | DateHired | FirstReview | +-------------------+------------+-------------+ | Bishop, Scott | 1998-02-05 | 1998-08-04 | | Dumbwit, Daffy | 2000-02-05 | 2000-08-03 | | Kennedy, John | 1997-05-15 | 1997-11-11 | | Patterson, Maria | 1997-09-03 | 1998-03-02 | | Smith, Karen | 1998-03-05 | 1998-09-01 | | Thompson, William | 1997-05-15 | 1997-11-11 | | Viescas, Caleb | 1998-02-16 | 1998-08-15 | | Viescas, Carol | 1997-11-19 | 1998-05-18 | | Wier, Marianne | 1998-02-02 | 1998-08-01 | +-------------------+------------+-------------+ (9 rows)
School Scheduling Database
Give me a list of staff members, and show them in descending order of salary.
+----------+--------------------+ | Salary | StaffMember | +----------+--------------------+ | 60000.00 | Abolrous, Sam | | 60000.00 | Black, Alastair | | 60000.00 | Bonnicksen, Joyce | | 60000.00 | Brehm, Peter | | 60000.00 | Hernandez, Michael | | 57000.00 | Hallmark, Alaina | | 56000.00 | Patterson, Luke | | 53000.00 | Hallmark, Gary | | 52000.00 | Coie, Caroline | | 52000.00 | Smith, David | | 52000.00 | Smith, Jeffrey | | 50000.00 | Viescas, Carol | | 50000.00 | Wilson, Jim | | 49000.00 | Brown, Robert | | 48000.00 | Keyser, Liz | | 48000.00 | Patterson, Maria | | 45000.00 | DeGrasse, Kirk | | 45000.00 | Ehrlich, Katherine | | 45000.00 | Glynn, Jim | | 45000.00 | Patterson, Ann | | 45000.00 | Sergienko, Mariya | | 45000.00 | Viescas, Caleb | | 44000.00 | Viescas, Suzanne | | 44000.00 | Waldal, Deb | | 40000.00 | Smith, Tim | | 35000.00 | Rosales III, Joe | | 25000.00 | Patterson, Kathryn | +----------+--------------------+ (27 rows)Can you give me a staff member phone list?
+--------------------+----------------+ | StaffMember | Phone | +--------------------+----------------+ | Abolrous, Sam | (760) 555-2611 | | Black, Alastair | (206) 555-0039 | | Bonnicksen, Joyce | (425) 555-2726 | | Brehm, Peter | (425) 555-2501 | | Brown, Robert | (713) 555-2491 | | Coie, Caroline | (206) 555-2306 | | DeGrasse, Kirk | (210) 555-2311 | | Ehrlich, Katherine | (425) 555-0399 | | Glynn, Jim | (425) 555-2531 | | Hallmark, Alaina | (425) 555-2631 | | Hallmark, Gary | (253) 555-2676 | | Hernandez, Michael | (253) 555-2711 | | Keyser, Liz | (425) 555-2556 | | Patterson, Ann | (253) 555-2591 | | Patterson, Kathryn | (206) 555-2651 | | Patterson, Luke | (503) 555-2316 | | Patterson, Maria | (915) 555-2291 | | Rosales III, Joe | (253) 555-2281 | | Sergienko, Mariya | (503) 555-2526 | | Smith, David | (510) 555-2646 | | Smith, Jeffrey | (510) 555-2596 | | Smith, Tim | (206) 555-2536 | | Viescas, Caleb | (562) 555-0037 | | Viescas, Carol | (425) 555-2576 | | Viescas, Suzanne | (425) 555-2686 | | Waldal, Deb | (253) 555-2496 | | Wilson, Jim | (503) 555-2636 | +--------------------+----------------+ (27 rows)List the names of all our students, and order them by the cities they live in.
+--------------+---------------------+ | StudCity | Student | +--------------+---------------------+ | Auburn | Hallmark, Elizabeth | | Bremerton | Chavez, George | | Dallas | Pundt, Steve | | Eugene | Smith, Karen | | Long Beach | Jones, Brannon | | Lubbock | Thompson, Sarah | | Medford | Bishop, Scott | | Palm Springs | Stadick, Betsy | | Portland | Kennedy, John | | Portland | Sheskey, Sara | | Redmond | Galvin, Janice | | Redmond | Viescas, Michael | | San Antonio | Patterson, Kerry | | Seattle | Bonnicksen, Kendra | | Seattle | Hartwig, Doris | | Seattle | Lum, Richard | | Tacoma | Hamilton, David | | Tacoma | Wier, Marianne | +--------------+---------------------+ (18 rows)
Bowling League Database
Show next year’s tournament date for each tournament location.
+--------------------+-------------+---------------------+ | TourneyLocation | TourneyDate | NextYearTourneyDate | +--------------------+-------------+---------------------+ | Acapulco Lanes | 2012-12-04 | 2013-12-03 | | Acapulco Lanes | 2012-10-16 | 2013-10-15 | | Bolero Lanes | 2012-11-06 | 2013-11-05 | | Bolero Lanes | 2012-09-18 | 2013-09-17 | | Bolero Lanes | 2013-07-26 | 2014-07-25 | | Imperial Lanes | 2012-11-13 | 2013-11-12 | | Imperial Lanes | 2012-09-25 | 2013-09-24 | | Imperial Lanes | 2013-08-09 | 2014-08-08 | | Red Rooster Lanes | 2012-09-04 | 2013-09-03 | | Red Rooster Lanes | 2013-07-12 | 2014-07-11 | | Red Rooster Lanes | 2012-10-23 | 2013-10-22 | | Sports World Lanes | 2012-11-20 | 2013-11-19 | | Sports World Lanes | 2013-08-02 | 2014-08-01 | | Sports World Lanes | 2012-10-02 | 2013-10-01 | | Thunderbird Lanes | 2012-10-30 | 2013-10-29 | | Thunderbird Lanes | 2012-09-11 | 2013-09-10 | | Thunderbird Lanes | 2013-07-19 | 2014-07-18 | | Totem Lanes | 2012-10-09 | 2013-10-08 | | Totem Lanes | 2012-11-27 | 2013-11-26 | | Totem Lanes | 2013-08-16 | 2014-08-15 | +--------------------+-------------+---------------------+ (20 rows)List the name and phone number for each member of the league.
+---------------------+----------------+ | Bowler | Phone | +---------------------+----------------+ | Black, Alastair | (206) 555-1189 | | Cunningham, David | (206) 555-8122 | | Ehrlich, Zachary | (206) 555-9857 | | Fournier, Barbara | (206) 555-9876 | | Fournier, David | (206) 555-9876 | | Hallmark, Alaina | (206) 555-8990 | | Hallmark, Bailey | (206) 555-8990 | | Hallmark, Elizabeth | (206) 555-8990 | | Hallmark, Gary | (206) 555-8990 | | Hernandez, Kendra | (206) 889-9191 | | Hernandez, Michael | (206) 889-9191 | | Kennedy, Angel | (206) 555-7854 | | Kennedy, John | (206) 555-7854 | | Patterson, Ann | (206) 555-3487 | | Patterson, Kathryn | (206) 555-3487 | | Patterson, Megan | (206) 555-3487 | | Patterson, Neil | (206) 555-3487 | | Patterson, Rachel | (206) 555-3487 | | Pundt, Steve | (206) 555-3412 | | Rosales, Joe | (206) 555-9482 | | Sheskey, Richard | (206) 555-9893 | | Sheskey, Sara | (206) 555-9893 | | Thompson, Mary | (206) 555-8989 | | Thompson, Sarah | (206) 555-8989 | | Thompson, William | (206) 555-8989 | | Viescas, Caleb | (206) 882-8878 | | Viescas, Carol | (206) 555-7295 | | Viescas, David | (206) 882-8878 | | Viescas, John | (206) 881-5596 | | Viescas, Michael | (206) 881-5596 | | Viescas, Stephanie | (206) 882-8878 | | Viescas, Suzanne | (206) 881-5596 | +---------------------+----------------+ (32 rows)Give me a listing of each team’s lineup. (Hint: Base this query on the Bowlers table.)
+--------+---------------------+ | TeamID | Bowler | +--------+---------------------+ | 1 | Fournier, Barbara | | 1 | Fournier, David | | 1 | Kennedy, John | | 1 | Sheskey, Sara | | 2 | Patterson, Ann | | 2 | Patterson, Neil | | 2 | Viescas, David | | 2 | Viescas, Stephanie | | 3 | Black, Alastair | | 3 | Cunningham, David | | 3 | Kennedy, Angel | | 3 | Viescas, Carol | | 4 | Hallmark, Elizabeth | | 4 | Hallmark, Gary | | 4 | Patterson, Kathryn | | 4 | Sheskey, Richard | | 5 | Hernandez, Kendra | | 5 | Hernandez, Michael | | 5 | Viescas, John | | 5 | Viescas, Suzanne | | 6 | Ehrlich, Zachary | | 6 | Hallmark, Alaina | | 6 | Thompson, Sarah | | 6 | Viescas, Caleb | | 7 | Patterson, Megan | | 7 | Thompson, Mary | | 7 | Thompson, William | | 7 | Viescas, Michael | | 8 | Hallmark, Bailey | | 8 | Patterson, Rachel | | 8 | Pundt, Steve | | 8 | Rosales, Joe | +--------+---------------------+ (32 rows)