5 Aspects of SQL that You Must Understand to Write Queries

50 %
50 %
Information about 5 Aspects of SQL that You Must Understand to Write Queries
Education

Published on September 24, 2020

Author: davidjohnn

Source: authorstream.com

slide 1: 5 A s p e c t s of S Q L t h at Y ou M u s t U n d e r s t an d t o Wr i t e Q u e r i e s D a t a ba s e m a na ge m e nt s y s t e m s l i ke S Q L O R A C L E M ong oD B ha ve be e n i ndi s pe ns a bl e . H ow e ve r da t a s c i e nc e ha s m a de i t w a y m or e i nt e r e s t i ng. I f y o u t a ke S Q L hom e w or k he l p f r om t he pr of e s s i ona l e x pe r t s y o u w i l l g e t t o s e e t ha t t he r e i s s o m uc h t o l e a r n. Y ou m us t 1. U n d e r s t an d Joi n s O f t e n w e ha ve t o w r i t e c om pl e x que r i e s r a t he r t ha n s i ng l e t a bl e S Q L s e l e c t s t a t e m e nt s . F or i ns t a nc e y o u w a nt t o f i nd a l l t he or de r s of e a c h c us t om e r a nd s how c a s e t he pr oduc t s f or e a c h or de r . I t i s qui t e a ppa r e nt t ha t y o u w oul d ha ve t o i ns e r t a n or de r t a bl e c us t om e r t a bl e a nd or de r _l i ne t a bl e f ol l ow i ng y o u ha ve t o de c i de t he pr i m a r y . I n c a s e s l i ke t hi s y o u r e qui r e j oi ns . I f y o u a r e una bl e t o c om pr e he nd i t y o u s houl d t a ke S Q L hom e w or k he l p s e r vi c e onl i ne . 2. Wr i t e C od e s C om p l yi n g S t an d ar d R u l e s Y ou m us t a bi de by a l l t he S Q L pr ogr a m m i ng s t a nda r ds w he n y o u w r i t e t he que r i e s . S Q L ha s be e n s t a nda r di z e d by A N S I f ol l ow e d by I S O . H e nc e y o u ha ve t o s t i c k t o t he l a t e s t r e vi s i on w hi c h i s S Q L : 2008. S om e of t he not a bl e f e a t ur e s of t he s t a nda r d i nvol ve r e c ur s i ve que r i e s t r i g ge r s T - S Q L a nd s uppor t f or P L / S Q L . I f y o u a r e not f a m i l i a r w i t h t he c ha nge s y o u s houl d t a ke t he he l p of t he S Q L hom e w or k he l p t ut or s . 3. C om p r e h e n d I n d e xi n g I n de x r e f e r s t o a da t a s t r uc t ur e t ha t e nha nc e s t he s pe e d of ope r a t i ons on a da t a ba s e t a bl e . I t i s e x e c ut e d us i ng one or m or e c ol um ns of a da t a ba s e t a bl e . M os t i m por t a nt l y i t pr ovi de s t he ba s i s f or bot h e f f i c i e nt a c c e s s of or de r e d r e c or ds a nd i ns t a nt r a ndom slide 2: l ookups . T hus i t be c om e s i ndi s pe ns a bl e w he n y o u a r e w or ki ng w i t h t a bl e s . H ow e ve r y o u ne e d t o r e bui l d i nde xe s oc c a s i ona l l y a s da t a i s a dde d t o t he t a bl e . 4. S t u d y E ac h D at a T yp e I t i s i m por t a nt f or t he de ve l ope r s t o be f a m i l i a r w i t h t he di f f e r e nt da t a t y p e s . I n S Q L y o u ha ve t o know a bout T e x t I n t e ge r V a r C ha r D a t e B i gi nt F l oa t e t c . T he r e a r e pr i m a r i l y 6 da t a t y p e s - num e r i c da t e a nd t i m e c ha r a c t e r a nd s t r i ng U ni c ode bi na r y a nd m i s c e l l a ne ous . I n t e r e s t i ng l y r e l a t i ona l da t a ba s e ve ndor s ha ve t he i r ow n da t a t y p e s . F or e x a m pl e M i c r os of t S Q L S e r ve r ha s s m a l l m one y a nd m one y . I f y o u w i s h t o know m or e y o u c a n t a ke g ui da nc e f r om S Q L hom e w or k he l pe r s . 5. N or m al i z e Y ou r D at a I f y o u w i s h t o pr ope r l y or ga ni z e t he c ont e nt s of t he da t a ba s e y o u ha ve t o r e l y on nor m a l i z a t i on. O t he r w i s e de ve l ope r s ha ve r e por t e d t ha t t he da t a ba s e m a na ge m e nt s y s t e m s be c om e i na c c ur a t e s l ow a nd i ne f f i c i e nt . N e x t y o u ha ve t o know a bout e a c h l e ve l of nor m a l i z a t i on know n a s f or m s . T he r e a r e f i ve f or m s w i t h t he f i r s t nor m a l f or m r e pr e s e nt i ng t he l ow e s t l e ve l of nor m a l i z a t i on. T he f i f t h nor m a l f or m w hi c h i s r a r e de not e s t he hi g he s t f or m of nor m a l i z a t i on. I f y o u a r e j us t s t a r t i ng out w i t h S Q L y o u m us t t a ke t he a bove m e nt i one d poi nt s i n m i nd. I n c a s e y o u c om e a c r os s a ny t hi ng c onf l i c t i ng do not he s i t a t e i n t a ki ng S Q L hom e w or k he l p f r om t he t e a c he r s .

Add a comment

Related presentations