Date:/-/-

S9L
ntrduction
wht i database?
data base ba lollectio _odata ktored i a forma
hat Ca be easdy be eccesked, Iookz tomanage
Ou databre we use the _Aoftrbiu applicalior Lalles
databax manage ment SyklemDaMs)_
We pnncet to aDSMS Agee iastauclians por qurgang-
o1 edipina alate Tho DBMS _L Cxe e instuelau
Q0e by And wil providlu _a Juut brek

DRMS clekiheod into tho aalegons

Relatonal
2 Kelation I.(No SQL)

Laihat aJatimal atabate managemeat Sgsin

Relatinnal Database:ase thoze dalahae Manageriet
S here_jwe e olatiet n tables thd ale
ekA do eash_glhes using a AelaionthpsAs here
a data Jolaiu inbiluween the data ipi calle
Pelatiana bke dota bases,
The DBMS each Lables Atorcs data akJ
a Apei4 tyhe o ohject lke Cstome produet,-

DBHS1n hse_we quiS te

query ey mocl u ous daa
#learnthesmarterwaj
 Date:/L/
me e he RDBM
My SQL
2 SQL Senver by
3 Ovaele ioso

ha non-vdaluona Pata hcue

In no- relatianul dala bese we_elont ha
tabls o
relatonyhihl,
Histon o SQL
SQL saA_oxigall, develohed
at 1RM_in he
19os and Was naml al SeQvEL wluch Skands
forStuehuxed Engli QUEry Language mak
he shot oim oA SeQvEL
Instali My SQL _ou WndouE
Stez to insall
Oen up uDuY brotw Ser ad head ove to My SQL
2 Go to he dauonlpasls baga
Scaoll doon to botom to kn d Mu SQL lommuniy
edition_whith ifree o losts
Sedeet Mu QLammuniks e
SScll dlownte 4nd MSQL ntallY or wiclnwa
6 Douy nnad dlow g (Xe6, E), M /nstelk
Dn e Nevt oge SCyoll olouou and ckie ou
Jo enks wst stat my dowrloael.
B:Save ha iutaller n you Computa ad opta /n
hen Ynu un Yo odLe_a_2l4_Ji2r

#learnthesmarterway
 Date://-

10 Jut Llit an Devdopua olealsJetuf1 hëk

lo
on Neet dlownlbaola
asks u Du ha Lon ne.ctero python t b k y o
t
lDunloadtor nrd clzck on ncod
2 0 n ckchq nect uou wilu &e a Indallakon w d
cick ouCxeut" to iastau
13AeY all broduetMe ntaled cick ou neye
OnOn n e t Page ow d be proodut onkgaaloo Pea.
ctk Ou ned
S Hee you OU be (oup uplicate l k on nees
Here de ee Le Nehoortug poge Jeave al hu
cleat Settie and clitl ou Nne
On NOt Page 4ou hae p a paskwore
Yoof or asmi uwr So kR O n c t
Here a ae0AEel to tpe pakuo
addnain Uscy dad en cl _Du ect
19 rU_oa nent Ore mare t e
hiaisk the setp n nlcutz
2 CliCk on Nent agaw
23
23 Nou ouLwU Aee aPage called wncta
Senuhere you lee Deey Name as Joot) whi
esptn Tcpreea Aldmia vie knd as tothe
bakuwae! Pla enle He snea peuu lbuch
youatuhn Steb_B t h Letk ou letk

25 Cl C oU Hhish end Chr Ne ano Ourmylee

Serve0a d hnish

#learnthesmarterway
 Date:_//-

Creatin4 a ddabase
we heve Loadel te Quuy_o ceete adeta
yello hundn button to e raede lhe aueis
We selhd a artiulas line o quy Thiu
Yollors hundu buka wil execukthck parkiula
Qy andbae hat selebed a Line
alDte whole t quuiu.
NoL on he SChemas whih ë On the E
nadgauon plane Ne n ke not b akle to
mne daishase er that we haue to
Ires the Schemaso ble to Aee als h
me ato basc_leats_

n elslmg tls Schemas wL hill be

all to nes Vdatabo, On Cleckap _On the
data bac yeu w £ee tablea Veeu, sod
poCcdss tuntionz which as Colled ebrte
J

Vceud 1 1a lik Virtal Cacksohe

