Week 3 exercises

Lab Questions

These lab questions are written to query the Bike Theft database. If you don't have dBeaver set up, you can still use the web-based phpMyAdmin interface Opens in a new window.

This week, I've made an effort to write the lab questions as just that - questions that need answers. Pay special attention to your formatting. Use aliases where aliases help readability. Show the columns that someone would need in order to make sense of the return table.

Remember - this is a table of bike thefts. That means 1 row = 1 theft.

Lab Questions:

  1. Write a statement that shows all the thefts that happened in neighbourhoods that have 'Humber' in the name.
  2. Narrow the list from question 1 down by only showing thefts that occurred in odd-numbered years.
  3. Get the average cost of the bikes recorded in this table. Format this result as a dollar figure, including a dollar sign, rounded to the nearest penny.
  4. Write a query that returns the number of unique combinations of bike colour & speed. Note: null is not a colour or a speed.
  5. Write a statement that shows a list of location types with more than 500 total thefts.
  6. Write a statement that returns the date of the first bike theft contained in this table. (Use an aggregate function for this.)
  7. Write a statement that returns only the top 10 neighbourhoods with the most reported thefts.
  8. Select 2 columns. The first column should be the sum of the 'Cost of Bike' column, divided by the total number of rows. The second column should be result of running the AVG() function on the 'Cost of Bike' column.
  9. In a code comment, tell me if first column and the second column of the previous question are equal. If they are, explain why. If they are not, explain what you could do to make the result of the AVG() function match the value of the first column.

BONUS QUESTION

  1. Write a statement that returns the top 5 neighbourhoods in terms of thefts, and a column that shows how many incidents they had above the average neighbourhood. This may be a good case for a subquery.