1 / 29

“If I can do it, ANYONE can do it.”

“If I can do it, ANYONE can do it.”. February 12, 2008 NCDPI 2008 Accountability Conference. Using MySQL in ABCTools. David J. Millush Testing & Accountability Coordinator Carteret County Public Schools.

les
Download Presentation

“If I can do it, ANYONE can do it.”

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. “If I can do it, ANYONE can do it.” February 12, 2008 NCDPI 2008 Accountability Conference UsingMySQL in ABCTools David J. Millush Testing & Accountability Coordinator Carteret County Public Schools

  2. The processes and procedures in this presentation have not been evaluated by the United States Food and Drug Administration or the North Carolina Department of Public Instruction and are not approved to diagnose, treat, cure, or prevent headaches in test coordinators. Although it has demonstrated some effectiveness in the elimination of bovine hoof fungus, the information provided in this presentation is for instructional purposes only and is not intended as a substitute for advice from your SQL programmer or other professional who actually knows what they are doing. You should consult with your RAC or other accountability professional before pulling your hair out, changing data, pretending you know what you are doing, or if you have or suspect you have figured out the North Carolina Accountability data collection and reporting process. Disclaimer

  3. It has nothing to do with birds. MySQL:

  4. Introduce the SQL elements needed for queries in ABCTools08 Demonstrate common SQL query structure Relieve fear of MySQL tab in ABCTools08 Encourage your experimentation with SQL objectives

  5. MySQL 5.1 Reference Manual http://dev.mysql.com/doc/refman/5.0/en/ W3Schools' Online SQL Tutorial http://www.w3schools.com/sql/ ABCTools 2008 Training Manual https://www.rep.dpi.state.nc.us/abctools08/ Accountability Database / Library https://www.rep.dpi.state.nc.us/adb/ (Table Format Browser) Key References

  6. Database management system Stores data in separate tables Uses Structured Query Language (SQL) MySQLis the database server software. SQLis the language usedto interact with MySQL. MySQL: What is it?

  7. Try it. It doesn’t hurt. You can’t break anything with a query. You can’t break anything with a query. Important RULES!!!

  8. Like your English Composition teacher: SQL requires good grammar; SQL requires proper syntax and punctuation; MySQL expects you to have well thought-out statements; SQL is easy to understand… when you pay attention; MySQL tells you when corrections are required; MySQL rewards you for your effort; SQL doesn’t require more from you than you can handle. Grammar counts

  9. Simple, but essential rules: Commas separate all fields / elements in a query list. Apostrophes enclose all field contents. Parentheses enclose statement groups. An asterisk ( * ) means “everything.” A percent sign ( % ) means “anything.” Keyboard math symbols are routinely used. Punctuation Basics

  10. Commas separate all fields and elements. For example: SELECT school_code, collection_code, student_id, or … and englrsncd not in ('E','G','J','P','Q','S','V‘) … Punctuation cont’d

  11. Apostrophes enclose all field contents For example: … WHERE (grade = '09' and ma08score < '321' and (compscore < '100' or compscore is null) … or … and englrsncd not in ('E’,‘G’,‘J’,‘P’,‘Q’,‘S’) … Punctuation cont’d

  12. Parentheses enclose statement groups For example: … or (grade = '09' and ma08score < '321' ) and (compscore < '100' or compscore is null) … or … WHERE ( (a.2006_ID = b.2005_ID) or ((a.2006_last = b.2005_last) and (a.2006_first = b.2005_first) and (a.2006_dob = b.2005_dob)) ) Punctuation cont’d

  13. An asterisk ( * ) means “everything.” For example: SELECT * from accdata where collection_code = ‘FDF’ A percent sign ( % ) means “anything.” For example: SELECT * from accdemo where last_name LIKE ‘%Hallor%’ Punctuation cont’d

  14. Keyboard math symbols are routinely used. For example: … L.reporting_year = '2007' and L.collection_code = 'FDS' and D.reporting_year = '2007' and D.collection_code != 'FDS' and H.test_date > '20060730' and H.test_date <= '20070801’ … Punctuation cont’d

  15. (SELECT) (FROM) (WHERE) (ORDER BY) Start with simple questions: What do you want reported? What table(s) contains the information? What restrictions need to apply? How should the results be sorted? Who? What? When?

  16. What SELECT statement starts routine queries SELECT determines what will be displayed SELECT is followed by the name of the fields you want displayed Must use the exact field names from the table The order of the SELECT statement creates the order fields are displayed in the output (report) Select =

  17. Start with SELECT and then add fields to be displayed Fields are separated by commas For example: SELECT school_code, collection_code, student_id Or SELECT * Select

  18. Where After SELECT statement, add FROM statement FROM identifies the table(s) where the desired data is recorded Multiple tables in the FROM statement creates a “inner join” of the tables… this is slightly more complex coding and requires specification with the SELECT, WHERE, and ORDER BY statements Must use the exact table names FROM =

  19. FROM follows immediately after the last field in the SELECT statement For example: SELECT school_code, collection_code, student_id, grade, dob FROM accdemo or SELECT L.collection_code, L.school_code, D.last_name, D.student_id, D.test_date FROM accdemo L, acchist D from

  20. If… kind of After FROM statement, add WHERE WHERE sets the limits or conditions on the data being queried WHERE subsets the data WHERE statements are field names with a qualifying indicator and the limits Add additional field names to the WHERE statement with ANDorOR WHERE =

  21. “Abouts” Plan your WHERE statements based on the results desired Group WHERE elements in parentheses Priority order of elements may alter the output 2 X 10 + .95 = 20.95 or 2(10 + .95) = 21.90 If the output is questionable, rethink your WHERE statement Where

  22. WHERE follows the FROM statement For example: SELECT school_code, collection_code, student_id, grade, dob FROM accdemo WHERE grade = ‘09’ AND school_code = ‘160312’ OR school_code = ‘160313’ Or… Where

  23. SELECT DISTINCT h.student_id, h.reporting_year, h.collection_code, h.school_code, h.last_name, h.first_name, h.grade, h.i_path, a.date_enter_hs, h.ctamrsncd, h.ctamscore, h.ma08rsncd, h.ma08score, h.ma08level, h.alg1rsncd, h.alg1score, h.alg1level,h.ctarrsncd, h.ctarscore, h.rd08rsncd, h.rd08score, h.rd08level, h.englrsncd, h.englscore, h.engllevel FROM histaudit h, accdemo a WHERE ((h.collection_code = a.collection_code) and h.collection_code = 'FDF') and (h.student_id = a.student_id) and (a.date_enter_hs is null or a.date_enter_hs < '20060616') and (h.i_path is null or (h.i_path != 'OCC'and h.i_path != 'GNRL')) and h.grade in ('9','10','11','12') and ((h.ctamscore is null or h.ctamscore != 'P') and (h.ma08score is null or h.ma08score < '321') and ((h.alg1score is null or (h.alg1score BETWEEN '21' and '50') or (h.alg1score BETWEEN '115' and '144')))) OR ((h.collection_code = a.collection_code) and h.collection_code = 'FDF') and (h.student_id = a.student_id) and (a.date_enter_hs is null or a.date_enter_hs < '20060616') and (h.i_path is null or (h.i_path != 'OCC' and h.i_path !='GNRL')) and h.grade in ('9','10','11','12') and ((h.ctarscore is null or h.ctarscore != 'P') and ((h.rd08score is null or h.rd08score < '254') and (h.ma08score is null or h.ma08score < '321')) and (h.englscore is null or (h.englscore BETWEEN '18' and '48') or (h.englscore BETWEEN '120' and '142'))) Where

  24. Sort • After WHERE statement, add ORDER BY • ORDER BY organizes data in the same manner as sorting data in an Excel spreadsheet • ORDER BY sort elements are field names • Use commas to separate ORDER BY elements • For example: • … FROM histaudit WHERE grade = 10 and i_path != 'GNRL' and ((engllevel is NULL and (englrsncd is NULL or englrsncd not in ('E','G','J','P','Q','S','V'))) or (alg1level is NULL and (alg1rsncd is NULL or alg1rsncd not in ('E','G','J','P','Q','S','V')))) and collection_code = 'FDF' ORDER BY school_code, last_name, first_name ORDER BY =

  25. IS NULLis not the same as zero • != means “not equal” • ‘09’ is not the same as ‘9’ • Use LIKE and %when you aren’t sure: • …WHERE last_name LIKE ‘%Hallor%’ • Write your queries in Notepad so you can see them. Copy and paste into ABCTOOLS08 MySQL tab to run. • Cut and paste from queries that work. • Use a scratch pad to outline your search criteria, then construct the SQL query. Run it and then modify until you get what you need. Nice-to-Knows

  26. SELECT DISTINCT student_id, reporting_year, collection_code, school_code, grade, last_name, first_name, cspfrsncd, cspfscore, cscmrsncd, cscmscore, comprsncd, complevel, i_path, ma08score, ma08rsncd FROM histaudit WHERE collection_code = 'NOV ' and (grade in ('9','09','10','11','12') and i_path is null or i_path != 'GNRL') and (complevel is null or complevel = '2') and ((cspfrsncd is null or (cspfrsncd != 'J' and cspfrsncd != 'K' and cspfrsncd != 'E' )) or (cscmrsncd is null or (cscmrsncd != 'J' and cscmrsncd != 'K' and cscmrsncd != 'E'))) ORDER BY school_code, grade, last_name, first_name Computer Skills failed or not taken

  27. “Test everything. Hold on to what is good.” 1 Thessalonians 5:21 David J. Millush Testing & Accountability Coordinator Carteret County Public Schools dmillush@co.carteret.k12.nc.us Phone: 252-728-4583 ext. 111

More Related