Simple Select Statements
SELECT * FROM CUSTOMERS
SELECT COMPANY, CONTACT FROM CUSTOMERS
Where Clause / Filter Criteria
SELECT COMPANY FROM CUSTOMERS WHERE COMPANY = 'SYSTEMS'
SELECT COMPANY FROM CUSTOMERS WHERE COMPANY = 'SYSTEMS OR
SOFTWARE'
SELECT COMPANY FROM CUSTOMERS WHERE COMPANY = 'SYSTEMS' OR
COMPANY = 'SOFTWARE'
SELECT COMPANY FROM CUSTOMERS WHERE COMPANY <> 'SYSTEMS'
SELECT COMPANY FROM CUSTOMERS WHERE ACCT > 100
SELECT COMPANY, CONTACT, STATE FROM CUSTOMERS WHERE STATE
IN ('CA','TX','FL')
SELECT COMPANY, CONTACT, STATE FROM CUSTOMERS WHERE STATE
NOT IN ('WY','UT')
SELECT COMPANY FROM CUSTOMERS WHERE COMPANY LIKE 'DYN%'
SELECT PRICE FROM PRODUCTS WHERE PRICE BETWEEN 10 AND 20
Order By Clause / Sorting
SELECT COMPANY, CONTACT FROM CUSTOMERS ORDER BY COMPANY
SELECT COMPANY, CONTACT FROM CUSTOMERS ORDER BY 1
SELECT COMPANY FROM CUSTOMERS ORDER BY COMPANY DESC
SELECT COMPANY, STATE FROM CUSTOMERS ORDER BY 2 ASC
Order By columns must be in the SELECT list.
Group By Clause / Subtotals
SELECT SUM(AMOUNT) FROM ORDERS GROUP BY ORDER_ID
SELECT SUM(AMOUNT) FROM ORDERS GROUP BY ACCT, PERIOD
SELECT CUSTOMER_NO, SUM(QUANTITY) FROM ORDERS GROUP BY CUSTOMER_NO
Having Clause / Selection on Grouped Records
SELECT SUM(AMOUNT) FROM ORDERS GROUP BY ORDER_ID HAVING SUM(AMOUNT)
> 100
Select Into
SELECT * INTO MYCONTACTS FROM CONTACTS
SELECT * INTO $TEMPD.MYCONTACTS
SELECT * INTO $TEMPD.MYCONTACTS PHYSICAL "C:\MY_CONTACTS"
Use the DROP TABLE command to drop tables created with a
Select Into command.
System Values
SELECT $ROWID, COMPANY FROM CUSTOMERS
Column and Table Alias
SELECT COMPANY Organization, CONTACT Representative FROM
CUSTOMERS
SELECT COMPANY AS Organization, CONTACT AS Representative
FROM CUSTOMERS
SELECT A.COMPANY, A.CONTACT, B.STATUS FROM CUSTOMERS A, ORDERS
B WHERE A.CUSTOMER_NO=B.CUSTOMER_NO
SELECT A.COMPANY FROM CUSTOMERS AS A
SELECT CUSTOMERS.COMPANY, ORDERS.ORDER_NO FROM CUSTOMERS,
ORDERS WHERE CUSTOMERS.CUSTOMER_NO=ORDERS.CUSTOMER_NO AND
ORDERS.STATUS = 'CNCL'
Distinct / All
SELECT DISTINCT COMPANY, CONTACT FROM CUSTOMERS
SELECT ALL COMPANY, CONTACT FROM CUSTOMERS WHERE COMPANY
= 'SYSTEMS'
SELECT DISTINCT(COMPANY) FROM CUSTOMERS WHERE COMPANY = 'SYSTEMS'
SELECT COUNT(DISTINCT COMPANY) FROM CUSTOMERS
Top / Random
SELECT TOP 10 PRODUCT_NO, SUM(AMOUNT) FROM ORDERS WHERE STATUS
= 'SHIP' GROUP BY PRODUCT_NO
SELECT TOP 10% PRODUCT_NO, SUM(AMOUNT) FROM ORDERS WHERE
STATUS = 'SHIP' GROUP BY PRODUCT_NO
SELECT RANDOM 10 COMPANY FROM CUSTOMERS
SELECT RANDOM 10% COMPANY FROM CUSTOMERS
Quoted Columns and Tables for Reserved
Words and Literals
SELECT CONTACT, ' WORKS FOR: ', COMPANY FROM CUSTOMERS
SELECT COMPANY, "MONTH" FROM CUSTOMERS
SELECT COMPANY, CONTACT FROM "ORDER"
SQL reserved words should be quoted in double quotes.
Aggregations
SELECT SUM(QUANTITY) FROM ORDERS
SELECT COUNT(*) FROM CUSTOMERS WHERE COMPANY = 'SYSTEMS OR
SOFTWARE'
SELECT COUNT(COMPANY) FROM CUSTOMERS
SELECT MIN(QUANTITY), MAX(QUANTITY), AVG(QUANTITY) FROM ORDERS
SELECT SUM(PRICE * QUANTITY) FROM CUSTOMERS
SELECT SUM(AMOUNT) * SUM(QUANTITY) FROM ORDERS
Mathematical Expressions
SELECT COMPANY, PRICE * QUANTITY FROM ORDERS
SELECT COMPANY, PRICE - COST FROM ORDERS
SELECT COMPANY, TOTAL_COST / UNITS_SOLD FROM ORDERS
SELECT COMPANY, (FEB_SALES + JAN_SALES) + MAR_SALES AS QTR1
FROM ORDERS
SELECT COMPANY, PRICE * 0.80 FROM ORDERS
SELECT COMPANY, (PRICE * QUANTITY) - INFLATION FROM ORDERS
top
|
