Create a table with indicator variables

Determine whether medium and high priority requests in the evanston311 data are more likely to contain requesters' contact information: an email address or phone number.

Emails contain an @.
Phone numbers have the pattern of three characters, dash, three characters, dash, four characters. For example: 555-555-1212.

Use LIKE to match these patterns. Remember % matches any number of characters (even 0), and _ matches a single character. Enclosing a pattern in % (i.e. before and after your pattern) allows you to locate it within other text.

For example, '%___.com%'would allow you to search for a reference to a website with the top-level domain '.com' and at least three characters preceding it.

Create and store indicator variables for email and phone in a temporary table. LIKE produces True or False as a result, but casting a boolean (True or False) as an integer converts True to 1 and False to 0. This makes the values easier to summarize later.

This exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Hands-on interactive exercise

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

-- To clear table if it already exists
DROP TABLE IF EXISTS indicators;

-- Create the indicators temp table
___ ___ ___ ___ AS
  -- Select id
  SELECT id, 
         -- Create the email indicator (find @)
         CAST (description LIKE '___' AS integer) AS email,
         -- Create the phone indicator
         ___ (___ ___ '___' AS integer) AS phone 
    -- What table contains the data? 
    FROM ___;

-- Inspect the contents of the new temp table
SELECT *
  FROM indicators;

Edit and Run Code