SSP External Data Validation Queries Enhanced

Below are sample queries to validate external data loaded into SSP.  All queries produce row counts in order given in the file SSPValidationQueriesEnhanced.doc.  The majority of queries attempt to identify common problems such as:

  • Orphaned rows (rows that exist in the external data that can't be tied to a row in  external_person)
  • Values in tables that are not configured in SSP
  • Duplicate rows

SSPValidationQueriesEnhanced.doc | SSPValidationQueriesEnhanced.docx | SSPValidationQueriesEnhanced.odt | SSPValidationQueriesEnhanced.xls

 

A template that can be used: (the queries are below this template table in easy copy form)  Note:  to paste this into Google Drive Document, first paste into Google Sheet and then paste into Document

Table

Query

Description

Results

person

SELECT COUNT(*) FROM external_person

Row count

 

person

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null

Student  row count

 

person

SELECT COUNT(DISTINCT race_code) FROM external_person WHERE race_code NOT IN (SELECT code FROM race)

Find race codes in external person

not defined in race

 

person

SELECT COUNT(DISTINCT student_type_code) FROM external_person WHERE student_type_code NOT IN (SELECT code FROM student_type)

Find student type codes in external person

not defined in student_type

 

person

SELECT COUNT(DISTINCT marital_status) FROM external_person WHERE marital_status NOT IN (SELECT name FROM marital_status)

Find marital status names in external person

not defined in marital_status

 

person

SELECT COUNT(DISTINCT ethnicity) FROM external_person WHERE ethnicity NOT IN (SELECT name FROM ethnicity)

Find ethnicity names in external person

not defined in ethnicity

 

person

SELECT COUNT(DISTINCT actual_start_term) FROM external_person WHERE actual_start_term NOT IN (SELECT code FROM external_term)

Find term_codes not defined in external_term

 

academic_program

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_academic_program)

Number of students with a row in academic program

 

academic_program

SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program

Distinct number of students with an

academic program record

 

academic_program

SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Students defined in academic_program but not person

 

financial_aid

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_financial_aid)

Number of students with a row in financial_aid

 

financial_aid

SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid

Distinct number of students with a financial_aid record

 

financial_aid

SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Students defined in financial_aid but not person

 

test

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_test)

Number of students with a row in test

 

test

SELECT COUNT(DISTINCT school_id) FROM external_student_test

Distinct number of students with a test record

 

test

SELECT COUNT(DISTINCT school_id) FROM external_student_test WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Students defined in test but not person

 

transcript

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript)

Number of students with a row in transcript

 

transcript

SELECT COUNT(DISTINCT school_id) FROM external_student_transcript

Distinct number of students with an transcript record

 

transcript

SELECT COUNT(DISTINCT school_id) FROM external_student_transcript WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Students defined in transcript but not person

 

transcript_course

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_course)

Number of students with a row in transcript_course

 

transcript_course

SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Distinct number of students with a

transcript_course record

 

transcript_course

SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id NOT IN (SELECT school_id FROM external_person)

Students defined in transcript_course but not person

 

transcript_course

SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course

Distinct rows for faculty ids

 

transcript_course

SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person)

Faculty ids that don’t exist in external person

 

transcript_course

SELECT COUNT(DISTINCT status_code) FROM external_student_transcript_course WHERE status_code NOT IN (SELECT code FROM enrollment_status)

Find status_codes not defined in configuration

 

transcript_course

SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_course WHERE term_code NOT IN (SELECT code FROM external_term)

Find term_codes not defined in external_term

 

transcript_course

SELECT COUNT(*) FROM external_student_transcript_course WHERE grade IS null

Find the number of courses without a grade

 

transcript_term

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_term)

Number of students with a row in transcript_term

 

transcript_term

SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term

Distinct number of students with a

transcript_term record

 

transcript_term

SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Students defined in transcript_term but not person

 

transcript_term

SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_term WHERE term_code NOT IN (SELECT code FROM external_term)

Find term_codes not defined in external_term

 

