3/4/11%1%SQL%CISC437/637,%Lecture%#7%Ben%Cartere7e%1%Copyright%©%Ben%Cartere7e%SQL%Queries%• Basic%form%of%a%SQL%query:% SELECT [DISTINCT] target FROM relations WHERE qualification • target%is%a%list%of%a7ributes/fields%• rela'ons+is%a%list%of%relaHons%to%get%data%from%• qualifica'on%is%a%a%set%of%+comparisons%joined%with%AND/OR/NOT%• DISTINCT%is%an%opHonal%keyword%for%dropping%duplicates+2%Copyright%©%Ben%Cartere7e%3/4/11%2%Conceptual%EvaluaHon%• A%simple%evaluaHon%strategy%for%SQL%queries:%– Compute%cross%product%of%rela'ons%– Discard%records%that%do%not%match%qualifica'on%– Drop%a7ributes%not%in%the%target+– If%DISTINCT%is%specified,%drop%duplicates%• This%works%for%any%basic%query%– But%it%is%usually%the%least%efficient%way%to%evaluate%a%query%– Query%opHmizaHon%finds%be7er%ways%to%compute%the%same%answer%Copyright%©%Ben%Cartere7e% 3%ProjecHon%in%SQL:%%SELECT%SELECT%A1,%A2,%…%FROM%RelaHon%• Use%SELECT%*%to%skip%projecHon%(get%all%fields)%• Perform%generalized%projecHon%on%numeric%fields%by%including%arithmeHc%expressions%or%funcHons%– SELECT%c+A1%from%RelaHon%– SELECT%A1*A2%from%RelaHon%• String%funcHons%can%be%applied%to%character%fields%– SELECT%upper(A1)%from%RelaHon%– SELECT%substring(A1,%a,%b)%from%RelaHon%Copyright%©%Ben%Cartere7e% 4%3/4/11%3%SelecHon%in%SQL:%%WHERE%SELECT%A1,%A2,%…%FROM%RelaHon%WHERE%P%• P%is%a%sentence%of%comparison%operators%on%fields%A1,%A2,%…%and%constants,%linked%with%AND,%OR,%NOT%• Other%useful%keywords:%– A1%BETWEEN%c%AND%d%is%equivalent%to%A1%>=%c%AND%A1%<=%d%– A1%LIKE%‘str%’%%match%strings%starHng%with%“str”%%Copyright%©%Ben%Cartere7e% 5%CrossmProduct%in%SQL:%%FROM%SELECT%R1.A1,%R1.A2,%…,%R2.B1,%R2.B2,%…%FROM%RelaHon1%R1,%RelaHon2%R2,%…%WHERE%P%• Usually%you’d%want%a%natural%join%– Specify%WHERE%R1.foreignKey%=%R2.primaryKey%Copyright%©%Ben%Cartere7e% 6%3/4/11%4%Renaming%in%SQL:%%AS%SELECT%R1.A1%AS%a,%R1.A2%as%b,%…,%R2.B1%as%c,%…%FROM%RelaHon1%R1,%RelaHon2%R2,%…%WHERE%a%=%c%AND%b%>%100%• Useful%for%defining%shorter%names%to%refer%to%fields%– and%for%defining%column%names%for%arithmeHc%expressions%in%generalized%projecHon%– and%for%defining%column%names%for%funcHons%Copyright%©%Ben%Cartere7e% 7%SorHng%SELECT%*%FROM%R1,%R2,%…%WHERE%R1.fKey%=%R2.pKey%AND%…%ORDER%BY%R1.A1%• Return%results%sorted%in%increasing%order%of%R1.A1%• Specify%mulHple%fields%to%break%Hes%– ORDER%BY%R1.A1,%R2.B1,%…%• Specify%DESC%or%ASC%for%decreasing%or%increasing%– ORDER%BY%R1.A1%DESC,%R2.B1%ASC,%…%• SorHng%can%be%expensive!%Copyright%©%Ben%Cartere7e% 8%3/4/11%5%Set%Union%in%SQL:%%UNION%(SELECT%A1,%A2%FROM%R1%WHERE%P)%UNION%(SELECT%A1,%A2%FROM%R2%WHERE%Q)%• The%two%resulHng%relaHons%must%be%unionmcompaHble%– Same%number%of%fields,%same%domains%• Use%UNION%ALL%to%keep%duplicates%Copyright%©%Ben%Cartere7e% 9%Set%IntersecHon%in%SQL:%%INTERSECT%(SELECT%A1,%A2%FROM%R1%WHERE%P)%INTERSECT%(SELECT%A1,%A2%FROM%R2%WHERE%Q)%• The%two%resulHng%relaHons%must%be%unionmcompaHble%– Same%number%of%fields,%same%domains%• Use%INTERSECT%ALL%to%keep%duplicates%• MySQL%does%not%support%this%Copyright%©%Ben%Cartere7e% 10%3/4/11%6%Set%Difference%in%SQL:%%EXCEPT%(SELECT%A1,%A2%FROM%R1%WHERE%P)%EXCEPT%(SELECT%A1,%A2%FROM%R2%WHERE%Q)%• The%two%resulHng%relaHons%must%be%unionmcompaHble%– Same%number%of%fields,%same%domains%• Use%EXCEPT%ALL%to%keep% d uplicates%• MySQL%does%not%support%this%Copyright%©%Ben%Cartere7e%
View Full Document