SQL Tutorial 2

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

INSERT INTO

The INSERT INTO statement will create new row and insert the value into the table.

Syntax:

INSERT INTO table1 (column1,column2,column3) VALUES (value1,value2,value3);

Example:

INSERT INTO Temperature_Log (Set_point1,Set_Point2,Set_Point3)
VALUES ('35.7', '37.9', '44.1');

UPDATE Statement

The INSERT INTO statement will replace the existing value with new value.

Syntax:

UPDATE table1 SET column1=value1,column2=value2 WHERE column3=value1;

Example:

UPDATE Temperature_Log SET Set_point1='35.7', Set_point2 ='37.9' WHERE Present_Value ='20';

SQL DELETE Statement
The DELETE statement is used to delete single row or multiple rows in a table. 

Syntax:
DELETE FROM table1 WHERE column1=value1;

Example:
To delete all rows in a table
  DELETE FROM table1;
To delete all rows in a table
  DELETE FROM Temperature_Log WHERE Channel_Name='Chamber_PID' AND Furnace_name='Hardening';

SQL LIKE Operator

The LIKE operator is used to fetch for a specified text in a column.

Syntax:

SELECT column1 FROM table1 WHERE column1 LIKE specific_text;

Example:
SELECT * FROM Temperature_Log WHERE Channel_Name LIKE 'PID%';

BETWEEN Operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or date. This is very important in SCADA projects because mostly we used to generate reports between two date and time.
Syntax:
SELECT column1 FROM table1 WHERE column1 BETWEEN value1 AND value2;

Example:
Using Number:
SELECT * FROM Temperature_Log WHERE Temperature BETWEEN 35 AND 66;
Using Date:
Select * FROM Temp_Log  where DATE_TIME >= CDATE('" & Start_Date1 & "')and DATE_TIME <=CDATE('" & End_Date1 & "') order by DATE_TIME;

INSERT INTO SELECT Statement
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. This will not delete any data in existing table, it will just append the data.
Syntax:
INSERT INTO table2 SELECT * FROM table1;

Example:
"INSERT INTO Temp_Report_Date_Trend (DATE_TIME ,HARD_TEMP1_SP) SELECT DATE_TIME ,HARD_TEMP1_SP from Temp_Log where Date_Time >= CDATE('" & Start_dates & "')and Date_Time <=CDATE('" & End_dates & "') order by Date_Time"