Cas Con bi da (tm _mliple t l

Stores Prceluws 1Ihe aks he Ltth poorem

het as ktked Lus otr
tunos databes kos 4eys dela

#learnthesmarterway
 Date://

1o Aec q_pakliola
lo_Lee talele hoved Ha mous on th
alole nam and he on tha uglt mest you
hJ e_atablh wt ndu on it lhkig
O &you Can hea tu date of 1hs take

hie bare database_ol toxe rkick kave fabls

named

Cusfomeys
2eOredalans
Orehs
40sdenstatuses
SPodue
6 Sppe
Whin we_oplm fa.be uutome
l kae_pelds namel
No
ustamer-id
fast- name
3las na ne
4bük_date
Dhone
6 aolosegs

8
9 . Ponss

Cutimer_o haye Unio pulails knoun al keLords

eae
harepuld a d
e ue ofn tabe_orelue e
No
Drolt-d
2 Lutomer-id
Ordn-olate

S Comwod

Shead data
#learnthesmarterw3y
 Date:/L/_

7Sbpeutd nploce o ustome nane

No e sce thad uatom
H e wntgue l o q
we kave hed betoznale
elatioshp
hLhich ts
orelm D to thi jlalssk
onA ex am browa a
he olata base ofthese_pad a
DB CRelatiadatabane2
oflustama
Ne to edit dn_oleta
hen
wllgto Cubme tal ama dE
e orreapedurg lukezna
Hhe utoacx on t

teble ef th dato hake

To Selcetphe woi4.databrr We US
yar
Use ddabase. name

7he SELECT Statemed

Th SeLECT Aatumerd c lous uto exlaca

ahaetionlwhede ( Bu entw elata Ae

Used toehreve dato rom datahae cabze,

Ueel to qy_olata hem aolatabee

Sytax
SeLeCTColumn Calumn-2, Column -n_
i s ed at th ened o th CuehaSyho
xact a wkcand whieh uAt
Cxctyatt al k l d a arDcakor
tot
aa
ke Can Uc As to ferae a Colum
Cam use arthmemc 0perion in geletCalu
#learnthesmarterway
 Date://

Where Clawe USee to flte CCod

SGnta
nta

Sled
teAnana
wer Condu

Ordex by Clae USes to Sof HheKod

We Ue2 DE to (range He dlata n delceraling& ASC for Ase

Snta
Celet
tomelole -m
Osclor bs (olumn

NOTE Do nst mteek SegueAeL_g

The Suntqwes to ael avad Sunta
exror Jou migli qel Cnox Cool
lo64 O Synauemí

And And, OL NOT Ohuakor

And U Sad fos_ Com binin to STstemd
OR US fo Sele.tag tatiskeOn Candelen
Mor VCed for iavesf Hhe Lonotia
yntax A ND
SELECT Column, Column-2, olumn-n
om TablenanL
r LondibionI AND andi hon 2
OR
SELeCT (olumn-L lolumn,
rDm Talele- narn
e CondibonI oR lonoition-2
leftitnesr ref
 Date://-

No1
SELET Column- Lolumn-2, olum-
mTll-name
Nhere NoTConoition oJAND Orolitisr

Logical Oberalcoy Preceslentee

AND OR

Ne Can u e Artmetre_oheat n where C{aur

alko

INand NoT N
TA and NOT IN au used uhen tuo or more o
Condilion a a ued

Syntaa
SELLCTT Column-l,
Lolumn-2. Colu mnn
om 1ahle na me
hhere olumn-nae l UCCndiI,Cordliae
Condlenen)j
BETWWEEN
T+ i ued khes two oy Moe ANS (andiios
Aed

Snda
SELeCT Column-L Ldumn 2 lelumn-n
taoTaele -name
wlkurc olumn -ne RETEN(Cndibs-1\AND ((Ordili

#learnthesmarterw3y
 Date://

2LKe Operator
Like operat c Med to it ecord_Wich
Atatik, Uu tondilianof Spctel patlen_in a lolumn

Sytanc
SELLCT *
om 1abl mame
Where Column-No iKE Condhi ")

NOTe ' Sgn i uses to tl e epositovo

Ouy Conelitun iwe ue Z alfhe Conellio
t_mean hi oñeilion necel2 to ke kllilA Linf
Lf we Use % betore Be londtion it mean
4hat here can be ayt laeevve Dur nduio
ilfls
we USe y o be de of_Kenditin t
mem had Ous Condii née to b e l f s

m e a m Cny numaero Chanat

meom
Slehaae

REGEXP4 T stancds por kgular Etes

EoressLon
Kegulaéren

Synta
SeleeR

m 4alse-name
hua Column-No .ReGEXPCondits)

#iearntnesmarterviay
 Date://-

USed For olny

uedw whtn_we eanatre usiy mulpy
Condltse
Example
REG ExP (prdlituwo!|lorditiin.)
løndllpn
L ed when we Wani a
Coredllia
tobe llLd beaela
RGGEHP CConlls]lendlio2
ed to depict rang-

S NULL otor T+ buses to nd NUL

Valu dn a lalumn
Syntem
trom laele- Nar

LMIT oþevator LHis LAed to Set the

ne.YDuR tD b-diShlaged ifhe Oud pud box

SeLCT*
om k e n a m e
LINIT Number

#learnthesmarterway
 Date:_//-

NNER JoN I Sedeuts kecarc thad bave matulg

/luus iw both tabls

Syta
SELECT
lable- (olumnNomcis)oble Coluan Namel)
fYom table.
ToIN
table -2 On tableI Column -namd-labl2 lolumna

nsbike orting tabk-I4 tabl-2 agaim fagain W

yn uie Alia to veame table & lalle-2 nith
Cou
Some o hbnatmn o thortform Ao make Loole ok ma
heat ano Clean

onng acrOgS the dafabage7 Useol o oun Tables acaoes

ba ddabay we rekix te tusle name wtu
dala badename aA he falole_namus)_d net he
pahl o khu norles databex.

Sekd*
dtatals ome ies
Jo daahax_ ma fale _nas
ON taele_nans r oluma.l talele-nae , oln

eLF QIN Joinin Take nithin the olata base

Sele * Colu m-name Cs)

from 1aek 1, Til T2
Condior

#learnthesmarterway
 Date://-

oToinmss Mubib, talla I Uwed o

bin u i ekle one table2

Sunta
SELECT*
toom Tagle-rame
Som a ble-nan
On Co ndiian
Jo in able- name
On Condilan

LOmpoun Toining2 THZ Uted fo jo tebk

Wtk_e hm ne Colitiem.

tan
SELECT*
rOm Table_va mne Al4s
Join Tabe-na me
On ToditovlAnd endilun 2

wis wng Jom eepalou

Syntsn
SWe
DmTdde-Ynan ala mare
Lhehe Learellan
 Date

Outer Jo

LfFT TOTN RiGT orN

Al Value otoo Al Valuu otwottblA
taele t allvalus ol
p t tablee). taa
Syntax
eleet_ Column-namk J
rom
Sl Column-rom
Tasla- TL from
LEEToIN
ToulleT
1Able21 On_Conoltho Piaht Jovm
Table2 T O
Londls

2ein Mulo table uAm Outer Joini

We ca join Mwlhple te hde ukgukes So
SinilarlG as we oldd Inned oia

SELeCT
(olumn-n@ me&|
tmmTapotl
LEFT 0tN
0n Condulon
LEF oIN
On londlon-)
NOTE Nev OSe Let oi ighd orinre or
A °d ma e rSlaze our Quy
#learnthesmarterway
 Date:/-/.

Sey Soin UStns OuTlY Joina

Selet
Colu mn-hameCSL
+om Ea-blesecg Taþle-m me
LEETom
On Lonalio

Operdor
h he (olumn-ome a exda SL2
Cntha two febles ohich ne Cosego TD

yaSelaat
LOlumnamel&
trOm_Take -na me
on 1akle-nAe
OSiC B|u mn_nA
LEP ToIN Table_na
UCn Columaname)

Natural Son t ib Usel n tallou londilio

1 he aSoc'led takes lgve 0n o
move eud o talunbitally mamco Column
1he Columns Mut he the Sane datz tup

3Donlt_uLL0N clau anal

o
#learnthesmarterway
 Date /-

Surdaeet1 SeLECT
m tolel
Toble 2
Natural So

CDmbiutwol teble (Wheotabe

towhee dabl_

The relultLd iz the pooducB o oohow ol

taba & Tocelu2
e UBe whie wth (xees loi t ecomu
IONER SotN
Synta
SELECT *
From oAle
CvossJom Table 2
nion 0peralb7 Ti 's vsed fo Combtint tweef
Moe

NoTE-1.&vey S elee Statere WA Vnion mu*

havethi Same numbe p{ Columns
. he olu mns mst alzo have Sindarclata Ghes
h e alumnz eyey sele Mallmer mut
ayobe Same oda

#learnthesmarterway
 Date:/_/_

Sunie
SELECT CCumn-namels)fron table
Unton
SELECTColumn-ume(s) From tablu2

Coluy AtrbuteE The þrobetiu ol He

date Column Called Celumn Attret

whil olefnins Column weolefena ollous

atnibut

Data tyhe Dala tyhe

CAART)
T ARCHHREJ-
(Charautes|
tukode noH DA16
uamablk
Datz) harátr|

2Nof NUL Empy values oN not aLcepás

3A Auto InCKCno3

4belalt/xpSSt I4 he leaue a feld

benkt be lleL with tni
Delault yolue.
LnseRI 'hok Le We e
NSER1INTO +nken a Lou_a takle
h'ch s kollowed by yaluL

#learnthesmarterway
 Date://.

Sycto
NSERT INTO Tabu-ame (olumh-nameco)
VALUES

we wantto acld mulpL roo aonce

yta
nbert 1TO labla_ramiColumn.-namea
Values CD,2, G,E) n

NSERTIG Hierarchieal data in SQ Li

UBed_whn tuo o mos talole have arelationslis_
and dn table fadh or analhr tabl_

NSGAT NTo takle-rame lolumnsame)

Valuus

Values Last-i nget joO tor multhl o

Creatun a Copy ata 1 t s IHo

to Cretz dupicakr a fe lke

Cresti table telke-ham

Seleet * wm teble- name 0
Seleck olumn-nameCa) om_ fahke hna

#learnthesmarterway
 Date:/-/

Updatins a Smge/multihle Kows

USeel to Upol kh He exi'stin valur mK
neWvaluus

Syntae To updat ingle

OPOATE Tabe-nana
SET Olumn.na mecs)
Where Coroliien

