FIT100 FIT100 FIT100 Anno unc e me nts FIT100 FIT100 FIT100 - - PowerPoint PPT Presentation

FIT100 FIT100 FIT100

FIT100 FIT100 FIT100

Anno unc e me nts

• Pro je c t 3B

∗ Build the q ue rie s

Build the q ue rie s

∗ Answe r the q ue stio ns ∗ Due T

ue sda y June 10

∗ Due T

ue sda y, June 10, b e fo re No o n

• Sa ve da ta b a se a nd

q ue rie s, a nd uplo a d to Co lle c t I t

• Sub mit q uiz
• Sub mit q uiz

FIT100 FIT100 FIT100

De mo

• q ue rySa ilo rAg e

∗ c urre nt a g e :

c urre nt a g e : Da te Diff("yyyy",[ta b le Sa ilo rs]![b irthda te ],No w())

FIT100 FIT100 FIT100

Anno unc e me nts

• F

re e c o py o f Ac c e ss, Vista , e tc ., fo r e duc a tio na l/ a c a de mic use : /

∗ L

inks o n Co mputing pa g e o n Co urse We b site

• Se a rc h fo r CSE
• r I

NF O to find the link o n the pa g e i f ll il dd

• Use rna me is yo ur full UW e ma il a ddre ss
• Pa sswo rd is diffe re nt!

– Clic k o n "se nd a re minde r" Clic k o n se nd a re minde r – Che c k whe re ve r yo ur e ma il fo rwa rds to

FIT100 FIT100 FIT100

Anno unc e me nts

• Rub ric s fo r Quic k Write s will b e

a va ila b le this we e k

FIT100 FIT100 FIT100

De sig ning a nd Que rying De sig ning a nd Que rying the Da ta b a se

Hands o n in Ac c e ss

FIT100 FIT100 FIT100

Anima tio n

• A na tura l jo in

FIT100 FIT100 FIT100

Stude nt Athle te s

• T

he Ar e a be ing Mode le d is: ∗ Athle te s a nd T

e a ms in Hig h Sc ho o l. Athle te s a nd T e a ms in Hig h Sc ho o l.

• Use r

R e quir e me nts: ∗ A Da ta b a se fo r a n a thle tic s ∗ A Da ta b a se fo r a n a thle tic s

de pa rtme nt

∗ Sto ring de ta ils o f ∗ Sto ring de ta ils o f

• T

e a ms, with divisio n, g e nde r, c o a c h (Stude nt) Athle te s da ta file .

FIT100 FIT100 FIT100

Stude nt Athle te s

• I

ndividua ls a re se le c te d fo r a te a m.

• T

ra c k po ints a wa rde d to e a c h T ra c k po ints a wa rde d to e a c h stude nt fo r pa rtic ipa ting in a spo rt fo r the a wa rding o f sc ho o l le tte rs fo r the a wa rding o f sc ho o l le tte rs.

• T

he Da ta b a se tra c ks stude nt Athle te s o ve r five ye a rs with a ny Athle te s o ve r five ye a rs with a ny g ive n Athle te pa rtic ipa ting in multiple spo rts in a g ive n ye a r multiple spo rts in a g ive n ye a r.

FIT100 FIT100 FIT100

Stude nt Athle te s

T HI NGS f I t t T h T HI NGS

• T

HI NGS o f I nte re st, inc lude :

∗ Athle te s

• T

he se T HI NGS a re

r e late d a s fo llo ws:

• A Stude nt Athle te

∗ Athle te s ∗ E

ve nts

∗ Po ints e a rne d fo r

• A Stude nt Athle te

c a n pa rtic ipa te in ze ro , o ne o r ma ny T E AMS suc c e ss

∗ T

e a ms T E AMS.

FIT100 FIT100 FIT100

Athle te s a nd T e a ms

