~[wc:commonscripts] ~[wc:admin_header_css] ~[text:psx.html.admin_reports.runreports.start_page] > PowerSchool Customization ~[wc:admin_navigation_css] ~[SetPostValue:tab=sql]

PowerSchool Customization

~[x:insertfile;tabs.html]

Database Access

Requirements

Many software packages exist that will facilitate ODBC access and querying. The most popular among PowerSchool users is Oracle's SQL Developer. See the SQL Developer tab for more information on downloading and using this tool.

SQL Studio

SQL Studio is a PowerSchool plugin developed by Adam Larsen of Aurora Educational Technology. This plugin provides the ability to write and execute queries directly within the PowerSchool interface. The plugin is available for download at auroraedtech.com/plugins-all/subscription

Important - Users can query information that would otherwise be restricted by field-level security. It is recommended to apply page permissions to the interface to prevent unauthorized access to PS data.

Commonly Used PowerSchool Tables

The Basics

Students This table maintains Student demographics and other School related information such as Lunch ID, Grade Level and scheduling data.
Reenrollments This table stores all enrollments, except the current one, for a student such as the ones for end-of-year (EOY) processing or in-term transfers.
Users This table maintains teacher demographic information and their preferences.
Note: The Teachers table is no longer used and has been renamed Teachers_ncf31. The SchoolStaff table and Users table have been created in its place. The information that appeared in the Teachers table can now be viewed in the Teachers reporting view.
SchoolStaff This table maintains School information related to the one or more teachers are assigned to.
Schools Information held on each of the schools.
Courses This table lists course information such as the Course Name, Number and Scheduling data.
Sections Section maintains occurrences of courses in a period and other related information such as Teacher and Grade level.
Terms Term related information used for the school such as Semester, Full Year, etc.
Gen This table serves as a catchall table containing a variety of information, such as look up tables.
Prefs Stores all general preferences for the system, user, school etc.

Grading

StoredGrades All of the students' historical grade information is maintained in this table.
PgFinalGrades Students final grades stored PowerTeacher Gradebook and PowerTeacher Pro.
Standard This table is used to store the standards the district needs/wants to assess students on.
StandardGradeSection This table contains the standards final grades for a student based on a year, reporting term, standard, school, and section.

Attendance

Attendance This is the main attendance table (4.0 and later) and can hold both Positive and Negative attendance. There are different methods of keeping attendance, Time, Day, Period, etc. for a student for a given year. This table also can be used for backfilling purposes
Attendance_Code The attendance codes that a school set up are maintained in this table. These could be codes for when student is tardy, present, absent or whatever they prefer to relate to attendance.
Att_Code_Code_Entity This Many-to-Many associative table contains all the codes that belong to one or more attendance codes.
Code_Entity This generic table contains data in code or lookup tables. For example, states and their codes (AZ for Arizona, etc.) are stored here.
Att_AdaAdm_Defaults_All This internal-use table supports ADA/ADM calculation using default settings for all valid attendance mode and conversion mode combinations. The data is available for reporting through the PS_ADAADM_DEFAULTS_ALL View.

Scheduling

CC This table maintains the student schedules. It contains information such as Section ID, Student ID, Term ID and Teacher ID.
SectionTeacher his table contains co-teaching information and maintains the associations between sections, teachers, and roles.
Section_Meeting This table contains the individual separation of periods and days of the full Expression, such as 1-2(M) would have 2 rows one for 1(M) and one for 2(M).
Room This table stores information about actual rooms within a school that can be used for scheduling purposes. This is copied from ScheduleRooms when the schedule is published.
Calendar_Day School year calendar is maintained is this table. It's used for Bell Schedules, ADM/ADA reports and overall attendance by showing which days are in-session within different tracks or overall. This used to be named DailySchedules.
Period Periods are used in combination with days to create schedule expressions. For example, a section of Biology that meets for the first period on each day of a two-day schedule has the expression 1(A-B). Periods are also part of what is defined as a section meeting.

PowerScheduler

PowerScheduler data is stored in "schedule" table (e.g. schedulecourses, schedulesections, schedulecc)

All PowerSchool Tables

