> DFC bjbj *0cc\ojgggggBBB$ #RBBBBBgg)BggBgpcv?0o#(## BBBBBBoBBBB#BBBBBBBBB
: Notes on SQL:
Lets say I have a table named inven that has the following layout:
Name of fieldTypeSizeitemnoshort text5itemnameshort text25deptshort text2pricenumberdoubleonhandnumberintegeronordernumberintegerreorderpointnumber integer
When I want to write SQL, I go into the SQL part of Access and I can write the SQL. Below are some examples.
If I want all items that are in dept TY and have a price > 25, I would write.
select itemno, itemname, dept, price
from inven
where dept = 'TY' and price > 25.
Notice that I included itemno and itemname to help me figure out which items meet the criteria. These two things both have to be true so they are in an AND relationship.
If I want all items that are in dept TY or dept SP, I would write.
select itemno, itemname, dept
from inven
where dept = 'TY' or dept = 'SP';
I could have added other fields to the select if I wanted more information about the items.
Now I want to write a query using SQL that will look for onhand > 50 or onorder > 100.
select itemno, itemname, onhand, onorder
from inven
where onhand > 50 or onorder > 100;
If I had wanted both onhand > 50 and onorder > 100 then I would write"
select itemno, itemname, onhand, onorder
from inven
where onhand > 50 and onorder > 100;
Now lets say we want all items in dept TY and they have either onhand > 50 or onorder > 100.
select itemno, itemname, dept, onhand, onorder
from inven
where dept = 'TY' and (onhand > 50 or onorder > 100);
Remember I wanted all the items I displayed to have a dept of TY and also to have either of two other conditions. So dept must be TY and either onhand must be greater than 50 or onorder greater than 100.
Notice the parenthesis. In the order of operation ANDs are resolved before ORs. So if I wrote this without the parenthesis I would get different results.
select itemno, itemname, dept, onhand, onorder
from inven
where dept = 'TY' and onhand > 50 or onorder > 100;
This means that dept = 'TY' and onhand > 50 both have to be true to meet the criteria
OR if they are not both true we can check for onorder > 100.
If onorder > 100 then we have met the criteria as well.
So the parenthesis changed things and we then said either of the things in the parenthesis have to be true AND also the dept absolutely has to be TY.
Now lets look at another example. We want the dept = 'TY' and price < 100 OR we want the dept = 'SP' and onhand < 50.
So since ANDs are resovled before ORs we combine the dept = 'TY" with the price < 100 and we combine the dept = 'SP' and onhand < 50. The OR tells us that either of these cominations has to be true to meet the criteria.
select itemno, itemname, dept, price, onhand
from inven
where dept = 'TY' and price < 100 or dept = 'SP' and onhand < 50;
Now lets look at this example
select itemno, itemname, dept, price, onhand, onorder
from inven
where dept = 'IT' and price > 400 or onhand > 40 or onorder > 50;
When we look at the AND and the OR we realize that dept = 'IT' and price > 400 are in an AND relationship so they are combined and the OR onhand > 40 stands alone or the onorder > 50 stands alone. SO we have
if dept = 'IT" and price > 400 the criteria is met
OR
if onhand > 40 the criteria is met.
OR
if onorder > 50 the criteria is met
Now what happens if we add parenthessis.
select itemno, itemname, dept, price, onhand, onorder
from inven
where dept = 'IT' and (price > 400 or onhand > 40 or onorder > 50);
This means that dept has to be IT and at least one of the criteria inside the parenthesis has to be true.
SO
if dept = 'IT' and price > 100 the criteria is met
OR
if dept = 'IT' and onhand > 40 the criteria is met
OR
if dept = 'IT" and onorder > 50 the criteria is met
ht3XhZV5\hZV RSafkls~gkd$$IfF&47a7p$If$Ifgkd$$IfF&47a7p$Ifgkd $$IfF&47a7p$Ifgkd$$IfF&47a7p$Ifgkd2$$IfF&47a7p$Ifgkd$$IfF&47a7p
$IfgkdD$$IfF&47a7p
} ~
.9[gkd$$IfF&47a7p[\:Eij
j
k
FGvIpq
AMb
CCO47jm(/ =!n"n#n$n%$$If!vh#v:V5////47a7p$$If!vh#v:V5///47a7p$$If!vh#v:V5///47a7p$$If!vh#v:V5///47a7p$$If!vh#v:V5///47a7p$$If!vh#v:V5///47a7p$$If!vh#v:V5///47a7p$$If!vh#v:V5///47a7pu666666666vvvvvvvvv66666686666666666666666666666666666666666666666666666666hH66666666666666666666666666666666666666666666666666666666666666666p62&6FVfv2(&6FVfv&6FVfv&6FVfv&6FVfv&6FVfv&6FVfv8XV~ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@66_HmH nH sH tH R`RNormal*$1$$CJKHPJ^J_H9aJmH sH tH9DA DDefault Paragraph FontVi@V
0Table Normal :V44
la(k (
0No ListNNHeading
x$OJQJCJPJ^JaJ6B6 Body Text
x(/(List^JH""HCaption
xx$CJ6^JaJ].2.Index$^J<OB<Table Contents$PK![Content_Types].xmlN0EH-J@%ǎǢ|ș$زULTB l,3;rØJB+$G]7O٭VGRU1a$N% ʣꂣKЛjVkUDRKQj/dR*SxMPsʧJ5$4vq^WCʽD{>̳`3REB=꽻UtQy@\.X7<:+&
0h@>nƭBVq1u`X=:tڣf{Xx
{dV=|kPo۵(h[x
)IеnW[@;0PDbRy( "IROnVxCD' bJZ65I[adH+^D
)>s=h
۟~7zWo^=[Qr||[?|̦o{O=4?}}L$Xx%@|Ư'11%i$P,c[GD7sH5.4kIƉZKTPBM HN7|nB#L7^`!\29pCH3nMb#$.'19b%+vJ~.!DI;DPz l2I͚;|y3+~Dؕjw0e1vdǪzO^I57y+d8bnv 4?6CK\M#= ċ(0'1
zxpWc/_X4/A_[)^&(fp[_NiK7@wd5= I?>_q샏[ꚝΡdrv5CojFh>PG3mOs=͡|7n;:N&?\8Lټ@_<}9H}]RuVx~p{3AsP~\v!cZ
6JOw׃SB
HfЖ
gpj*4nR2BM}
rfw[BfV= vͅh7/sɳ
̲B3뤓eH=J_:hn$O\ʧFn;/x.M#+d,W87+IwOťwF)uepwPϬ pV$2v
Sv(C8L\~+l`k&Y
iTU#p~XHYHeʹ,fͰ-;Y7XmM9ͬYMm*/稺W(r2bUGڱ]]HYUcF8U~ۍZZnJmi}kn^l
0
[C
8@0(
B
S ?-2lr7=BJ')-38?CNR (*029?DKQZa/3LR[brxzRVmsNTV^`dflnu{:ALS ! H L s w
=
H
x
~
"$(179@FKSWrx!;Bei
$
&
*
3
9
;
B
H
M
U
Y
u
{
:>JPpt !
ce333333333ZVt3X@@UnknownG.[x Times New Roman5Symbol3..Cx Arial;(SimSun[SO5 MangalACambria Math"A hBG
J/s/s! 20K P t3X!xx$Priscilla GrocerPris
Oh+'0x
(4@
LX`hpPriscilla GrocerNormal.dotmPris2Microsoft Office Word@F#@@ih@o/s
՜.+,0hp|
Title
"#$%&'()*+,-./012456789:<=>?@ABERoot Entry F iGData
1Table!#WordDocument*0SummaryInformation(3DocumentSummaryInformation8;CompObjr
F Microsoft Word 97-2003 Document
MSWordDocWord.Document.89q