stude nt_id stude nt_re sult_a t_e ve nt stude nt po ints to da te te a m_na me stude nt_g e nde r te a m de sc riptio n stude nt_po ints_to _da te stude nt_po ints_a t_e ve nt stude nt_first_na me te a m_de sc riptio n stude nt_a ddre ss c o a c h_na me i le tte rs_spo rt_c o de stude nt_middle _na me le tte rs_a wa rde d_da te stude nt_o the r_de ta ils te a m_o the r_de ta ils divisio n_de sc riptio n stude nt_la st_na me te a m_g e nde r stude nt da te o f b irth spo rt_de sc riptio n e ve nt_na me e ve nt sta rt da te stude nt_da te _o f_b irth e ve nt_lo c a tio n e ve nt_sta rt_da te e ve nt_e nd_da te e ve nt_o the r_de ta ils

FIT100 FIT100 FIT100

E xe rc ise

• Build the Stude nt Athle te s da ta b a se

∗ Wo rk in te a ms o f 2 o r 3

Wo rk in te a ms o f 2 o r 3

∗ Yo u ha ve 15 minute s

FIT100 FIT100 FIT100

Da ta b a se Sc he ma

FIT100 FIT100 FIT100

De mo

• Re la tio nships in Ac c e ss

∗ Da ta b a se T

• o ls > Re la tio nships

Da ta b a se T

• o ls Re la tio nships

∗ Rig ht-Clic k > Sho w T

a b le s

• Add ta b le s ne e de d fo r Que ry

Q y

∗ Do ub le -c lic k o n fie lds ne e de d fo r q ue ry ∗ Che c kb o x is c he c ke d fo r c o lumns tha t

Che c kb o x is c he c ke d fo r c o lumns tha t will sho w

FIT100 FIT100 FIT100

Ac c e ss

• E

ntity Re la tio nship Dia g ra m (E RD)

FIT100 FIT100 FIT100

DB De sig n Que stio ns

• Bridg e ta b le s

∗ T

ra nsfo rm ma ny:ma ny into o ne :ma ny T ra nsfo rm ma ny:ma ny into o ne :ma ny

• Stude nts_in_T

e a ms

• Stude nts_in_E

ve nts

• Co a c he s_o f_T

e a ms

• L
• o kup T

a b le s p

∗ He lps da ta e ntry

• Divisio n
• Spo rt
• Sta te a b b re via tio ns

FIT100 FIT100 FIT100

QUE R Y E XAMPL E S

F ro m simple to c o mplic a te d

QUE R Y E XAMPL E S

FIT100 FIT100 FIT100

Que rie s

• L

ist a ll stude nt a thle te s

1. SE L E CT* F R OM Stude nt_Athle te s

– Wild c a rd * se le c ts AL L fie lds

2. SE L E CT* F R OM Stude nt_Athle te s OR DE R BY stude nt na me ASC OR DE R BY stude nt_na me ASC

– So rts o n spe c ifie d fie ld in a sc e nding o rde r (A-Z, 0-9)

3. SE L E CT* F R OM Stude nt_Athle te s OR DE R BY t d

t DE

SC OR DE R BY stude nt_na me DE SC

– So rts o n spe c ifie d fie ld in de sc e nding o rde r (Z-A, 9-0)

FIT100 FIT100 FIT100

Que rie s

• 4. Whic h stude nts’ la st na me s b e g in

with ‘ Smith’ ?

SE L E CTla st_na me , first_na me F R OM Stude nt_Athle te s WHE R El

t

L IKE“S

ith*”

WHE R Ela st_na me L IKE“Smith*”

• * is the wildc a re tha t re pla c e s a ny numb e r o f

c ha ra c te rs

FIT100 FIT100 FIT100

Que rie s

• 5. Ho w ma ny stude nts ha ve la st

na me s b e g inning with ‘ Sm’ ? g g

SE L E CTCOUNT

(stude nt_id)

F R OM Stude nt_Athle te s WHE R El

t L

IKE“S

*”

