Queries

1
Queries

• Creating partial listings of table contents

SELECT ltcolumn(s)gtFROM lttable namegtWHERE
ltconditionsgt
Arithmetic Operators Operator Description
Add - Subtract Multiply / Divide Raise
to the power of
Mathematical Operators Symbol Meaning
Equal to lt Less than lt Less than or
equal to gt Greater than gt Greater than
or equal to ltgt Not equal to
From left to right The string Ardmore is greater
than the string Aareson, but less than the
string Brown
2
Examples

• Mathematical operators
• Mathematical operators on character attributes
• Mathematical operators on dates

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE ltgt 21344
SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFR
OM PRODUCTWHERE P_CODE lt 1558-QWI
SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEF
ROM PRODUCTWHERE P_INDATE gt 01/20/2002
3
Computed Columns

• New columns can be created through valid
  computations or formulas
• Formulas may contain mathematical operators
• May contain attributes of any tables specified in
  FROM clause
• Alias is alternate name given to table or column
  in SQL statement

SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHANDP_PRIC
E AS TOTVALUE FROM PRODUCT
4
Logical Operators

• Logical AND, OR

SELECT FROM PRODUCTWHERE V_CODE 21344 OR
V_CODE 24288
SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE FROM
PRODUCTWHERE P_PRICElt50 AND P_INDATEgt01/05/2002

SELECT P_DESCRIPT,P_INDATE,P_PRICE,V_CODE FROM
PRODUCTWHERE (P_PRICElt50 AND P_INDATEgt01/05/2002
) OR V_CODE24288
5
Logical Operators

• Rules of precedence
• Conditions within parenthesis executed first
  (Where you place the parenthesis depends on how
  you want the logical
• restrictions to be executed)
• Parenthesis, Power, Multiplication and Division,
  Addition and Subtraction
• Logical NOT

82518 but (82)550 452379 but
(45)23243
SELECT FROM PRODUCTWHERE V_CODE NOT 21344
SELECT FROM PRODUCTWHERE V_CODEltgt21344
or
6
Special Operators

• Special Operators
• BETWEEN - defines limits
• IS NULL - checks for nulls
• LIKE - checks for similar strings (when the
  entire string is
• not known). LIKE must be used with wildcard
  characters!
• means any and all following characters are
  eligible
• ? means any one character may be substituted for
• the question mark
• IN - checks for value in a set of listed values

J yields Johnson, Jones, Jernigan, July,
J-231Q Jo yields Johnson and Jones ?23-456
yields 123-456, 223-456, 323-456 ?23-?56-678?
yields 123-156-6781, 123-256-6782,
823-956-6788 ?l yields Al,Alton,Elgin,Blakeston
,blank,bloated,eligible
7
Special Operators (Cont.)
SELECT FROM PRODUCTWHERE P_PRICE BETWEEN 30
AND 100
SELECT P_CODE, P_DESCRIPT FROM PRODUCT WHERE
V_CODE IS NULL
SELECT V_CODE,V_CONTACT,V_AREACODE,V_PHONE FROM
VENDOR WHERE V_CONTACT LIKE Smith
8
Special Operators (Cont.)

• Logical Operators may be used with the wildcard
  characters

SELECT V_NAME,V_CONTACT,V_AREACODE,V_PHONE FROM
VENDOR WHERE V_CONTACT NOT LIKE 'Smith'
SELECT FROM VENDOR WHERE V_CONTACT LIKE
'Ort??'

• Many queries that would require the use of the
  logical
• OR can be more easily handled with the help of
  IN

SELECT FROM PRODUCT WHERE V_CODE IN
(21344,24288)
9
Ordering A Listing

• Results ascending by default
• Descending order uses DESC
• Cascading (kademeli) order sequence

SELECT FROM PRODUCT ORDER BY P_PRICE
ORDER BY ltattributesgt
SELECT FROM PRODUCT ORDER BY P_PRICE DESC
ORDER BY ltattributesgt DESC
ORDER BY ltattribute 1, attribute 2,..gt
SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACOD
E,EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME,
EMP_FNAME, EMP_INITIAL
10
Ordering A Listing

• You can use the ORDER BY clause with other SQL
  commands

SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE FROM
PRODUCT WHERE P_INDATElt01/21/2002 AND P_PRICE
lt50.00 ORDER BY V_CODE DESC, P_PRICE DESC
11
Listing Unique Values

• DISTINCT clause produces list of different
  values

How many different vendors are currently
presented in the PRODUCT table?
SELECT DISTINCT V_CODE FROM PRODUCT
How many different employee managers are
currently presented in the EMP table?
SELECT DISTINCT EMP_MGR FROM EMP