CIT 111: Introduction to Databases

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

1. Using the v_art, magazine and employee databases, write queries in Workbench to solve all the answers to the following scenarios.
2. 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.
3. Please use comments to number your queries. 
4. 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:

1. 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. 

   

2. 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') .

   

3. 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. 

   

Magazine Database:

4. List all subscriptions with the magazine name, last name, first name, and sort alphabetically by magazine name. 

   

5. List all the magazines that Samantha Sanders subscribes to. 

   

Employee Database:

6. List the first five employees from the Customer Service Department. Put them in alphabetical order by last name.

   

7. 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.

   

Summary Queries

Using the bike database:

8. Get the average quantity that we have in all our bike stocks. Round to the nearest whole number. 

   

9. 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.)

   

10. 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. 

    

Use the employee database to answer the following:

11. How many employees do we have? (3 points)

    

12. 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. 

    

13. Find out how many females work in each department. Sort by department name.

    

Submission

Upload a single SQL workbench file containing your SQL commands. Make sure you use comments to identity the question number at the start of each statement.

Useful Links:

• Next Activity: Complete W01 Prove Quiz and W01 60-second Status Update in Canvas
• Previous Activity: Team Activity: SQL Select Practice.
• Go to: Week Index • Course Home • Canvas