Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
Entertainment Agency Database
Use the
sourcecommand to load the Entertainment Agency database..Give me the names and phone numbers of all our agents, and list them in last name/first name order.
+-------------+--------------+----------------+ | AgtLastName | AgtFirstName | AgtPhoneNumber | +-------------+--------------+----------------+ | Bishop | Scott | 555-2666 | | Dumbwit | Daffy | 555-1234 | | Kennedy | John | 555-2621 | | Patterson | Maria | 555-2291 | | Smith | Karen | 555-2551 | | Thompson | William | 555-2681 | | Viescas | Caleb | 555-0037 | | Viescas | Carol | 555-2571 | | Wier | Marianne | 555-2606 | +-------------+--------------+----------------+ (9 rows)Give me the information on all our engagements.
+------------------+------------+------------+-----------+----------+---------------+------------+---------+---------------+ | EngagementNumber | StartDate | EndDate | StartTime | StopTime | ContractPrice | CustomerID | AgentID | EntertainerID | +------------------+------------+------------+-----------+----------+---------------+------------+---------+---------------+ | 2 | 2012-09-01 | 2012-09-05 | 13:00:00 | 15:00:00 | 200.00 | 10006 | 4 | 1004 | | 3 | 2012-09-10 | 2012-09-15 | 13:00:00 | 15:00:00 | 590.00 | 10001 | 3 | 1005 | | 4 | 2012-09-11 | 2012-09-17 | 20:00:00 | 00:00:00 | 470.00 | 10007 | 3 | 1004 | | 5 | 2012-09-11 | 2012-09-14 | 16:00:00 | 19:00:00 | 1130.00 | 10006 | 5 | 1003 | | 6 | 2012-09-10 | 2012-09-14 | 15:00:00 | 21:00:00 | 2300.00 | 10014 | 7 | 1008 | | 7 | 2012-09-11 | 2012-09-18 | 17:00:00 | 20:00:00 | 770.00 | 10004 | 4 | 1002 | | 8 | 2012-09-18 | 2012-09-25 | 20:00:00 | 23:00:00 | 1850.00 | 10006 | 3 | 1007 | | 9 | 2012-09-18 | 2012-09-28 | 19:00:00 | 21:00:00 | 1370.00 | 10010 | 2 | 1010 | | 10 | 2012-09-17 | 2012-09-26 | 13:00:00 | 17:00:00 | 3650.00 | 10005 | 3 | 1003 | | 11 | 2012-09-15 | 2012-09-16 | 18:00:00 | 00:00:00 | 950.00 | 10005 | 4 | 1008 | | 12 | 2012-09-18 | 2012-09-26 | 18:00:00 | 22:00:00 | 1670.00 | 10014 | 8 | 1001 | | 13 | 2012-09-17 | 2012-09-20 | 20:00:00 | 23:00:00 | 770.00 | 10003 | 1 | 1006 | | 14 | 2012-09-24 | 2012-09-29 | 16:00:00 | 22:00:00 | 2750.00 | 10001 | 1 | 1008 | . . . (111 rows)List all engagements and their associated start dates. Sort the records by date in descending order and by engagement in ascending order.
+------------+------------------+ | StartDate | EngagementNumber | +------------+------------------+ | 2013-03-03 | 131 | | 2013-02-26 | 128 | | 2013-02-25 | 123 | | 2013-02-24 | 122 | | 2013-02-24 | 126 | | 2013-02-24 | 127 | | 2013-02-24 | 129 | | 2013-02-23 | 124 | | 2013-02-23 | 125 | | 2013-02-19 | 112 | | 2013-02-19 | 114 | | 2013-02-19 | 115 | | 2013-02-19 | 119 | | 2013-02-18 | 118 | | 2013-02-17 | 120 | | 2013-02-16 | 116 | | 2013-02-16 | 121 | | 2013-02-12 | 111 | | 2013-02-11 | 109 | | 2013-02-11 | 110 | . . . (111 rows)
School Scheduling Database
Show me a complete list of all the subjects we offer.
+--------------------------------------------+ | SubjectName | +--------------------------------------------+ | Financial Accounting Fundamentals I | | Financial Accounting Fundamentals II | | Fundamentals of Managerial Accounting | | Intermediate Accounting | | Business Tax Accounting | | Introduction to Business | | Developing A Feasibility Plan | | Introduction to Entrepreneurship | | Information Technology I | | Information Technology II | | Introduction to Art | | Design | | Drawing | | Painting | | Computer Art | | Art History | | Biological Principles | | General Biology | | Microbiology | | Chemistry | | Fundamentals of Chemistry | | Organic Chemistry | | Microcomputer Applications | | Information Systems Concepts | | Problem Solving and Structured Programming | . . . (56 rows)What kinds of titles are associated with our faculty?
+---------------------+ | Title | +---------------------+ | Instructor | | Associate Professor | | Professor | +---------------------+ (3 rows)List the names and phone numbers of all our staff, and sort them by last name and first name.
+-------------+--------------+----------------+ | StfLastname | StfFirstName | StfPhoneNumber | +-------------+--------------+----------------+ | Abolrous | Sam | 555-2611 | | Black | Alastair | 555-0039 | | Bonnicksen | Joyce | 555-2726 | | Brehm | Peter | 555-2501 | | Brown | Robert | 555-2491 | | Coie | Caroline | 555-2306 | | DeGrasse | Kirk | 555-2311 | | Ehrlich | Katherine | 555-0399 | | Glynn | Jim | 555-2531 | | Hallmark | Alaina | 555-2631 | | Hallmark | Gary | 555-2676 | | Hernandez | Michael | 555-2711 | | Keyser | Liz | 555-2556 | | Patterson | Ann | 555-2591 | | Patterson | Kathryn | 555-2651 | | Patterson | Luke | 555-2316 | | Patterson | Maria | 555-2291 | | Rosales III | Joe | 555-2281 | | Sergienko | Mariya | 555-2526 | | Smith | David | 555-2646 | | Smith | Jeffrey | 555-2596 | | Smith | Tim | 555-2536 | | Viescas | Caleb | 555-0037 | | Viescas | Carol | 555-2576 | . . . (27 rows)
Recipes Database
Show me a list of all the ingredients we currently keep track of.
+-------------------------------+ | IngredientName | +-------------------------------+ | Beef | | Onion | | Water | | Guinness Beer | | Potato | | Carrot | | Tomato | | Jalapeno | | Garlic | | Black Pepper (ground) | | Salt | | Halibut | | Chicken, Fryer | | Bacon | | Romaine Lettuce | | Iceberg Lettuce | | Butterhead Lettuce | | Scallop | | Salmon | | Vinegar | | Olive Oil | | Cucumber | | Mushrooms | | Red Wine | . . . (79 rows)Show me all the main recipe information, and sort it by the name of the recipe in alphabetical order.
+----------+-------------------------------------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | RecipeID | RecipeTitle | RecipeClassID | Preparation | Notes | +----------+-------------------------------------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 12 | Asparagus | 2 | Wash the asparagus and break off the tough part (if any) at the bottom of the stalks. Arrange on a steaming rack in a large saucepan. Dab liberally with pats of butter and sprinkle on the chopped garlic. Steam until just tender -- no more than 5 minutes for large stalks. Serve immediately. | You can chill the cooked asparagus and serve with your favorite dip as an appetizer. | | 15 | Coupe Colonel | 6 | For each person, place 2 scoops of lemon sorbet in a stemmed glass. Top with vodka. | This is a lovely, light, and refreshing dessert. Use the best sorbet and vodka you can find. Serve with crisp cookies. | (15 rows)