SQL Intermediate
Intended for:
One support staff
Performance officers or local authority report writers
Session length: 2 – 3 hours
Outline:
SQL is the basis of all reporting options within the Capita One system and a working knowledge is a significant advantage to anybody who is required to extract information from the database. This session will talk delegates who have either attended the SQL Basics course previously, or already have a basic knowledge of SQL through some of the more advanced SQL commands.
What will be included:
- Applying filters within your join statements to return only relevant data – e.g. to return all students and any permanent exclusions within the current academic year
- Using subqueries – e.g. list all involvements and the number of reasons recorded for each of them
- ‘Self-joining’ tables – e.g. finding all other people with the same correspondence address as a student
- Using ‘Union’ to return all data from multiple subqueries as rows – e.g. total cases by caseworker and total for the whole authority
- Returning previous or next field values – e.g. end date of previous school for each student
- Restricting records to a number of rows – e.g. a student’s current school and the previous school they attended
- Pivoting rows of data into a single (comma) separated column – e.g. all outcomes rows recorded against an involvement as a single column within the involvement row
- Other useful SQL functions
- Q&A – Time to ask the consultant queries specific to your Local Authority