1 / 48

An Intro to Writing SQL Queries & Using the sqlReports Customization for PowerSchool

An Intro to Writing SQL Queries & Using the sqlReports Customization for PowerSchool . Chris A. McManigal Camden County Schools Kingsland, GA. GaETC App. Session Evaluation. Vendors. Please make sure to visit the Vendors Room in Kenyan ½ Vendors will be here all day Tuesday.

cwen
Download Presentation

An Intro to Writing SQL Queries & Using the sqlReports Customization for PowerSchool

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. An Intro to Writing SQL Queries & Using the sqlReports Customization for PowerSchool Chris A. McManigal Camden County Schools Kingsland, GA

  2. GaETC App

  3. Session Evaluation

  4. Vendors • Please make sure to visit the Vendors Room in Kenyan ½ • Vendors will be here all day Tuesday

  5. Overview • Basic database architecture • SQL Query Tools • SQL definitions and structure • SQL functions • What are sqlReports? • Downloading/Installing sqlReports • Creating an sqlReport • Importing/Exporting sqlReports • Q & A

  6. Database architecture

  7. Database table FIELDS (COLUMNS) RECORDS (ROWS)

  8. PowerSchool Tables/Guides • Data dictionary contains all tables and fields within each table (Data Dictionary Tables for PowerSchool 7.x) • Custom fields API guide provides functions to quickly access various tables’ custom fields (PS_CUSTOMFIELDS API Guide for PowerSchool 7.x)

  9. SQL Query Tools • Oracle comes with SQL *Plus • Command-line interface • Cannot edit and resubmit; must retype • Mainly used for scripted queries • Many GUI/IDE query tools • Show database layout (tables, views, columns, etc.) • Color coding of keywords and formatting for easier readability • Editing for trial-and-error querying • Display results on same page • Saving of SQL queries • Exporting of results in various formats (.xls, .txt, .xml, etc.) • Examples: Oracle SQL Developer, RazorSQL, Advanced Query Tool, Aqua Data Studio

  10. SQL Developer • Download from oracle.com • Must register • Must have Java Development Kit installed • SQL Developer comes with or without JDK • Database connection • Need DB server IP address • Need password for one of the PS users (ps, psnavigator, psdataminer) • No need to create separate ODBC connection

  11. What is SQL?? • Structured Query Language • Standard language structure used to access and manipulate relational databases • Many different “flavors” depending on type of database; Oracle uses PL/SQL

  12. Parts of the SQL query • SELECT statement • Declares the fields to be returned by the query • ‘*’ returns all fields • FROM clause • Defines the table being queried • Can contain JOIN clauses to query multiple tables • WHERE clause • Provides constraints on the records to be returned • GROUP BY clause • Eliminates duplication and provides for using aggregate functions • HAVING clause • Provides constraints on items in a GROUP BY clause, including aggregate functions • ORDER BY clause • Sorts the records that are returned

  13. Comparison Operators

  14. Logical Operators

  15. SELECT & FROM SELECT * FROM students SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students SELECTcourse_number, section_number FROM cc

  16. DISTINCT • Selects distinct rows from a table SELECT DISTINCT last_name FROMstudents SELECT DISTINCT course_number, course_name FROMstoredgrades

  17. WHERE SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHEREschoolid = 100 SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHERE schoolid= 100 AND gender = ‘F’

  18. WHERE (cont.) SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHEREschoolid= 100 ANDgender = ‘F’ AND(grade_level = 3 OR grade_level= 4 ORgrade_level = 5) SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHEREschoolid= 100 ANDgender = ‘F’ ANDgrade_levelIN(3,4,5) SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHERE schoolid= 100 ANDgender = ‘F’ ANDgrade_level >= 3 SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHERE schoolid= 100 ANDgender = ‘F’ ANDgrade_levelBETWEEN 3 AND 5

  19. ORDER BY SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHERE schoolid= 100 ANDgender = ‘F’ ANDgrade_levelIN (3,4,5) ORDER BYgrade_level SELECT schoolid, student_number, grade_level, gender, ethnicity FROM students WHERE schoolid= 100 ANDgender = ‘F’ ANDgrade_levelIN(3,4,5) ORDER BY grade_level,student_number

  20. LIKE & Wildcards • Wildcards • ‘%’ (percent) • substitute for one or more characters • ‘_’ (underscore) • Substitute for exactly one character SELECTcourse_number, course_name FROM courses WHEREcourse_numberLIKE‘00%’ SELECT course_number, course_name FROM courses WHEREcourse_numberLIKE ’_ _1%’ (note: underscores separated by space for clarity only; do NOT separate with space in query)

  21. SQL Aliases • Used for • Specifying the output column names • Abbreviating table names SELECTs.lastfirst “Student”, s.home_room “Teacher” FROM students s

  22. JOIN • Used to link multiple tables based on the relationship between certain columns (called “keys”) • JOIN notation • Explicit • Uses SQL keywords JOIN and ON SELECTs.lastfirst,sg.course_name, sg.grade FROM students s JOINstoredgradessgONsg.studentid = s.id • Implicit • Lists the tables separated by commas and the WHERE clause provides additional constraints SELECT s.lastfirst, sg.course_name, sg.grade FROM students s, storedgradessg WHERE s.id = sg.studentid

  23. JOIN Types • Types • INNER JOIN • Returns records when there is at least one match between tables • LEFT JOIN • Returns records from the left table regardless of matches in other table • RIGHT JOIN • Returns records from the right table regardless of matches in other table • FULL JOIN • Combines LEFT and RIGHT JOINs • Returns records for both tables regardless of matches in other table • Self-Join • Joins a table to itself

  24. INNER JOIN SELECThr.levelvalue, s.lastfirst, s.grade_level FROM honorrollhr JOIN students s ON s.id = hr.studentid WHEREhr.yearid = 20 ANDhr.storecode = ‘Q1’ ORDER BY hr.levelvalue, s.lastfirst SELECTs.schoolid, t.lastfirst, s.lastfirst FROM students s JOIN cc ONcc.studentid = s.id ANDcc.termid = 2000 AND cc.course_numberLIKE ‘00%’ ANDcc.expressionLIKE ‘1(%’ JOIN teachers t ONcc.teacherid = t.id WHEREs.enroll_status = 0 ORDER BY s.schoolid, t.lastfirst, s.lastfirst

  25. LEFT JOIN SELECTs.grade_level, s.lastfirst, hr.levelvalue FROM students s LEFT JOINhonorrollhrONs.id = hr.studentidAND hr.yearid= 20 ANDhr.storecode = ‘Q1’ ORDER BY s.grade_level, s.lastfirst SELECTs.grade_level, s.lastfirst, sg.percent FROM students s LEFT JOIN storedgradessgONsg.studentid = s.id AND sg.percent < 70 ANDsg.storecode = ‘Q1’ AND sg.termid = 2000 ANDsg.course_numberLIKE ‘271%’ WHEREs.enroll_status = 0 ANDs.schoolid = 100 ORDER BY s.grade_level, s.lastfirst

  26. SQL Built-in Functions • Allow manipulation of returned fields • Examples • To_char() • To_char(entrydate,’YYYYMMDD’) • Upper()/Lower() • Upper(last_name) • Decode() • Decode(grade_level,-1,PK,0,K,grade_level) • Many more • Oracle/PLSQL: Built-in Functions

  27. What are sqlReports? • Free customization for PowerSchool created by Dean Dahlvang (PS user in Minnesota) • Creates a new tab on the Reports page • Harnesses SQL to search multiple tables and create user-runnable reports • Allows importing and exporting for sharing among systems

  28. How do I get sqlReports? • Download Custom Reports Bundle from powerdatasolutions.org • Installation: • Copy the bundle into the custom web_root, or • Upload via CPM • Ensure customization is turned on • System  System Settings  Customization

  29. Creating an sqlReport • Start Page  System Reports  Custom SQL Reports

  30. Creating an sqlReport (cont.) • Click the “Create a new sqlReport” link

  31. Report Information

  32. Report Information (cont.)

  33. Query Section

  34. SQL Query select s.lastfirst, decode(s.schoolid,100,'MLGES',105,'SMMS',110,'SMES', 182,'CMS',187,'CRES',189,'MHES',192,'WES',193,'KES', 195,'DLRES',197,'SES',295,'CCHS',495,'MLCES'), decode(s.enroll_status,-1,'Pre-Registered', 0,'Active',2,'Transferred Out',3,'Graduated', 4,'Imported as Historical') from students s where s.last_namelike 'Smith%'

  35. Query Section (cont.) • Paste SQL query and click Build Header • Replace “Column X” with header names

  36. Student Selection

  37. Student Selection (cont.) • Check box and click Build Query • Insert DCID alias (if necessary) and remove Order By clause

  38. Parameters

  39. Parameters (cont.) • Replace comparison value with %paramX”, where X is the parameter number

  40. Parameters (cont.) • Enter display name and default value

  41. Examples

  42. Export a template • Right click export link to save report template as a text file

  43. Template Contents

  44. Import Template

  45. Import Template (cont.)

  46. Template Contents • Remove &nbsp;

  47. Examples

  48. Q & A

More Related