SQL Basics
 

 
VOUCHERSBK VOUCHERSBKP (CONTINUED)
IDEN CHAR(10) NOT NULL CREDIT1 NUMERIC(9, 2)
FARM CHAR(6) CREDIT2 NUMERIC(9, 2)
INVOICE_DATE DATE CREDIT3 NUMERIC(9, 2)
WHOLE SMALLINT TOTAL NUMERIC(9, 2)
PIECES SMALLINT NOTES CHAR(50)
UNITS SMALLINT PO_NUMBER CHAR(30)
GROSSWEIGHT NUMERIC(9, 2) FOB CHAR(30)
NETWEIGHT NUMERIC(9, 2) SALETERMS CHAR(30)
COMMISSION NUMERIC(9, 2) TOADDRESS CHAR(200)
PROCESSING NUMERIC(9, 2) STATUS SMALLINT
BOOM_TRUCK NUMERIC(9, 2) TOT_CASES SMALLINT
TAGS NUMERIC(9, 2) AMOUNT NUMERIC(9, 2)
TRAYS NUMERIC(9, 2) FROM_DAY DATE
FREIGHT NUMERIC(9, 2) TO_DAY DATE
SUBTOTAL NUMERIC(9, 2) CURRENCY SMALLINT
CHARGE1 NUMERIC(9, 2) USEXCHANGE NUMERIC(9, 4)
CHARGE2 NUMERIC(9, 2) CDNEXCHANGE NUMERIC(9, 4)
CHARGE3 NUMERIC(9, 2) DATESTR CHAR(11)

A SQL command that requests data has three essential parts:

  1. SELECT-this specifies which fields to include...a * is a shorthand way of saying all fields.
  2. FROM-specifies the table to include.
  3. WHERE-optionally specifies a condition the data must conform to to be included.

An example is a query that requests all boxes from production with BOXNO equal to "1234". The SQL command would look like this:

SELECT *
FROM production
WHERE boxno="1234"

(*Note that SQL commands are not case sensitive.) Fields that are defined as type CHAR or DATE require opening and closing quotes around them.

The WHERE clause can include multiple fields joined by the logical operators and/or/not and can use the /= operators as in the following example:

SELECT *
FROM production
WHERE datecode > "01/01/2001" and datecode < "01/31/2001"

                                                                 

                                                      Phone: (506) 857-0909
                                                        Fax: (506) 858-9271
                                                  Email: advatek@nbnet.nb.ca