W05 Assignment: Joining and Summarizing Data
- Purpose: Use queries that combine multiple tables to get the desired results. Use aggregate function to summarize data.
- Task: Create queries that use different types of JOINs and aggregate functions to get result sets from multiple tables.
Instructions
- Using the v_art, magazine and employee databases, write queries in Workbench to solve all the answers to the following scenarios.
- All the queries will be written in one tab in Workbench and then save the SQL script with all the queries and submit that .sql file.
- Please use comments to number your queries.
- Note: the exact steps required are not supplied to you so you can practice situations from the real world. When a CEO gives you a task you need to extrapolate the important pieces of information. You may refer back to practice problem-solving with the Problem Solving Case Study.
Joins
v_art Database:
-
When you visit the Virtual Art Gallery Database and you search by Period/Style and you choose Impressionism, you get two resulting images ("Woman in the Garden" and "Irises"). What query would be used in the code here to allow the user to see these images? No join is needed.
artfile irises.jpg womengarden.jpg -
When you visit the Virtual Art Gallery Database, search by Subject and type in the word flower, you get three images. What query would have allowed the user to get those results (remember, the keyword might have been 'flowers' but they typed 'flower') .
artfile irises.jpg sunflowers.jpg womengarden.jpg -
List all the artists from the artist table, but only the related artwork from the artwork table. We need the first name, last name, and artwork title.
fname lname title Vincent van Gogh Irises Vincent van Gogh Starry Night Vincent van Gogh Sunflowers Rembrandt van Rijn Night Watch Rembrandt van Rijn Storm on the Sea of Galilee Leonardo da Vinci Head of a Woman Leonardo da Vinci Last Supper Leonardo da Vinci Mona Lisa Venture Coy Hillside Stream Venture Coy Old Barn Deborah Gill Beach Baby Claude Monet Woman in the Garden Pablo Picasso Old Guitarist Michelangelo Simoni NULL
Magazine Database:
-
List all subscriptions with the magazine name, last name, first name, and sort alphabetically by magazine name.
magazineName subscriberLastName subscriberFirstName Beautiful Birds Sanders Samantha Beautiful Birds Lamont Lucy Car Racing Made Easy Anderson Albert Cooking Like Mad Sanders Samantha Corn Shucking for Fun and Profit Lamont Lucy Corn Shucking for Fun and Profit Jimenez Jose Fishing in the Mojave Johnston Julie Fishing in the Mojave Sanders Samantha Fishing in the Mojave Jimenez Jose If Only I Could Sing Sanders Samantha Pine Cone Computing Sanders Samantha -
List all the magazines that Samantha Sanders subscribes to.
magazineName Beautiful Birds Cooking Like Mad Fishing in the Mojave If Only I Could Sing Pine Cone Computing
Employee Database:
-
List the first five employees from the Customer Service Department. Put them in alphabetical order by last name.
first_name last_name Youpyo Aamodt Basim Aamodt Rajmohan Aamodt Marla Aamodt Arumugam Aamodt -
Find out the current salary and department of Berni Genin. You can use the ORDER BY and LIMIT to get just the most recent salary.
first_name last_name dept_name salary from_date Berni Genin Development 60598 2001-12-27
Summary Queries
Using the bike database:
-
Get the average quantity that we have in all our bike stocks. Round to the nearest whole number.
Stock Average 14 -
Show each bike that needs to be reordered. Filter the results to only the lowest quantity of zero. Order by product_name The image below show the first 12 of 24 rows total. You don't need to use a LIMIT. (Hint for this one: Two different stores have the same bike that needs to be reordered. You only need it to show up once.)
product_name Electra Amsterdam Royal 8i Ladies - 2018 Electra Cruiser 1 Ladies' - 2018 Electra Girl's Hawaii 1 (16-inch) - 2015/2016 Electra Townie Commute Go! - 2018 Electra Townie Go! 8i - 2017/2018 Electra Townie Original 1 - 2018 Electra Townie Original 1 Ladies' - 2018 Electra Townie Original 21D - 2018 Electra Townie Original 3i EQ Ladies' - 2018 Haro Shredder 20 - 2017 Surly Ice Cream Truck Frameset - 2016 Surly Pack Rat - 2018 -
How many of each category of bikes do we have in stock (inventory) at our "Baldwin Bikes" store, which has the store_id of 2. We need to see the name of the category as well as the number of bikes we have in inventory in the category. Sort by lowest inventory items first.
category_name instock Cyclocross Bicycles 97 Electric Bikes 328 Comfort Bicycles 393 Road Bikes 758 Children Bicycles 769 Mountain Bikes 877 Cruisers Bicycles 1137
Use the employee database to answer the following:
-
How many employees do we have? (3 points)
Number of Employees 300024 -
Get the average salaries in each department. We only need those departments that have average salaries that are below 60,000. Format the salary to 2 decimal places and a comma in the thousands place.
dept_name average_salary Development 59,503.57 Production 59,539.79 Human Resources 55,353.52 Research 59,866.24 Quality Management 57,294.66 Customer Service 58,755.44 -
Find out how many females work in each department. Sort by department name.
dept_name Number of Females Customer Service 9448 Development 34258 Finance 7015 Human Resources 7075 Marketing 8037 Production 29549 Quality Management 8078 Research 8439 Sales 20854
Submission
Upload a single SQL workbench file containing your SQL commands. Make sure you use comments to identify the question number at the start of each statement.
Useful Links:
- Go to: Week Index • Course Home • Canvas