Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
Entertainment Agency Database
Display agents and the engagement dates they booked, sorted by booking start date. (Hint: The solution requires a JOIN of two tables.)
+------------------+------------+ | AgtFullName | StartDate | +------------------+------------+ | Karen Smith | 2012-09-01 | | Carol Viescas | 2012-09-10 | | Caleb Viescas | 2012-09-10 | | Carol Viescas | 2012-09-11 | | Karen Smith | 2012-09-11 | | Marianne Wier | 2012-09-11 | | Karen Smith | 2012-09-15 | | Carol Viescas | 2012-09-17 | | William Thompson | 2012-09-17 | | Scott Bishop | 2012-09-18 | | Carol Viescas | 2012-09-18 | | Maria Patterson | 2012-09-18 | | William Thompson | 2012-09-24 | | William Thompson | 2012-09-24 | | Maria Patterson | 2012-09-29 | | Maria Patterson | 2012-09-29 | | Karen Smith | 2012-09-30 | | Marianne Wier | 2012-09-30 | | William Thompson | 2012-09-30 | | Karen Smith | 2012-10-01 | | Marianne Wier | 2012-10-02 | | Karen Smith | 2012-10-06 | | Marianne Wier | 2012-10-06 | | Caleb Viescas | 2012-10-07 | | Maria Patterson | 2012-10-07 | | Caleb Viescas | 2012-10-07 | | John Kennedy | 2012-10-09 | | Carol Viescas | 2012-10-13 | | Carol Viescas | 2012-10-13 | | Karen Smith | 2012-10-14 | | Maria Patterson | 2012-10-14 | | Maria Patterson | 2012-10-14 | | William Thompson | 2012-10-20 | | Carol Viescas | 2012-10-20 | | Maria Patterson | 2012-10-21 | | William Thompson | 2012-10-21 | | Carol Viescas | 2012-10-22 | . . . (111 rows)List customers and the entertainers they booked. (Hint: The solution requires a JOIN of more than two tables.)
+--------------------+--------------------------+ | CustFullName | EntStageName | +--------------------+--------------------------+ | Mark Rosales | Carol Peacock Trio | | Doris Hartwig | Carol Peacock Trio | | Elizabeth Hallmark | Carol Peacock Trio | | Zachary Ehrlich | Carol Peacock Trio | | Matt Berg | Carol Peacock Trio | | Dean McCrae | Carol Peacock Trio | | Estella Pundt | Carol Peacock Trio | | Dean McCrae | Topazz | | Doris Hartwig | Topazz | | Estella Pundt | Topazz | | Elizabeth Hallmark | Topazz | | Matt Berg | JV & the Deep Six | | Elizabeth Hallmark | JV & the Deep Six | | Carol Viescas | JV & the Deep Six | | Deb Waldal | JV & the Deep Six | | Mark Rosales | JV & the Deep Six | | Zachary Ehrlich | JV & the Deep Six | | Doris Hartwig | JV & the Deep Six | | Matt Berg | Jim Glynn | | Liz Keyser | Jim Glynn | | Sarah Thompson | Jim Glynn | | Zachary Ehrlich | Jim Glynn | | Kerry Patterson | Jim Glynn | | Doris Hartwig | Jazz Persuasion | | Dean McCrae | Jazz Persuasion | | Peter Brehm | Jazz Persuasion | | Sarah Thompson | Jazz Persuasion | | Zachary Ehrlich | Jazz Persuasion | | Peter Brehm | Modern Dance | | Estella Pundt | Modern Dance | | Zachary Ehrlich | Modern Dance | | Elizabeth Hallmark | Modern Dance | | Matt Berg | Modern Dance | | Carol Viescas | Modern Dance | | Dean McCrae | Modern Dance | | Sarah Thompson | Modern Dance | | Matt Berg | Coldwater Cattle Company | . . . (75 rows)Find the agents and entertainers who live in the same postal code. (Hint: The solution requires a JOIN on matching values.)
+-----------------+--------------------+------------+ | AgtFullName | EntStageName | AgtZipCode | +-----------------+--------------------+------------+ | Scott Bishop | Country Feeling | 98125 | | Scott Bishop | Saturday Revue | 98125 | | Carol Viescas | Susan McLain | 98006 | | Karen Smith | Country Feeling | 98125 | | Karen Smith | Saturday Revue | 98125 | | John Kennedy | Country Feeling | 98125 | | John Kennedy | Saturday Revue | 98125 | | Caleb Viescas | Carol Peacock Trio | 98052 | | Caleb Viescas | JV & the Deep Six | 98052 | | Maria Patterson | Susan McLain | 98006 | +-----------------+--------------------+------------+ (10 rows)
School Scheduling Database
Display buildings and all the classrooms in each building. (Hint: The solution requires a JOIN of two tables.)
+------------------------+-------------+ | BuildingName | ClassRoomID | +------------------------+-------------+ | Arts and Sciences | 1514 | | Arts and Sciences | 1515 | | Arts and Sciences | 1519 | | Arts and Sciences | 1525 | | Arts and Sciences | 1530 | | Arts and Sciences | 1532 | | Arts and Sciences | 1619 | | Arts and Sciences | 1622 | | Arts and Sciences | 1624 | | Arts and Sciences | 1627 | | College Center | 2357 | | College Center | 3343 | | College Center | 3345 | | College Center | 3346 | | College Center | 3352 | | College Center | 3353 | | College Center | 3355 | | College Center | 3443 | | College Center | 3445 | | College Center | 3446 | | College Center | 3452 | | College Center | 3455 | | Instructional Building | 2408 | | Instructional Building | 2423 | | Instructional Building | 3305 | | Instructional Building | 3307 | | Instructional Building | 3309 | | Instructional Building | 3313 | | Instructional Building | 3315 | | Instructional Building | 3317 | | Instructional Building | 3319 | | Instructional Building | 3322 | | Instructional Building | 3330 | | Instructional Building | 3404 | | Instructional Building | 3406 | | Instructional Building | 3409 | | Instructional Building | 3415 | | Instructional Building | 3420 | | Instructional Building | 3422 | | Instructional Building | 3431 | | Library | 1131 | | Library | 1142 | | Library | 1231 | | Technology Building | 1639 | | Technology Building | 1640 | | Technology Building | 1641 | | Technology Building | 1642 | +------------------------+-------------+ (47 rows)List students and all the classes in which they are currently enrolled. (Hint: The solution requires a JOIN of more than two tables.)
+---------------------+---------+--------------------------------------------+ | StudentFullName | ClassID | SubjectName | +---------------------+---------+--------------------------------------------+ | Patterson, Kerry | 4180 | Composition - Intermediate | | Patterson, Kerry | 5917 | Intermediate Algebra | | Patterson, Kerry | 6082 | U.S. History Since 1865 | | Stadick, Betsy | 4180 | Composition - Intermediate | | Stadick, Betsy | 4560 | Second Year Music Theory | | Stadick, Betsy | 6082 | U.S. History Since 1865 | | Galvin, Janice | 4020 | Computer Art | | Galvin, Janice | 4180 | Composition - Intermediate | | Galvin, Janice | 5430 | Problem Solving and Structured Programming | | Galvin, Janice | 6082 | U.S. History Since 1865 | | Hartwig, Doris | 4196 | Advanced English Grammar | | Hartwig, Doris | 5510 | Financial Accounting Fundamentals II | | Hartwig, Doris | 5917 | Intermediate Algebra | | Bishop, Scott | 4002 | Design | | Bishop, Scott | 4180 | Composition - Intermediate | | Bishop, Scott | 5907 | Elementary Algebra | | Hallmark, Elizabeth | 4196 | Advanced English Grammar | | Hallmark, Elizabeth | 5071 | Survey Of Physics | | Hallmark, Elizabeth | 5933 | Intermediate Algebra | | Sheskey, Sara | 4180 | Composition - Intermediate | | Sheskey, Sara | 5917 | Intermediate Algebra | | Sheskey, Sara | 6082 | U.S. History Since 1865 | | Smith, Karen | 4183 | Composition - Intermediate | | Smith, Karen | 4196 | Advanced English Grammar | | Smith, Karen | 6030 | Survey of Economics | | Kennedy, John | 4180 | Composition - Intermediate | | Kennedy, John | 5430 | Problem Solving and Structured Programming | | Kennedy, John | 6082 | U.S. History Since 1865 | | Thompson, Sarah | 4196 | Advanced English Grammar | | Thompson, Sarah | 5510 | Financial Accounting Fundamentals II | | Thompson, Sarah | 5917 | Intermediate Algebra | | Viescas, Michael | 4002 | Design | | Viescas, Michael | 4180 | Composition - Intermediate | | Viescas, Michael | 5907 | Elementary Algebra | | Bonnicksen, Kendra | 4196 | Advanced English Grammar | | Bonnicksen, Kendra | 5071 | Survey Of Physics | | Bonnicksen, Kendra | 5933 | Intermediate Algebra | | Jones, Brannon | 4183 | Composition - Intermediate | | Jones, Brannon | 4196 | Advanced English Grammar | | Jones, Brannon | 6030 | Survey of Economics | | Pundt, Steve | 4020 | Computer Art | | Pundt, Steve | 4180 | Composition - Intermediate | | Pundt, Steve | 5430 | Problem Solving and Structured Programming | | Pundt, Steve | 6082 | U.S. History Since 1865 | | Chavez, George | 4002 | Design | | Chavez, George | 4180 | Composition - Intermediate | | Chavez, George | 5907 | Elementary Algebra | | Lum, Richard | 4196 | Advanced English Grammar | | Lum, Richard | 5071 | Survey Of Physics | | Lum, Richard | 5933 | Intermediate Algebra | +---------------------+---------+--------------------------------------------+ (50 rows)List the faculty staff and the subject each teaches. (Hint: The solution requires a JOIN of more than two tables.)
+--------------------+--------------------------------------------+ | StfFullName | SubjectName | +--------------------+--------------------------------------------+ | Viescas, Suzanne | Design | | Viescas, Suzanne | Art History | | Viescas, Suzanne | First Year Theory and Ear Training | | Viescas, Suzanne | History of Jazz | | Viescas, Suzanne | Composition - Intermediate | | Hallmark, Gary | Financial Accounting Fundamentals II | | Hallmark, Gary | Business Tax Accounting | | Hallmark, Gary | Painting | | Hallmark, Gary | Information Systems Concepts | | Hallmark, Gary | Elementary Algebra | | Smith, Jeffrey | Financial Accounting Fundamentals II | | Smith, Jeffrey | Business Tax Accounting | | Smith, Jeffrey | Information Systems Concepts | | Smith, Jeffrey | Geometry and Visualization | | Patterson, Ann | Composition - Intermediate | | Patterson, Ann | U.S. History to 1877 | | Patterson, Ann | Trigonometry | | Brown, Robert | Financial Accounting Fundamentals I | | Brown, Robert | Intermediate Accounting | | Brown, Robert | Principles of Economics: Macroeconomics | | Brown, Robert | Preparatory Mathematics | | Waldal, Deb | Microcomputer Applications | | Waldal, Deb | Database Management | | Waldal, Deb | Programming in BASIC | | Waldal, Deb | Computer Programming | | Waldal, Deb | Elementary Algebra | | Brehm, Peter | Introduction to Art | | Brehm, Peter | Computer Art | | Brehm, Peter | Advanced English Grammar | | Brehm, Peter | World History to 1500 | | Brehm, Peter | U.S. History Since 1865 | | Sergienko, Mariya | Drawing | | Sergienko, Mariya | Principles of Economics: Macroeconomics | | Sergienko, Mariya | Intermediate Algebra | | Glynn, Jim | Chemistry | | Glynn, Jim | Organic Chemistry | | Glynn, Jim | Problem Solving and Structured Programming | | Glynn, Jim | Survey Of Physics | | Glynn, Jim | Engineering Physics I | | Viescas, Carol | Fundamentals of Managerial Accounting | | Viescas, Carol | Developing A Feasibility Plan | | Viescas, Carol | Information Technology I | | Viescas, Carol | Intermediate Algebra | | Hallmark, Alaina | Introduction to Art | | Hallmark, Alaina | Computer Art | | Hallmark, Alaina | Music in the Western World | | Hallmark, Alaina | Second Year Music Theory | | Hallmark, Alaina | Advanced English Grammar | | Keyser, Liz | Painting | | Keyser, Liz | Survey of Economics | | Keyser, Liz | World History to 1500 | | Keyser, Liz | U.S. History Since 1865 | | Keyser, Liz | Geometry and Visualization | | Abolrous, Sam | Design | | Abolrous, Sam | Art History | | Abolrous, Sam | First Year Theory and Ear Training | | Abolrous, Sam | History of Jazz | | Wilson, Jim | Problem Solving and Structured Programming | | Wilson, Jim | Programming in BASIC | | Wilson, Jim | Computer Programming | | Wilson, Jim | Preparatory Mathematics | | Smith, David | Principles of Economics: Microeconomics | | Smith, David | Introduction to Geography | | Smith, David | Physical Geography | | Smith, David | American Government | | Smith, David | Women and Politics | | Hernandez, Michael | Drawing | | Hernandez, Michael | Microcomputer Applications | | Hernandez, Michael | Database Management | | Hernandez, Michael | Music in the Western World | | Hernandez, Michael | Second Year Music Theory | | Bonnicksen, Joyce | Developing A Feasibility Plan | | Bonnicksen, Joyce | Information Technology I | | Bonnicksen, Joyce | Survey of Economics | | Bonnicksen, Joyce | Introduction to Political Science | | Bonnicksen, Joyce | American Government | | Ehrlich, Katherine | Biological Principles | | Ehrlich, Katherine | General Biology | | Ehrlich, Katherine | Fundamentals of Chemistry | | Ehrlich, Katherine | Survey Of Physics | | Ehrlich, Katherine | Engineering Physics I | | Viescas, Caleb | General Biology | | Viescas, Caleb | Microbiology | | Viescas, Caleb | Fundamentals of Chemistry | | Viescas, Caleb | General Physics | | Viescas, Caleb | Engineering Physics II | | Black, Alastair | Fundamentals of Managerial Accounting | | Black, Alastair | Introduction to Business | | Black, Alastair | Introduction to Entrepreneurship | | Black, Alastair | Information Technology II | | Black, Alastair | Composition - Fundamentals | | Patterson, Maria | Principles of Economics: Microeconomics | | Patterson, Maria | U.S. History to 1877 | | Patterson, Maria | Introduction to Political Science | | Patterson, Maria | Women and Politics | | Coie, Caroline | Financial Accounting Fundamentals I | | Coie, Caroline | Intermediate Accounting | | Coie, Caroline | Introduction to Business | | Coie, Caroline | Introduction to Entrepreneurship | | Coie, Caroline | Information Technology II | | Coie, Caroline | Trigonometry | | DeGrasse, Kirk | Biological Principles | | DeGrasse, Kirk | Microbiology | | DeGrasse, Kirk | Chemistry | | DeGrasse, Kirk | Organic Chemistry | | DeGrasse, Kirk | General Physics | | DeGrasse, Kirk | Engineering Physics II | | Patterson, Luke | Composition - Fundamentals | | Patterson, Luke | Introduction to Geography | | Patterson, Luke | Physical Geography | +--------------------+--------------------------------------------+ (110 rows)
Recipes Database
List all the recipes for salads. (Hint: The solution requires a JOIN of two tables.)
+---------------------+ | RecipeTitle | +---------------------+ | Mike's Summer Salad | +---------------------+ (1 row)List all recipes that contain a dairy ingredient. (Hint: The solution requires a JOIN of more than two tables.)
+--------------------+ | RecipeTitle | +--------------------+ | Fettuccini Alfredo | | Yorkshire Pudding | +--------------------+ (2 rows)Find the ingredients that use the same default measurement amount. (Hint: The solution requires a JOIN on matching values.)
+-------------------------------+------------------------+-------------------------------+ | FirstIngredientName | MeasurementDescription | SecondIngredientName | +-------------------------------+------------------------+-------------------------------+ | Water | Ounce | Beef | | Guinness Beer | Ounce | Beef | | Vinegar | Ounce | Beef | | Olive Oil | Ounce | Beef | | Fettuccini Pasta | Ounce | Beef | | Heavy Cream | Ounce | Beef | | Spaghetti | Ounce | Beef | | Mustard, Regular | Ounce | Beef | | Mustard, Dijon | Ounce | Beef | | Ketchup | Ounce | Beef | | Salsa | Ounce | Beef | | Parmesan Cheese | Ounce | Beef | | Blue Cheese | Ounce | Beef | | White Pepper (ground) | Ounce | Beef | | Cayenne Pepper, Ground | Ounce | Beef | | Balsamic Vinaigrette Dressing | Ounce | Beef | | Red Snapper | Ounce | Beef | | Beef | Ounce | Water | | Guinness Beer | Ounce | Water | | Vinegar | Ounce | Water | | Olive Oil | Ounce | Water | | Fettuccini Pasta | Ounce | Water | | Heavy Cream | Ounce | Water | | Spaghetti | Ounce | Water | . . . (628 rows)