~[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

- Network Access - The PowerSchool database server must be accessible. For self-hosted clients, this may simply be a matter of being on the district network. For hosted clients, a virtual private network (VPN) connection will be required. PowerSchool hosting will provide the necessary information to establish VPN if requested.
- Open Database Connectivity (ODBC) - This is the process of authenticating access to the PowerSchool database. The psnavigator user account should always be used when connecting to the database, as this is a read-only account.

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

- The SELECT statement is used to select data from a database.

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`

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

## WHERE

- The WHERE clause is used to extract only those records that fulfill a specified criterion.

`SELECT column_name, column_name FROM table_name WHERE column_name operator value` Example `SELECT lastfirst, gender FROM students WHERE gender = 'M'`

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

## 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

- If a column in a row has no value, then the column is said to be null, or to contain 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

- The AND operator displays a record if both the first condition AND the second condition are true.

Example `SELECT lastfirst, gender, grade_level FROM students WHERE grade_level = 10 AND gender = 'M'`

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

## OR

- The OR operator displays a record if either the first condition OR the second condition is true.

Example `SELECT lastfirst, gender, grade_level FROM students WHERE enroll_status = 0 OR enroll_status = -1`

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

## 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.

- to_char - Convert dates, LOBs, or numbers to character data types.
- to_date - Convert character data to dates.

`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]
Notes
dual is a dummy table that we can query from when we just want information from Oracle without querying a specific table
Use + or - to add or subtract a day form sysdate or current_date.
"SELECT current_date - 1 FROM dual" will give you yesterday's date.

## Get Table Definition

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

`DESC table_name`
Note - This will not work in SQL Studio
OR `SELECT table_name, column_name, data_type FROM all_tab_columns WHERE LOWER(table_name) = 'table_name'`

## ORDER BY

- The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

Example `SELECT last_name, first_name FROM students ORDER BY last_name`

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

Example - DESC `SELECT last_name, first_name FROM students ORDER BY last_name DESC`

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

## Test Your Skills

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

- Inactive students are included (active and pre-registered students only please)
- Students without an alert are included
- The results should be listed by student in alphabetical order

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

- The LIKE operator is used to search for a specified pattern in a column.

Example `SELECT course_number, section_number, room FROM sections WHERE termid LIKE '~(curyearid)%' ORDER BY schoolid, course_number, section_number`

course_number | section_number | room
--- | --- | ---
~[tlist_sql; SELECT course_number, section_number, room FROM sections WHERE termid LIKE '~(curyearid)%' AND rownum < 5 ORDER BY schoolid, course_number, section_number ;]
~(1) | ~(2) | ~(3)
[/tlist_sql]

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

- The IN operator allows you to specify multiple values in a WHERE clause.

Example `SELECT last_name, first_name, grade_level FROM students WHERE enroll_status IN (0,-1)`

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

## BETWEEN Operator

- The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

Example `SELECT lastfirst, grade_level FROM students WHERE grade_level BETWEEN 1 AND 5`

lastfirst | grade_level
--- | ---
Adair, Brandon | 5
Right, James | 2
Hempton, Emilia | 1
Randolph, Jessica | 4
Winston, Veronica | 3

## Concatenation

- Concatenation allows the combination of multiple columns into a single result using double-pipes (||)

Example `SELECT first_name || ' ' || last_name FROM students`

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

## Aliases

- SQL aliases are used to temporarily rename a table or a column heading.

Example `SELECT lastfirst, street || ' ' || city || ', ' || state || ' ' || zip AS address FROM students`

lastfirst | address
--- | ---
~[tlist_sql; SELECT lastfirst, street || ' ' || city || ', ' || state || ' ' || zip AS address FROM students WHERE rownum < 5 ;]
~(1) | ~(2)
[/tlist_sql]

In addition to aliasing columns, tables can also be aliased.
Aliases become very useful when using JOINS and the WITH clause (covered below).

## DISTINCT

- In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.
- The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax `SELECT DISTINCT column_name, column_name FROM table_name` Example `SELECT DISTINCT grade_level, home_room FROM students`

grade_level | home_room
--- | ---
~[tlist_sql; SELECT DISTINCT grade_level, home_room FROM students WHERE home_room IS NOT NULL AND grade_level < 13 AND rownum < 5 ;]
~(1) | ~(2)
[/tlist_sql]

## 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

- An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

Example `SELECT students.lastfirst, log.subject FROM log JOIN students ON log.studentid = students.id`

lastfirst | subject
--- | ---
Smith, John | Excessive Tardies
Adams, Julia | Stomach Ache
Kelsier, Abigail | Math Intervention
Zumbrow, Drake | Guidance 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 Join - selects all rows from both tables as long as there is a match between the columns in both tables.
  An inner join is the default when only the JOIN keyword is used in a query. This is the most commonly used join.

