School Library SQL Queries – Survey Sample

Username, password, address file for survey login

Option 1: Pull all schools EVER included in the survey

In school>libraries> school_libraries

SELECT

a.id, a.name, a.username, a.password_plain, b.address, b.city, b.state, b.zip

FROM

`schools` a

JOIN

`addresses` b ON a.id = b.school_id

Option 2: Pull all only the schools in that year’s sample

In school>libraries> school_libraries_v2

SELECT * FROM survey_sample_2017 a LEFT JOIN addresses b ON a.id = b.school_id

Here is a screencast of option 2 in action

View the sample in a graphic interface

https://staff.lrs.org/tools/school-libraries?filters%5B1%5D%5Bcolumn%5D=survey_id&filters%5B1%5D%5Boperator%5D=%3D&filters%5B1%5D%5Bvalue%5D=2017

Survey Sample Calculation

In `school_libraries_v2`:

  • CDE staffing data uploaded to `cde_2017`.
    • This is a temporary table.
  • CDE address data uploaded to `cde_addresses_2017`.
    • This is a temporary table.
  • Identified new schools in CDE data, inserted them into `schools`.
    • Created usernames and passwords.
  • Identified new districts in CDE data, inserted them into `districts`.
  • Created school<->survey relationships for 2017 in `school_survey`.
  • (Re)created the 3 views from `school_libraries`:
    • survey_sample_2017
    • survey_sample_2017_courier
    • survey_sample_2017_no_courier

Queries used:

INSERT INTO school_survey (school_id, survey_id, created_at) SELECT school_code, ‘2017’, NOW() FROM cde_2017 WHERE school_code > 0 GROUP BY school_code ORDER BY school_code;

— Identify schools in CDE data & not in LRS data
— SELECT * FROM `cde_2017` a LEFT JOIN schools b ON a.school_code = b.id WHERE school_code > 0 AND b.id IS NULL GROUP BY a.school_code ORDER BY a.school_code;

— Insert new schools into LRS data
— INSERT INTO schools (id, name, district_id, district_name, created_at) SELECT a.school_code, a.school_name, a.district_code, a.district_name, NOW() FROM `cde_2017` a LEFT JOIN schools b ON a.school_code = b.id WHERE school_code > 0 AND b.id IS NULL GROUP BY a.school_code ORDER BY a.school_code;

— View new schools in LRS data
— SELECT * FROM schools WHERE DATE(created_at) = ‘2018-03-19’;

— Identify districts in CDE data & not in LRS data
— SELECT * FROM `cde_2017` a LEFT JOIN districts b ON a.district_code = b.id WHERE district_code > 0 AND b.id IS NULL GROUP BY a.district_code ORDER BY a.district_code;

— Insert new districts into LRS data
— INSERT INTO districts (id, name, created_at) SELECT a.district_code, a.district_name, NOW() FROM `cde_2017` a LEFT JOIN districts b ON a.district_code = b.id WHERE district_code > 0 AND b.id IS NULL GROUP BY a.district_code ORDER BY a.district_code;

— View new districts in LRS data
— SELECT * FROM districts WHERE DATE(created_at) = ‘2018-03-19’;

— 2017 survey sample
— select `a`.`id` AS `id`,`a`.`name` AS `school_name`,`a`.`district_id` AS `district_id`,`c`.`name` AS `district_name`,`a`.`courier_code` AS `courier_code`,`b`.`survey_id` AS `year`,`b`.`is_exception` AS `is_exception`,`b`.`notes` AS `notes`,`a`.`username` AS `username`,`a`.`password_plain` AS `password` from ((`school_libraries_v2`.`schools` `a` join `school_libraries_v2`.`school_survey` `b` on((`a`.`id` = `b`.`school_id`))) left join `school_libraries_v2`.`districts` `c` on((`a`.`district_id` = `c`.`id`))) where ((`b`.`survey_id` = 2017)) order by `a`.`name`;

— with courier
— select `a`.`id` AS `id`,`a`.`name` AS `school_name`,`a`.`district_id` AS `district_id`,`c`.`name` AS `district_name`,`a`.`courier_code` AS `courier_code`,`b`.`survey_id` AS `year`,`b`.`is_exception` AS `is_exception`,`b`.`notes` AS `notes`,`a`.`username` AS `username`,`a`.`password_plain` AS `password` from ((`school_libraries_v2`.`schools` `a` join `school_libraries_v2`.`school_survey` `b` on((`a`.`id` = `b`.`school_id`))) left join `school_libraries_v2`.`districts` `c` on((`a`.`district_id` = `c`.`id`))) where (`b`.`survey_id` = 2017) AND (`a`.`courier_code` != ”) order by `a`.`name`;

— without courier
— select `a`.`id` AS `school_id`,`a`.`school_name` AS `school_name`,`a`.`district_id` AS `district_id`,`a`.`district_name` AS `district_name`,`a`.`survey_id` AS `year`,`b`.`address` AS `school_address`,`b`.`city` AS `school_city`,`b`.`state` AS `school_state`,`b`.`zip` AS `school_zip`,`a`.`username` AS `username`,`a`.`password` AS `password` from (`school_libraries_v2`.`schools` `a` left join `school_libraries_v2`.`addresses` `b` on((`a`.`id` = `b`.`id`))) where (`a`.`courier_code` = ”) order by `a`.`school_name`;

— insert 2017 address data
— INSERT INTO addresses (school_id, survey_id, address, city, state, zip, created_at) SELECT school_id, ‘2017’, address, city, state, zip, NOW() FROM cde_addresses_2017;

Removing Jeff Co Schools

Table `school_survey` in `school_libraries_v2` stores the school<->survey relationships.  I removed JeffCO schools from that table.

— See JeffCO schools
— SELECT * FROM schools WHERE district_id = 1420
— Remove JeffCO schools from 2017 survey
— DELETE a FROM `school_survey` a JOIN schools b ON a.school_id = b.id WHERE b.district_id = 1420
— 283 schools removed from 2017 survey

The `survey_sample_*` tables update themselves accordingly, making the current 2017 sample size 1,008.

Scroll to Top