SQL Intermediate

Intended for:

One support staff

Performance officers or local authority report writers

Session length: 2 – 3 hours


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