SQL Query Examples
PreviousNext

On this page you will find various query examples for Azure Blob XML ODBC Driver.

SQL Syntax


sql-statement :=
SELECT 
 [* | [ expression [[AS] column_name_alias] [, ...] ]
 [INTO #temp_table_name]
 [FROM table_name]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 
 [WITH  (option_name=option_value] [, ...]) ] [;]

sql-temp-table-usage := 
 SELECT ... FROM #temp_table_name 
 [[INNER | OUTER | CROSS] JOIN #temp_table_name [#alias] ON condition 
   [join_statement] 
   [join_statement] 
   ... 
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 

comment-line := 
--some comment in single line 
|
/* some block comment */  


create-stored-procedure := 
CREATE PROCEDURE proc_name 
  [@parameter_name [= 'Some Default Value']  ] 
  [@parameter_name [= 'Some Default Value']  ] 
  ...
  ...
AS 
  sql-statement; 
 [sql-statement | sql-temp-table-usage] ;
 [sql-statement | sql-temp-table-usage] ;
 ...
 ... 

call-stored-procedure := 
EXEC proc_name [value1] [,value2] ... [,valueN]

drop-stored-procedure := 
DROP PROCEDURE proc_name 

Query Single File

This example shows how to query single XML file (For multiple file use wildcard (i.e. *.xml)). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.

SELECT * FROM $ WITH (SRC='zappysys-public-bucket/cust-1.xml')

Query Multiple Files

This example shows how to query multiple XML files using wildcard (i.e. *.xml). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).
SELECT * FROM $ 
WITH (
    SRC='zappysys-public-bucket/cust*-?.xml'
    --,RECURSIVE='True' --Include files from sub folder
)

Query direct XML string (embedded inside query)

This example shows how to query direct XML string embedded inside query. This is useful for testing purpose. In real world this may not be the case because data may be coming from File or URL (API call). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.
SELECT * FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)

URL JOIN Pattern - Read unstructured data and pass to API call

This example shows how to query unstructured data and parse using Regex, XPath or XMLPath)

Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/
Examples of XmlPath: https://zappysys.com/blog/xmlpath-examples-expression-cheetsheet/
Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/

Also try following Query Examples to extract Raw data.

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
)

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='<title>([^<]*)<//title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, XmlPath or XPath. Use double pipe to split multiple columns
,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Xml template
,RawOutputExtractMode='Regex' --can be Regex, Xml, Xml or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
)

SELECT * FROM $
WITH(
Src='http://httpbin.org/get'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='$.orgin||$.url' --can be Regex, XmlPath or XPath. Use double pipe to split multiple columns
--,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Xml template
,RawOutputExtractMode='Xml' --can be Regex, Xml, Xml or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
)

SELECT c.title,o.url,o.data 
FROM $
WITH(
	 Src='http://google.com'
	,EnableRawOutputModeSingleRow='True'
	,RawOutputFilterExpr='<title>([^<]*)<//title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, XmlPath or XPath. Use double pipe to split multiple columns
	,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Xml template. Comment this line to generate default columns e.g. col1, col2...If you comment this then use c.col1 and c.col2 instread of c.title and c.content in Select and join1_xxxxx attributes
	,RawOutputExtractMode='Regex' --can be Regex, Xml, Xml or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
    ,Alias='c'
    ,join1_RequestMethod='GET'
    ,join1_src='mybucket/myservice/[$c.title$]/log.ext'
    ,join1_RequestContentTypeCode = 'TextPlain'
    ,join1_alias='o'
    ,join1_EnableRawOutputModeSingleRow='False'
)

Functions - String Manipulation

This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT 
  substr('ZappySys', 1, 5)
, printf('%s %s %.2f', 'v1', CompanyName, CompanyId)
, ('Str1' || CompanyName || 'Str2') as ConcatExample
, upper('ZappySys')
, lower('ZappySys')
, replace('ZappySys', 'Sys', 'XYZ')
, instr('ZappySys', 'Sys')
, trim('   Zappy Sys  ') trim1
, trim('@@@ZappySys@@@', '@') trim2
, rtrim('    ZappySys   ')
, ltrim('    ZappySys   ')
, length( CompanyName )
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')

Functions - Control flow

