CIT 111: Introduction to Databases

Team Activity - Querying Company Data

• Purpose: Participate in a 60-minute online team meeting to practice writing queries that answer questions about company data.
• Task: Write SQL queries that answer questions about a bike store's business.

Overview

This week you will work together to answer questions about a companies data. You will be using the bike database for this assignment. (If you have not installed the class databases do so now, instructions are at the first of the Querying Databases Activity ) Allot 60 minutes for this activity.

Questions

1. I need a list of all the customers with Gmail accounts because there is a new Google application that can interface with them through their email. We'd like to contact them and invite them to use it.
2. Haro called and wants to discount all their bikes by 20%. Please get me the name of the bikes from Haro (brand_id 2) and show the original price and then a column with the sales price with a readable column header.
3. We need to see the order number and order date from all orders at Santa Cruz Bikes (store_id 1) but I don't need to see the orders made by Mireya Copeland who's staff_id is 2. Just Fabiola, Genna and Virgie's orders (everyone else who works there).
4. I need all the order numbers and order dates for the month of February 2017. We need to clear out all our frame specific bikes and Women's bikes. They are not selling well and we will be displaying them in our annual sidewalk sale. Find all of the bike product names with 'Frame' or 'Frameset' in the name or the word 'Women's'.
5. We need all the bikes product names that start with A-H and whose list price is more than $299.99 or exactly $299.99. Sort them by product name. We will need to inventory those bikes first during inventory.

Instructions

• Start with a prayer to invite the spirit to help you with your studies.
• Create a MS Word document in your Group's Channel in MS Teams named Team Activity 04.
• Each person will lead the discussion to solve one question. Select a team member to begin. This team member will:
  1. Select a question to work on.
  2. Using MySQL Workbench, write a query to answer the selected question. This team members should help and discuss but the question leader types the query.
  3. When you are confident you have the correct answer. Record the following information in the Word document.
     1. Question #
     2. Team member name that lead the discussion for this question
     3. SQL query used to answer the questions
     4. Query results, either as a screen shot or table
  4. Select another team member to lead the discussion and repeat until all the questions are answered or you have spent 60 minutes.

Submission

• Submit the Word document you created in Teams to Canvas
• Complete the W04 Team Activity: Participation Report in Canvas

Useful Links:

• Next Activity: Single Table Queries
• Previous Activity: MySQL Workbench Errors
• Go to: Week Index • Course Home • Canvas