Suntan to upda multipe YD

UP DATE TalsleL-na me
SeT olumngmek 2
Whene Column mqms) 1W ( lordln)

Smg SubquerteA upolata

We (an ue Subgiuwriswhle updehng tb
eng yin n Whew 0paata

Sun«
UPdab ae-nama
SeA Columns.na mek) 2
Were Column-rame&) IN

Suu
Deletg Row USed to delet roeon
eae.
Sytau
Dele tm tahle -nomws
WheL Column. nq wl Subq u

#learnthesmarterway
 Date://-

MySaL indouwr funatios

qiven a toble Salc
Sales- employec HSCalYea Sale
Auce 2016 I SD. O
Auce GD.o
AC 2018 2o0.o
isob 2ol6 00.o
Sob LISo.o
2018 2sD.0
John
Soh n S3.0

Tohn 017
ob 219 20D.o

Hagreqcte function Lt Aumnaizes da

mulkp JowE nto Age row,
Eq Selest SuM (Sale)
from 2alex,
9adup by Clause Allowg4ato abply Agqreald
4ursleto subic
Eg Sekt fiocal thear, SumSale)
t6om Salca
20uh By hscal-yea
Aleabblyunggrous Ae ahl tosee
Bin enhe basi Ayear

Hecal-yC Sun (Sale)|

