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. 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, 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 quotes must 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, where a comma is used to separate the individual columns. To return all columns of the table, use an asterisk ( * ) in place of [select_list]. The [select_list] may also contain an expressions or literal values.

The FROM clause specifies the name of the table from which you want to select data

The SELECT statement can extended by including additional clauses:

• Select distinct rows using DISTINCT operator.

• Sort rows using ORDER 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.

Retrieve data from a table

1. Examine the structure of the general.school table in pgAdmin

2. Open the Query Tool

3. Compose a query to return the name of the school, postcode, and phase of education then, Execute (F5 or play button), for example
   SELECT "EstablishmentName","Postcode","PhaseOfEducation (name)"
FROM general.school;

4. Try different variation of column names, noting the order of the the column names in the query impacts the sequence of columns in the results

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 a table.

Retrieve unique values from a table