SQL

Guidance and tips for accessing data via databases with SQL

What is SQL

SQL or Structured Query Language, is a programming language used to talk to relational database management systems.

What is SQL for

SQL servers are where most of DfE’s data is held, making it ideal for database management.

SQL provides us with a language primarily for querying databases to extract data, though it is also capable of some basic data processing and analysis.

How to install SQL

Download SSMS from the DfE software center, talk to your team about getting access to the appropriate SQL servers and databases where the data you need to access is held and start writing SQL queries.

There are usually a couple of different versions available for software on the software center, we’d recommend you always go for the latest (newest) version possible.

If you can’t find the option in the software centre, then you may need to raise a service desk request to make SQL server management studio visible in your software centre.

Best place to start

Andy Brook’s excellent Introduction to SQL session, giving a visual overview of the basics of querying with SQL:

Best practice

Here are some tips to follow best practice in your SQL code, making it easier to read and pick up if another person is running your code. Following best practice guidance will help you to achieve RAP best practice with clean final code.

  • Avoid any trailing whitespace
  • Always capitalize SQL keywords (e.g., SELECT or AS)
  • Variable names should be in snake case - lower case words separated by underscores (e.g. pupil_age instead of PupilAge)
  • Comments should go near the top of your query, or at least near the closest SELECT
  • Try to only comment on things that aren’t obvious about the query (e.g. why hardcoded filters are used, how to update them)
  • Where possible, use Common Table Expressions (CTEs) early and often, and name them descriptively (e.g. “pupil_age_table” rather than “p”)

GitLab have produced a full SQL style guide, which we recommend following where possible.

How to work with SQL

SSMS is the best tool to get started with writing SQL queries and saving SQL scripts that produce your desired outputs.

Once you have saved SQL scripts or are more familiar with writing SQL queries on the fly, you can look at running your scripts or lines of SQL code directly in R. This will streamline your process, saving copying and pasting SQL outputs into csvs, and ultimately help with reaching RAP best practice by aiding production of a single publication production script

Quick reference lookup

  • w3schools.com offers a useful guide through the most common SQL commands.

Other resources

  • This tutorial script by Tom Franklin is a particularly good starting point as it includes the data you are manipulating, so you don’t need to worry about connecting to or getting access to specific databases before you can then run anything. Simply open up Microsoft SQL Server Management Studio and start playing with that query.

  • Avision Ho created the this SQL training course.

  • The Khan academy offers a great free introduction to the basics of SQL.

  • It’s also worth taking a look at Jon Holman’s presentation on ‘good to know’ SQL functions.

  • MoJ have produced a SQL from square one guide to using CTE’s in SQL as well as running SQL from RStudio

Andy’s follow up intermediate SQL session, covering more advanced features of SQL:

Tips for using SQL

Setting up a SQL area

Before you set up a SQL database, make sure you have the following information to pass on:

  • The name of the database you want to set up - Different servers will have different naming conventions, make sure to check this with the server owner before you confirm the name.
  • Who the database owners should be - This will most likely be yourself, but you can have multiple (e.g. your team leader). It can be helpful to have more than one owner, so one can grant permissions when the other is unavailable.
  • Who should have access, and what their access levels should be - Users can have read or read/write access. Make sure you have a list of users (with their AD names) and their access levels ready.
  • The database structure - Do you need certain schemas setting up? This will help organise your database. Without schemas, all tables will be saved under [dbo].

There are a few common servers that statistics producers (and analysts in general) make use of at DfE. Use the following contacts below to pass on the above information to get your new database set up:

  • PDR (T1PRMDRSQL,55842) - contact the PDR team
  • PDB16 (3DCPRI-PDB16) - raise a request through the service desk under “non-standard” > “any other request”
  • Analyse & Modelling server (T1PRANMSQL,60125) - raise a request on the service desk under the following options:

Managing access

To gain access to a SQL database, you must have written confirmation from the database owner specifying whether your access is read-only or both read and write.

If the area you require access to is in the T1PRMDRSQL,55842 SQL server, contact the PDR team with your permission attached, stating the name of the database you want access to.

If the area is in any other server, raise a request through the central IT service portal under “non-standard” > “any other request”. In your request make sure you attach the written confirmation and specify:

  • The server name
  • The database name
  • Whether it’s read or write access you need

Moving data to different areas

Information on how to do this in R can be found in our RAP for Statistics page

Back to top