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
SELECT
query that ensures the returned records contain afrom_hours_in_effect
value that is greater than theto_hours_in_effect
value. - Add a condition that ensures the
violation_time
is less than thefrom_hours_in_effect
. - Add a condition that ensures the
violation_time
is 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
___ ___ ___;