Curso S uperior de T ec nologia em A nálise e Desenvolvi mento de
S istemas
Disc iplina Banc o de Dados
S egundo semestre de 20 10
L inguagem S Q L para c onsultas – P art e V
Consultas de Ref erênc ia Cruzada
A t é agora, est i vemos t rabal h an do com con su l t as do t i po S el eção.
Cri ávamos as con su lt as, especi fi cávamos al gu n s parâmet ros, e
obt í n h amos u ma li st agem com os regi stros qu e at en di am aos cri t éri os
especi f i cados.
Nest a l i ção i remos apren der a cri ar u m ou t ro t i po de con sul t a acei t a pel o
Mi crosof t A ccess. A pren deremos a cri ar con su lt as do segu in t e t i po:
"Con su l t a de t abel a de ref erênci a cru zada."
O Mi crosof t A ccess n os of erece 6 t i pos di f erent es de con su lt as, con f orme
i n di cado abai xo:
Con su lt a de S el eção
Con su lt a de t abel a de ref erên ci a cru zada
Con su lt a cri ar t abel a
Con su lt a at u al i zação
Con su lt a acréscim o
Con su lt a exclu são
Para u ma descri ção dos con ceit os bási cos desses sei s t i pos de consu lt as,
con su lt e o Cu rso de A ccess B ásico
A con sul t a do ti po Tabel a de Ref erên ci a cru zada, con f orme o própri o
n ome su gere, é u t il i zada para cru zar valores de du as gran dezas. Por
exempl o, posso cri ar u ma Con sul t a do tipo Tabel a de Ref erên ci a cru zada
para exi bi r o nú mero de pedi dos (qu ant it at i vo de pedi dos) por Paí s De
Dest i n o e por an o da Dat aDoPedi do, con f orme i n dicado n a f i gu ra abaixo:
-1-
Inf ormaç ões do Q uantitativo de P edi dos por P aísDeDestino e ano
da DataDoP edido.
Podemos observar qu e as i n f ormações ret orn adas por u ma con su l t a
dest e t i po, são i nf ormações con sol i dadas, on de est amos cru zan do
i n f ormações do Paí sDeDest i n o, com o Ano da Dat aDo Pedi do. A
I n f ormação qu e est amos obt en do é o Quan t it at i vo de Pedi dos por A n o e
Paí sDeDest i n o.
N uma c onsulta do tipo T abela de ref erênc ia c ruzada, temos três
elementos básic os:
Li nh a
Col u n a
V al or
-2-
L inha: No exempl o da f i gu ra an t eri or, o campo qu e f orma as li nh as é o
campo Paí sDeDest i n o, i sso si gn i fi ca qu e para cada Paí s De Dest i n o o
Mi crosof t A ccess ret orn a uma li nh a.
Coluna: No exempl o da f i gu ra ant eri or, o campo qu e f orma as col u n as é
o A n o da Dat aDoPedi do , i sso si gn i fi ca que para cada A n o o Mi crosof t
A ccess ret orn a u ma n ova col un a
Valor: No exempl o da f i gu ra ant eri or, os val ores qu e preen ch em o
rest an t e da con sul t a são obt i dos a part i r da co n t agem do Nú mero de
Pedi dos.
Para en t en dermos bem como f u n ci on am as con su lt as do t i po Tabel a de
ref erên ci a cru zada, vamos prat i car u m pou co cri an do al gu mas con sul t as
dest e t i po.
E xemplo 01: Criar a c onsulta ind ic ad a na f igura anterior, onde
será exibido o Q uantitativo de P edi d os por A no da DataDoP edido e
P aís De Destino.
P ara c riar a c onsulta proposta f aç a o seguinte:
Cri e um a n ova con su lt a, baseada n as t abel as Pedi dos.
A di ci on e os campos Paí sDeDest in o, Dat aDoPedi do e Nú meroDo Pedi do,
n est a ordem. Na col u n a Dat aDoPedi do, preci samos ext rai r apen as o an o
da Dat aDoPedi d o, para i st o vamos u t il i zar a f u n ção An o. Na l i nh a campo,
on de est á Dat aDoPedi do, al t ere para o segu i nt e: An o([Dat aDoP edi d o]).
Observe qu e ao sai r do campo o Mi crosof t A ccess adi ci on a u m E xpr1 :.
A l t ere o E xpr0 1 para A n o do Pedi do.
A gora t em os qu e t ran sf orm ar a con su l t a em um a con sul t a do ti po
"Tabel a de ref erên ci a cru zada". Para i sso f aça o segu i n t e: S el eci on e o
coman do Consulta -> T abela de ref erênc ia c ruzada.
Observe qu e du as n ovas l in h as apareceram n a est ru tu ra da n ossa
con su lt a: A li nh a Tot al (qu e já u ti l i zamos an t eri ormen t e) e a l in h a
-3-
Ref erên ci a Cru zada. Ut i li zaremos est as du as l in h as para defi n irmos a
n ossa con su lt a.
Na col u n a Paí sDeDesti n o con fi gu re da segu i nt e m an ei ra:
Tot al :
A gru par Por
Ref erên ci a Cru zada: Li n h a
Com as con f i gu rações an t eri or, est amos i n f orman do ao Mi crosof t
A ccess qu e h averá u ma li nh a para cada Paí s de Dest i n o
Na col u n a A n o([Dat aDoPedi do]) con f i gu re da segu in t e man eira:
Tot al :
A gru par Por
Ref erên ci a Cru zada: Col u n a
Com est as con fi gu rações, est amos i nf orman do ao Mi crosof t A ccess
qu e h averá u ma col un a para cada A n o da Dat aDoPedi d o.
Na col u n a Núm eroDoPedi do con f i gu re da segu i nt e man ei ra:
Tot al :
Con t ar
Ref erên ci a Cru zada: V al or
Com est as con fi gu rações, est amos i nf orman do ao Mi crosof t A ccess
qu e o campo Nú meroDoPedi do f ormará o s V al ores da con sul t a e qu e est e
val or será obt i do con t an do o Nú mero de Pedi dos para u m det ermi n ado
Paí sDeDest i n o e An o.
E m resum o n ossas con f i gu rações deverão f i car con f orme in di cado n a
t abel a abai xo:
Coluna
L inha T otal L inha Ref erênc ia Cruzada
P aísDeDestino
A gru par Por Li n h a
A no( [DataDoP edido] ) A gru par Por Col u n a
N úmeroDoP edido
Con t ar
V al or
S u a con sul t a deve est ar con f orm e a in di cada pel a f i gu ra abai xo:
-4-
M odo E strutura da Consulta do t ipo T abela de ref erênc ia c ruzada.
S al ve a con sul t a com o n om e de Ref Cru zada 0 1 .
V á para o m odo Fol h a de Dados e ob serve o resu lt ado, deve est ar
con f orme a f i gu ra i n di cada n o in í ci o desta l i ção.
V amos exercit ar u m pou co mai s as consu l t as do ti po Tabel a de
ref erên ci a cru zada.
E xemplo 02: A lterar a c onsulta Ref Cruzada 01, para q ue sejam
exibidos os totais de P edido por mês ao invés do A no. Com isso
teremos 12 Colunas, uma para c ada mês.
P ara c riar a c onsulta proposta f aç a o seguinte:
V ol t e para o Modo E st rut u ra da Con su l t a Ref Cru zada 0 1 .
Na l i nh a Cam po, n a col un a A n o([DataDoPedi do ]), al t era para
Mês([Dat aDo Pedi do])
V á para o m odo Fol h a de Dados e ob serve os resul t ados. V ocê deve
obt er u m resu lt ado semel h an t e ao in di cado n a f i gu ra abai xo:
-5-
N úmero de P edidos pelo P aís De Destino e pelo M ês do A no.
A ú ni ca mu dan ça qu e f i zemos f oi ut i li zar a f u n ção Mês ao i nvés da
f u n ção An o. Observe qu e a fu n ção mês ret orn a o n úmero do mês (
1 =Jan ei ro, 2 =Feverei ro, et c). Como são doze meses n o an o, obt i vemos
doze col u n as de val ores, uma vês qu e a expressão Mês([Dat aD o Pedi do]),
est a f orman do as col un as da n ossa Ref erên ci a Cru zada.
V ocê t ambém poderi a especi f i car cri t éri os. Por exempl o você poderi a
col ocar u m crit éri o para qu e f ossem exi bi dos somen t e os Pedi dos para
B rasi l , Fran ça ou A l eman h a. Com i sso n os t erí amos s omen t e 3 li nh as n o
resu l t ado fi n al , u ma vez qu e Paí sDeDesti n o f orma as li nh as da ref erênci a
cru zada e est amos exi bi n do soment e para t rês paí ses.
V amos f azer est e t est e, vá para o modo est rut u ra da con su l t a Ref
Cru zada 0 1 , e n a l i nh a crit éri o do campo Pa í sDeDest i n o, di git e o
segu i nt e:
In ( " Brasil" ;" Franç a";" A lemanha" )
V á para o modo Fol h a de Dados e Obser ve os resu lt ados, somen t e serão
exi bi das t rês l in h as: U ma para o B rasil , ou t ra para a Fran ça e uma para
-6-
a A l emanh a. S al ve e f eche a con sul t a.
E xemplo 03: Criar uma c onsulta do t i po T abela de ref erênc ia
c ruzada, a qual exiba o valor total d as vendas para Cada Cliente e
pelo A no da Data do P edido. Classif ic ar em ordem asc endente pelo
N omeDaE mpresa.
P ara c riar a c onsulta proposta f aç a o seguinte:
S al ve a con sul t a Ref Cru zada 0 1 e Fech e a con su l t a.
Cri e um a n ova con su lt a baseada n as t abel as: Pedi dos, Cl i en t es e
Det al h es Do Pedi do
A di ci on e o cam po Nom eDaE m presa da t abel a Cl i en t es
A di ci on e um a col un a cal cu l ada com a expressão: A n o do
pedi do:A n o([Dat aDo Pedi do]).
Cri e um a col un a cal cu l ada, para o subt ot al de cada í t em , u ti li zan do a
segu i nt e f órmu l a n a pri mei ra col un a em bran co:
T otal de vendas: ( [ P reç oUnitário] *[ Q uantidade] ) *( 1 -[ Desc onto])
Tran sf orm e a con sul t a em u m a con sul t a do t i po Con sul t a de t abel a de
ref erên ci a cru zada. Para i st o dê u m cli qu e n o men u consu lt a e depoi s dê
u m cl i qu e n a opção "Con su l t a de t abel a de ref erên ci a cru zada".
Con f i gu re a con sul t a, con f orm e a t abel a abai xo:
Coluna
N omeDaE mpresa
A no( [DataDoP edido] )
( [ P reçoUnitário] *[ Q uantidade] ) *( 1 [ Desc onto] )
L inha
T otal
A gru par
Por
A gru par
Por
S oma
L inha
Ref erênc ia
Cruzada
Li n h a
Col u n a
V al or
Form at e a col un a cal cu l ada (Cl i can do com o di rei t o e escolh en do
propri edades), como M oeda e du as casas deci mai s.
-7-
V á para o modo f ol h a de dados e observe o resu lt ado, deve est ar
con f orme i n di cado n a f i gu ra abai xo:
Ref erênc ia c ruzada c om o T otal dos P edidos por N omeDaE mpresa e
A no da DataDoP edido.
S al ve e f ech e a con sul t a.
E xemplo 03: Criar uma c onsulta do t i po T abela de ref erênc ia
c ruzada que nos mostre o valor M éd i o do Frete por P aís de
Destino.
P ara c riar a c onsulta proposta f aç a o seguinte:
Cri e um a n ova con su lt a baseada n as t abel as: Pedi dos.
A di ci on e o cam po Paí sDeDest in o.
Cri e um a col un a para o m ês do pedi do, u t i li zan do a segu in t e
expressão:
M ês_ped :M ês( [ DataDoP edido] )
A di ci on e o cam po Fret e da t abel a Pedi dos
-8-
Tran sf orm e a con sul t a em u m a con sul t a do t i po Con sul t a de t abel a de
ref erên ci a cru zada: Dê u m cl i qu e n o men u Con sul t a e dê u m cli qu e n a
opção C on su l t a de t abel a de ref erên ci a cru zada
Con f i gu re a con sul t a, con f orm e a t abel a i n di cada abai xo:
Coluna
L inha T otal L inha Ref erênc ia Cruzada
P aísDeDestino
A gru par Por Li n h a
M ês( [ DataDoP edido] ) A gru par Por Col u n a
Frete
Médi a
V al or
S al ve a Con su lt a com o n ome de Ref Cru zada 0 3
V á para o m odo Fol h a de Dados, voc ê deve obt er u m resul t ado
semel h an t e ao in di cado n a fi gu ra abai xo:
Ref erênc ia c ruzada, c om a méd ia dos f retes por M ês e por
P aísDeDestino.
S al ve e f ech e a con sul t a.
-9-
V amos f azer al gun s exercí ci os para f ixar bem o con cei t o de consu lt as do
t i po Tabel a de ref erên ci a cru zada.
E XE RCÍCIO S :
0 1 ) Al t erar a con su lt a Ref Cru zada 03 para qu e exi ba os t ot ai s de f ret e
por Paí sDeDest i n o e por mês do an o.
0 2 ) Al t erar a con su lt a Ref Cru zada 03 para qu e exi bo o Qu ant i t at i vo de
Pedi dos por Paí sDeDest i n o e por mês do an o
0 3 ) Cri ar u ma con su l t a de ref erên ci a cruzada qu e exi ba o Tot al de Fret e
por Tran sport adora (campo V i a da t abel a Pedi dos qu e se rel aci on a com o
campo Códi goD aTran sport adora da t abel a Tran sport adoras, u t i li zar o
campo NomeDaE mpresa da t abel a Tran sport adoras) e por Mês da
Dat aDeE n t rega. S al var est a con su l t a como Ref Cru zada 0 4
0 4 ) Al t erar a con su lt a an t eri or para qu e ao i n vés do Tot al de Fret e,
sejam exi bi das as médi as de f ret e.
0 5 ) Al t erar a con su lt a an t eri or para qu e seja exi bi da a médi a anu al de
f ret e por t ran sport adora.
-10-