This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT 
	 COALESCE(10,20) coalesce1 --returns first non-null arg
    ,COALESCE(null,20) coalesce2 --returns first non-null arg
    ,COALESCE(null,20,30,40) coalesce3 --returns first non-null arg
    ,IFNULL(null,20) --returns first non-null arg. Same function as COALESCE but simple (just two arguments allowed)
    ,NULLIF(20,20) nullif1 --returns null if both argument same
    ,NULLIF(10,20) nullif2 --returns null if both argument same
FROM $ WITH (
FILTER='$.Info.Rows[*]',         
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
</Info>
')

Functions - Date/Time

This example shows how to use date/time functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

XML driver supports following 5 datetime functions. The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns YYYY-MM-DD HH:MM:SS.

date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

=======================================
time string (input data)
=======================================
A time string (input) can be in any of the following formats. You can hard code or supply from column.

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

=======================================
format specifier for strftime function
=======================================
Use below format specifiers to output date in desired format. For example to output date in MM-DD-YYYY use below select query

select STRFTIME('%Y-%m-%d','now') formatted_date_time from mytable
select STRFTIME('%Y-%m-%d',[my column]) formatted_date_time from mytable

%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

=================
Modifiers
=================
The time string (input) can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

For example to add 5 days in current date you call it as below

select DATE('now', '+5 day') from mytable

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

The 'weekday' modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.
SELECT 
 DATE('now') date_now
,STRFTIME('%Y-%m-%dT%H:%M:%f','now') formatted_Date_time
,DATETIME('now') datetime_now
,DATE('now','localtime') datetime_now_local
,TIME('now') time_now
,JULIANDAY('now')
,DATE('now', 'start of month', '+1 month' , '-1 day' ) dt_modify1
,DATE('now', '+5 day') dt_modify2

FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')

Functions - Math

This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT 
	 abs(-500)
    ,random()
    ,round(1000.236, 2)
FROM $WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
</Info>
')

Functions - String Manipulation

This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT 
  substr('ZappySys', 1, 5)
, printf('%s %s %.2f', 'v1', CompanyName, CompanyId)
, ('Str1' || CompanyName || 'Str2') as ConcatExample
, upper('ZappySys')
, lower('ZappySys')
, replace('ZappySys', 'Sys', 'XYZ')
, instr('ZappySys', 'Sys')
, trim('   Zappy Sys  ') trim1
, trim('@@@ZappySys@@@', '@') trim2
, rtrim('    ZappySys   ')
, ltrim('    ZappySys   ')
, length( CompanyName )
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')

Functions - Regular Expression

This example shows how to regular expression functions.
SELECT 
 regex_ismatch(SSN, '\d{3}-\d{3}-\d{4}') IsValidSSN, --check pattern found
 regex_replace(SSN, '\d','*') MaskedSSN, --search/replace pattern
 regex_search(SSN, '\d{4}')  SSNLast4Digits, --extract substring using pattern search
 regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',0, 2)  DomainOf1stEmailByIndex, --extract 1st email (zero based index) and get domain name (by group name). For 2nd argument you can use numeric index or named argument. Both arguments are zero based index. 
 regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',2, 2)  DomainOf3rdEmailByIndex, --extract 3rd email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index.
 regex_search('a@x.com | b@y.com | c@z.com', '(?<name>\w+)@(?<domain>\w+.com)','-0', 2)  DomainOfLastEmail, --extract last email (use negative sign in quote - zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index.
 regex_search(
       'a@x.com | b@y.com | c@z.com'
     , '(?<name>\w+)@(?<domain>\w+.com)'
     , '*' --occurrence match index ( * , N or -N ) negative number will do reverse find (e.g. to get last match use -0 , to get second last use -1)
     , 2  --group index from match (default is full match or -1)
     --, 0  -- ignore case for pattern match (0=strict match, 1=ignore case, default is 0) 
     --, 0  -- throw error if not matched 0=don't throw error, 1=throw error
     --, '#'  -- separator for multi match
    )  GetAllDomains --extract domains from all emails and show as email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index.    
FROM $ WITH (Filter='$.data.row[*]', DATA= '<data>
<row><SSN>111-222-4444</SSN><Email>a@x.com | b@y.com | c@z.com</Email></row>
<row><SSN>111-X2Z-AB44</SSN><Email>a@x.com | b@y.com | c@z.com</Email></row>
</data>')

Query using filter option

This example shows how to query data using direct Filter expression and some other filter related settings.
SELECT * FROM $
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data parentCol1="p1" parentCol2="p2">
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,Filter='$.data.row[*]' -- for more information check this https://zappysys.com/blog/xmlpath-examples-expression-cheetsheet/
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row)
,IncludeParentColumns='true'  --this will include any columns found above filtered node (e.g. rows).
,ParentColumnPrefix='p_'
)

Pivot Value (Columns to Rows)

This example shows how to pivot column to rows (e.g. Transform property name as row value). By default Tag name (i.e. Property) outputs as Column name but when your column name is Dynamic then you can use Pivot Feature to transform the data.
SELECT 
  Pivot_Name as ProjectName, 
  Pivot_Value_id as Id, 
  Pivot_Value_code as Code
FROM $
WITH (
Filter='$.data',
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<project1><id>1</id><code>AAA</code></project1>
	<project2><id>2</id><code>BBB</code></project2>
	<project3><id>3</id><code>CCC</code></project3>    
</data>',
EnablePivot='True'
)

Language - Using Placeholder Functions

This example shows how to use placeholders functions anywhere in your SQL Query. Placeholders are replaced in SQL text before Parser so you can use it anywhere in SQL. Another advantage of placeholder function is it doesn't invoke client side query processing engine unlike other advanced SQL constructs (e.g. WHERE, GROUP BY, ORDER BY, UDF). Placeholders are searched and replaced before parser can analyze the SQL query. Here are list of placeholder functions.

<<your_file_path,FUN_READFILE>>
<<input_text,FUN_BASE64ENC>>
<<input_text,FUN_BASE64ENC>>
<<input_text,FUN_BASE64DEC>>
<<input_text,FUN_URLENC>>
<<input_text,FUN_URLDEC>>
<<date_format,FUN_TODAY>>
<<date_format,FUN_TO_UTC_DATE>>
<<date_format,FUN_TO_UTC_DATETIME>>
<<date_format,FUN_TO_LOCAL_DATE>>
<<date_format,FUN_TO_LOCAL_DATETIME>>

Full list of functions found here: https://zappysys.com/links/?id=10103

Where date_format can be from any valid format specifiers from here https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Examples:
select * from $ WITH (SRC='http://httpbin.org/get', HEADER='Authtorization: Basic <<Myuser12:MyP@ssW!,FUN_BASE64ENC>>')
select * from $ WITH (SRC='http://httpbin.org/get?id=<<FUN_NEW_GUID>>')
select * from $ WITH (SRC='<<c:/temp/path.txt,FUN_READFILE>>')
select * from $ WITH (SRC='http://httpbin.org/get?startdate=<<yyyy-MM-dd HH:mm:ss,FUN_TODAY>>')
select * from $ WITH (SRC='http://httpbin.org/get', HEADER='date:<<2012-01-01T00:00:00,FUN_TO_UTC_DATE>>')
SELECT * FROM $ WITH(SRC='mybucket/myservice/myfile_<<yyyy-MM-dd,FUN_TODAY>>.dat')  --read today's file

Language - Group By / Limit / Order By

This example shows how to use GROUP BY / ORDER BY and LIMIT clauses.
SELECT 
  Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM "Root.value" 
WHERE Discount > 0
--AND DATETIME(OrderDate)<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
GROUP BY Country 
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY Invoice_Total DESC --,DATETIME(OrderDate)
LIMIT 3
WITH (SRC='zappysys-public-bucket/invoices.xml')

Language - Case Statement

This example shows how to write CASE statement to select / output different value based on multiple conditions.
SELECT 
  name 
, CASE id 
 WHEN 1 THEN 1+1 
 WHEN 2 THEN 2+2 
 ELSE 0 
 END ThisIsCaseColumn 
FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)

