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.
Deze oefening maakt deel uit van de cursus
Exploratory Data Analysis in SQL
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
-- 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;