Auditing Tables/Fields
AccessAdmin
AccessSchool
AccessStudent
AccessTeacher
Activities
Address (164)
AdminAlert
Agg_Att_Detail (171)
Agg_Attendance (167)
AggStats (46)
APEX_Appln_Bckp
Assignment
AssignmentCategoryAssoc
AssignmentDroppedByLowScore
AssignmentRetakeScore
AssignmentScore
AssignmentScoreComment
AssignmentScoreImportLine
AssignmentSection
AssignmentSectOnLineWork
AssignmentStandardAssoc
AssignmentStudentAssoc
ATN_ConsecutiveProcessing
ATN_ITVLBellSchedItms
ATN_MeetingConversion
ATN_NotifyRecToProcess
ATN_SchoolAttendanceCode
ATN_SchoolDays
ATN_TermOrder
Att_AdaAdm_Defaults_All
Att_AdaAdm_Defaults_HIS
Att_Code_Code_Entity (163)
Att_DayPart_CurYear
Att_DayPart_PrevYear
Att_Inc_Action_Codes_Mapping
Att_Nightly_Log
Att_Nightly_Process_Schools
Att_Nightly_Schools
Att_Process_Schools
Attendance_Code (156)
Attendance_Conversion_Items2
Attendance_Conversion (131)
Attendance_CurYear_School
Attendance_Taken (172)
Attendance_Time (158)
Attendance (157)
AttendanceQueue (48)
Au_TableName_
Aud_Calendar_Day
Audit_Log (170)
AutoCalendarSetting
AutoComm (47)
AutoSend (49)
Awsched_Constraint (152)
Awsched_Preference (155)
Batches (28)
Bell_Schedule_Items (134)
Bell_Schedule (133)
BLOBS (12)
Books (14)
BulletinItems (26)
C_ReenrollmentsOutOfDistrict
C_Schools_Registrar
C_StudentLookup
Cache_Message (178)
Calendar_Day (51)
Calendar (29)
CanonicalFieldList
CareerTechAuditHistory
CareerTechCluster
CareerTechClusterPrograms
CareerTechClusterType
CareerTechCredential
CareerTechCredentialType
CareerTechLocality
CareerTechProgram
CareerTechProgramCredentials
CareerTechProgramType
CareerTechSchool
CareerTechSite
CareerTechStudentCredentials
CareerTechStudentPrograms
CategorySchoolExcludeAssoc
CC_Exclusions
CC (4)
ChangeHistorySettings
ClassRank (15)
Code_Entity (162)
CodeSet
ColumnReMap
Component (161)
Config_Group (176)
ConsecutiveDaysAlert
CountryISOCodeLU
Course_Relationship (151)
CourseEquivalency
CourseFee (104)
CoursePreReqEvaluated
CoursePreReqOverride
CoursePreReqRecommend
CoursePreReqRule
Courses (2)
CoursesByYear
CoursesCoreFields
CReq (38)
CST_AssociatedResult
CST_AssocResultForeignKey
CST_ChildReport
CST_ConfigFile
CST_ExternalAdapter
CST_Param
CST_ParamDefault
CST_PrefConfig
CST_PropertyFiles
CST_Query
CST_QueryDependency
CST_QueryResult
CST_QueryResultColumn
CST_QueryResultTable
CST_RenderExpression
CST_RenderExpressionItem
CST_RenderExpressionOption
CST_ReportConfig
CST_ReportProcess
CST_ReportProcessTable
CST_ReportSubProcess
CST_Result
CST_SQLMapFiles
CST_Template
CST_UpdateCycleLock
CST_VLDConfigFile
CST_VLDGroup
CST_VLDGroupRule
CST_VLDParam
CST_VLDParamVal
CST_VLDQuery
CST_VLDRecord
CST_VLDRecordVal
CST_VLDResultByGroup
CST_VLDResultByRule
CST_VLDRule
CST_VLDSeverity
CustomDates (185)
CustomFieldMap
CustomIntegers (181)
CustomReals (182)
CustomText (183)
CustomTimes (186)
CustomVarchars (184)
Cycle_Day (135)
DailyAttendance (84)
Dalx_Semaphore
DataExportTemplate
DataExportTemplateField
DataExportTemplateFilter
DataExportTemplateOption
DataExportTemplateSchedule
DataFailedRecord
DataImport
DataImportTemplate
DataImportTemplateColumn
DayPart
DB_Credentials
DB_Object (169)
DB_Version (187)
DBLog (20)
DCFApplication
DCFColumnMap
DCFTable
Demographic (166)
Department (136)
Dialogs (10)
DictionaryColumn
DictionaryObject
DistrictBellSchedule
DistrictCalendar
DistrictCalendarDay
DistrictCategory
DistrictCycleDay
DistrictTeacherCategory
DM_CurrentGrade
DM_HistoricalGrade
DM_SchoolEnrollment
DM_StandardGrade
DnlDQueue (19)
DocDistrict
DocMetaData
DocMetaDataDistrictCategory
DQ$_TableName_
DS (23)
DV_TextMasks
DV_TextMaskSets
EmailAddress
Enrollment_Withdrawl_Tracking
EnterpriseReportPage
ERPFields
Ethnicity (168)
EvaluationFactors
EvaluationLevels
EvaluationMethods
EvaluationSchoolConfig
ExtSchemaCustomRemap
ExtSchemaDef
ExtSchemaDefAsset
ExtSchemaDefField
ExtSchemaDefForeignKey
ExtSchemaDefIndex
ExtSchemaDefTable
ExtSchemaMigrationIssue
Facility (137)
Fee_Balance (148)
Fee_Transaction (147)
Fee_Type (144)
Fee (146)
Fees (54)
FieldLevelSecurity
FieldsTable (7)
FieldTypeChangeRequest (196)
FinalGradeAttrib
FTE_Grade (160)
FTE (159)
Gen (6)
GLDetail (25)
GPExpectation
GPExpectationItem
GPNode
GPProgressSubject
GPProgressSubjectEarned
GPProgressSubjectEnrolled
GPProgressSubjectRequested
GPProgressSubjectWaived
GPProgressSubjWaivedApplied
GPProgressTest
GPScoreConfig
GPSelectedCrs
GPSelectedCrType
GPSelector
GPStudentPlan
GPStudentTestWaiver
GPStudentWaiver
GPTarget
GPTestConfig
GPTestScoreConfig
GPVersion
GPWaiverConfig
GradeCalcDropLowScore
GradeCalcFormulaWeight
GradeCalcSchoolAssoc
GradeCalculationType
GradeCourseConfig
GradeFormulaSet
GradePolicyStoreCodes
GradeReplacementPolicy
GradeScaleItem_Backup (90)
GradeScaleItem
GradeScaleRelated
GradeScaleSchoolAssoc
GradeScaleScoreCode
GradeScaleSectionStudent
GradeSchoolConfig
GradeSchoolFormulaAssoc
GradeSectionConfig
GradPlan
GradReq (37)
GradReqSets (57)
Guardian
GuardianNotificationEmail
GuardianPersonAssoc
GuardianRelationshipType
Guardians (55)
GuardianStudent
GuardianStudRestrictionMap
GUID_SetUpMetaData
HealthAccident
HealthConcerns
HealthContactLog
HealthGradeLevel
HealthImmExempt
HealthImmSource
HealthMainStudRec
HealthMedAdmin
HealthMedication
HealthMedInventory
HealthMisc
HealthOfficeVisit
HealthPEWaiver
HealthPhysical
HealthScreenChngDetail
HealthScreenLookup
HealthScreenWaiver
HealthStudChngDetail
HealthStudChngRsn
HealthStudDiabetesMonitor
HealthStudDoseRec
HealthStudGradeLevel
HealthStudHearing
HealthStudImmRec
HealthStudLead
HealthStudMonitor
HealthStudOral
HealthStudScolio
HealthStudTB
HealthStudTreatmentAssoc
HealthStudVision
HealthStudentHealth
HealthStudentPlan
HealthVaccine
HealthVaccineRule
HealthVitalSigns
Help (44)
HonorRoll (34)
IDPContext
IDPMetaData
IDPMetaDataMAP
IDPRelyingParty
IDPService
IDPServiceMap
IDPServiceParam
ImportLocalizationResults
ImportLocalizationStatus
Incident_Action_Attribute
Incident_Action
Incident_Change_Rsn_Desc
Incident_Detail
Incident_LU_Code
Incident_LU_Sub_Code
Incident_Object_Person
Incident_Object
Incident_Other_Person
Incident_Person_Action
Incident_Person_Detail
Incident_Person_Role
Incident_Personal_Detail
Incident_Personal
Incident_Security_Group
Incident
IncidentTemplate
Institution
InstitutionCodeList
KeyStore
LanguageISOCodeLU
LG_TableName
Locale
LocaleDateFormat
LocaleNumCurrFormatLU
LocaleSeparatorLU
LocaleTimeFormat
LockOverride
LockOverrideSection
LockOverrideTermBin
Log2 (50)
Log (8)
Logins (16)
MessageKeyMap
MessageRequestSummary
MessageStaging
Metaphone
MimeTypes (11)
OauthAccessToken
OIDServerAssociationStore
OriginalContactMap
Pages (30)
PCAS_Account
PCAS_AccountAccessHist
PCAS_AccountServicePref
PCAS_AccountServiceRel
PCAS_AccountSession
PCAS_Audit
PCAS_Configuration
PCAS_CredentialComplexity
PCAS_CredentialRecoveryToken
PCAS_CredentialReuse
PCAS_CredentialType
PCAS_DeviceToken
PCAS_EmailContact
PCAS_ExternalAccountMap
PCAS_Service
PCAS_ServiceTicket
Period (138)
PermissionGroupLabel
PermissionGroupValue
Person_Obsolete
Person (188)
PersonAddress
PersonAddressAssoc
PersonAddressCoreFields
PersonCoreFields
PersonEmailAddressAssoc
PersonLanguageAssoc
PersonPhoneNumberAssoc
PGAssignments (92)
PGAssignmentStandards (93)
PGCategories (94)
PGCommentBank (97)
PGFinalGrades (95)
PGFinalGradesSetup (191)
PGGradeScales (192)
PGGradeScalesMark (193)
PGIncomingQueue (40)
PGNotification (173)
PGPreferences (43)
PGScores (96)
PGSections (195)
PGStudents (194)
PhoneLog (27)
PhoneNumber
PhoneNumberCoreFields
PISA_Session
PluginBackupData
PluginConfigDefaults
PluginConfigOIDProvider
PluginConfigWSTrustBridge
PluginConfOAuthConsumer
PluginConfOAuthProvider
PluginConfWebCustomPage
PluginConfWSSoapClient
PluginConfWSSoapConsumer
PluginConfWSSoapProvider
PluginDef
PluginDefAccessRequest
PluginDefAsset
PluginDefEntityAttribMap
PluginDefInterfaceMap
PluginDefRole
PluginDefSchoolsMap
PluginDefStatesMap
PluginEntity
PluginEntityAttrib
PluginEventSubscriber
PluginEventType
PluginInterface
PluginKnownHost
PluginLink
PluginLinkContext
PlugInLinkRoleCapMap
PluginPublisher
PluginRegistration
PluginRegistrationParam
PluginResourceEntity
PluginSubscriberEventType
PostSecondary (165)
Pre_Mig_Fld_Lvl_Security_Bkup
Pre_Mig_Guardian_Details_Bkup
Pre_Mig_Student_Details_Bkup
Prefs (9)
Program (174)
PS_Common_Code_Config
PS_Common_Code
PS_Membership_Defaults
PS_WritebackAuditTrail
PSM_Asset
PSM_AssetContent
PSM_AssetContentArchive
PSM_AssetFolder
PSM_ChartLayout
PSM_ChartLayoutPreference
PSM_ChartObject
PSM_ChartSection
PSM_ChartTeacherSection
PSM_JobQueueCurrent
PSM_ReportAppCatAssoc
PSM_ReportApplication
PSM_ReportClass
PSM_ReportColumn
PSM_ReportEntity
PSM_ReportMapping
PSM_ReportModel
PSM_ReportNode
PSM_ReportOutput
PSM_ReportParameter
PSM_ReportParamOptions
PSM_ReportPath
PSM_ReportProject
PSM_ReportPub
PSM_ReportPubAppCat
PSM_ReportPubRole
PSM_ReportPubSchool
PSM_ReportRunTimeRequest
PSM_ReportTree
PSM_ReportTreeParamAssoc
PSM_TeacherFavoriteComment
Queries_Table
Queue (24)
ReadReceipt
ReEnrollments (18)
RegistReq (56)
Relationship (189)
RepoBatchSetups (101)
RepoLookupTables (65)
RepoLookupTablesContentsI (67)
RepoLookupTablesContRecords (6)
RepoLookupTablesDefItems (66)
Reports (22)
RepoSetups (59)
RepoSetupsItems (60)
RepoSetupsOrderBy (64)
RepoSetupsQueries (63)
RepoSetupsQueryItems (62)
RepoSetupsUserData (61)
RepoSetupsUserDataDefaults (91)
RepoSetupsVariables (83)
RObj (36)
RoleAccess
RoleAttributeGroup
RoleAvailableAccess
RoleAvailableCapability
RoleAvailableDataGroup
RoleAvailableIDPCapability
RoleAvailableProperty
RoleCapability
RoleCategoryAccess
RoleChangeHistoryAccess
RoleData
RoleDef
RoleDefTeachersMap
RoleEnterpriseRptAccess
RoleFieldAccess
RoleMassDataAccess
RoleModule
RoleProperty
Room (139)
SCED_Code_Mapping
Sched_Debug (115)
SchedCategoryBalancing
SchedSectProgSettings
ScheduleActivityStatus (73)
ScheduleBldSessions (114)
ScheduleBuildCourseRank (116)
ScheduleBuildDiagnostics (118)
ScheduleBuildings (71)
ScheduleBuilds (105)
ScheduleCatalogs (109)
ScheduleCC (111)
ScheduleConstraints (110)
ScheduleCourseCatalogs (107)
ScheduleCourseRelationships (7)>
ScheduleDays (75)
ScheduleDepartments (69)
ScheduleFacilities (76)
ScheduleHouses (72)
ScheduleItems (52)
ScheduleLoadDiagnostics (119)
ScheduleLoadTypes (77)
SchedulePeriods (80)
ScheduleQueue (113)
ScheduleRequests (82)
ScheduleRooms (81)
ScheduleRoomTypes (74)
ScheduleSectionMeeting (130)
ScheduleSections (112)
ScheduleSectionTypes (79)
ScheduleTeacherAssignments (10)>
ScheduleTerms (108)
ScheduleValidation (117)
ScheduleValidTerms (70)
ScheduledEvent
SchemaUpdateStatus
School_Course_Sched_Parm (154)
School_Course (153)
SchoolAlertConfig
SchoolFee (145)
SchoolMap
SchoolProfile
Schools (39)
SchoolsCoreFields
SchoolStaff
Section_Meeting (140)
SectionAttrib
SectionReadiness
Sections (3)
SectionScores (100) (Obsolete)
SectionScoresAssignments (198)
SectionScoresID (197)
SectionTeacher
SectProgSettings
Selections (85)
SeqNo (58)
Server_Config (175)
Server_Instance_Singleton
Server_Instance (177)
SGRCalculationSettings
SIF_Message (179)
SPEnrollments (41)
Standard
StandardCourseAssoc
StandardCourseLowerWeight
StandardGradeRollup
StandardGradeRollupComment
StandardGradeSection
StandardGradeSectionComment
StandardReportingTermWeight
StandardRetakeScore
Standards_Migration_Backup (53)
StandardScore
StandardsCurrent (98)
StandardsGrades_Backup (99)
StandardThirdParty
StandardThirdPartyAssoc
StateEventQueue (102)
StateExtract_CodeConversion8
StateExtract_LayoutElements7
StateExtract_Layouts (126)
States
StateSupportData (21)
StateTransactionQueue (103)
Stats (17)
StoredGrades (31)
StudentAttendanceSummary (200)
StudentContactAssoc
StudentContactAssocCoreFields
StudentContactDetail
StudentContactDetailCoreField>
StudentCoreFields
StudentRace (201)
Students (1)
StudentSchedulingResults (190)
StudentSectCategoryTotal
StudentTest (87)
StudentTestScore (89)
Sys_Sequence (180)
SystemHistory
TableNumberMap
TeacherCategory
TeacherCatSectExcludeAssoc
TeacherCommentBank
TeacherDailyLoad (199)
TeacherFavoriteComment
TeacherPreference
TeacherRace (202)
Teachers_ncf31 (5)
TeacherScoreCode
Template
TermBinAttrib
TermBins (33)
Terms (13)
Test (86)
TestScore (88)
TmpDalxTables
TransformTemp
Transportation (120)
Truancies (42)
UCols (35)
UnexcusedHistorical
UnexcusedNotify
UnexcusedNotifyDates
UnexcusedNotifyDatesHist
UnexcusedNotifyHistory
UnexcusedYTD
UnSchedStudSchedLink (143)
UnSchedTermDayPeriodSlot (142)
UnScheduledRoom (150)
UnScheduledStudent (141)
UnScheduledTeacher (149)
Users
UsersCoreFields
UTable (32)
VirtualFieldsDef (122)
VirtualTablesDef (121)
VirtualTablesLegacyData1
VirtualTablesLegacyData2
VirtualTablesLegacyData3
VS, 45
WebAsmt, 124

