SQL: Structured Query Language - The Basics for Use with Delphi

SQL: Rationale

• ANSI-Standard Structured Query Language
• Widely used by a range of database servers
• Transferable Knowledge

SQL: Basics I

• SQL is used in data based environments mainly to:
  • select and view data
  • join tables
  • update, insert and delete data
• SQL can be used to derive data
• SQL is very large

SQL in Delphi I

• Delphi's SQL Engine uses a subset of ANSI SQL 92
• It is referred to as the Data Manipulation Language (DML)
• SQL is processed by:
  • Delphi SQL Compiler
  • The server (gets around some limitations of DML)

SQL in Delphi

• To place a query on a Delphi form use a TQuery Object.
• Delphi's Object Inspector can then be used to change the parameters of the TQuery object.
• The TQuery's SQL Property is used to access the SQL command.

DML Commands

• SELECT, UPDATE, INSERT, DELETE
• SUM, AVG, MIN, MAX, COUNT
• +,-,*, /,=, <,<=,>,>=,<>, IS NULL, AND, OR, NOT, || (string concatenation), ANY, ALL, IN

Using SQL/DML I

• SELECT Operation

SELECT * FROM <TableName>

SELECT <FieldName> FROM <TableName>

SELECT <FieldName> FROM <TableName> WHERE <FieldName> = <FieldValue OR Parameter>

For example:

SELECT OrderNo

FROM Order

WHERE OrderNo = :OrderNo

Using SQL/DML II

• Inserting and Deleting Data

INSERT INTO <TableName> (<FieldName/s>)

VALUES (<FieldValue/s OR Parameters>)

DELETE FROM <TableName>

WHERE <FieldName> = (<FieldValue OR Parameter>)

For example:

INSERT INTO Order (OrderNo, OrderName)

VALUES (1, "Another Big Job")

Using SQL/DML III

• Deriving Data

SELECT<FieldName>, aggregator(<FieldName>)

AS <ResultFieldName>

FROM<TableName>

GROUP BY <FieldName>

For example:

SELECT CustNo, COUNT(OrderNo)

AS TotalOrders

FROM Order

GROUP BY CustNo

Using SQL/DML IV

• Operating on more than one table

SELECT <TableName1> . <FieldName1>, <TableName2> . <FieldName2>

FROM <TableName1> , <TableName2>

WHERE <TableName1> . <FieldName2> = <TableName2> . <FieldName2>

For example:

SELECT Order.OrderName, Customer.CustName

FROM Order, Customer

WHERE Order.OrderNo = Customer.OrderNo