Q1: Seld and emouu blank c a s , iCk by Ccandiia
Re moe
Tanspase data, chagc Uppaicase do Louicase
Dupliccus.
Select and Ramoves Blank calAs
X
1 clieK fund2 Selct
2. elick do Gio Jo apecial
3. choose Blanks
4. click OK and dhun al Jhe blank auslCQs uli
be highighBed
Calls &ecdian an tha
5. Choose he dlde unde
Home Tab
6. click Delede ha C005.
t b Ccanditian
unctian aCCausyau do t
aC ange
Th FILT ER
Ciloua yu dalne.
daBa based
Hanhpae Data
1 selut blank Cca0ls
2
Typt
ype TRANSPOs EC
3. ype th ange he augunal calls.
Ex
TRANSPosE CAL :64)
. s CTRL+ SHI FT + ENTER
UPPARCASE Jo Lowe31Case
1. In&ut a
dumpuGHY Calumn
2 Type UPPERCA2)
Removes Duplicalus
1 Slact the CQ0s yau ulant to check ast dupdicats
2. Click Home >> Canditianal Faumaing> Highiàht ca0ls Rule
Duplicals Vclus
3. In Jhe box
box next do Val ulh, pick th JGumatti
amattir
yecaLwant Jo cLpp Jo dhe duplicalu vauluas, and
u n clíck ok.
4. Sclect Jh sange al Cas dhat has duplicale Vale
5. click DaBa > Ramove Dupicadas, and dhun undtn Calumuns
6. Clfck ok
2 CuLLY he DaBabase ith csV il
1 Canne ciing a Datababe
y s q - hast = localhast- uáu= utihame PasaLGIG
2. Tmpati ng cSVFLL a
FL let's Cale o ne w Schema using he CREATE
SCHEMA Cammmand.
CREATE SCHEMA dcta;
USE data
CREATE TA BLE 'Caunbuus C
id' int DE FAULT NULL
CaLLnbY Lext,
Papulatian' dexB,
Capilad taxt );
Nou we Can load Ahe Cauntsutes dable utth h dada
dada
biom L s CSV Fle
LOAD DATA INFILE Caunt.csv
INTO TABLE Cauntuus
FIELDS TERMINATED 8Y,'
ENCLOSED 8Y
LINES TERMI NATED 8Y 'yn'
I6NORE 1 ROWS
3 . aing CancaB Function unSpuedshatt
Spuud&het
The CoNCAT
The Junc tion Ccohineb dhe dex o
muultinte tanges and 1 álbungs
ayntax CONCAT CLexi , lexu 7 - )
Lexti. Text ilum Jo be jained. A Slung, a aULA
Sltings Such as C ange c e l s .
Lled2 Addidiana dext llms do be jained.
Uing
Uaing VLOOKUP in Speacdshaut.
is deigned Jo
The VLOOK UP Juunctian Un Spuadshat
ROasum a Vcrtical loocKup
=VLOOkOPC)
In the Fasumula Bav, dype
OocKup Veulua, falauwed
2 Tn he parLendhtsl ehle yaWI
y a Cauma H2,
dable a looKp Jable, he siang
3. Enl yawi
ulant do Seasich and a Comuma
deda yau
CH2,83 F25,
index humhiH:(H2, 83: F25,3,
Caluun
4. Ents
Valut, etho
TRUE a FALSE
5. Ento1 hi Jahg lookup malcha.
matche.
unds exact
maichs, FALSE
TRUE unds padial
VLOOKUP CH 2,83 F25, 3, FALSE)
=
O5. Uing jauns un Big@usuy
Bagduy Suppauts ANST SaL jain typas. JOIN apuiadianus
0t parned an dwo lums based an jain Candulianhs
and Jain dypa.
BugCuu Suppats au falauiing jain dy
INNER JOTN INNER1
JOTN
RIGHT 0UT ER PIGifT
JOIN 2OUTER 2 = 2 21
JOTN
3 3
LEFT OUT ER LEFT
JoIN
OUTER3 2
JOIN
FULL OUTER EULL
2OUTER2
=
2 2
JOIN
30.T Nu
CROSS JOTN
CROSs
JoIN
3
(O6 dun SOL
Usin Subquu
9I be
do vn dola Jha
ASAbqu
A ubq u is used
Condidian do
ab a
ubed n dh maun guuy
be uieved.
wthe Juslbucd kho data Jo
the Selec Slarmnt
Subguu utlh
.
aubguuy
SELECT
fROM CUSTOMERS
w HERE ID INCSELECT IO
FROM CUSTOME RS
LUHERE SALARY > 5000),
Jh INSERT Sdadornand
Subgu uth .
INSERT INTO CUSTOMERSBKP
SELECT *FROM CUSToMERS
WHERE I0 IN (SELECT ID
FROM CUSTo ME RS),
SUMIF ,AVERAGEIF Funcin
O7 Use
SUMIF I Iaddb any dht C 00s Jha mt Speciied
Cuebua.
conly he 0ls JhaB mets
AVERAGEIF ' I Auag
Speciied C ug
Ex Aag
Ex' Auvage h CdH alals a andy
andy London Sals.
Sale
08 se PIVOT Table
apatutttt Jool Jo caltulate
ABivaTable as
See
e
urcLize , and analy ze daia dhaB s u
daua
Ccpaiens pattascns and dend n G
Cttale a Pivat Table
Ltlant do ctale a Rival Table EHom
1 4tc Jh C.aULs
2
2. act Iruat>RivaBTable
3.
3 This
Thhib l caade CA eivat Tabk based an an exisluig
Jable Iang
Jha PivaBTable n
4. Sloct New Wakaheet Jo place
watRhut and
C new uwcakAhual o1 Exusing
lho yt uloht t h new PivadTable Jo ppta
5. click ok
Add
Calcuatian ih u
9
CUnitkice
*
iscaLunt)
SELECT Avg
As LAuuage Discaunt
FROMLandu Daialj;
010 Lmpart Fil n Specdsha.
dht Ribbon.
1 click the Dada ab an
2. click he Guat Data budBon
3. Selecd Erom El
. S l k c t Ftom Textlcsv
ulant to umpet
5 Selac th eyau
6. click Impat
Caect
iy h puvew looks
8. click Load
