Database Tour Documentation Contents Index

Query Examples

Top Previous Next

1. Select those 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. Create in the current directory the table "books.dbf" with a char field "title" of length 20 and numeric field "cost" of size 7 and 2 digits after decimal point (this example works for BDE connections only):

CREATE TABLE "books.dbf"

(

title CHAR (20),

cost DECIMAL (7, 2)

)

3. Delete table "regions":

DROP TABLE regions

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

DELETE FROM books

WHERE cost > 150

5. Select those 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

6. Create an index "bydate" by the field "pay_date" in the table "payments":

CREATE INDEX bydate

ON payments (pay_date)

7. Select all data from all fields of the table "banks" from the database "payments" (if you are in the local database, opened via BDE, then you can also refer to other aliases):

SELECT *

FROM ":payments:banks"

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

SELECT *

INTO [dbcopy]

IN "c:\"[dBase IV;]

FROM banks

9. Using dates when writing SQL for Access database (source database must be connected by ADO using Jet):

SELECT *

FROM documents

WHERE DocDate = #2011-11-24#

SELECT *

FROM documents

WHERE DocDate = Format('11/24/2011', 'mm/dd/yyyy')

10. Using dates when writing SQL for Oracle database:

SELECT *

FROM documents

WHERE doc_date = date '2011-11-24'

SELECT *

FROM documents

WHERE doc_date = to_date('11/24/2011', 'MM/DD/RRRR')