• Designed for data manipulation in a relational database
• Nonprocedural language--specify what, not how
• ANSI standard does exist, but most DBMS packages provide a superset of SQL commands (ISO/IEC 9579:1999)

CHECKING TABLE CONTENTS (119)

The SELECT command lists the table contents. SELECT can be followed by a set of all attributes to be listed or by the wildcard symbol * if all attributes are to be listed.

SELECT * FROM PRODUCT;

or

SELECT P_DESCRIPT, P_INDATE, P_PRICE, ..., V_CODE
     FROM PRODUCT;

Notes:

• Although SQL commands may be issued on a single line, command sequences are best structured when the SQL command's components are shown on separate and indented lines.

The SELECT command can be used to limit the table contents included in the output:

SELECT <column(s)>
     FROM <table name>
          WHERE <conditions>;

example:
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
     FROM PRODUCT
          WHERE V_CODE = 21344;

examples:
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
     FROM PRODUCT
          WHERE V_CODE <> 21344;

 

SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
     FROM PRODUCT
          WHERE P_PRICE <= 10;

SELECT P_CODE, P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
     FROM PRODUCT
          WHERE P_CODE < '1558-QW1';

Notes:

• Character attributes are compared on the basis of their ASCII codes. For example, the character 'A' has a code 65, while an 'a' has code 97. A '1' has code 49. All other character symbols have similar codes, and character strings are compared from left to right, character by character. Therefore 'zebra' is greater than 'giraffe' but less than 'zeus'. Such comparisons can be used to generate sorted lists.
• While '5' is greater than '4', it is also greater than '44'.

=====================

SELECT *
     FROM PRODUCT
          WHERE STOCK_DATE >= #07/01/1999#;

SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE
     FROM PRODUCT
          WHERE P_INDATE >= #08/15/1999#;

Notes:

• Lists all records in which the stock dates occur on or after August 15, 1999.
• Oracle requires the apostrophes around the date.
• Access requires pound signs (#) around the date.

SQL allows the use of the logical operators NOT, AND, and OR.
NOT -- negates the condition.
AND -- requires that both parts of the condition be true.
OR -- requires that either part of the condition be true.

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
     FROM PRODUCT
          WHERE V_CODE = 21344
               OR V_CODE = 24288;

Notes:

• Lists records with a V_CODE field of either 21344 or 24288.

---

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
     FROM PRODUCT
          WHERE P_PRICE < 50
               AND P_INDATE > #07/15/1999#;

Notes:

• Lists all rows for which the PRICE is less than $50.00 and for which P_INDATE occurs after July 15, 1999.

SELECT *
     FROM PRODUCT
          WHERE V_CODE NOT 21344;

Notes:

• Lists all rows for which the vendor code is not 21344.

Logical operators can be combined to place complex restrictions on the output.

example:

1. P_INDATE after July 15, 1999 and P_PRICE less than $50.00.
2. Or, a V_CODE of 24288.

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
     FROM PRODUCT
          WHERE (P_PRICE < 50 AND P_INDATE > #07/15/1999#)
               OR V_CODE = 24288;

• BETWEEN -- used to define range limits (inclusive) (126)

   

  • BETWEEN checks to see if a value is between value1 and value2 inclusive.

    SELECT *
         FROM PRODUCT
              WHERE P_PRICE BETWEEN 50.00 and 100.00;

  • Note: Access is inclusive at beginning but not end.
    List the product description all products whose descriptions fall between 'B' and 'C', inclusive.
    SELECT P_DESCRIPT
        FROM PRODUCT 
            WHERE P_DESCRIPT Between "B*" AND "D*"; (Access is inclusive at beginning but not end)

     

  ---

• IS NULL -- used to check for null attribute values (126-127)

SELECT P_CODE, P_DESCRIPT
     FROM PRODUCT
          WHERE P_MIN IS NULL;

• LIKE -- used to check for similar character strings (127-128)

• Conditions involving characters allow the use of wildcards: % means any and all following characters match
• _ matches any one character
• Access uses * and ? instead of % and _.
• Character comparisons are case sensitive. SMITH <> Smith
• Oracle provides the UPPER function to convert both table and query character entries to uppercase. 'SMITH' = UPPER('Smith')
• Access is not case sensitive.

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
     FROM VENDOR
          WHERE V_CONTACT  LIKE 'Smith%';

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
     FROM VENDOR
          WHERE V_CONTACT NOT LIKE 'Smith%';

 

SELECT *
     FROM VENDOR
          WHERE V_CONTACT LIKE 'Johns_n';

• IN -- used to check whether or not an attribute value matches any of the values contained within a set of listed values. (128-129)

SELECT *
     FROM PRODUCT
          WHERE V_CODE IN (21344, 24288);

• NOT IN is also valid.

• EXISTS -- used to check whether an attribute has a value (129)

• NOT can be used in combination with many special operators.  IS NOT NULL is the same as EXISTS.  

DELETE FROM PRODUCT
     WHERE P_CODE EXISTS;

• EXISTS does not work in Access.