SELECT HowTo FROM WriteSQLSyntax Part 1 : DML


In my years working in BPM, SQL syntax has played a part in almost every peice of work. The ‘long running’ characteristic of BPM requires that process and state data be persisted to a long term data store because this will ultimatley outlast the hosting server in terms of lenth of operation (some processes could go on for years). As well as using databases for peristing BPM state, almost every application that exists these days, including applications or systems that utilise BPM have a data store of some kind that will need to be queried for user or process data. It’s a given that you know SQL in this day and age.

For the experienced developer, this article is not for you.

SQL is a standard that allows for the general interaction with database data. We have the commands that manipulation existing data and the commands  that build the structure of the data (tables, schemas etc). Microsoft have a flavour of standard SQL-92 that they name Transact-SQL and split this language into 2 main categories. DML (data manipulation language) are the statements used to manipulate your data using common statements such as SELECT and UPDATE. DDL (Data Definition Language) represents the TSQL statements that assist the management and maintenance of your database objects (ALTER PROCEDURE, DROP TABLE etc).

SQL skills are seen as a basic essential requirement these days in the fields of software development, BPM, EAI, web site design and even scripting and so having a this under your belt is a must. I’ve tried to create a ‘cheat sheet’ of sorts for the DML side of the TSQL language which can be used as a quick reference, starting with the basics :

SELECT (* means ALL columns)

SELECT * FROM POItems | SELECT POL_RowID, POL_OrderNo FROM POItems

DISTINCT (Unique values, non duplicates returned)

SELECT DISTINCT POL_InvApproverName FROM POItems

SELECT INTO (selects from one table and inserts into the other)

SELECT * INTO NewPOItems FROM POItems SELECT POL_OrderNo, POL_Description INTO NewPOItems FROM POItems

WHERE (which selected data you want returned)

SELECT * FROM POItems WHERE Active = 1

AND / OR

SELECT * FROM POItems WHERE (POL_Originator = ‘nmaf’) OR (POL_Originator = ‘cnbr’)

ORDER BY

SELECT * FROM POItems ORDER BY POL_InvApproverStatus

INSERT INTO

INSERT INTO POItems (POL_RowID, POL_OrderNo, POL_Amount) VALUES (456,’54665’,45.05)

UPDATE

UPDATE POItems SET POL_InvAuthoriserName = ‘scott’ WHERE POL_OrderNo = ‘1648’

DELETE

DELETE FROM POItems WHERE POL_RowID = ‘432’ AND POL_OrderNo = ‘1489’

TOP

SELECT TOP 10 * FROM POItems / SELECT TOP 500 POL_OrderNo FROM POItems

WILDCARDS

% – Zero or more characters

[avd] – Includes one of the enclosed characters

[!avd] – Includes any character not listed here (e.g. sc[!o]tt will not return scott)

LIKE

SELECT * FROM POItems WHERE POL_OrderNo LIKE ‘165%’

SELECT * FROM POItems WHERE POL_OrderNo NOT LIKE ‘ 16% ‘

IN (Included Nested SELECT)

SELECT * FROM POItems WHERE POL_OrderNo IN (‘1651’,’1653’,’17%’,’16_7’)

SELECT * FROM POItems WHERE POL_OrderNo IN (SELECT S_OrderNo FROM POSummary WHERE Active = 1)

BETWEEN

SELECT * FROM POItems WHERE POL_OrderNo BETWEEN ‘1655’ AND ‘1685’

ALIASING (AS)

SELECT HD.POH_OrderNo, PO.POL_Level1AuthStatus, PO.POL_Level1AuthName FROM POHeader AS HD, POItems AS PO WHERE HD.POH_OrderNo = ‘15%’

INNER JOIN (pulls from different tables based on key relationship)

SELECT POItems.POL_Description, POHeader.POH_POMatched FROM POItems

INNER JOIN POInvHeader ON POItems.POL_OrderNo = POHeader.POH_OrderNo

UNION (joins selects together in one resulting table with unique distinct values)

SELECT POL_OrderNo FROM POItems UNION SELECT POH_OrderNo FROM POItems

UNION ALL (joins selects together in one resulting table with ALL results even duplicates)

SELECT POL_OrderNo FROM POItems UNION ALL SELECT POH_OrderNo FROM POItems

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s