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
Exercise instructions
- Add a condition to the
SELECTquery that ensures the returned records contain afrom_hours_in_effectvalue that is greater than theto_hours_in_effectvalue. - Add a condition that ensures the
violation_timeis less than thefrom_hours_in_effect. - Add a condition that ensures the
violation_timeis greater than theto_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
___ ___ ___;