Here only the basic examples of database queries are shown. If you need more complex queries, please refer to SQL tutorials and your database documentation.

DML Examples (Data Manipulation)

1. Select records from the table payments where value of the field paysum is greater than 400 and less than 2000:

SELECT *
FROM payments
WHERE paysum > 400 AND paysum < 2000

2. Select records from the fields number and sum of the table payments where values of the field paysum are greater than 400 and less than 2000 and sort this data by the field paysum (in descending order):

SELECT payments.number, payments.paysum
FROM payments
WHERE paysum > 400 AND paysum < 2000
ORDER BY paysum DESC

3. Select information about number and amount of large orders (i.e. orders where amount is greater than 5000) from the table orders grouped by order_date column:

select o.order_date, count(o.order_id) as order_count, sum(o.order_amount) as total_order_amount
from orders o
where order_amount > 5000
group by o.order_date
order by o.order_date

4. Select data from all fields of the table PAYMENT, and the payment date and payment sum ranges must be specified as parameters to let the user ability to input them before the query execution:

SELECT *
FROM PAYMENT
WHERE PAYMENTDATE > :Param1 /*ParamType=Date*/ AND PAYMENTSUM > :Param2 /*ParamType=Float*/

(such comments are added to let the application to automatically select parameter types, without needing to do this by the user).

5. Using dates when writing SQL for Access database (the database connected by ADO using Jet):

SELECT *
FROM documents
WHERE DocDate = #2020-11-24#

SELECT *
FROM documents
WHERE DocDate = DocDate = Format('11/24/2020', 'mm/dd/yyyy')

6. Using dates when writing SQL for Oracle or PostgreSQL database:

SELECT *
FROM documents
WHERE doc_date = date '2020-11-24'

SELECT *
FROM documents
WHERE doc_date = to_date('11/24/2020', 'MM/DD/RRRR')

7. Export data from table banks to file c:\MyData\dbcopy.dbf (using ISAM driver dBase IV) by ADO SQL (source database must be connected by ADO):

SELECT *
INTO dbcopy
IN "c:\MyData\"[dBase IV;]
FROM banks

8. Delete from the table books those records where values in field cost are greater than 150:

DELETE
FROM books
WHERE cost > 150

DDL Examples (Data Definition)

1. In Access database, create a table employee with the following columns: EmployeeId, HireDate, FirstName, LastName, BirthDate, Title, Salary. EmployeeId must be of autoincrement type. The primary key must be built by EmployeeId column.

CREATE TABLE employee (
  ClientId COUNTER NOT NULL PRIMARY KEY,
  HireDate DATETIME,
  FirstName VARCHAR(40),
  LastName VARCHAR(40),
  BirthDate DATETIME,
  Title VARCHAR(40),
  Salary MONEY
)

2. In current local BDE database, create a table books with a text field title of length 50 and numeric field cost of size 7 and 2 digits after decimal point:

CREATE TABLE "books.dbf" (
  title CHAR(50),
  cost DECIMAL(7, 2)
)

3. Delete table regions:

DROP TABLE regions

4. Create an index bydate on the table payments by field pay_date:

CREATE INDEX bydate
ON payments (pay_date)