registration_status_by_term

SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_registration_status_by_term)

Number of students with a row in

registration_status_by_term

 

registration_status_by_term

SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term

Distinct number of students with a

registration_status_by_term record

 

registration_status_by_term

SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)

Students defined in registration_status_by_term

but not person

 

registration_status_by_term

SELECT COUNT(DISTINCT term_code) FROM external_registration_status_by_term WHERE term_code NOT IN (SELECT code FROM external_term)

Find term_codes not defined in external_term

 

faculty_course

SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course)

Number of faculty members with a row

in faculty_course

 

faculty_course

SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course

Distinct number of faculty members with a

faculty_course record

 

faculty_course

SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person)

Faculty defined in faculty_course but not person

 

faculty_course

SELECT COUNT(DISTINCT term_code) FROM external_faculty_course WHERE term_code NOT IN (SELECT code FROM external_term)

Find term codes not defined in external_term

 

faculty_course_roster

SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course_roster)

Number of faculty members with a row in

faculty_course_roster

 

faculty_course_roster

SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster

Distinct number of faculty members with a

faculty_course_roster record

 

faculty_course_roster

SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person)

Faculty defined in faculty_course_roster

but not external_person

 

faculty_course_roster

SELECT COUNT(DISTINCT status_code) FROM external_faculty_course_roster WHERE status_code NOT IN (SELECT code FROM enrollment_status)

Find status code not defined in configuration

 

faculty_course_roster

SELECT COUNT(DISTINCT term_code) FROM external_faculty_course_roster WHERE term_code NOT IN (SELECT code FROM external_term)

Find term codes not defined in external_term

 

course

SELECT COUNT(*) FROM external_course

Count of courses

 

course

SELECT COUNT(DISTINCT formatted_course) FROM external_course

Count of distinct formatted courses

 

course

SELECT COUNT(DISTINCT division_code) FROM external_course WHERE division_code NOT IN (SELECT code FROM external_division)

Division codes not defined in external_division

 

course

SELECT COUNT(DISTINCT department_code) FROM external_course WHERE department_code NOT IN (SELECT code FROM external_department)

Department codes not defined in external_department

 

course

SELECT COUNT(DISTINCT code) FROM external_course WHERE code NOT IN (SELECT DISTINCT course_code FROM external_course_term)

Find courses that don’t have a term offering defined

 

course

SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite)

Find courses with requisite definitions

 

course

SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT course_code FROM external_course_program)

Find courses with course_program definitions

 

course_term

SELECT COUNT(DISTINCT term_code) FROM external_course_term WHERE term_code NOT IN (SELECT code FROM external_term)

Find term codes not defined in external_term

 

course_term

SELECT COUNT(DISTINCT course_code) FROM external_course_term WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course)

Find course_codes that don’t exist in external_course

 

course_program

SELECT COUNT(DISTINCT course_code) FROM external_course_program WHERE course_code NOT IN (SELECT code FROM external_course)

Find course_codes that don’t exist in external_course

 

course_program

SELECT COUNT(DISTINCT program_code) FROM external_course_program WHERE program_code NOT IN (SELECT DISTINCT code FROM external_program)

Find program_codes that don’t exist in external_program

 

course_requisite

SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite)

Find courses with course_requisite definitions

 

course_tag

SELECT COUNT(DISTINCT course_code) FROM external_course_tag WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course)

Find course_codes that don’t exist in external_course

 

course_tag

SELECT COUNT( distinct tag) FROM external_course_tag

WHERE tag NOT IN (SELECT code FROM tag)

 

Find tag codes that are not defined in tag

 
***Non Count based Queries

(must be run by manually)

Results

academic_program

 

SELECT school_id, COUNT(*) FROM external_student_academic_program GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Students in academic program

with more than one academic_program

record

 

financial_aid

SELECT school_id, COUNT(*) FROM external_student_financial_aid GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Students in academic program with

more than one financial_aid record

 

transcript

SELECT school_id, COUNT(*) FROM external_student_transcript GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Students in external_student_transcript