- Left Join - returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

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, Julie | A
Johnson, Cindy | T
Smith, Joel |

- Right Join - returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
  not very common

- Full Outer Join - combines the result of both LEFT and RIGHT joins.
  also not very common

## 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:

- Student Name
- Student Number
- Course Name
- Section Number
- Teacher Name
- Grade
- Storecode

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.

- AVG() - Returns the average value.
- COUNT() - Returns the number of rows.
- MAX() - Returns the largest value.
- MIN() - Returns the smallest value.
- SUM() - Returns the sum.

Example AVG() `SELECT AVG(no_of_students) FROM Sections`

no_of_students
~[tlist_sql; SELECT AVG(no_of_students) FROM sections ;]
~(1)
[/tlist_sql]

Example COUNT() `SELECT COUNT(*) FROM Students WHERE enroll_status = 0`

Count(*)
~[tlist_sql; SELECT COUNT(*) FROM Students WHERE enroll_status = 0 ;]
~(1)
[/tlist_sql]

Example MAX() `SELECT MAX(no_of_students) FROM Sections`

no_of_students
~[tlist_sql; SELECT MAX(no_of_students) FROM Sections ;]
~(1)
[/tlist_sql]

Example MIN() `SELECT MIN(no_of_students) FROM Sections`

no_of_students
~[tlist_sql; SELECT MIN(no_of_students) FROM Sections ;]
~(1)
[/tlist_sql]

Example SUM() `SELECT SUM(no_of_students) FROM Sections`

no_of_students
~[tlist_sql; SELECT SUM(no_of_students) FROM Sections ;]
~(1)
[/tlist_sql]

## 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`

course_name | no_of_students
--- | ---
~[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 ;]
~(1) | ~(2)
[/tlist_sql]

## 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, Brandon | High
James, Julie | PRE-K
Chapman, Silas | High
Zelinski, Aria | Elementary
Innman, Susan | Kindergarten

## Subqueries

- In Oracle, a subquery is a query within a query.
- Subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

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`

lastfirst | current_class_count
--- | ---
~[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 ;]
~(1) | ~(2)
[/tlist_sql]

## 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`

lastfirst | class_count
--- | ---
~[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 ;]
~(1) | ~(2)
[/tlist_sql]

## 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.

- More than one low grade: D, D-, or F
- At least one discipline log entry

This May Help

- log.entry_date is the date a log was entered. Keep it in the current year by referencing the terms table.
- log.logtypeid represents a log's logtype. -100000 is the logtypeid for a discipline log.
- terms.isyearrec is a field that indicates whether a given term is a year-long term (1 = year-long, 0 = not year-logn).
- firstday and lastday are fields in the terms table that may come in handy.

## 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`

entrydate | exitdate | grade_level
--- | --- | ---
8/3/2020 | 6/1/2021 | 12
8/3/2019 | 6/1/2020 | 11
8/3/2018 | 6/1/2019 | 10
8/3/2017 | 6/1/2018 | 9
8/3/2016 | 6/1/2017 | 8
8/3/2015 | 6/1/2016 | 7

## 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

NOTE - This section is included for reference regarding queries that may have been written years ago.
PowerSchool no longer uses custom fields. Instead, database extensions are used.
Access to extended fields found in database extensions is documented in the next section.
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.

- getCF('table_name', table_name.id, 'custom_field_name')
- getStudentsCF(students.id, 'custom_field_name')
- getCoursesCF(courses.id, 'custom_field_name')
- getSectionsCF(sections.id, 'custom_field_name')
- getSchoolsCF(schools.school_number, 'custom_field_name')
- getTeachersCF(teachers.id, 'custom_field_name')

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.

- Querying extended field data is far more efficient than querying custom fields.
- When custom fields are migrated to database extensions, PowerSchool keeps a map of the custom fields and their new (extended) equivalent. Because of this, any queries using the custom fields package will continue to function after a field has been migrated. However, the performance enhancements of extensions will not be recognized unless queries are rewritten after migration.

## 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`

lastfirst | student_number | courseList
--- | --- | ---
~[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 ;]
~(1) | ~(2) | ~(3)
[/tlist_sql]

