Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
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)