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

FIT100 FIT100 FIT100

Anno unc e me nts FIT100 FIT100 FIT100 • 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 ∗ Due T ue sda y June 10 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

De mo FIT100 FIT100 FIT100 • 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())

Anno unc e me nts FIT100 FIT100 FIT100 • 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 o r I NF O to find the link o n the pa g e • Use rna me is yo ur full UW e ma il a ddre ss i f ll il dd • 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

Anno unc e me nts FIT100 FIT100 FIT100 • 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

Anima tio n • A na tura l jo in FIT100 FIT100 FIT100

Stude nt Athle te s FIT100 FIT100 FIT100 • T he Ar e a be ing Mode le d is: ∗ Athle te s a nd T Athle te s a nd T e a ms in Hig h Sc ho o l. 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 .

Stude nt Athle te s FIT100 FIT100 FIT100 • I ndividua ls a re se le c te d fo r a te a m. • T T ra c k po ints a wa rde d to e a c h 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.

Stude nt Athle te s FIT100 FIT100 FIT100 • T T HI HI NGS f I t NGS o f I nte re st, t • T T he se T h T HI HI NGS a re NGS inc lude : e late d a s fo llo ws: r ∗ Athle te s ∗ Athle te s • A Stude nt Athle te • A Stude nt Athle te ∗ E c a n pa rtic ipa te in ve nts ze ro , o ne o r ma ny ∗ Po ints e a rne d fo r T T E E AMS AMS. suc c e ss ∗ T e a ms

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

E xe rc ise FIT100 FIT100 FIT100 • 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

Da ta b a se Sc he ma FIT100 FIT100 FIT100

De mo FIT100 FIT100 FIT100 • Re la tio nships in Ac c e ss ∗ Da ta b a se T Da ta b a se T o o ls > Re la tio nships o 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

Ac c e ss FIT100 FIT100 FIT100 • E ntity Re la tio nship Dia g ra m (E RD)

DB De sig n Que stio ns FIT100 FIT100 FIT100 • Bridg e ta b le s ∗ T T ra nsfo rm ma ny:ma ny into o ne :ma ny 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 o kup T p a b le s ∗ He lps da ta e ntry • Divisio n • Spo rt • Sta te a b b re via tio ns

S S E E XAMPL XAMPL ro m simple to c o mplic a te d Y E Y E R R QUE QUE FIT100 FIT100 FIT100 F

Que rie s FIT100 FIT100 FIT100 • L ist a ll stude nt a thle te s CT * F OM Stude nt_Athle te s 1. SE L E R – Wild c a rd * se le c ts AL L fie lds CT * F OM Stude nt_Athle te s 2. SE L E R BY stude nt_na me ASC BY stude nt na me ASC OR OR DE DE R R – So rts o n spe c ifie d fie ld in a sc e nding o rde r (A-Z, 0-9) CT * F OM Stude nt_Athle te s 3. SE L E R BY t d BY stude nt_na me DE t DE OR OR DE DE R R SC SC – So rts o n spe c ifie d fie ld in de sc e nding o rde r (Z-A, 9-0)

Que rie s FIT100 FIT100 FIT100 4. Whic h stude nts’ la st na me s b e g in with ‘ Smith’ ? CT la st_na me , first_na me SE L E OM Stude nt_Athle te s F R E l E la st_na me L t IKE “Smith*” IKE “S ith*” WHE WHE R R L • * 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

Que rie s FIT100 FIT100 FIT100 5. Ho w ma ny stude nts ha ve la st na me s b e g inning with ‘ Sm’ ? g g (stude nt_id) SE L E CTCOUNT OM Stude nt_Athle te s F R E la st_na me L E l t L IKE “S IKE “Sm*” *” WHE WHE R R ORDE R BY la st_na me • Use L I K E a nd the wildc a rd (*)

Que rie s FIT100 FIT100 FIT100 5. Ho w ma ny stude nts ha ve la st na me s b e g inning with ‘ Sm’ ? g g (stude nt_id) SE L E CTCOUNT OM Stude nt_Athle te s F R E la st_na me L E l t L IKE “S IKE “Sm*” *” WHE WHE R R ORDE R BY la st_na me • Use L I K E a nd the wildc a rd (*)

Que rie s FIT100 FIT100 FIT100 6. Whic h stude nts ha ve la st na me s with ‘ nn’ so me whe re in the middle ? (la st_na me ) SE L E CTCOUNT OM Stude nt_Athle te s F R E la st_na me L E l t L IKE “*nn*” IKE “* *” WHE WHE R R • Use wildc a rd (*) a t b o th e nds ( )

Que rie s FIT100 FIT100 FIT100 7. Wha t a re the a ve ra g e po ints wo n b y stude nt a thle te s? y AVG (stude nt_po ints_a wa rde d) F SE L E CT R OM Stude nt_Athle te s

Que rie s FIT100 FIT100 FIT100 8. Sho w a listing o f e ve nts so rte d b y sta rting da te . g * SE L E CT OM E ve nts F R BY e ve nt_sta rt_da te t t t d t OR OR DE DE R R BY

Que rie s FIT100 FIT100 FIT100 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? la st_na me , first_na me , po ints_e a rne d p SE L E CT OM Stude nt_Athle te s F R 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

Que rie s 9. De sig n Vie w: FIT100 FIT100 FIT100

Que rie s FIT100 FIT100 FIT100 10. Ho w ma ny time s ha s Jo Smith c o mpe te d? p

Que rie s FIT100 FIT100 FIT100 10. Ho w ma ny time s ha s Jo Smith c o mpe te d? p

Que rie s FIT100 FIT100 FIT100 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.

Que rie s FIT100 FIT100 FIT100 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.

Que rie s FIT100 FIT100 FIT100 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.

Que rie s FIT100 FIT100 FIT100 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.

Que rie s FIT100 FIT100 FIT100 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.

F o r mo re info rma tio n FIT100 FIT100 FIT100 • sum, avg, max, min, c ount, e tc . • W3 Sc hools: SQL W3 Sc hools: SQL T T utor utor ial ial ∗ Se ar c h for Aggr e gate F unc tions ∗ http:/ / www.w3sc hools.c om/ sql/ sql gr http:/ / www.w3sc hools.c om/ sql/ sql_gr oupby.asp oupby.asp