WHE R Ela st_na me L IKE“Sm*”

ORDE R BY la st_na me

• Use L

I K E a nd the wildc a rd (*)

FIT100 FIT100 FIT100

Que rie s

• 5. Ho w ma ny stude nts ha ve la st

na me s b e g inning with ‘ Sm’ ? g g

SE L E CTCOUNT

(stude nt_id)

F R OM Stude nt_Athle te s WHE R El

t L

IKE“S

*”

WHE R Ela st_na me L IKE“Sm*”

ORDE R BY la st_na me

• Use L

I K E a nd the wildc a rd (*)

FIT100 FIT100 FIT100

Que rie s

• 6. Whic h stude nts ha ve la st na me s

with ‘ nn’ so me whe re in the middle ?

SE L E CTCOUNT

(la st_na me )

F R OM Stude nt_Athle te s WHE R El

t L

IKE“*

*”

WHE R Ela st_na me L IKE“*nn*”

• Use wildc a rd (*) a t b o th e nds

( )

FIT100 FIT100 FIT100

Que rie s

• 7. Wha t a re the a ve ra g e po ints wo n

b y stude nt a thle te s? y

SE L E CT AVG(stude nt_po ints_a wa rde d) F R OM

Stude nt_Athle te s

FIT100 FIT100 FIT100

Que rie s

• 8. Sho w a listing o f e ve nts so rte d b y

sta rting da te . g

SE L E CT * F R OM E

ve nts

OR DE R BY

t t t d t

OR DE R BY e ve nt_sta rt_da te

FIT100 FIT100 FIT100

Que rie s

• 9. Ho w ma ny stude nts we re b o rn

b e twe e n 7/ 21/ 91 a nd 7/ 20/ 95 a nd / / / / ha ve e a rne d o ve r 8 po ints?

SE L E CT la st_na me , first_na me , po ints_e a rne d

p

F R OM Stude nt_Athle te s WHE R E

da te o f b irth > # 7/ 21/ 1991# AND da te _o f_b irth > # 7/ 21/ 1991# AND da te _o f_b irth < # 7/ 20/ 1995# AND po ints_e a rne d > 8

FIT100 FIT100 FIT100

Que rie s

• 9. De sig n Vie w:

FIT100 FIT100 FIT100

Que rie s

• 10. Ho w ma ny time s ha s Jo Smith

c o mpe te d? p

FIT100 FIT100 FIT100

Que rie s

• 10. Ho w ma ny time s ha s Jo Smith

c o mpe te d? p

FIT100 FIT100 FIT100

Que rie s

• 11. L

ist a ll the e ve nts invo lving tra c k a nd stude nts who ha ve e a rne d a t le a st 5 po ints.

FIT100 FIT100 FIT100

Que rie s

• 11. L

ist a ll the e ve nts invo lving tra c k a nd stude nts who ha ve e a rne d a t le a st 5 po ints.

FIT100 FIT100 FIT100

Que rie s

• 12. L

ist a ll stude nts who ha ve e a rne d b e twe e n 5 a nd 8 po ints so rte d with p hig he st po ints first.

FIT100 FIT100 FIT100

Que rie s

• 12. L

ist a ll stude nts who ha ve e a rne d b e twe e n 5 a nd 8 po ints with hig he st po ints liste d first.

FIT100 FIT100 FIT100

Que rie s

• 13. Sho w a listing o f the a ve ra g e

numb e r o f po ints wo n b y stude nts p y in e a c h spo rt.

FIT100 FIT100 FIT100

F

• r mo re info rma tio n
• sum, avg, max, min, c ount, e tc .
• W3 Sc hools: SQL

T utor ial W3 Sc hools: SQL T utor ial ∗ Se ar

c h for Aggr e gate F unc tions

∗ http:/ / www.w3sc hools.c om/ sql/ sql gr

• upby.asp

http:/ / www.w3sc hools.c om/ sql/ sql_gr

• upby.asp