1. Learn
    2. /
  2. Courses
    3. /
  3. Cleaning Data in PostgreSQL Databases

Exercise

Selecting data for a pivot table

In an effort to get a better understanding of which agencies are responsible for different types of parking violations, you have been tasked with creating a report providing these details. This report will focus on four issuing agencies: Police Department (P), Department of Sanitation (S), Parks Department (K), and Department of Transportation (V). All of the records required to create such a report are present in the parking_violations table. An INTEGER violation_code and CHAR issuing_agency is recorded for every parking_violation.

In this exercise, you will write a SELECT query that provides the underlying data for your report: the parking violation code, the issuing agency code, and the total number of records with each pair of values.

Instructions

100 XP
  • Include violation_code and issuing_agency in the SELECT list for the query.
  • For each violation_code and issuing_agency pair, include the number of records containing the pair in the SELECT list.
  • Restrict the results to the agencies of interest based on their single-character code (P, S, K, V).