Querying data

A simple query might return a sub-set of the data held within the database. In English there are multiple ways of posing a question, whereas in SQL you need to be specific about the language you use and the precise columns, tables, schemas and databases you are referring to. Fortunately, pgAdmin provides some tools to help construct these queries.

So if you wanted to generate a list of names of all the Parishes and their areas in hectares within the county of Surrey, the SQL query needed might look like this:

SELECT name, hectares
FROM boundaryline.parish
WHERE file_name = 'SURREY_COUNTY';

Note keywords or statements like SELECT, FROM and WHERE are not case case sensitive (SELECT = select = SeLecT), but by convention we use uppercase for all SQL keywords in order to make queries easier to read. Queries can be composed as a single line, or spread over a number of lines to ease reading. A semicolon ( ; ) is used to signal to PostgreSQL the end of an SQL statement

Single Quotes contain string values e.g. name = ‘Epsom’. Only use these with string data types such as text and character varying.

String values are case sensitive so SELECT * FROM general settlement WHERE name like 'Epsom'; and SELECT * FROM general settlement WHERE name like 'ePsOm'; will produce different results.

If the string itself includes a quote, replace the single quotes with $$ e.g. name = $$St. David’s$$

Double quote enclose database object names that:

• include a space

• use upper case characters

• use ‘special’ characters like punctuation

• are a reserved SQL word

SELECT

The most basic form of the SELECT statement retrieves data from a single table. The syntax is as follows

SELECT [select_list]
FROM [table_name];

• [select_list] can be a single column, or a list of columns from the table you want to retrieve data from. A comma ( , ) is used to separate the individual columns. If you want to return all columns of the table you can use an asterisk ( * ) in place of [select_list]. The [select_list] may also contain an expressions or literal values.

• The FROM clause is optional and specifies the name of the table from which you want to query data.

The SELECT statement can be used to form incredibly flexible queries by including additional clauses

• Select distinct rows using DISTINCT operator.

• Sort rows usingORDER BY clause.

• Filter rows using WHERE clause.

• Select a subset of rows from a table using LIMIT or FETCH clause.

• Group rows into groups using GROUP BY clause.

• Filter groups using HAVING clause.

• Join with other tables using joins such as INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.

• Perform set operations using UNION, INTERSECT, and EXCEPT.

SELECT DISTINCT

The DISTINCT clause is used in a SELECT statement to remove duplicate values. It can be applied to one or more columns in the [select_list] using the following syntax:

• [select_list] can be a single column, or a list of columns from the table

Retrieving unique values from a table

1. In the Query Tool compose a query to produce a list of wards that contain school

Sorting results

The ORDER BY clause allows you to sort rows returned by a SELECT clause in ascending or descending order using ASC or DESC options. It is possible to sort based on multiple columns by separating expressions with a comma ( , ).

The syntax employed follows this format;

sql

Sort data into ascending or descending order

1. In your query window create a query to list all schools by Trust in order of largest capacity down to lowest

2. Try sorting in ascending and descending order

4. Note you can order by more than one column

WHERE

The syntax of the WHERE clause can be used to return records that satisfy a specified condition. The condition must evaluate as true or false with only records where condition is true to be included within the result set. The following comparison and logical operators may be used to form the condition. Logical operators can be used to combine additional WHERE clauses

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<> or !=	Not equal
AND	Logical operator AND
OR	Logical operator OR
IN	Return true if a value matches any value in a list
BETWEEN	Return true if a value is between a range of values
LIKE	Return true if a value matches a pattern
IS NULL	Return true if a value is NULL
NOT	Negate the result of other operators

Identifying records that meet particular conditions

1. Which current schools have the DFEE code of 6386?

3. List all the primary schools in the parliamentary constituency of Guildford

4. Identify all Primary Schools that are currently open and have a capacity between 300 and 500

SQL in QGIS

pgAdmin has a query window to use for interaction with the database with user-defined SQL. You can access it through the Tools menu or the right click menu. QGIS can be used as an alternative to pgAdmin for running SQL queries.. Note if you want to display the distribution on the map you need to include the geometry column

Display the distribution of data

1. Launch QGIS and open the SQL Window within the DB Manager

2. Display the distribution of all currently open primary schools with a capacity between 300 and 500