Messy Student Records SQL Practice Dataset

select status, count(*) as rows
from enrollments
group by status
order by rows desc;

select e.course_code,
       length(e.course_code) as length,
       count(*) as rows
from enrollments e
left join courses c on c.course_code = e.course_code
where c.course_id is null
group by e.course_code
order by rows desc;

select student_id,
       school_id,
       '[' || first_name || ']' as first_name,
       '[' || last_name || ']' as last_name
from students
where first_name <> trim(first_name)
   or last_name <> trim(last_name)
   or first_name in (upper(first_name), lower(first_name))
   or last_name in (upper(last_name), lower(last_name))
order by student_id;

select school_id, score, count(*) as rows
from grades
where score is not null
  and try_cast(replace(trim(score), '%', '') as double) is null
group by school_id, score
order by rows desc;

select s.school_name,
       cast(regexp_extract(st.grade_level, '[0-9]+') as integer) as grade,
       count(*) as students
from students st
join schools s on s.school_id = st.school_id
group by s.school_name, grade
order by s.school_name, grade;

select case
         when lower(relationship) like '%grand%' then 'grandparent'
         when lower(relationship) like 'parent%' then 'parent'
         when lower(relationship) like '%guardian%' then 'guardian'
       end as relationship,
       count(distinct relationship) as spellings,
       count(*) as contacts
from contacts
group by 1
order by contacts desc;

select c.course_code, c.course_name, count(*) as enrollments
from enrollments e
join courses c
  on c.course_code = regexp_replace(upper(trim(e.course_code)), '^([A-Z]+)[ -]?([0-9]+)$', '\1-\2')
group by c.course_code, c.course_name
order by enrollments desc, c.course_code
limit 10;

select e.student_id, count(*) as enrollment_rows
from enrollments e
where not exists (
  select 1 from students s where s.student_id = e.student_id
)
group by e.student_id
order by e.student_id;

select school_id, student_id, course_code, term, enrolled_on, status,
       count(*) as copies,
       min(import_row) as first_import_row,
       max(import_row) as last_import_row
from enrollments
group by school_id, student_id, course_code, term, enrolled_on, status
having count(*) > 1
order by student_id, course_code;

select s.school_name,
       count(g.clean_score) as scored,
       count(*) - count(g.clean_score) as unusable,
       round(avg(g.clean_score), 1) as avg_score
from (
  select school_id,
         try_cast(replace(trim(score), '%', '') as double) as raw_score,
         case when raw_score between 0 and 100 then raw_score end as clean_score
  from grades
  where letter_grade is distinct from 'W'
) g
join schools s on s.school_id = g.school_id
group by s.school_name
order by s.school_name;

select phone,
       regexp_replace(
         regexp_replace(phone, '[^0-9]', '', 'g'),
         '^1([0-9]{10})$', '\1'
       ) as digits
from contacts
where regexp_matches(phone, '[0-9]')
order by length(phone) desc, phone;

select lower(email) as email,
       count(*) as registrations,
       count(distinct email) as spellings,
       string_agg(student_id::varchar, ', ' order by student_id) as student_ids
from students
where email like '%@%'
group by lower(email)
having count(*) > 1
order by email;

select st.student_id,
       s.school_name,
       st.birth_date,
       case
         when st.birth_date is null then 'missing'
         when not regexp_matches(st.birth_date, '[0-9]') then 'not a date'
         else 'impossible date'
       end as problem
from students st
join schools s on s.school_id = st.school_id
where case st.school_id
        when 1 then try_strptime(st.birth_date, '%Y-%m-%d')
        when 2 then try_strptime(st.birth_date, '%m/%d/%Y')
        when 3 then try_strptime(st.birth_date, '%d/%m/%Y')
      end is null
order by st.student_id;

with parsed as (
  select school_id,
         case school_id
           when 1 then try_strptime(enrolled_on, '%Y-%m-%d')
           when 2 then try_strptime(enrolled_on, '%m/%d/%Y')
           when 3 then try_strptime(enrolled_on, '%d/%m/%Y')
         end::date as enrolled_date,
         try_strptime(enrolled_on, '%m/%d/%Y')::date as read_as_us
  from enrollments
)
select school_id,
       count(*) filter (where enrolled_date is null) as unparsed,
       count(*) filter (
         where read_as_us is not null and read_as_us <> enrolled_date
       ) as wrong_if_read_as_us,
       min(enrolled_date) as first_date,
       max(enrolled_date) as last_date
from parsed
group by school_id
order by school_id;

with clean as (
  select student_id,
         school_id,
         lower(trim(first_name)) as first_name,
         lower(trim(last_name)) as last_name,
         case school_id
           when 1 then try_strptime(birth_date, '%Y-%m-%d')
           when 2 then try_strptime(birth_date, '%m/%d/%Y')
           when 3 then try_strptime(birth_date, '%d/%m/%Y')
         end::date as born
  from students
)
select a.student_id as first_id,
       a.school_id as first_school,
       b.student_id as second_id,
       b.school_id as second_school,
       a.first_name,
       a.last_name,
       a.born
from clean a
join clean b
  on b.first_name = a.first_name
 and b.last_name = a.last_name
 and b.born = a.born
 and b.school_id <> a.school_id
 and b.student_id > a.student_id
order by a.student_id;

with cleaned as (
  select import_row,
         student_id,
         regexp_replace(upper(trim(course_code)), '^([A-Z]+)[ -]?([0-9]+)$', '\1-\2') as course_code,
         case
           when upper(term) in ('FALL 2024', '2024-FA', 'FA24') then 'Fall 2024'
           when upper(term) in ('SPRING 2025', '2025-SP', 'SP25') then 'Spring 2025'
         end as term,
         case lower(trim(status))
           when 'w' then 'withdrawn'
           else lower(trim(status))
         end as status
  from enrollments
),
deduped as (
  select *
  from cleaned
  qualify row_number() over (
    partition by student_id, course_code, term
    order by import_row
  ) = 1
)
select term, status, count(*) as enrollments
from deduped
where student_id in (select student_id from students)
group by term, status
order by term, enrollments desc;

with scores as (
  select regexp_replace(upper(trim(course_code)), '^([A-Z]+)[ -]?([0-9]+)$', '\1-\2') as course_code,
         try_cast(replace(trim(score), '%', '') as double) as score
  from grades
)
select c.department,
       count(*) as graded,
       round(avg(s.score), 1) as avg_score,
       round(100.0 * count(*) filter (where s.score < 70) / count(*), 1) as pct_below_70
from scores s
join courses c on c.course_code = s.course_code
where s.score between 0 and 100
group by c.department
order by avg_score desc;

with codes as (
  select e.school_id,
         e.course_code in (select course_code from courses) as exact,
         regexp_replace(upper(trim(e.course_code)), '^([A-Z]+)[ -]?([0-9]+)$', '\1-\2') as clean_code
  from enrollments e
),
checked as (
  select *,
         clean_code in (select course_code from courses) as cleaned
  from codes
)
select s.school_name,
       count(*) as enrollments,
       count(*) filter (where exact) as exact_match,
       count(*) filter (where cleaned and not exact) as after_cleaning,
       count(*) filter (where not cleaned) as no_match,
       string_agg(distinct clean_code, ', ') filter (where not cleaned) as unmatched_codes
from checked
join schools s on s.school_id = checked.school_id
group by s.school_name
order by s.school_name;