Northern Arizona University
AC-1: Academic Courses

Office of Sustainability Data Analyst worked with Vice Provost for Academic Affairs Business Analyst, to produce SQL that will effectively yield all of NAU's sustainability course offerings for the desired fiscal years. We used AASHE's Suggested Keywords for the Sustainability Course and Research Inventories template and then removed false positives.

SQL:

SELECT CC.EFFDT, CC.CRSE_ID, CO.SUBJECT, CO.CATALOG_NBR,
CC.COURSE_TITLE_LONG AS COURSE_TITLE,
CC.DESCRLONG AS COURSE_DESCRIPTION
FROM PS_CRSE_CATALOG CC,
PS_CRSE_OFFER CO
WHERE CC.EFF_STATUS = 'A'
AND CC.EFFDT =
(SELECT MAX(CC_ED.EFFDT) FROM PS_CRSE_CATALOG CC_ED
WHERE CC.CRSE_ID = CC_ED.CRSE_ID)
AND CC.EFFDT = CO.EFFDT
AND CC.CRSE_ID = CO.CRSE_ID
/* Examples (leave these commented out & copy to use:
AND CC.DESCRLONG LIKE LOWER('%CIVILITY%') --This is case sensitive
AND DBMS_LOB.INSTR(CC.DESCRLONG, 'civility') > 0 --This is case sensitive
AND REGEXP_LIKE(CC.DESCRLONG, '2 hrs. lecture, 3 hrs. lab.', 'i') --This is case insensitive
*/
AND (
REGEXP_LIKE(CC.COURSE_TITLE_LONG, 'Accountable consumption', 'i')
OR REGEXP_LIKE(CC.COURSE_TITLE_LONG, 'Accountable production', 'i')
OR REGEXP_LIKE(CC.COURSE_TITLE_LONG, 'Clean energy', 'i')
OR REGEXP_LIKE(CC.COURSE_TITLE_LONG, 'Climate change, 'i')
OR REGEXP_LIKE(CC.COURSE_TITLE_LONG, 'Conserv', 'i')
)
ORDER BY CO.SUBJECT, CO.CATALOG_NBR;

--I have removed many of the keywords for brevity in this description--

Term codes used for the Report:
Spring 2020: 1201
Summer 2020: 1204
Fall 2020: 1207
Winter 2020: 1208
Spring 2021: 1211
Summer 2021: 1214
Fall 2021: 1217
Winter 2021: 1218
Spring 2022: 1221
Summer 2022: 1224
Fall 2022: 1227
Winter 2022: 1228
Spring 2023: 1231
Summer 2023: 1234
Fall 2023: 1237
Winter 2023: 1238

Note that SQL assumes a “wild card” on each end of the search string – i.e., “Sustain” will retrieve “Sustainable,” “Sustainability,” “Sustaining,” and “Sustains.” Also, the SQL is coded to ignore upper- and lower-case.

Step 2: Course titles and descriptions were reviewed. Duplicates were removed. Many courses are offered at different levels at NAU, and they are different courses, as they include different content and skills, depending on the level, even if the title is the same. For example, a course that is a 200-level, 400-level, and 500-level course is taught with three different course designs to cover the material for different audiences. Taking one of these courses by a student precludes the ability to take the course at a different level.

Below are course subjects without sustainability course offerings. This is only for NAU's reference for future improvement. The majority of the Academic Divisions missing sustainability concepts are schools of language. While sustainability courses could be incorporated, the current lack is sensical.

-Advertising
-Arabic
-Art history
-Asian Studies
-College of arts and letters
-Comparative cultural studies
-Chinese
-Frankie college of business
-Fitness
-First-year seminar
-Second-year seminar
-German
-Greek
-Italian
-Japanese
-Latin American studies
-Navajo
-National student exchange
-Russian