The SQL SELECT Statement


SQL SELECT Syntax SELECT column_name, column_name FROM table_name OR SELECT * FROM table_name The first example above will select specific data (columns) from a table.
The second example uses * to select all columns from a table.

Example SELECT lastfirst, gender FROM students ~[tlist_sql; SELECT lastfirst, gender FROM students WHERE rownum < 5 ;] [/tlist_sql]
lastfirst gender
~(1)~(2)

WHERE


SELECT column_name, column_name FROM table_name WHERE column_name operator value Example SELECT lastfirst, gender FROM students WHERE gender = 'M' ~[tlist_sql; SELECT lastfirst, gender FROM students WHERE gender = 'M' AND rownum < 5 ;] [/tlist_sql]
lastfirst gender
~(1)~(2)

Common Basic Operators

Operator Comparison Type
=Equal To
!=Not Equal To
>Greater Than
<Less Than
>=Greater Than or Equal To
<=Less Than or Equal To

Test Your Skills

The school nurse needs a list of all medical alerts for students at Apple Grove High School (100).
Include the student's name, student number, and medical alert text.

NULL


When using NULL in SQL, basic operators cannot be used. Instead, use one IS NULL or IS NOT NULL

Example - IS NULL SELECT lastfirst, middle_name FROM students WHERE middle_name IS NULL This query will return all students with NO value set for the middle_name field.