with more than one transcript record

 

transcript_term

SELECT school_id, term_code, COUNT(*) FROM external_student_transcript_term GROUP BY school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Students in transcript term with more

than one transcript_term record

 

transcript_term

SELECT term_code, COUNT (distinct school_id) FROM external_student_transcript_term GROUP BY term_code ORDER BY term_code;

Distinct number of students by term

with a transcript_term record

 

faculty_course

SELECT faculty_school_id, term_code, COUNT(*) FROM external_faculty_Course GROUP BY faculty_school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Faculty members in faculty_course

with more than one faculty_course record

 

faculty_course

SELECT faculty_school_id, term_code, formatted_course, section_code, COUNT(*) FROM external_faculty_Course GROUP BY faculty_school_id, term_code, formatted_course, section_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Find duplicate rows in

external_faculty_course

 

faculty_course

SELECT a.term_code, a.faculty_school_id, a.formatted_course, a.section_code FROM external_faculty_course AS a LEFT OUTER JOIN external_faculty_course_roster AS b

ON a.term_code = b.term_code AND a.faculty_school_id = b. faculty_school_id AND a.formatted_course = b.formatted_course AND a.section_code = b.section_code

WHERE b. term_code IS null AND b.faculty_school_id IS null AND b. formatted_course IS null AND b.section_code IS null;

Find courses in faculty_course

with no rows in faculty_course_roster

 

faculty_course_roster

SELECT faculty_school_id, term_code, COUNT(*) FROM external_faculty_course_roster GROUP BY faculty_school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*);

Faculty members with more than one

faculty_course_roster record

 

faculty_course_roster

SELECT distinct a.term_code, a.faculty_school_id, a.formatted_course, a.section_code FROM external_faculty_course_roster AS a LEFT OUTER JOIN external_faculty_course AS b

ON a.term_code = b.term_code AND a.faculty_school_id = b. faculty_school_id AND a.formatted_course = b.formatted_course AND a.section_code = b.section_code

WHERE b. term_code IS null AND b.faculty_school_id IS null AND b.formatted_course IS null AND b.section_code IS null;

Find courses in faculty_course_roster with no rows in faculty_course 

------------

 

The code block below is a compilation of the count queries in order from above:

