Exploring unstructured text
The description
column of evanston311
has the details of the inquiry, while the category
column groups inquiries into different types. How well does the category
capture what's in the description
?
LIKE
and ILIKE
queries will help you find relevant descriptions and categories. Remember that with LIKE
queries, you can include a %
on each side of a word to find values that contain the word. For example:
SELECT category
FROM evanston311
WHERE category LIKE '%Taxi%';
%
matches 0 or more characters.
Building up the query through the steps below, find inquires that mention trash or garbage in the description without trash or garbage being in the category. What are the most frequent categories for such inquiries?
This exercise is part of the course
Exploratory Data Analysis in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Count rows
SELECT ___
FROM evanston311
-- Where description includes trash or garbage
WHERE ___ ___ ___
___ ___ ___ ___;