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;
SYMBOL |
MEANING |
= |
Equal |
< |
Less than |
<= |
Less than or equal to |
> |
Greater than |
>= |
Greater than or equal to |
<> |
Not equal to |
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:
=====================
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:
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:
- P_INDATE after July 15, 1999 and P_PRICE less than $50.00.
- 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;
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)
SELECT P_CODE, P_DESCRIPT
FROM PRODUCT
WHERE P_MIN IS NULL;
- 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';
SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288);
- NOT IN is also valid.
- 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.