Open topic with navigation



In SQL language the only way to add data to a table is using the INSERT statement like the following:

INSERT INTO adm_Category (CategoryID,NameCategory,Photo) VALUES (9,'CAR','carros.gif')



It updates a given line.

UPDATE adm_category SET NameCategory 'rent car' WHERE CategoryId = 9


Exclude a given table line.

DELETE FROM adm_category WHERE categoryId = 9

/*it eliminates in the table adm_category the line with category code equal 9 *

Commands INSERT, UPDATE E DELETE in the ScriptCase.

To execute these commands the ScriptCase use the macro sc_exec_sql.

sc_exec_sql('DELETE FROM adm_category WHERE categoryId =9');



Use the Query statement to search a given DB table. A Query has at least two components:

SELECT : It enumerates the columns name that contains the desired data.

FROM : It specifies the tables where the columns are located.

Ex.: SELECT categoryId,NameCategory,Photo FROM adm_category;


It returns only the values from the lines that satisfies the conditions specified in the WHERE clause.

SELECT NameCustomers FROM adm_Customers WHERE Sex = 'M'






Not equal








Not Shorter


Not Higher


Higher or equal


Shorter or equal

BETWEEN - AND : It specifies data range.

SELECT CustomersId,NameCustomers FROM adm_Customers WHERE BETWEEN '1997-01-01' AND '1997-04-31'

SELECT SupplierId,Name_supplier FROM adm_supplier WHERE SupplierId BETWEEN 1 AND 9

IS NUL : Select lines where the content of the field is null (EMPTY).

SELECT CustomersId FROM adm_order WHERE DateOrder IS NULL

LIKE : Use masks in conditions to compare text values:

SELECT NameCustomers FROM adm_Customers WHERE NameCustomers LIKE 'RIC%';

SELECT NameCustomers FROM adm_Customers WHERE NameCustomersLIKE 'J_A_';

IN : Selects data from a given set or lists.

SELECT CustomersId,NameCustomers FROM adm_Customers WHERE IN ('ABCDE','ZXED','WW3CD')

Operators AND and OR

Allows to use more than one condition in one WHERE clause.

SELECT P.ValueOrderFROM adm_Customers C, adm_order P WHERE

(CustomersId ='CustomersId') AND

(P.dateOrder IS NULL AND C.CustomersId = 'ABCDE')

Arithmetical operators









In the example to follow, we add 0,05 to Customer “ABCDE” order value.

SELECT DISTINCT C.CustomersId, C. NameCustomers,P.ValueOrder + 0,05 AS DISCOUNTING FROM adm_Customers C, adm_order P WHERE C.CustomersId = P.CustomersId AND CustomersId= 'ABCDE'

Concatenation Operator

In the MySQL the function concat implements the concatenation functionality:

SELECT concat(Name Customers " Happy Birthday”)from admCustomers where month (DateBirth)= month(now()) AND day(DataBirth) = day(now()); ;

Note the use of the functions day, month e now, that manipulate date type fields.


Use functions to perform the operations on query results.

Aggregate functions

They produce one value from an entire data column. They are also called column functions.

AVG: Calculates the query selected items average.

SELECT AVG(ValueOrder) FROM adm_orderP CLASS="western" STYLE="margin-bottom: 0cm">

MAX : Returns the max value from the query selected items.

SELECT MAX(ValueOrder) FROM adm_order

MIN: min value from the query selected items.

SELECT MIN(ValueOrder) FROM adm_order;

SUM : Returns the sum of all items for the selected column.

SELECT SUM(ValueOrder) FROM adm_orders

COUNT : Returns the line count (number of records) of a query result.

SELECT COUNT(*)adm_Customers;

Not Aggregate functions

Return a value for each line.

MID : Display a part of a text. For example, it lists all the customers with only the 5 first characters of its name.

SELECT MID(NameCustomers,1,5) FROM adm_Customers;

UCASE : Returns a text in capital letters.

SELECT UCASE(NamCustomers FROM adm_Customers

LCASE : Returns a text in lower case.

SELECT LCASE(NameCustomers) FROM adm_Customers

LENGTH : Returns the amount of characters from a field/text/P>

SELECT LENGTH(Namesupplie) FROM adm_supplies

Breaks and Organization

Clauses that can be added to a query to organize the results.


Used to group lines in the result.

SELECT F.Namesupplier,P.NameProduct FROM adm_supplier F, adm_product P WHERE P.SupplierId= F. SupplierId GROUP BY F. SupplierId

Aggregate Functions cannot be used in GROUP BY clause.


Apply search conditions to the lines in GROUP BY clause.

For example :

SELECT CustomersId MAX(ValueOrder),MIN(ValueOrder),MAX(ValueOrder) – MIN(ValueOrder) as Differences FROM adm_pedidos GROUP BY CustomersId HAVING MAX( ValueOrder)- MIN(ValueOrder) > 12700;

List all the customers who had made more than 20.

SELECT CustomersId COUNT(CustomersId) FROM adm_order GROUP BY CustomersId HAVING COUNT(CustomersId) > 20;;


Order (sort) the lines (records).