hey! write this code in a different way - but achieves same functionality From the…
- User
hey! write this code in a different way - but achieves same functionality
From the Salaries table, get the average base pay for "firefighters" (all job titles consisting of the word "firefighter" (not case sensitive)) between the year 2011 to 2013.
query = ''' SELECT AVG(basePay) as avg_basePay FROM Salaries WHERE jobTitle LIKE '%firefighter%' AND year BETWEEN 2011 AND 2013; '''
KEEP THIS. It will display the whole dataframe.
df = pd.read_sql(query, conn) df
- Assistant
Certainly! Here's a rewritten version of your SQL query that achieves the same functionality but is written differently. This version converts the
jobTitleto lowercase before performing theLIKEcomparison, ensuring case-insensitive matching.query = ''' SELECT AVG(basePay) as avg_basePay FROM Salaries WHERE LOWER(jobTitle) LIKE '%firefighter%' AND year >= 2011 AND year <= 2013; ''' # KEEP THIS. It will display the whole dataframe. df = pd.read_sql(query, conn) dfThis query uses the
LOWER()function to convert alljobTitleentries to lowercase, making the search for'firefighter'case-insensitive. Also, instead of usingBETWEEN, it usesyear >= 2011 AND year <= 2013for the year range, which is functionally the same but written differently.