Get startedGet started for free

Invalid violations with overnight parking restrictions

In the previous exercise, you identified parking_violation records with violation_time values that were outside of the restricted parking times. The query for identifying these records was restricted to violations that occurred at locations without overnight restrictions. A modified query can be constructed to capture invalid violation times that include overnight parking restrictions. The parking violations in the dataset satisfying this criteria will be identified in this exercise.

For example, this query will identify that a record with a from_hours_in_effect value of 10:00 PM, a to_hours_in_effect value of 10:00 AM, and a violation_time of 4:00 PM is an invalid record.

This exercise is part of the course

Cleaning Data in PostgreSQL Databases

View Course

Exercise instructions

  • Add a condition to the SELECT query that ensures the returned records contain a from_hours_in_effect value that is greater than the to_hours_in_effect value.
  • Add a condition that ensures the violation_time is less than the from_hours_in_effect.
  • Add a condition that ensures the violation_time is greater than the to_hours_in_effect.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT
  summons_number,
  violation_time,
  from_hours_in_effect,
  to_hours_in_effect
FROM
  parking_violation
WHERE
  -- Ensure from hours greater than to hours
  ___ ___ ___ AND
  -- Ensure violation_time less than from hours
  ___ ___ ___ AND
  -- Ensure violation_time greater than to hours
  ___ ___ ___;
Edit and Run Code