Learn the Basics of PI Programming - Review of SQL Select Statements

Version 1

    SQL stands for Structured Query Language. SQL is an American National Standards Institute (ANSI) definition for the language used to communicate with relational database systems. It is used by virtually all relational databases in the world today.

     

    SELECT, FROM and WHERE

     

    A common SQL syntax starting command is SELECT which is used to query the database.  The data retrieved from the statement is based on the criteria specified in the SELECT statement.

    Following the SELECT command identifies the columns to be selected from the tables(s).

    SELECT *

    • retrieves all the columns from the table being referenced.

    SELECT column1, column2, column3

    • retrieves 3 columns of the table being referenced.

     

    The FROM command identifies the first (or perhaps only) table being queried.

    SELECT * FROM tablename

    • retrieves all the columns from tablename.

    SELECT column1, column2, column3 FROM tablename

    • retrieves all data for the 3 columns of tablename.

     

    The WHERE command contains criteria to filter the data being retrieved.

    The conditional operators include:

    • equal (=)
    • greater than (>)
    • less than (<)
    • greater than or equal (>=)
    • less than or equal (<=)
    • not equal to (<>)
    • LIKE (which is a pattern matching operator)

    Note:  If the conditional clause is set to compare to text, the text value is encased in single quotes (‘text’).

    SELECT * from tablename WHERE column1 = 5

    • Retrieves only rows where column1 has a value equal to the number 5.

     

    AND, OR and LIKE

     

    AND indicates both statements must be TRUE for the row to be returned when the query is executed.

    SELECT column1, column2, column3 from tablename WHERE column1 = 5 and column2 = ‘junk’

    • retrieves only rows where column1 has a value equal to the number 5 and column2 value equals junk.


    OR returns data rows if either condition is met

    SELECT column1, column2, column3 from tablename WHERE column1 = 5 or column2 = ‘junk’

    • retrieves only rows where column1 has a value equal to the number 5 or column2 value equals junk.


    LIKE is used to search for a specific pattern in a column.  In conjunction with the LIKE operator a wildcard of % is used for comparison.  The % can represent a single character or multiple characters.  Another wildcard is the underscore (_) which can be used to represent a single character.

    SELECT * from tablename WHERE column2 LIKE ‘%unk’

    • retrieves rows from tablename where column2 values end with the letters ‘unk’

    SELECT * from tablename WHERE column2 LIKE ‘%un%’

    • retrieves rows from tablename where column2 values contain the letters ‘un’

    SELECT * from tablename WHERE column2 LIKE ‘_un_’

    • Retrieves rows from the tablename where column 2 values only contains 4 characters and the middle two characters are un.

    SELECT * from tablename WHERE column2 LIKE ‘j%’

    • retrieves rows from tablename where column2 values start with the letter ‘j’

     

    Using Aliases

     

    Sometimes table name or columns are lengthy or lack clarity.  Using an ALIAS can simplify typing and clarify table field names that are otherwise unclear. The “AS” command defines an ALIAS for the item prior to the AS with the abbreviation following the command.

     

    SELECT a.* FROM [Employees].[Addresses] as a

     

    In the above statement, “a” can be used to identify the table instead of the full [Employees].[Addresses] table name.  Aliases become more significant when creating joins.

     

    Joining Tables

     

    Rarely does data exist in one place or in one table.  Sometimes the results of a query has to come from a correlation of two or more distinct tables.  To JOIN tables, a relationship is required between the tables and must be identified in the SQL statement.

     

    Two key words are used when creating joins between tables.  The words JOIN and ON can be used in the statement to identify the relationship between the tables being used.  The key word ON sets up the relationship of columns in the selected tables so the desired rows are returned. For example, our organization has two tables, [Names] and [Addresses]. The [Names] table has columns corresponding to EmployeeID, FirstName, MiddleName, and LastName. The [Addresses] table has columns corresponding to EmployeeID, Country, State, City, Street, Number, and Apartment. If we want a list of our employee’s Names along with the City they live in, we have to join these two tables and select the columns we desire:

     

    SELECT n.FirstName, n.LastName, a.City

    FROM [Employees].[Names] as n JOIN [Employees].[Addresses] as a

    ON n.EmployeeID = a.EmployeeID

    • Retrieves the first name, last name and city of each employee by joining these two tables on a common unique identifier.