Example - IS NOT NULL SELECT lastfirst, middle_name FROM students WHERE middle_name IS NOT NULL This query will return all students with ANY value set for the middle_name field.

AND


Example SELECT lastfirst, gender, grade_level FROM students WHERE grade_level = 10 AND gender = 'M' ~[tlist_sql; SELECT lastfirst, gender, grade_level FROM students WHERE rownum < 5 AND (grade_level = 10 AND gender = 'M') ;] [/tlist_sql]
lastfirst gender grade_level
~(1)~(2)~(3)

OR


Example SELECT lastfirst, gender, grade_level FROM students WHERE enroll_status = 0 OR enroll_status = -1 ~[tlist_sql; SELECT lastfirst, gender, grade_level FROM students WHERE rownum < 5 AND (enroll_status = 0 OR enroll_status = -1) ;] [/tlist_sql]
lastfirst gender grade_level
~(1)~(2)~(3)

Oracle Data Types

Data Type Examples
Character char, varchar2
Numeric Number (Decimal Precision), Integer (Whole Numbers)
Date Date, Timestamp
Large Object (LOB) CLOB, BLOB
Oracle can often perform implicit conversions of data, but it is best to perform explicit conversions to avoid errors when comparing values. Explicit conversions can also be useful to display data in a desired format. SELECT last_name, first_name, to_char(dob,'MM/DD/YYYY') FROM students ORDER BY last_name