## 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, Brandon | Algebra | B | Q1
Adair, Brandon | Algebra | A | Q2
Adair, Brandon | Algebra | B+ | S1
Adair, Brandon | Algebra | A | Q3
Adair, Brandon | Algebra | A- | Q4
Adair, Brandon | Algebra | A | S2
Adair, Brandon | Biology | C | Q1
Adair, Brandon | Biology | B | Q2
Adair, Brandon | Biology | B- | S1
Adair, Brandon | Biology | A | Q3
Adair, Brandon | Biology | C | Q4
Adair, Brandon | Biology | B | S2
Adair, Brandon | Zoology | A+ | S1
Adair, Brandon | Zoology | A | S2

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, Brandon | Algebra | B | A | B+ | A | A- | A
Adair, Brandon | Biology | C | B | B- | A | C | B
Adair, Brandon | Zoology |  |  | 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`

SELECT lastfirst, student_number, alert_medical FROM students WHERE schoolid = 100
Note There are some concerns about these results.

- Inactive students are included
- Students with no alert are included

We'll learn to solve these problems soon.
SELECT lastfirst, student_number, alert_medical FROM students WHERE schoolid = 100 AND (enroll_status = 0 OR enroll_status = -1) AND alert_medical IS NOT NULL ORDER BY lastfirst
Your job is safe... for now
SELECT lastfirst, student_number, alert_medical FROM students WHERE schoolid = 100 AND (enroll_status = 0 OR enroll_status = -1) AND alert_medical IS NOT NULL AND ( alert_medicalexpires > sysdate OR alert_medicalexpires IS NULL OR alert_medicalexpires < to_date('01/01/1950','MM/DD/YYYY') ) ORDER BY lastfirst
We showed that nurse! We deserve a raise 😜
SELECT DISTINCT city, street FROM students WHERE grade_level IN (9,10,11,12) AND (enroll_status = 0 OR enroll_status = -1) ORDER BY city, street OR... SELECT DISTINCT city, street FROM students WHERE grade_level BETWEEN 9 AND 12 AND enroll_status IN (0,-1) ORDER BY city, street
SELECT students.lastfirst, students.student_number, courses.course_name, sections.section_number, teachers.lastfirst, storedgrades.grade, storedgrades.storecode FROM storedgrades JOIN sections ON storedgrades.sectionid = sections.id JOIN courses ON courses.course_number = sections.course_number JOIN students ON students.id = storedgrades.studentid JOIN teachers ON teachers.id = sections.teacher WHERE students.schoolid = 100 AND storedgrades.grade IN ('D','D-','F') AND sections.termid LIKE '~(curyearid)%' ORDER BY students.lastfirst, courses.course_name
SELECT teachers.lastfirst, COUNT(storedgrades.dcid) FROM sections JOIN teachers ON teachers.id = sections.teacher LEFT JOIN storedgrades ON storedgrades.sectionid = sections.id AND storedgrades.grade = 'F' WHERE sections.schoolid = 100 AND sections.termid LIKE '~(curyearid)%' GROUP BY teachers.lastfirst ORDER BY COUNT(storedgrades.dcid), teachers.lastfirst
SELECT teachers.lastfirst, sections.course_number || '.' || sections.section_number, COUNT(storedgrades.dcid) FROM sections JOIN teachers ON teachers.id = sections.teacher LEFT JOIN storedgrades ON storedgrades.sectionid = sections.id AND storedgrades.grade = 'F' WHERE sections.schoolid = 100 AND sections.termid LIKE '~(curyearid)%' HAVING count(storedgrades.dcid) = 0 OR count(storedgrades.dcid) > 5 GROUP BY teachers.lastfirst, sections.course_number || '.' || sections.section_number ORDER BY COUNT(storedgrades.dcid), teachers.lastfirst
SELECT SUBSTR(first_name,1,1) || SUBSTR(middle_name,1,1) || last_name || '@gmail.com' FROM students WHERE enroll_status IN (0,-1)
WITH lowGrades AS( SELECT studentid, count(*) AS lowGradeCount FROM storedgrades WHERE storedgrades.grade IN ('D','D-','F') AND storedgrades.termid LIKE '~(curyearid)%' GROUP BY studentid ), logEntries AS( SELECT log.studentid, count(*) logCount FROM log JOIN terms ON terms.yearid = 33 AND terms.schoolid = log.schoolid AND terms.isyearrec = 1 WHERE log.logtypeid = -100000 AND log.entry_date BETWEEN terms.firstday AND terms.lastday GROUP BY log.studentid ) SELECT students.lastfirst, students.student_number, lowGrades.lowGradeCount, logEntries.logCount FROM lowGrades JOIN logEntries ON logentries.studentid = lowGrades.studentid JOIN students ON students.id = logEntries.studentid WHERE lowGrades.lowGradeCount > 1 AND logEntries.logCount > 0 ORDER BY students.lastfirst
Show Sharing Section
~[displaypref:mbaSqlLastQry]
~[x:insertfile;footer.html] ~[wc:admin_footer_css]