Versions Compared

Old Version 29

changes.mady.by.user Kim Stimpson

Saved on

compared with

New Version Current

changes.mady.by.user Kim Stimpson

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Numberedheadings
number-formatdecimal
skip-headings
start-numbering-with5
h1
h2
h3
h4
h5
enabledtrue
h6
start-numbering-atH1

Getting Started with SQL

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. There are many different dialects of SQL, and whilst they are broadly similar, the more advanced dialects, like that used in PostgreSQL, offer additional functions. SQL in PostgreSQL conforms to the ANSI (American National Standards Institute) standard. To be compliant with ANSI, systems must support the major commands, including SELECT, UPDATE, DELETE, INSERT, WHERE so many statements will be similar to that used in other systems like Oracle and MS SQL Server.

SQL in pgAdmin

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.

The query window is divided into three areas:

  • an input pane for writing queries

  • an output pane to receive results

  • a scratch pad for making notes

The query windows allows the user to:

  • execute a query and see the results in the results pane

  • execute a query and save results to a text file

  • save a query as SQL

  • open a saved query

pgAdmin includes some standard scripts and functions that are available by right-clicking on table name. These allow you to view all, or a subset of the data in a table, or construct common SQL scripts.

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 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 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$$David's$$

Double quote 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

from the table you want to retrieve data from. A comma ( , )

, where a comma is used to separate the individual columns.

If you want to

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

select data

.

The SELECT statement can be used to form incredibly flexible queries 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.

Retrieving

Retrieve data from a table

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

  2. Open the Query Tool

  • Examine the table structure

    1. Compose a query

     in the Query pane

    1. 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;

    2. 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 the [select_list] using the following syntax:

    sql[select_list] can be

    a

    single column, or a list of columns from the tableRetrieving

    table.

    Retrieve unique values from a table

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

    that contain school

    1. in the general.school table, with no duplicates
      SELECT DISTINCT "administrativeward (name)" AS "Ward" FROM general.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 orderIn your query window create

    1. Create a query to list all schools by Trust, in order of largest capacity down to

    lowest

    1. smallest

    2. Try sorting in ascending and descending order
      SELECT "Trusts (name)","EstablishmentName", "SchoolCapacity" FROM general.school ORDER BY "Trusts (name)" ASC, "SchoolCapacity" DESC;

    3. Note you can

    order

    1. sort 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.

    =Equal>Less than or equal

    Operator

    Description

    Operator

    Description

    Operator

    Greater than

    <

    Less than

    >=

    Greater than or equal

    <=

    Description

    =

    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

    >

    Greater than

    AND

    Logical operator AND

    LIKE

    Return true if a value matches a pattern

    <

    Less than

    OR

    Logical operator OR

    IS NULL

    Return true if a value is NULL

    >=

    Greater than or equal

    IN

    Return true if a value matches any value in a list

    NOT

    Negate the result of other operators

    Identifying

    <=

    Less than or equal

    Identify records that meet

    particular

    conditions

    Which

    1. Run the following query, to answer the question ‘which current schools have the DFEE code of

    6386?List

    1. 6386’?
      SELECT "EstablishmentNumber","EstablishmentName","EstablishmentStatus (name)"
      FROM general.school
      WHERE "EstablishmentNumber" = 6386 AND "EstablishmentStatus (name)" like 'Open';

    2. Write a new query to list all the primary schools in the parliamentary constituency of Guildford

    Identify all Primary Schools

    1. Write a query to identify all primary schools that are currently open and have a capacity between 300 and 500

    SQL in QGIS

    QGIS can be used as an alternative interface 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

    1. in the DB Manager

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

    1. Run the last query above in the the DB Manager, then ensure Load as new layer is checked, and Execute

    2. The result of the query will be shown on the map