to_char() ensures that the date value is returned in a desired format.

SELECT last_name, first_name, (dob,'MM/DD/YYYY') FROM students WHERE dob > to_date('01/10/2015','MM/DD/YYYY') ORDER BY last_name

to_date() ensures that the date value provided (as character data) is properly converted to a date value.
In some countries 01/10/2015 will represent January 10, 2015 (MM/DD/YYYY).
In other countries 01/10/2015 will represent October 01, 2015 (DD/MM/YYYY).

Today's Date

SYSDATE - returns the current date and time set for the operating system on which the database server resides. SELECT to_char(sysdate,'MM/DD/YYYY HH:MI AM') FROM dual ~[tlist_sql; SELECT to_char(sysdate,'MM/DD/YYYY HH' || CHR(58) || 'MI AM') FROM dual ;] ~(dt) [/tlist_sql]

CURRENT_DATE - returns the current date in the session time zone. SELECT to_char(current_date,'MM/DD/YYYY HH:MI AM') FROM dual ~[tlist_sql; SELECT to_char(current_date,'MM/DD/YYYY HH' || CHR(58) || 'MI AM') FROM dual ;] ~(dt) [/tlist_sql]

Get Table Definition

Use either of the following to find the fields and data types for a given table (table_name)

DESC table_name OR SELECT table_name, column_name, data_type FROM all_tab_columns WHERE LOWER(table_name) = 'table_name'

ORDER BY


Example SELECT last_name, first_name FROM students ORDER BY last_name ~[tlist_sql; SELECT DISTINCT last_name, first_name FROM students WHERE rownum < 5 ORDER BY last_name ;] [/tlist_sql]
last_name first_name
~(1)~(2)
Example - DESC SELECT last_name, first_name FROM students ORDER BY last_name DESC ~[tlist_sql; SELECT DISTINCT last_name, first_name FROM students WHERE rownum < 5 ORDER BY last_name DESC ;] [/tlist_sql]
last_name first_name
~(1)~(2)

Test Your Skills

The school nurse is unhappy with your solution to the previous request.

Fix these issues or start preparing your resume.

Ooops... some of these alerts are expired. The nurse missed this, but we're smart, and we noticed.
We need to look at the alert expiration and show only alerts that have no expiration or expire in the future.

LIKE Operator


Example SELECT course_number, section_number, room FROM sections WHERE termid LIKE '~(curyearid)%' ORDER BY schoolid, course_number, section_number ~[tlist_sql; SELECT course_number, section_number, room FROM sections WHERE termid LIKE '~(curyearid)%' AND rownum < 5 ORDER BY schoolid, course_number, section_number ;] [/tlist_sql]
course_number section_number room
~(1)~(2)~(3)
The % symbol serves as a wildcard in the LIKE statement. WHERE last_name LIKE 'An%' would give us all students with last names starting with An.

Strings are case-sensitive ('an' is not the same as 'An'). Use UPPER(last_name) or LOWER(last_name) to convert a field to a specific case and do a non case-sensitive comparison.

IN Operator


Example SELECT last_name, first_name, grade_level FROM students WHERE enroll_status IN (0,-1) ~[tlist_sql; SELECT distinct last_name, first_name, grade_level FROM students WHERE enroll_status IN (0,-1) AND rownum < 6 ;] [/tlist_sql]
last_name first_name grade_level
~(1)~(2)~(3)

BETWEEN Operator


Example SELECT lastfirst, grade_level FROM students WHERE grade_level BETWEEN 1 AND 5
lastfirst grade_level
Adair, Brandon5
Right, James2
Hempton, Emilia1
Randolph, Jessica4
Winston, Veronica3

Concatenation


Example SELECT first_name || ' ' || last_name FROM students ~[tlist_sql; SELECT first_name || ' ' || last_name FROM students WHERE rownum < 5 ;] [/tlist_sql]
first_name || ' ' || last_name
~(1)

Aliases


Example SELECT lastfirst, street || ' ' || city || ', ' || state || ' ' || zip AS address FROM students ~[tlist_sql; SELECT lastfirst, street || ' ' || city || ', ' || state || ' ' || zip AS address FROM students WHERE rownum < 5 ;] [/tlist_sql]
lastfirst address
~(1)~(2)
In addition to aliasing columns, tables can also be aliased.
Aliases become very useful when using JOINS and the WITH clause (covered below).

DISTINCT


SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name, column_name FROM table_name Example SELECT DISTINCT grade_level, home_room FROM students ~[tlist_sql; SELECT DISTINCT grade_level, home_room FROM students WHERE home_room IS NOT NULL AND grade_level < 13 AND rownum < 5 ;] [/tlist_sql]
grade_level home_room
~(1)~(2)

