Here are SQL queries to extract various kinds of data from PowerSchool. They have been tested with SQL Developer version 126.96.36.199 and PowerSchool 11. The queries presume a basic knowledge of SQL syntax. I have made an effort to explain anything that goes beyond the basic use of the SQL SELECT, FROM, WHERE, and ORDER BY clauses. The explanations will help you understand how to modify these queries and create your own. If you are not an employee of Smyth County Public Schools, I recommend you obtain a copy of the PowerSchool data dictionary from Pearson to help you with PowerSchool column names and table relationships.
To use these queries, you need Oracle's free SQL Developer software installed on your computer. Please contact me or your school's ITRT or technician in order to get SQL Developer. Once you get it installed and configured, you can use any of the following queries by just copying and pasting them into SQL Developer. The queries are highlighted in light yellow.
Students By Homeroom | Birthdays by Month | Alexandria Library System Extract | iStation Import | Students With No Class Assignments | Renaissance Place Import | Google Import File | Student Attendance Listing | Student Import File for Cafeteria Point of Sale Program | Grades From PowerTeacher Gradebook | Student Test Scores | Student Attendance Code Count | Homeroom Counts by Gender | Student Class Rank by Course | English Sections for Alexandria Import | Total Students Enrolled in Specific Courses
This query produces a list of students sorted by classroom teacher. School photographers often request this information. In PowerSchool, students are not assigned directly to teachers. Instead we create class sections, which are stored in the Sections table. Teachers and students are then assigned to these sections. Because students are enrolled in more than one section, and a section contains more than one student, we have an additional table that is used to record student enrollments. It is called the CC (current classes) table. It contains studentids and sectionids, plus the date the student enrolled in the section and the date the student left the section. The following query joins the PowerSchool Students, CC, Sections, and Teachers tables. The Oracle DECODE function is used to map PowerSchool's PK and KG grade levels, -1 and 0, to PK and KG. To adapt this query to your school, change the st.schoolid criterion to your school's three-digit number. Note: For SGES, use schoolid = 530, not 555.
The s.course_number criterion in the query limits the results to students enrolled in the homeroom course, 0001. You can change this to retrieve student enrollments for other courses. PowerSchool stores course numbers as text, not actual numbers, so you must enclose the course number in single quotes. If you need enrollments for a school year other than 2014-2015, change the s.termid criterion. The following table shows how PowerSchool termids are mapped to school years.
|School Year||PowerSchool TermID||First Semester||Second Semester|
|2021-…||Add 100 to the previous year's TermIDs|
SELECT st.student_number, st.lastfirst AS Student, DECODE(st.grade_level,-1,'PK',0,'KG',st.grade_level) AS Grade, t.lastfirst AS Homeroom FROM students st,cc,sections s,teachers t WHERE st.id = cc.studentid AND cc.sectionid = s.id AND s.teacher = t.id AND st.schoolid = 290 --Change this to your school number. AND s.termid = 2400 --This is for 2014-2015 school year. AND st.enroll_status = 0 --actively enrolled = 0, pre-registered = -1, transferred out = 2, graduated = 3 AND s.course_number = '0001' --This is homeroom course number. ORDER BY st.grade_level,t.lastfirst,st.lastfirst; --If you export as CSV, specify left and right enclosures because student and teacher names contain commas
This query returns a list of students who have birthdays in the same month. The students will be sorted by birthdate, then name. The EXTRACT function extracts the month from the PowerSchool DOB (date of birth) field, so in this example, students who were born in month 12 will be listed.
SELECT first_name, middle_name, last_name, DOB AS Date_of_Birth FROM students WHERE schoolid = 680 --Change to your school number. AND enroll_status = 0 AND EXTRACT(month from dob) = 12 --Change to the appropriate month. Jan = 1, Feb = 2, etc. ORDER BY TO_CHAR(DOB,'dd'),lastfirst;
This query joins the PowerSchool Students, CC, Sections, and Teachers tables, in order to build data for an Alexandria library system import file. The query presumes that the student's PowerSchool student_number will be used for the Alexandria student ID, password, and barcode. It uses the Oracle SQL Decode function to map PowerSchool's PK and KG grade levels (-1 and 0) to PK and KG. To modify the query for different schools, just change the students.schoolid parameter; for example, to select Chilhowie Elementary students, use students.schoolid = 730.
Note: The || in line 11 is the Oracle SQL concatentation character. In this example, it concatenates the teacher's first name to a space (' ') followed by the teacher's last name.
SELECT student_number, student_number AS PASSWORD, student_number AS BARCODE, students.last_name, students.first_name, students.middle_name, students.gender AS SEX, DECODE(students.grade_level,-1,'PK',0,'KG',students.grade_level) AS GRADE, students.mother AS PARENT, students.home_phone AS PHONE, teachers.first_name || ' ' || teachers.last_name AS HOMEROOM, students.street AS ADDRESS, students.city AS CITY, students.state AS STATE, students.zip AS POSTALCODE FROM students,cc,sections,teachers WHERE students.id = cc.studentid AND cc.sectionid = sections.id AND sections.teacher = teachers.id AND students.schoolid = 290 AND students.enroll_status = 0 AND sections.termid = 2400 AND sections.course_number = '0001' ORDER BY students.lastfirst;
This query uses Oracle's Decode function to map schoolid numbers to iStation's school names. It also associates students with their reading class, through the use of the sections.course_number LIKE 'LA%' parameter. Students who are not enrolled in a language arts class won't be included in the query.
SELECT students.first_name, students.last_name, students.student_number, students.grade_level, teachers.first_name, teachers.last_name, teachers.email_addr, teachers.teachernumber, DECODE(students.schoolid,290,'atkins.smyth.va',530,'sugargrove.smyth.va',690,'marionis.smyth.va', 710,'marionprimary.smyth.va',720,'richvalley.smyth.va',730,'chilhowie.smyth.va', 740,'saltville.smyth.va',students.schoolid) CAMPUS_CODE FROM students,cc,sections,teachers WHERE students.id = cc.studentid AND cc.sectionid = sections.id AND sections.teacher = teachers.id AND enroll_status = 0 AND sections.termid = 2400 AND sections.course_number LIKE 'LA%' AND students.GRADE_LEVEL BETWEEN -1 and 5 ORDER BY CAMPUS_CODE,students.last_name,students.first_name;
This query will find any students who are active in PowerSchool (students.enroll_status = 0), but have not been assigned to classes. It accomplishes this by creating a subquery containing all student enrollments for the current school and year. This subquery joins the class enrollments table (CC) to the sections table (SECTIONS), and filters that query by schoolid and termid. The PowerSchool termid is a four-digit number that represents a year-long term or a shorter term, such as a semester. The 2014-2015 school year is represented by termid 2400. The 2015-2016 school year will be represented by 2500. The first semester of the 2014-2015 school year will be 2401 and the second semester will be 2402. The sections.schoolid value in the subquery must match the students.schoolid value in the main query. The sections.course_name parameter in the subquery in this example will match any course number, but you could change it to match specific courses or courses containing specific characters. For example, to find any elementary students who have not been assigned to a reading course, use
sections.course_number LIKE 'LA%' That parameter will find anyone who is not enrolled in LA01,LA02,LA03,LA04,LA05, or LAKG, which are the elementary reading courses.
After the subquery collects all of the students' class enrollments for the specified school and term, and optionally, a course, the NOT EXISTS clause finds any currently enrolled students who do not appear in those records. This query is a fast and efficient way to find students who may have been no-shows, but have not been withdrawn from school.
SELECT students.student_number,students.last_name,students.first_name,students.grade_level FROM students WHERE students.grade_level > -1 AND students.schoolid = 690 AND students.enroll_status = 0 AND NOT EXISTS (SELECT 'x' FROM cc,sections WHERE students.id = cc.studentid AND cc.sectionid = sections.id AND sections.termid = 2400 AND sections.course_number LIKE '%' AND sections.schoolid = 690);
This query creates an import file for Renaissance Place applications such as Accelerated Reader, Star Reading/Math, etc. Please refer to the Renaissance Place Data Translator documentation before you run the actual import.
The TO_CHAR function in line 6 takes a data value and formats it according to the specification represented by the second argument in the TO_CHAR function. This example results in dates formatted like this: 07/04/2014. The formatting options are quite extensive. Google "oracle to_char format mask" for more information.
SELECT students.student_number SID, students.first_name SFIRST, students.middle_name SMIDDLE, students.last_name SLAST, students.gender SGENDER, TO_CHAR(students.dob,'MM/DD/YYYY') SBIRTHDAY, DECODE(students.grade_level,0,'K',students.grade_level) SGRADE, LOWER(students.first_name || students.last_name) SUSERNAME, students.student_number SPASSWORD FROM STUDENTS WHERE students.grade_level BETWEEN 6 AND 8 AND enroll_status = 0 AND schoolid = 851 ORDER BY students.lastfirst;
This query creates a CSV file used to create multiple student accounts in Google Apps for Education. There are only four columns: First Name, Last Name, Email Address, and Password. Our Google organizational structure includes an organizational unit (OU) for students. The Students OU has three sub OUs, which correspond to elementary, middle, and high schools. We use those sub OUs to mass-assign collections of Chromebook apps to computers. Below those sub OUs are additional grade-level OUs. Those OUs are named for the students' graduation year: 2015, 2016, etc. When students graduate, we give them a window of time to move any Google documents they wish to keep to another location, then we delete all of the accounts in that OU. To help identify student accounts, we precede each student's first name with the year of graduation. The Decode statement in the first line of the query matches the students' 2014-2015 grade levels with their graduation years.
Generating the students' email addresses is the most complicated part of the query. The addresses have to be unique within our Google domain. I couldn't be certain that using first name plus last name would generate unique addresses. I wanted something easy for the students to remember, so I settled on a combination of the students' names and birthdays; specifically, the first three letters of their first names, followed by their last names, followed by their birthdays in mmdd format. Because students' names may contain characters that are invalid in an email address, I use the Oracle REGEX_REPLACE function to remove everything but alphanumeric characters from the names. The first use of REGEXP_REPLACE removes any non-alphanumeric characters from the student's first name:
The REGEXP_REPLACE function's second argument specifies a regular expression search string: '[^a-zA-Z0-9]'. This will match any non-alphanumeric character. The ^ operator negates the text that follows it, so this expression means find anything that is not included in a-z, A-Z, or 0-9. (I included 0-9 in the expression to deal with any instances of students having numbers in their names. The day may come when someone names a child after guitarist John5.)
The first instance of the REGEX_REPLACE function is nested inside an Oracle SUBSTR function. SUBSTR carves a string of characters out of a value, in this case the student's first name after it has been stripped of non-alphanumeric characters. The SUBSTR's second argument specifies the starting position of the substring, 1 or the first letter of the name in this case, and the third argument specifies the length of the substring, 3. The || is the Oracle concatenation character. I use the REGEXP_REPLACE a second time to strip any non-alphanumeric characters out of the student's last name, then concatent the result to the student's birthday in mmdd format. The Oracle TO_CHAR function takes a value, in this case the student's date of birth (dob), and converts it to a string according to the format specified as the TO_CHAR function's second argument. The final touch is to use Oracle's LOWER function to lower-case everything, then concatent it to 'scsb.org', our Google domain.
To better understand how this works, consider the example of a student named D'Angelo Smith Jr. born on July 4:
SELECT DECODE(grade_level,12,'15',11,'16',10,'17',9,'18',8,'19',7,'20',6,'21',5,'22',4,'23') || first_name First_Name, last_name Last_Name, LOWER(SUBSTR(REGEXP_REPLACE(first_name,'[^a-zA-Z0-9]'),1,3) || REGEXP_REPLACE(last_name,'[^a-zA-Z0-9]') || TO_CHAR(dob,'MM') || TO_CHAR(dob,'DD')) || '@scsb.org' Email_Address, student_number || '-' || TO_CHAR(dob,'MM') || TO_CHAR(dob,'DD') Password FROM STUDENTS WHERE enroll_status = 0 AND grade_level = 6 ORDER BY students.schoolid,students.lastfirst;
This example produces a listing of a single student's absences by school, sorted in descending order by attendance date. Replace the s.last_name and s.first_name placeholders with the last and first names of the appropriate student and modify the a.att_date as necessary. The a.att_date is the attendance date field in the attendance table. It represents the earliest date for which you want to retrieve attendance data. For example, if you want all attendance for the current school year, put the first day of the school year in the a.at_date placeholder.
This query comes in handy when you have a student who has transferred to your school from another in-county school during the school year. When you look at a student's attendance record in the PowerSchool attendance screen, you only see attendance records from the student's current school. This query will let you see all of a student's attendance records, regardless of the school in which the student was enrolled.
This is a fairly complicated query. It is not necessary to understand how it works in order to use it. If you want to understand how it works in case you want to modify it or build upon your knowledge of how to extract data from PowerSchool's tables, you need some knowledge of how PowerSchool stores attendance data. Attendance codes such as E and U are stored in a table called attendance_code, which I abbreviate as "ac" in this query. The ac_attendance code table stores the attendance codes, the schools that use these codes, and a longer description of what each codes means; e.g., PX = Parent Excused. Student attendance records themselves are stored in a table called attendance, which I abbreviate as "a" in this query. The attendance tables contains the student ID, the attendance date, the attendance code, and the attendance mode code (daily or period). For this query, we are only interested in daily attendance (ATT_ModeDaily). Finally, attendance records that involve a check-in or check-out time, such as tardies, have a related record in a table called attendance_time, which I abbreviate as "at" in this query. It is important to realize that:
The interesting parts of this query are the two pairs of FLOOR and DECODE functions that output the time-in and time-out values. The attendance_time.time_in and time_out values are the student's check in and check out times, measured in the number of seconds that have elapsed since midnight. To convert this value to hh:mm format, which makes it human-readable, I divide the number of seconds by 3600 (number of seconds in an hour) and pass the result to the FLOOR function. FLOOR truncates the fractional part of any number, so FLOOR(8.75)=8. That gives me the hour value. To get the number of minutes past the hour, I perform three functions:
SELECT sch.name AS School, s.lastfirst, a.att_date, ac.att_code, ac.description, a.att_comment, FLOOR(at.time_in / 3600) || ':' || DECODE(SIGN(10 - MOD(at.time_in,3600) / 60),1, '0' || TO_CHAR(MOD(at.time_in,3600) / 60),MOD(at.time_in,3600) / 60) AS TimeIn, FLOOR(at.time_out / 3600) || ':' || DECODE(SIGN(10 - MOD(at.time_out,3600) / 60),1, '0' || TO_CHAR(MOD(at.time_out,3600) / 60),MOD(at.time_out,3600) / 60) AS TimeOut FROM attendance_code ac,students s,schools sch, attendance a LEFT OUTER JOIN attendance_time at ON a.id = at.attendanceid WHERE a.attendance_codeid = ac.id AND a.studentid = s.id AND a.schoolid = sch.school_number AND ac.att_code IS NOT NULL AND a.att_mode_code = 'ATT_ModeDaily' AND s.last_name = 'Replace with student last name' AND s.first_name = 'Replace with student first name' AND a.att_date >= 'Replace with date using mm/dd/yyyy format' ORDER BY a.att_date DESC;
This query produces a student import file for our cafeteria point of sale program. It has three unusual features:
SELECT student_number AS StudentID, student_number AS PIN, first_name, last_name, DECODE(schoolid,851,460,schoolid) AS School, grade_level AS Grade, DECODE(street,'','No Address',REGEXP_REPLACE(street,'[^a-zA-Z0-9 ]')) AS Address, city, state, zip, home_phone, DECODE(SIGN(enroll_status),0,'T',-1,'T',1,'F') AS Enroll, dob FROM students WHERE grade_level < 13 ORDER BY last_name;
Use this query when you receive a student from another school and the transferring school forgets to send a copy of the student's assignment grades.
SELECT PGA.assignmentid, PGA.dcid, PGA.id, percent, score, grade, students.lastfirst STUDENT_NAME, teachers.lastfirst TEACHER_NAME, sections.course_number, PGA.Description,PGA.name, PGA.datedue, PGA.abbreviation, sections.termid FROM SectionScoresAssignments SSA,SectionScoresID SSI,PGAssignments PGA,Students,Teachers,Sections WHERE SSA.FDCID = SSI.DCID AND SSA.assignment = PGA.dcid AND SSI.studentid = students.id AND SSI.sectionid = PGA.sectionid AND PGA.sectionid = sections.id AND sections.teacher = teachers.id AND students.lastfirst = 'put students lastfirst here' AND sections.termid = 2400 ORDER BY course_number;
Information for student test scores is stored in five separate tables, as shown in the entity-relationship diagram below. The SQL example that follows the diagram retrieves all student SAT scores taken between July 1, 2013 and June 30, 2014. Alter the dates and test name if you wish to display different results.
SELECT schools.name AS School, s.lastfirst AS Student, t.name AS Test_Name, st.test_date, ts.description AS Score_Description, sts.numscore AS Score FROM students s,studenttestscore sts,studenttest st,test t,testscore ts,schools WHERE s.id = sts.studentid AND sts.studenttestid = st.id AND sts.testscoreid = ts.id AND ts.testid = t.id AND s.schoolid = schools.school_number AND t.name = 'SAT' --Replace 'SAT' with the name of the test you wish to display. AND sts.numscore > 0 AND st.test_date BETWEEN '07/01/2013' AND '06/30/2014' ORDER BY s.lastfirst,st.test_date DESC;
The following table contains a list of valid test names stored in PowerSchool as of November 19, 2014.
Tip: To obtain a list of all AP scores, replace
t.name = 'test name' with
t.name LIKE 'AP%'.
|AP US History|
|AP European History|
|AP English Language|
|AP Physics C Electricity Magnetism|
|AP Physics C Mechanics|
|AP Comp Government Politics|
|AP English Literature|
|AP Calculus AB|
|AP Environmental Science|
|SAT Subject U.S. History|
|SAT Subject French|
|SAT Subject Spanish|
|AP Art History|
|AP Latin: Virgil|
|AP Physics B|
|AP Calculus BC|
|AP US Government & Politics|
|AP World History|
|SAT Subject Literature|
|SAT Subject Mathematics Level 1|
|SAT Subject Mathematics Level 2|
|SAT Subject Chemistry|
|SAT Subject Biology E/M|
|SAT Subject Physics|
|SAT Subject Latin|
|SAT Subject World History|
|AP Human Geography|
|AP Computer Science A|
|AP Music Theory|
|AP Spanish Language and Culture|
This query can be used to identify students who have exhausted their absences that were counted as excused because of a parent note. It will list students by homeroom and name, and find any students who have more than 10 E (Excused) or PX (Parent Excused Absences).
SELECT st.student_number, st.lastfirst AS Student, -- DECODE(st.grade_level,-1,'PK',0,'KG',st.grade_level) AS Grade, t.lastfirst AS Homeroom, COUNT(a.id) AS Total_Absences FROM students st,cc,sections s,teachers t,attendance_code ac, attendance a LEFT OUTER JOIN attendance_time at ON a.id = at.attendanceid WHERE a.attendance_codeid = ac.id AND a.studentid = st.id AND st.id = cc.studentid AND cc.sectionid = s.id AND s.teacher = t.id AND st.schoolid = 730 --Change this to your school number. AND s.termid = 2400 --This is for 2014-2015 school year. AND st.enroll_status = 0 --actively enrolled = 0, pre-registered = -1, transferred out = 2, graduated = 3 AND s.course_number = '0001' --This is homeroom course number. --To search for additional codes, insert them within the parentheses in the following line. --Make sure that each code is enclosed within single quotes and that each code is separated by a comma. AND ac.att_code IN ('PX') AND a.att_mode_code = 'ATT_ModeDaily' --Setting this value to ATT_ModeDaily excludes period attendance records. AND a.att_date >= '08/20/2014' --This date should be the first day of the current school year. GROUP BY st.student_number,st.lastfirst,t.lastfirst HAVING COUNT(a.id) > 10 --This finds any student with more than 10 of the types of absences listed in the ac.att_code criteria. ORDER BY t.lastfirst,st.lastfirst; --If you export as CSV, specify left and right enclosures because student and teacher names contain commas
This query returns the count of students in each homeroom grouped by gender. The sort is by grade level, then by homeroom teacher. This query will fail if you don't have students assigned to a homeroom.
SELECT DECODE(s.grade_level,-1,'PK',0,'KG',s.grade_level) AS Grade, t.lastfirst AS Homeroom, DECODE(st.gender,'F','Girls','M','Boys') AS Student_Gender, COUNT(st.id) AS Total_Students FROM students st,cc,sections s,teachers t WHERE st.id = cc.studentid AND cc.sectionid = s.id AND s.teacher = t.id AND st.schoolid = 710 --Change this to your school number. AND s.termid = 2400 --This is for 2014-2015 school year. AND st.enroll_status = 0 --actively enrolled = 0, pre-registered = -1, transferred out = 2, graduated = 3 AND s.course_number = '0001' --This is homeroom course number. GROUP BY s.grade_level,t.lastfirst,st.gender ORDER BY s.grade_level,t.lastfirst,st.gender DESC;
This query returns a list of students ordered by their class rank in a specific course at a specific school. It is useful for identifying students who may be eligible for enrichment classes.
SELECT st.lastfirst AS Student, p.finalgradename, p.grade, p.percent, s.course_number, t.lastfirst AS Teacher FROM PGFinalGrades p,sections s,cc,students st,teachers t WHERE p.sectionid = s.id AND s.id = cc.sectionid AND p.studentid = cc.studentid AND st.id = cc.studentid AND s.teacher = t.id AND s.course_number = 'HIST05' --Replace with appropriate course number AND s.schoolid = 740 -- replace with your school number AND p.finalgradename IN ('Y1') -- replace final grade name as appropriate AND p.grade <> '--' AND s.termid >= 2400 -- replace with 2500 for 2015-2016, 2600 for 2016-2017, etc. ORDER By p.percent DESC,st.lastfirst;
This query returns a list of students with their English class as a field titled Location. It is used for creating Alexandria library automation system import files.
SELECT SUBSTR(t.first_name,1,1) || t.last_name || '-' || terms.abbreviation || '-Period ' || SUBSTR(s.expression,1,1) AS Location, st.last_name as LN, st.first_name as FN, st.student_number as StudentNumber, st.student_number as Barcode, st.grade_level, st.gender FROM students st, sections s, cc, teachers t, terms, courses c WHERE st.id = cc.studentid AND cc.sectionid = s.id AND s.teacher = t.id AND s.termid = terms.id AND s.schoolid = terms.schoolid AND s.course_number = c.course_number AND s.termid >= 2500 AND st.enroll_status = 0 AND st.schoolid = 250 AND c.course_name LIKE '%English%' ORDER by st.lastfirst;
This query returns the total number of students enrolled in courses that match a specific name pattern.
SELECT sections.course_number, course_name, count(cc.id) AS Total_MS_Enrollment FROM students, cc, sections, courses WHERE students.id = cc.studentid AND cc.sectionid = sections.id AND sections.course_number = courses.course_number AND students.enroll_status = 0 AND cc.termid >=2700 --termids between 2700 and 2799 represent the 2017-2018 school year. Decrement by 100 to go back a year; increment by 100 to go forward a year. AND courses.course_name LIKE '%Algeb%' --Change this value to get enrollment in other courses. It is case sensitive, so use Geometry and not geometry AND students.grade_level BETWEEN 6 AND 8 --Change this value to BETWEEN 9 AND 12 to get high school information GROUP BY sections.course_number, courses.course_name;