SELECT COUNT(*) FROM external_person
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null
UNION ALL
SELECT COUNT(DISTINCT race_code) FROM external_person WHERE race_code NOT IN (SELECT code FROM race)
UNION ALL
SELECT COUNT(DISTINCT student_type_code) FROM external_person WHERE student_type_code NOT IN (SELECT code FROM student_type)
UNION ALL
SELECT COUNT(DISTINCT marital_status) FROM external_person WHERE marital_status NOT IN (SELECT name FROM marital_status)
UNION ALL
SELECT COUNT(DISTINCT ethnicity) FROM external_person WHERE ethnicity NOT IN (SELECT name FROM ethnicity)
UNION ALL
SELECT COUNT(DISTINCT actual_start_term) FROM external_person WHERE actual_start_term NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_academic_program)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_financial_aid)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_test)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_test
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_test WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN  (SELECT DISTINCT school_id FROM external_student_transcript)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_transcript
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_transcript WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_course)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id NOT IN (SELECT school_id FROM external_person)
UNION ALL
SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course
UNION ALL
SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person)
UNION ALL
SELECT COUNT(DISTINCT status_code) FROM external_student_transcript_course WHERE status_code NOT IN (SELECT code FROM enrollment_status)
UNION ALL
SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_course WHERE term_code NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(*) FROM external_student_transcript_course WHERE grade IS null
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_term)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_term WHERE term_code NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_registration_status_by_term)
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term
UNION ALL
SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null)
UNION ALL
SELECT COUNT(DISTINCT term_code) FROM external_registration_status_by_term WHERE term_code NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course)
UNION ALL
SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course
UNION ALL
SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person)
UNION ALL
SELECT COUNT(DISTINCT term_code) FROM external_faculty_course WHERE term_code NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course_roster)
UNION ALL
SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster
UNION ALL
SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person)
UNION ALL
SELECT COUNT(DISTINCT status_code) FROM external_faculty_course_roster WHERE status_code NOT IN (SELECT code FROM enrollment_status)
UNION ALL
SELECT COUNT(DISTINCT term_code) FROM external_faculty_course_roster WHERE term_code NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(*) FROM external_course
UNION ALL
SELECT COUNT(DISTINCT formatted_course) FROM external_course
UNION ALL
SELECT COUNT(DISTINCT division_code) FROM external_course WHERE division_code NOT IN (SELECT code FROM external_division)
UNION ALL
SELECT COUNT(DISTINCT department_code) FROM external_course WHERE department_code NOT IN (SELECT code FROM external_department)
UNION ALL
SELECT COUNT(DISTINCT code) FROM external_course WHERE code NOT IN (SELECT DISTINCT course_code FROM external_course_term)
UNION ALL
SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite)
UNION ALL
SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT course_code FROM external_course_program)
UNION ALL
SELECT COUNT(DISTINCT term_code) FROM external_course_term WHERE term_code NOT IN (SELECT code FROM external_term)
UNION ALL
SELECT COUNT(DISTINCT course_code) FROM external_course_term WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course)
UNION ALL
SELECT COUNT(DISTINCT course_code) FROM external_course_program WHERE course_code NOT IN (SELECT code FROM external_course)
UNION ALL
SELECT COUNT(DISTINCT program_code) FROM external_course_program WHERE program_code NOT IN (SELECT DISTINCT code FROM external_program)
UNION ALL
SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite)
UNION ALL
SELECT COUNT(DISTINCT course_code) FROM external_course_tag WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course)
UNION ALL
SELECT COUNT(DISTINCT tag) FROM external_course_tag WHERE tag NOT IN (SELECT code FROM tag);

 

The final code block below is all of the manual queries that still need to be run and analysed manually, organized in order from above:

 SELECT school_id, COUNT(*)
FROM external_student_academic_program
GROUP BY school_id
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT school_id, COUNT(*)
FROM external_student_financial_aid
GROUP BY school_id
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT school_id, COUNT(*)
FROM external_student_transcript
GROUP BY school_id
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT school_id, term_code, COUNT(*)
FROM external_student_transcript_term
GROUP BY school_id, term_code
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT term_code, COUNT (distinct school_id)
FROM external_student_transcript_term
GROUP BY term_code
ORDER BY term_code;

SELECT faculty_school_id, term_code, COUNT(*)
FROM external_faculty_Course
GROUP BY faculty_school_id, term_code
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT faculty_school_id, term_code, formatted_course, section_code, COUNT(*)
FROM external_faculty_Course
GROUP BY faculty_school_id, term_code, formatted_course, section_code
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT a.term_code, a.faculty_school_id, a.formatted_course, a.section_code
FROM external_faculty_course AS a
LEFT OUTER JOIN external_faculty_course_roster AS b
ON a.term_code = b.term_code AND
a.faculty_school_id = b. faculty_school_id AND
a.formatted_course = b.formatted_course AND
a.section_code = b.section_code
WHERE b. term_code is null AND
b.faculty_school_id is null AND
b. formatted_course is null AND
b.section_code IS null;

SELECT faculty_school_id, term_code, COUNT(*)
FROM external_faculty_course_roster
GROUP BY faculty_school_id, term_code
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*);

SELECT distinct a.term_code, a.faculty_school_id, a.formatted_course, a.section_code 
FROM external_faculty_course_roster AS a 
LEFT OUTER JOIN external_faculty_course AS b
ON a.term_code = b.term_code AND 
a.faculty_school_id = b. faculty_school_id AND 
a.formatted_course = b.formatted_course AND 
a.section_code = b.section_code
WHERE b. term_code IS null AND 
b.faculty_school_id IS null AND 
b.formatted_course IS null AND 
b.section_code IS null;