Test Your Skills

The registrar needs a list of street addresses for all high school students.
If two students live at the same address, the address should only be listed once.
Results should include the city and street address.
Only include active and pre-registered students.
Order the results by city and then street address.

JOIN


Example SELECT students.lastfirst, log.subject FROM log JOIN students ON log.studentid = students.id
lastfirst subject
Smith, JohnExcessive Tardies
Adams, JuliaStomach Ache
Kelsier, AbigailMath Intervention
Zumbrow, DrakeGuidance Counseling
The ON keyword is used to tell the database how to match the log table to the students table.

The PowerSchool data dictionary provides definitions of how tables are linked, though field names often provide a solid indication.

Note that we now use the table_name.column_name syntax. It is necessary when joining tables to specify the table when referencing columns because the tables may have columns of the same name.

Example - Joining multiple tables SELECT students.lastfirst, courses.course_name, cc.section_number FROM cc JOIN students ON students.id = cc.studentid JOIN courses ON courses.course_number = cc.course_number WHERE cc.dateenrolled <= sysdate AND cc.dateleft > sysdate
lastfirst course_name section_number
Adair, Brandon AP Calculus 1
Miller, Joe Learning Shapes 3
Larsen, Adam SQL 4
Cornacchioli, Bob Standards Setup 2
Notice that the tables have been given aliases in the above example to make the query more concise.

The sysdate keyword is used to retrieve the current date.

JOIN Types

INNER vs LEFT Join - Sample Database Example
Students
ID LastFirst Grade_Level
1 Miller, Joe 10
2 Momrick, Al 12
3 Dunleavy, John 11
StoredGrades
Studentid Grade Storecode Course_Number
1 F Q1 HIST101
1 D Q2 HIST101
2 A Q1 SOC12
2 B+ S1 MAT12
Result of Students JOIN StoredGrades ON Students.id = StoredGrades.studentid (INNER JOIN)
Students.ID Students.LastFirst Students.Grade_Level StoredGrades.Studentid StoredGrades.Grade StoredGrades.Storecode StoredGrades.Course_Number
1 Miller, Joe 10 1 F Q1 HIST101
1 Miller, Joe 10 1 D Q2 HIST101
2 Momrick, Al 12 2 A Q1 SOC12
2 Momrick, Al 12 2 B+ S1 MAT12
Result of Students LEFT JOIN StoredGrades ON Students.id = StoredGrades.studentid
Students.ID Students.LastFirst Students.Grade_Level StoredGrades.Studentid StoredGrades.Grade StoredGrades.Storecode StoredGrades.Course_Number
1 Miller, Joe 10 1 F Q1 HIST101
1 Miller, Joe 10 1 D Q2 HIST101
2 Momrick, Al 12 2 A Q1 SOC12
2 Momrick, Al 12 2 B+ S1 MAT12
3 Dunleavy, John 11 (null) (null) (null) (null)
An example of a situation in which a left join might be needed in PowerSchool is joining the Students table to the Attendance table. The absence of a record in the absence table means a student was present. Consider the following query. By using the left join, we will ensure that we list all students in the given section. If there is no attendance code in a row, that student was present. SELECT students.lastfirst, attendance_code.att_code FROM cc JOIN students ON students.id = cc.studentid LEFT JOIN attendance ON attendance.ccid = cc.id AND attendance.att_date = to_date('1/10/2018','MM/DD/YYYY') LEFT JOIN attendance_code ON attendance_code.id = attendance.attendance_codeid WHERE cc.sectionid = 24
lastfirst att_code
Adair, Brandon
Adams, JulieA
Johnson, CindyT
Smith, Joel

Test Your Skills

The Apple Grove High School counselor needs a list of students in danger of failing a class this year.
Find students with a stored grade this year that is D, D-, or F.
Include the following:

Sort the results by student name then course name.

Common Oracle SQL Functions

Numeric Functions
ABS(n) Returns absolute value of n.
ROUND(n, integer) Returns n rounded to integer places to the right of the decimal point.
Character Functions
LOWER(string) Returns string with all lower case letters.
UPPER(string) Returns string with all upper case letters.
REPLACE(string, search_string, replace_string) Returns string with all occurrences of search_string replaced by replace_string
SUBSTR(string, start_position, length) Returns portion of string starting at start_position for length characters.
Coalesce
COALESCE(expr1, expr2, expr3...) Returns the first non-null expr in the expression list.

Test Your Skills

The district is implementing Gmail.
Each student's email will be their the first character of their first name, first charachter of their middle name, last name, and then @gmail.com.
Write a query to get student emails.

Example - John Frederick Doe = jfdoe@gmail.com

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.


Example AVG() SELECT AVG(no_of_students) FROM Sections ~[tlist_sql; SELECT AVG(no_of_students) FROM sections ;] [/tlist_sql]
no_of_students
~(1)
Example COUNT() SELECT COUNT(*) FROM Students WHERE enroll_status = 0 ~[tlist_sql; SELECT COUNT(*) FROM Students WHERE enroll_status = 0 ;] [/tlist_sql]
Count(*)
~(1)
Example MAX() SELECT MAX(no_of_students) FROM Sections ~[tlist_sql; SELECT MAX(no_of_students) FROM Sections ;] [/tlist_sql]
no_of_students
~(1)
Example MIN() SELECT MIN(no_of_students) FROM Sections ~[tlist_sql; SELECT MIN(no_of_students) FROM Sections ;] [/tlist_sql]
no_of_students
~(1)
Example SUM() SELECT SUM(no_of_students) FROM Sections ~[tlist_sql; SELECT SUM(no_of_students) FROM Sections ;] [/tlist_sql]
no_of_students
~(1)

GROUP BY

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

