Hubspot Connector - Bulk Load Query Examples
Contents
- Driver SQL Syntax
-
SQL Examples
- 1. INSERT
- 2. UPDATE
- 3. DELETE
- 4. UPSERT
- 5. Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause
- 6. Bulk DELETE (Read from MSSQL, ODBC) - SOURCE Clause
- 7. Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause
- 8. Custom Metadata / Override Mode
- 9. Using Connection Clause
- 10. Using Direct ConfigText in Connection Clause
- 11. DateTime field Compare in WHERE / ORDER BY
- 12. WITH / CONNECTION Clause
- 13. Filter on Date, Group By, Order By, Limit Clause
- 14. Case Statement
- 15. SELECT INTO / Multiple Statements
- 16. UNION ALL / UNION Statement
- 17. Basic SQL queries
Driver SQL Syntax
This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.
This driver offers highly flexible Client Size SQL Query engine which runs on client side to offer rich query language like a traditional RDBMS. Keep in mind that its not same as traditional database SQL so many features might not be supported also this engine is invoked on client side so there might be some performance issue if you have very large dataset. In below section you can see high level SQL Grammer / Syntax for writing SQL query for this driver.UNION / UNION ALL Syntax
You can use UNION or UNION ALL key word to output multiple temp tables with similar structure in a single resultset. UNION ALL keyword doesnt apply DISTINCT operation to eliminate duplicate rows so its faster.select_from_temp_table_1
UNION | UNION ALL
select_from_temp_table_2
[UNION | UNION ALL]
select_from_temp_table_3
...
...
Stored Procedure Syntax
This driver supports writing Stored Procedure. This is useful when writing reusable parameterized SQL for ease of use. Rather than calling long SQL from application you can call short name with parameters to invoke same logic. Stored procedure support multi-statements using TEMP tables (i.e. SELECT ... INTO #mytemptable clause). To invoke Stored Proc use EXEC keyword before its name. See below Syntax.CREATE 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] ;
...
...
Executing PROCEDURE
EXEC proc_name [value1] [,value2] ... [,valueN]
Deleting PROCEDURE
DROP PROCEDURE proc_name
INSERT Syntax
INSERT INTO table_name ( column_name_1 [, column_name_2] ... )
VALUES (value1 [, value2] ... )[;]
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
INSERT Bulk Syntax
INSERT INTO table_name ( column_name_1 [, column_name_2] ... )
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
,'driver_specific_connection_string'
,'driver_specific_sql_query'
] [;]
UPDATE Syntax
UPDATE table_name
SET column_name_1 = some_value
[,column_name_2 = some_value]
....
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
UPDATE Bulk Syntax
UPDATE table_name
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
,'driver_specific_connection_string'
,'driver_specific_sql_query'
] [;]
DELETE Syntax
DELETE FROM table_name [WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
DELETE Bulk Syntax
DELETE FROM table_name
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
,'driver_specific_connection_string'
,'driver_specific_sql_query'
] [;]
SQL Examples
INSERT
This example shows how to call INSERT row action for a specified table. Optionally you can supply Parameters via WITH clause.INSERT INTO MyTable(Col1,Col2) Values('something', 1234)
--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
-- , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
-- , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')
--WITH(Param1='something', Param2='something')
--CONNECTION( UserName='user1'
-- , Password='mypassword123'
-- , Provider='Custom'
-- , ConfigFile='c:\etl\myapi-connector.xml'
-- , ServiceUrl='https://myapi.com/v1'
-- , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
-- , AuthName='Http')
UPDATE
This example shows how to call UPDATE on a table. Optionally you can supply Parameters via WITH clause.UPDATE MyTable SET Col1='something', Col2=1234
--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
-- , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
-- , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')
--WITH(Param1='something', Param2='something')
--CONNECTION( UserName='user1'
-- , Password='mypassword123'
-- , Provider='Custom'
-- , ConfigFile='c:\etl\myapi-connector.xml'
-- , ServiceUrl='https://myapi.com/v1'
-- , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
-- , AuthName='Http')
DELETE
This example shows how to call DELETE row action for a specified table. Optionally you can supply Parameters via WITH clause.DELETE FROM MyTable
--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
-- , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
-- , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')
--WITH(Param1='something', Param2='something')
--CONNECTION( UserName='user1'
-- , Password='mypassword123'
-- , Provider='Custom'
-- , ConfigFile='c:\etl\myapi-connector.xml'
-- , ServiceUrl='https://myapi.com/v1'
-- , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
-- , AuthName='Http')
UPSERT
This example shows how to call UPSERT action for a specified table. Optionally you can supply Parameters via WITH clause.UPSERT INTO MyTable(Col1,Col2) Values('something', 1234)
--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
-- , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
-- , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')
--WITH(Param1='something', Param2='something')
--CONNECTION( UserName='user1'
-- , Password='mypassword123'
-- , Provider='Custom'
-- , ConfigFile='c:\etl\myapi-connector.xml'
-- , ServiceUrl='https://myapi.com/v1'
-- , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
-- , AuthName='Http')
Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause
This example shows how to use SOURCE clause to perform Bulk INSERT operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to INSERT command. Make sure you external SQL outputs exact same column names expected by Target table used in Insert. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'your-sql')
INSERT INTO MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')
--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
WITH(MyParam1='something', MyParam2='something')
Bulk DELETE (Read from MSSQL, ODBC) - SOURCE Clause
This example shows how to use SOURCE clause to perform Bulk DELETE operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to DELETE command. Make sure you external SQL outputs exact same column names expected by Target table used in Delete. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'your-sql')
DELETE FROM MyTable
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')
--For bulk input, map columns in External Query (Must use alias column name to match DELETE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToDelete.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
WITH(MyParam1='something', MyParam2='something')
Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause
This example shows how to use SOURCE clause to perform Bulk UPDATE operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to UPDATE command. Make sure you external SQL outputs exact same column names expected by Target table used in Update. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'your-sql')
UPDATE MyTable
--SET clause is omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')
--For bulk input, map columns in External Query (Must use alias column name to match UPDATE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToUpdate.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
WITH(MyParam1='something', MyParam2='something')
Custom Metadata / Override Mode
SELECT id,name,checknumber FROM MyTable
WITH
(
--//Custom Metadata (Direct Mode)<<<
--//Add all possible columns here and it will be part of metadata (this approach ignores any metadata comes from table itself). This approach usually helpful when Table has dynamic data (scan 300 rows to detect types) which may cause issue if zero records found.
META = 'id:int;checknumber:string(10);name:string(10);some_extra_column:datetime'
--//Custom Metadata with Override (Direct Mode)<<<
--You can use @OverrideMode:1 in the column list to override meta for specific column(s) only and use all other columns from reported metadata for table - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--META = '@OverrideMode:1;id:int;checknumber:string(10)'
--//Metadata from File (supply metadata stored in file)
--META = 'c:\mytable_meta.txt'
)
Using Connection Clause
This examples shows how to supply connection information via CONNECTION clause in the sql query. CONNECTION clause takes precedence over the default values configured for data source using UISELECT * FROM Customers
--WITH(Param1 = 'something', Param2 = 'something')
CONNECTION(
, Provider = 'OData'
, ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
--, Provider = 'Custom'
--, ConfigFile = 'C:\connectors\odata-connector.xml'
--, AuthName = 'BasicAuth'
--, UserName = 'user1'
--, Password = 'pass1'
--, Parameters = '[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)
Using Direct ConfigText in Connection Clause
This examples shows how to supply direct ConfigText (connector xml) in the optional CONNECTION clause of SQL query. CONNECTION clause takes precedence over the default values configured for data source using UISELECT * FROM Customers
--WITH(Param1='something', Param2='something')
CONNECTION(
-- ,Provider='OData'
,ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc'
,Provider='Custom'
,UseEmbeddedConfig=1
,ConfigText='<ApiConfig>
<ServiceUrls>
<ServiceUrl Url="https://services.odata.org/V3/Northwind/Northwind.svc" />
</ServiceUrls>
<EndPoints>
<EndPoint Name="Customers" Url="/Customers?$format=json" Method="GET" Filter="$.value[*]" Body="" />
</EndPoints>
</ApiConfig>'
-- ,ConfigFile='C:\connectors\odata-connector.xml' --to use file mode remove UseEmbeddedConfig, ConfigText and set Provider='Custom'
-- ,AuthName='BasicAuth'
-- ,UserName='user1'
-- ,Password='pass1'
-- ,Parameters='[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)
DateTime field Compare in WHERE / ORDER BY
This example shows how to use date time field in WHERE or / ORDER BY. Date Time field needs special handling. You have to use DATETIME function else it will do string compare rather true date time compare. Foe example WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR WHERE OrderDate<=DATE('1997-12-31'). You must use DATETIME either left side or right side. 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)SELECT * FROM Orders
WHERE 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)
ORDER BY DATETIME(OrderDate)
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');
WITH / CONNECTION Clause
This example shows how to use WITH clause along with Connection clause. Use WITH clause to supply request parameters (Click on [Query Builder] to build request correctly). You can use CONNECTION clause to override any connection level parameters.SELECT * FROM Customers
LIMIT 3
WITH($filter='Country eq ''UK''')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc')
Filter on Date, Group By, Order By, Limit Clause
This example shows how to use WHERE clause and search by datetime, use GROUP BY / HAVING, ORDER BY and LIMIT clauses. You can use OData Connector with Northwind Service URL to run this example.SELECT
Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM Invoices
WHERE Discount > 0
AND OrderDate<=DATETIME('1997-12-31 00:00:00')
--or use OrderDate<=DATE('1997-12-31')
--Valid formats are yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.fff (where fff is milliseconds)
GROUP BY Country
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY
Invoice_Total DESC
--,DATETIME(OrderDate)
LIMIT 3
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');
Case Statement
This example shows how to write CASE statement to select / output different value based on multiple conditions.SELECT
CustomerID
, CompanyName
, Country
, Region
--Pattern#1 - Match by clause
, CASE
WHEN Country IN('UK','Germany','France') OR Region='BC' THEN 'EU'
WHEN Country IN('USA','Maxico','Canada') THEN 'NA'
WHEN Country IN('Brazil','Argentina','Venezuela') THEN 'SA'
ELSE 'Unknown'
END Pattern1
--Pattern#2 - Match by static value or simple expression
, CASE Country
WHEN 'U'+'K' THEN 'EU'
WHEN 'USA' THEN 'EU'
WHEN 'Brazil' THEN 'SA'
WHEN 'Argentina' THEN 'SA'
WHEN 'Venezuela' THEN 'SA'
ELSE 'Unknown'
END Pattern2
FROM Customers
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc')
SELECT INTO / Multiple 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 separated by semi-colon ( ; ). Also notice use or DateTime column. To compare date in WHERE clause or use Order BY clause you must use DATETIME function else it will be treated as string value (e.g. use WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59'). 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)SELECT * INTO #tmpCustomers FROM Customers
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');
SELECT * INTO #tmpOrders FROM Orders
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');
--JOIN 2 temporary tables
select c.CompanyName,Count(*) TotalOrders
FROM #tmpCustomers c
INNER JOIN #tmpOrders o ON o.CustomerID=c.CustomerID
WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59') --date format must be yyyy-MM-dd or yyyy-MM-dd HH:mm:ss[.fff]
GROUP BY c.CompanyName;
UNION ALL / UNION Statement
This example shows how to write UNION or UNION ALL statement to combine results from multiple SQL queries. To remove duplicate rows from result use UNION rather than UNION ALL.SELECT * into #tmpCustomersUK FROM Customers Where Country='UK'
--WITH (MyParam1='something1', MyParam2='something2');
SELECT * into #tmpCustomersUSA FROM Customers WHERE Country='USA'
--WITH (MyParam1='something1', MyParam2='something2');
select * from #tmpCustomersUK
UNION ALL
select * from #tmpCustomersUSA;
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.
--For comment use line comment with two dash (e.g. --my comment) or use block comment for multi line comment (e.g. /*my comment*/ )
--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
--Use quoted identifiers when you have space or other character in names (e.g. Column or Table name contains space)
SELECT ID, "Account Name" FROM "Closed Accounts"
SELECT ID, [Account Name] 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.