Data Validation Queries

queries.sql

-- Three levels of aggregates

-- group by 
-- - puid, date
-- - school_id, date
-- - school_id, puid, date


--------------------------------
--| GradDB_Basic Information
--------------------------------

-- Query to aggregate by school_id on STUDENTS table

select 
    school_id, 
    count(first_name),
    count(middle_name),
    count(last_name),
    count(suffix),
    count(maiden_name),
    count(other_name),
    count(citizenship),
    count(citizen_country),
    count(state_us_citizen_only),
    count(visa_type),
    count(classification),
    count(do_not_register),
    count(dist_learning),
    count(encumbered),
    count(us_veteran_status),
    count(hon_discharge),
    count(banner_pin),
    count(transfer_from),
    count(transfer_date),
    count(comments)
from students
group by school_id
order by school_id;

-- Query to aggregate by school_id on STUDENTS table

select 
    puid, 
    count(distinct first_name),
    count(middle_name),
    count(last_name),
    count(suffix),
    count(maiden_name),
    count(other_name),
    count(citizenship),
    count(citizen_country),
    count(state_us_citizen_only),
    count(visa_type),
    count(classification),
    count(do_not_register),
    count(dist_learning),
    count(encumbered),
    count(us_veteran_status),
    count(hon_discharge),
    count(banner_pin),
    count(transfer_from),
    count(transfer_date),
    count(comments)
from students
group by puid
order by puid;

--------------------------------
--| Statuses
--------------------------------

-- Query to aggregate by status per school on STUDENTS/STATUSES table
    
SELECT 
    s1.school_id, 
    s2.name,
    COUNT(*) AS student_count
FROM students s1
JOIN statuses s2
    ON s1.status_id = s2.status_id 
GROUP BY 
    s1.school_id, 
    s2.name
ORDER BY 
    s1.school_id, 
    s2.name;
    
-- Query to aggregate by (Active, Graduated) status per school on STUDENTS/STATUSES table
    
SELECT 
    s1.school_id, 
    s2.name,
    COUNT(*) AS student_count
FROM students s1
JOIN statuses s2
    ON s1.status_id = s2.status_id 
WHERE s2.name in ('Active', 'Graduated')
GROUP BY 
    s1.school_id, 
    s2.name
ORDER BY 
    s1.school_id, 
    s2.name;

--------------------------------
--| GradDB Courses Taught
--------------------------------
-- Query to aggregate per school on TA_COURSES table
select 
    school_id, 
    count(semester),
    count(designator),
    count(course_number),
    count(title)
from ta_courses
group by school_id
order by school_id;

-- Query to aggregate per PUID on TA_COURSES table
select 
    puid, 
    count(semester),
    count(designator),
    count(course_number),
    count(title)
from ta_courses t
join students s on
    t.student_id = s.student_id
    and t.school_id = s.school_id
group by puid
order by puid;

-- Query to aggregate per PUID/school on TA_COURSES table
select 
    puid, 
    s.school_id,
    count(semester),
    count(designator),
    count(course_number),
    count(title)
from ta_courses t
join students s on
    t.student_id = s.student_id
    and t.school_id = s.school_id
group by puid, s.school_id
order by puid, s.school_id;

-- Query to aggregate total ta courses records per school
select school_id, count(*)
from ta_courses
group by school_id;