GROUP BY Syntax SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name Example SELECT courses.course_name, to_char(AVG(sections.no_of_students),'999.99') FROM sections JOIN courses ON courses.course_number = sections.course_number GROUP BY courses.course_name ~[tlist_sql; SELECT course_name, no_of_students FROM ( SELECT c.course_name, to_char(AVG(s.no_of_students),'999.99') no_of_students FROM sections s JOIN courses c ON c.course_number = s.course_number GROUP BY c.course_name ) WHERE rownum < 6 ;] [/tlist_sql]
course_name no_of_students
~(1)~(2)

HAVING

The HAVING clause allows us to filter results based on an aggregated value.

SELECT lastfirst, student_number, count(storedgrades.dcid) AS low_grade_count FROM students JOIN storedgrades ON storedgrades.studentid = students.id WHERE storedgrades.grade IN ('D','D+','D-','F') GROUP BY lastfirst, student_number HAVING count(storedgrades.dcid) > 10 ORDER BY count(storedgrades.dcid) DESC
lastfirst student_number low_grade_count
Miller, Joe 56896 15
Schaitel, Eric 98966 15
Momrick, Al 63348 12
Dunleavy, John 19893 10

Test Your Skills

S1 report cards have gone out, and we've received complaints from parents that some teacher's curriculum is too difficult and/or they are unfairly failing students.
We need a list including the name of each teacher at Apple Grove High School and the number of Fs they have graded for S1 this year.
Order the results by the number of Fs, then by the teacher name

Hints
A LEFT JOIN will be needed
The COUNT() function only counts non-null values
Need an extra challenge?
Split the results out by section (course_number.section_number).
Include only sections where there are 0 failing grades or more than 5 failing grades.

CASE Statement

The CASE statement chooses from a sequence of conditions, and executes a corresponding statement.

CASE Syntax CASE WHEN boolean_expression_1 THEN statement_1 WHEN boolean_expression_2 THEN statement_2 ELSE statement_3 END Example SELECT lastfirst, CASE WHEN grade_level < 0 THEN 'PRE-K' WHEN grade_level = 0 THEN 'Kindergarten' WHEN grade_level BETWEEN 1 AND 5 THEN 'Elementary' WHEN grade_level BETWEEN 6 AND 8 THEN 'Middle' WHEN grade_level BETWEEN 9 AND 12 THEN 'High' END AS school_level FROM students
lastfirst school_level
Adair, BrandonHigh
James, JuliePRE-K
Chapman, SilasHigh
Zelinski, AriaElementary
Innman, SusanKindergarten

Subqueries


Example SELECT students.lastfirst, ( SELECT COUNT(*) FROM cc WHERE cc.studentid = students.id AND sysdate BETWEEN cc.dateenrolled AND cc.dateleft ) AS current_class_count FROM students WHERE students.enroll_status = 0 ~[tlist_sql; SELECT s.lastfirst, ( SELECT COUNT(*) FROM cc WHERE studentid = s.id AND sysdate BETWEEN cc.dateenrolled AND cc.dateleft ) AS current_class_count FROM students s WHERE enroll_status = 0 AND rownum < 5 ;] [/tlist_sql]
lastfirst current_class_count
~(1)~(2)

EXISTS / NOT EXISTS

Find records where an associated record exists in another table.

SELECT lastfirst, student_number FROM students WHERE EXISTS( SELECT id FROM log WHERE entrydate > sysdate - 30 )

This will find all students who have had a log entry created within the last 30 days.

Simply use NOT EXISTS to find students with no logs within the last 30 days.

WITH

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name.

WITH Syntax WITH alias_name AS( SELECT column_name, column_name FROM table ) SELECT column_name, column_name FROM alias_name Example This example achieves the same result as the subquery example above using the WITH clause. WITH class_counts AS( SELECT studentid, COUNT(*) AS class_count FROM cc WHERE sysdate BETWEEN cc.dateenrolled AND cc.dateleft GROUP BY studentid ) SELECT students.lastfirst, COALESCE(class_counts.class_count,0) FROM students LEFT JOIN class_counts ON class_counts.studentid = students.id WHERE students.enroll_status = 0 ~[tlist_sql; WITH class_counts AS( SELECT studentid, COUNT(*) AS class_count FROM cc WHERE sysdate BETWEEN cc.dateenrolled AND cc.dateleft GROUP BY studentid ) SELECT s.lastfirst, COALESCE(counts.class_count,0) FROM students s LEFT JOIN class_counts counts ON counts.studentid = s.id WHERE s.enroll_status = 0 AND rownum < 5 ;] [/tlist_sql]
lastfirst class_count
~(1)~(2)

Test Your Skills

The superintendent has asked for a list of at risk students.
List the name, student number, low grade count, and log count for students who meet the following at risk criteria for the current year.

Union

Unions are used to retrieve the results of multiple queries in a single result set.

SELECT entrydate, exitdate, grade_level FROM students WHERE id = 2 UNION SELECT entrydate, exitdate, grade_level FROM reenrollments WHERE studentid = 2 ORDER BY entrydate desc
entrydateexitdategrade_level
8/3/20206/1/202112
8/3/20196/1/202011
8/3/20186/1/201910
8/3/20176/1/20189
8/3/20166/1/20178
8/3/20156/1/20167

As Of Timestamp

Use the AS OF TIMESTAMP feature to retrieve results as they existed in the database at a previous time.

SELECT * FROM students AS OF TIMESTAMP sysdate - 1 WHERE id = 2

If any fields have been modified or records have been deleted within the last 24 hours, this will retrieve the values prior to those changes.

Custom Fields

PS_CustomFields is an Oracle package that contains functions and procedures for extracting custom field data from any PowerSchool table that supports custom fields, including students, teachers, courses, sections, and schools.

The custom fields package contains the following functions.
Example SELECT lastfirst, ps_customfields.getCF('students', students.id, 'ACT_Composit_Score') FROM students The above query will return student names and the contents of a custom field called ACT_Composit_Score using the getCF function.

