PowerSchool SQL Examples

By Terry Hawthorne
Director of Technology, Smyth County Public Schools

I have written a very basic tutorial on using the SQL SELECT statement. If you don't know anything about SQL, that tutorial is a good place to start.

Here are SQL queries to extract various kinds of data from PowerSchool. They have been tested with SQL Developer version 4.0.3.16 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

Students By Class

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 YearPowerSchool TermIDFirst SemesterSecond Semester
2012-2013220022012202
2013-2014230023012302
2014-2015240024012402
2015-2016250025012502
2016-2017260026012602
2017-2018270027012702
2018-2019280028012802
2019-2020290029012902
2020-2021300030013002
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

Back to top

Birthdays by Month

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;

Back to top

Alexandria Extract for Elementary Schools

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;

Back to top

iStation Import File

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;

Back to top

Students Who Are Not Enrolled in Any Classes

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);

Back to top

Renaissance Place Import File

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;

Back to top

Google Import

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:

REGEXP_REPLACE(first_name,'[^a-zA-Z0-9]')

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:

  1. The first REGEXP_REPLACE function strips the apostrophe out of his first name, so D'Angelo becomes DAngelo.
  2. The SUBSTR function that encloses the first REGEXP_REPLACE function carves out of the first three letters, so DAngelo becomes DAn.
  3. The second REGEXP_REPLACE function strips the space and period out of the student's last name, so Smith Jr. becomes SmithJr
  4. The first TO_CHAR function extracts the student's birth month from his date of birth (dob column), and the second TO_CHAR extracts the date, so we end up with 0704.
  5. The LOWER function and the concatenation operators return dansmithjr0704@scsb.org as the student's email address.

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;

Back to top

Student Attendance Listing

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:

  1. PowerSchool does not store any attendance records for students who are present. If it did, the attendance table would grow to a huge size in a very short time.
  2. In order to make the attendance listing query work, we have to create what is known as a Left Outer Join between the attendance table and the attendance_time table. This type of join ensures that the query retrieves all of the rows from the attendance table that match our student and data criteria. If we created a regular join, known as an Inner Join, this query would only retrieve attendance records that included time-in or time-out records in the attendance_time table, such as tardies.

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:

  1. Use MOD(time,3600) to get the number of seconds left after we extract the hour. The MOD function returns the remainder, so in this case, when I run MOD(time,3600), I am left with the number of seconds remaining after dividing time_in or time_out (the number of seconds that have elapsed since midnight) by 3600. For example, MOD(54600,3600) = 600 seconds. I use 3600 as the divisor in the MOD function because that is the number of seconds in an hour.
  2. Divide MOD(time,3600) by 60 to convert seconds to minutes.
  3. That leaves us with the number of minutes past the hour. There is one problem: if the number of minutes is less than 10, we will end up with an hh:mm value that looks like 6:5 when what we really want is 6:05. That's where the DECODE and SIGN functions come in. The SIGN function takes a single numeric value and returns 1 if the value is positive, 0 if the value = zero, and -1 if the value is negative. The expression 10 - MOD(time,3600) / 60 produces a positive number if the number of remaining minutes < 10. In that case, the DECODE function returns '0' plus the number of minutes as the mm value. Otherwise it just returns the number of minutes.

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;

Back to top

Student Import File for Cafeteria Point of Sale Program

This query produces a student import file for our cafeteria point of sale program. It has three unusual features:

  1. One of our cafeterias serves a middle school and a high school. In that case, students who are enrolled at the middle school (schoolid = 851) have to be assigned to the high school (schoolid = 460) in the import file. I use the Oracle DECODE function to switch 851 to 460 in the fourth line of the query.
  2. We need to remove any commas from the street address, and replace blank street addresses with the words "No Address." I use the DECODE function again to accomplish that. I nest the REGEXP_REPLACE function inside the DECODE to remove any characters other than spaces, letters, and numbers from the street address.
  3. Students who are currently enrolled in our schools have a 0 in the PowerSchool enroll_status field. If they have transferred out of school, they will have a value of 1 or 2 in that field. If they are pre-registered, they will have a value of -1 in the enroll_status field. The cafeteria system expects currently enrolled and pre-registered students to have a value of T in the Enroll field, and F in that field is they have withdrawn from school. I pass the PowerSchool enroll_status value to Oracle's SIGN function, which returns 1 for positive numbers, 0 for 0, and -1 for negative numbers. If SIGN(enroll_status) equals 0 or -1, I use DECODE to return T; otherwise it returns F.

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;

Back to top

Grades From PowerTeacher Gradebook

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;

Back to top

Student Test Scores

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.

Student test scores entity-relationship diagram.


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%'.

Test Name
SAT
ACT
AP US History
AP European History
AP Microeconomics
AP English Language
AP Physics C Electricity Magnetism
AP Physics C Mechanics
AP Comp Government Politics
AP English Literature
AP Calculus AB
Industry Certification
AP Environmental Science
SAT Subject U.S. History
SAT Subject French
SAT Subject Spanish
AP Art History
AP Latin: Virgil
AP Physics B
AP Biology
AP Chemistry
AP Calculus BC
AP Psychology
AP US Government & Politics
AP Statistics
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

Back to top

Student Attendance Code Count

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

Back to top

Homeroom Counts by Gender

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;

Back to top

Student Class Rank by Course and School

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;

Back to top

English Class Enrollments for Alexandria Import

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;

Back to top

Total Students Enrolled in Specific Courses During a School Year

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;