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 offers 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 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 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:

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

Familiarity with pgAdmin

Note the query to generate these result is displayed in the Query pane, and the Data Output is displayed within the results pane

SELECT

The most basic form of the SELECT statement retrieves data from a single table

• [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.

Exercise - use of SELECT

Compose a query in the Query pane and execute (F5 or play button)

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]. The DISTINCT clause is often used with the COUNT() function in order to determine the quantity of records of a particular type

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

• COUNT() function can be used to determine number of records

• The keyword AS can be used to set a column alias which is more meaningful to the reader

Exercise - use of SELECT DISTINCT

Use the AS keyword to give the column a sensible name, note you made need to put this in double quotes if you are including spaces of special characters

ORDER BY

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;

Exercise - ORDER BY

WHERE

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.

Exercise - WHERE

Try using different operators to perhaps find

LIMIT

LIMIT is an option clause of the SELECT statement that constrains the number of rows returned by the query. This is useful as it provides a sample of the data and with large data sets generally executes quicker than without the clause.

You can optionally use the ORDER BY clause to return the first or last records in a series e.g. the largest or smallest values.

Optionally you can also use the OFFSET clause to start from the nth record.

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.

Other

• Date

• Timestamp

• Geometry (with PostGIS installed)

Numberedheadings
number-format decimal skip-headings start-numbering-with 5 h1 h2 h3 h4 h5 enabled true h6 start-numbering-at H1
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 offers 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 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 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: sql 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 Familiarity with pgAdmin Open pgAdmin and explore the hierarchy of tables within schemas of your training database Identify the school table within the general schema Click on the SQL tab and note SQL commands necessary to create the table Right-click > school > View/Edit Data > First 100 Rows to display all the data for the first 100 records within the school table Note the query to generate these result is displayed in the Query pane, and the Data Output is displayed within the results pane SELECT The most basic form of the SELECT statement retrieves data from a single table sql [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. Exercise - use of SELECT Open the Query Tool Compose a query in the Query pane and execute (F5 or play button) Try different variation of column names, noting the order of the the column names in the query impacts the order 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]. The DISTINCT clause is often used with the COUNT() function in order to determine the quantity of records of a particular type sql [select_list] can be a single column, or a list of columns from the table COUNT() function can be used to determine number of records The keyword AS can be used to set a column alias which is more meaningful to the reader Exercise - use of SELECT DISTINCT In the Query Tool compose a query to produce a list of wards that contain the highest number of schools together with that number The the COUNT() function to determine how many records meet this condition Use the AS keyword to give the column a sensible name, note you made need to put this in double quotes if you are including spaces of special characters ORDER BY 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 Exercise - ORDER BY In your query window amend your previous query to add an ORDER BY clause Try sorting in ascending and descending order Note you can order by more than one column WHERE 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. 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 Exercise - WHERE In your query window amend your previous query to add a WHERE clause Try using different operators to perhaps find Note you can order by more than one column LIMIT LIMIT is an option clause of the SELECT statement that constrains the number of rows returned by the query. This is useful as it provides a sample of the data and with large data sets generally executes quicker than without the clause. You can optionally use the ORDER BY clause to return the first or last records in a series e.g. the largest or smallest values. Optionally you can also use the OFFSET clause to start from the nth record. sql 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. Name Description character varying(n), varchar(n) variable-length with limit character(n), char(n) fixed-length, blank padded text variable unlimited length
Numberedheadings
number-format	decimal
skip-headings	HNaN
start-numbering-with	1
h1	[h1.decimal].
h2	[h1.decimal].[h2.upper-latin]
h3	h4	h5
enabled	true
h6
start-numbering-at	H1
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
Name	Description
character varying(n), varchar(n)	variable-length with limit
character(n), char(n)	fixed-length, blank padded
text	variable unlimited length