The same results can be returned using the getStudentsCF package as demonstrated below. SELECT lastfirst, ps_customfields.getStudentsCF(students.id, 'ACT_Composit_Score') FROM students

Extended Fields

Extended fields are PowerSchool's new way of storing custom data. Extended field data is stored in tables which link to core tables via the core table's DCID. Therefore, extended data can be queried using SQL JOIN statements.

See the DB extensions page for more detail.

Example SELECT students.lastfirst, x.ACT_Composit_Score FROM students LEFT JOIN u_def_ext_students x ON x.studentsdcid = students.dcid This query will produce the same results as the example above using the custom fields package if the custom data is stored as an extended field.

LISTAGG

The LISTAGG function provides a means of listing values in a given group

Example SELECT students.lastfirst, students.student_number, LISTAGG(courses.course_name, ' , ') WITHIN GROUP (ORDER BY courses.course_name) AS courseList FROM students JOIN cc ON students.id = cc.studentid JOIN courses ON courses.course_number = cc.course_number WHERE cc.termid LIKE '~(curyearid)__' GROUP BY students.lastfirst, students.student_number ORDER BY students.lastfirst ~[tlist_sql; SELECT lastfirst, student_number, courseList FROM( SELECT students.lastfirst, students.student_number, LISTAGG(courses.course_name, ' , ') WITHIN GROUP (ORDER BY courses.course_name) AS courseList FROM students JOIN cc ON students.id = cc.studentid JOIN courses ON courses.course_number = cc.course_number WHERE cc.termid LIKE '~(curyearid)__' GROUP BY students.lastfirst, students.student_number ORDER BY students.lastfirst ) WHERE rownum < 4 ;] [/tlist_sql]
lastfirst student_number courseList
~(1)~(2)~(3)

PIVOT

Oracle's PIVOT function provides a means to group results from rows into columns.

Example SELECT students.lastfirst, courses.course_name, storedgrades.grade, storedgrades.storecode FROM storedgrades JOIN students ON storedgrades.studentid = students.id JOIN courses ON courses.course_number = storedgrades.course_number ORDER BY courses.course_name, storedgrades.storecode
student course grade storecode
Adair, BrandonAlgebraBQ1
Adair, BrandonAlgebraAQ2
Adair, BrandonAlgebraB+S1
Adair, BrandonAlgebraAQ3
Adair, BrandonAlgebraA-Q4
Adair, BrandonAlgebraAS2
Adair, BrandonBiologyCQ1
Adair, BrandonBiologyBQ2
Adair, BrandonBiologyB-S1
Adair, BrandonBiologyAQ3
Adair, BrandonBiologyCQ4
Adair, BrandonBiologyBS2
Adair, BrandonZoologyA+S1
Adair, BrandonZoologyAS2
A more concise way to display this data would be to use the PIVOT function to group the grades by course, and display grades for each storecode in a designated column. WITH grades AS( SELECT students.lastfirst, courses.course_name, storedgrades.grade, storedgrades.storecode FROM storedgrades JOIN students ON storedgrades.studentid = students.id JOIN courses ON courses.course_number = storedgrades.course_number ) SELECT * FROM grades PIVOT( MAX(grade) FOR storecode IN ('Q1','Q2','S1','Q3','Q4','S2') )
Student course Q1 Q2 S1 Q3 Q4 S2
Adair, BrandonAlgebra BAB+AA-A
Adair, BrandonBiology CBB-ACB
Adair, BrandonZoology A+A

JSON Formatting

Oracle offers several functions for formatting output as JavaScript Object Notation (JSON)

JSON_ARRAY

Return results formatted as a JSON array

SELECT JSON_ARRAY(student_number, grade_level) FROM students
JSON_ARRAY
[31,11]
[32,12]
[33,11]
[34,12]

JSON_OBJECT

Return results formatted as a JSON object

SELECT JSON_OBJECT(student_number, grade_level) FROM students
JSON_OBJECT
{"student_number":31,"grade_level":11}
{"student_number":32,"grade_level":12}
{"student_number":33,"grade_level":11}
{"student_number":34,"grade_level":12}

JSON_OBJECT - Defining Property Names

To define specific property names, use the following syntax.
'propName' VALUE field

SELECT JSON_OBJECT( student_number, 'grade' VALUE grade_level ) FROM students
JSON_OBJECT
{"student_number":31,"grade":11}
{"student_number":32,"grade":12}
{"student_number":33,"grade":11}
{"student_number":34,"grade":12}

JSON_ARRAYAGG

Return results aggregated as a JSON array

SELECT JSON_ARRAYAGG( JSON_OBJECT( student_number, 'grade' VALUE grade_level ) ) FROM students
JSON_ARRAYAGG
[
    {"student_number":31,"grade":11},
    {"student_number":32,"grade":12},
    {"student_number":33,"grade":11},
    {"student_number":34,"grade":12}
]

JSON_OBJECTAGG

Return results aggregated as a JSON object

SELECT JSON_OBJECTAGG( to_char(student_number) VALUE JSON_OBJECT( lastfirst, 'grade' VALUE grade_level ) ) FROM students
JSON_OBJECTAGG
{ "31" : {"lastfirst":"Adair, Brandon","grade":11}, "32" : {"lastfirst":"Adams, Gina","grade":12}, "33" : {"lastfirst":"Kakak, Britney","grade":11}, "34" : {"lastfirst":"Johnson, Avery","grade":12} }

RETURNING CLOB

When the result of a JSON function may exceed 4000 characters, it's essential to include the RETURNING CLOB statement to prevent Oracle errors.

SELECT JSON_ARRAYAGG( JSON_OBJECT( student_number, 'grade' VALUE grade_level RETURNING CLOB ) RETURNING CLOB ) FROM students


Show Sharing Section ~[x:insertfile;footer.html] ~[wc:admin_footer_css]