Language - SELECT INTO (Temporary Table) / Multi Statements

This example shows how to use SELECT INTO statement to save result into temporary table and use it later in the same batch. Temp table is indicated by # prefix (e.g. #tmp). This example also uses multiple statements in the same batch. If you have multiple results in the same batch (i.e. SELECT * FROM ) then only last result is returned and others results are discarded. Each statement must be separated by semi-colon ( ; )
SELECT * INTO #tmp FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
	<row><id>1</id><name>AAA</name></row>
	<row><id>2</id><name>BBB</name></row>
    <row><id>3</id><name>CCC</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

--//second statement in the same batch 
select * from #tmp 
where id > 1
--AND SomeDateColumn='2012-12-31 00:00:00'  --DateTime column can be queried this way... Must use 'yyyy-MM-dd HH:mm:ss' format
;           

Language - JOIN Statement using SELECT INTO / Multi Statements

This example shows how to use SELECT INTO statement and use of multiple statements in the same batch. Output from only last SELECT is returned if you have multiple SELECT * FROM in the same batch. Each statement must be seperated by semi-colon ( ; )
SELECT * INTO #cust FROM [data.row] 
WITH (
--enter path in SRC or use static value in DATA
--SRC='zappysys-public-bucket/api/customers.xml'
--SRC='zappysys-public-bucket/cust*.xml'
--SRC='zappysys-public-bucket/customers.xml'
DATA='
<data>
	<row><customerid>1</customerid><name>AAA</name></row>
	<row><customerid>2</customerid><name>BBB</name></row>
    <row><customerid>3</customerid><name>CCC</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

SELECT * INTO #ord FROM [data.row] 
WITH (
DATA='
<data>
	<row><orderid>1000</orderid><customerid>1</customerid><orderdate>2012-01-01T00:00:00</orderdate><total>2000.50</total></row>
	<row><orderid>1001</orderid><customerid>1</customerid><orderdate>2012-01-02T00:00:00</orderdate><total>1000.50</total></row>
    <row><orderid>1002</orderid><customerid>2</customerid><orderdate>2013-01-01T00:00:00</orderdate><total>5000.70</total></row>
    <row><orderid>1003</orderid><customerid>2</customerid><orderdate>2013-01-02T00:00:00</orderdate><total>6000.70</total></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
);

--//second statement in the same batch 
SELECT c.customerid,o.orderid,o.orderdate,o.total 
FROM #cust c
INNER JOIN #ord o ON c.customerid=o.customerid
--LEFT OUTER JOIN #ord o ON c.customerid=o.customerid
--WHERE o.orderdate='2012-01-01 00:00:00' --for datetime column format must be queried in 'yyyy-MM-dd HH:mm:ss' format only
;           

Language - Basic SQL queries

Basic query examples.

--Whenever Possible try to write Simple Query. When you use WHERE / GROUP BY / ORDER BY Expressions. Driver may invoke Client side processing if server side doesnt support that construct.

--Line that starts with two dashes is a comment and wont be executed
--Any query must contain at least two required parts
--data source (or table) names and a list of column names
--column names come first (preceded by SELECT keyword)
--table names follow (preceded by FROM keyword)

--Here is the query that returns all rows with all columns (* stands for all columns) from 'Accounts' table
SELECT * FROM Accounts

--query with compound column and table names
SELECT "Account ID" FROM "Closed Accounts"
SELECT [Account ID] FROM [Closed Accounts]

--returns all rows from 'Accounts' table, each row will have Id and Amount columns
SELECT Id, Amount FROM Accounts

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater or equal to 1000.0
SELECT Id, Amount FROM Accounts WHERE Amount >= 1000.0

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater than 1000 or DepartamentID is equal to 123 
SELECT Id, Amount FROM Accounts WHERE (Amount > 1000.0 OR DepartamentID = 123)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 1, 101 or 202 
SELECT Id, Amount FROM Accounts WHERE DepartamentID IN (1, 101, 202)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 321 and Amount is less than 100 
SELECT Id, Amount FROM Accounts WHERE DepartamentID = 321 AND Amount < 100

--returns only those rows of 'Persons' table that meet next condition: Name column value starts with 'John'
SELECT * FROM Person WHERE Name LIKE 'John%'

--case statement
SELECT 
 FirstName,
 LastName,
 CASE Country 
  WHEN 'US' THEN 'NA' 
  WHEN 'Canada' THEN 'NA'
  WHEN 'India' THEN 'Asia'
  WHEN 'China' THEN 'Asia'
  ELSE 'Unknown' 
 END ColRegion 
 FROM Person 


Copyrights reserved. ZappySys LLC.