2olh
2o3 6s

#learnthesmarterway
 Date:/-/

htHion By

retumsthe Salk q eaih irployee, alery

wh dotal s Dthe enployee by kiSEL_
year
In the ve wl ynu paitzlion by
SeletHiscal-year, Sales.employeesal Seun k)
Ove CPARTITIDN BYfisca_yeä) fom Sees

Hscal-yearSoleemptoy.ce Sale | tslal sales

20lk Auce 1SO
widou T2ol6
Sum
Isob
2016
Suncbo Sohn
20!7 Aue
2o17 Sab
a o17
John yoo
2018 Ate 20
2018 Sob 2UO
6Co
20t8 hn &S 6 S 0

Wincdow neiump oe peadomed en he

ultxb ater all Sola heo gdpby
4 hav b'eto-re the Ocder Byute
selcit DictHnct
lindouw unton Sy es
Wndouw funetlonname CezhrecSin) OVeR C
Lhaxtior deunation
Order deknaEan

#learnthesmarterway
T Date//-

lhe opcnin 4 cles pazerlheses oo th 0VER

Clause a mandaos
indtu-Junttion name Cexpressiin) VERL)

Pantiton -Clause ynla

PARTITION y< exhrese8a)K,<,
brtaks up th ow6_hto Chunla kParlihaa s
uo pasution are Aepa1tedbyq pasttin houndag

rdu by-lam Syntan

Oxder byKetpresie AscIDESA,LLKAa
4 spedes how the uows at ehdued uw?Ua
a arttbko 1t a_possible to ordlu data willa
a pailihor on multipl-Keyk
trameclauge Syn tae

pame is a subSet olCrTen azWaa

rAmLUnd /kxanestart)Kame -betveen)

IE spceikie type edionshphl Lusrent uers
he Jsomcstast kfant-between_d
duaa Ihe ame
boundaly
0me stat
nboundeal precealungKasti at thaJ t sces
the posËlib
s ecedis A shyscel Volmws bete G

#learnthesmarterway
 Date://

famebetween
Betwem ehoundasy ane bound=
* f r n e-Skt
Onhaumolad ollaoma the aome_erds at he

physteal Ua rau a4o. tha

N olw A

Curent Juow

0-lhs My saL hlindou unction lgt

Cume -dht i Calclatathe mulaluuw distibu

Derselomk Assuana a ank to evoy ige w t
Bspastion bakd n te DRreR BY
Llau

*finst_Valne
|LAG
Last Valuu
|Lead
h-\yalue
NTILE
Percend kank
Kank
pw-Number:

#learnthesmarterway