SQL Tutorial 1

** SQL is very important for SCADA Report generation**

SQL is a standard query language for accessing databases. It can be used in vbscripts while accessing databases.

For Example:

strfile = "D:\Database.mdb"

Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

Set rs = New ADODB.Recordset

cn.Open Strcon

sqlstr = "SELECT * from Datalog_Table"

rs.Open sqlstr, cn, adOpenDynamic, adLockOptimistic

Text1.Text = rs!DateTime_Column

Text2.Text = rs!Data1_Column

rs.Close

cn.Close

from the above vbscript the green highlighted line is the SQL Query. There are different types of SQL queries for different types of database access. They are:

SELECT

Syntax:

SELECT column1,column2 FROM table1;

or

SELECT * FROM table1;

Example:

SELECT SupplierName,Country FROM Suppliers;

SELECT DISTINCT

Syntax:

SELECT DISTINCT column1,column2 FROM table1;

Example:

SELECT DISTINCT City FROM Suppliers;

WHERE Clause

Syntax:

SELECT column1,column2 FROM table1 WHERE column3 operator value;

Example:

SELECT * FROM Suppliers WHERE SupplierID=5;

AND & OR Operators

These operators are used in where clause.

The AND operator is used to display a record if both the condition1 AND the condition2 are true.

The OR operator is used to displays a record if either the condition1 OR condition2 is true.

Example:

SELECT * FROM Suppliers WHERE Country='India' AND City='Japan';

SELECT * FROM Suppliers WHERE Country='India' OR City='Japan';

ORDER BY

Syntax:

SELECT column1,column2 FROM table1 ORDER BY column3 ASC/DESC,column4 ASC/DESC;

ASC - Order(sort) by Ascending order

DESC - Order(sort) by Descending order

Example:

1. SELECT * FROM Suppliers ORDER BY City;

2. SELECT * FROM Suppliers ORDER BY City DESC;
3. SELECT * FROM Suppliers ORDER BY City ASC, Suppliername DESC;