INTRO DUCT ION TO SL

TRC
SGL fs hased on Reiatino Ngebra,
3. REFE RE
lanquoe
SE &EL Seuenhal tmlzh Gueny whee xo

CREATE eHEMA) Cnn A0TlOIenTio

CREATING TABE AND CONSTRAINS ON T foREIEN

CRTE TALE EmpLOYE No ir

NAmE VARCHAR(i5) NO NULL the aie

SSN NOI NOL,

The
ide DilE
SETNUL
Sute Ssn CuPR )

PRimERY KEY(ssN) hen te

tOREGN KEY (ue-sin) REFERENCES EMpLOYE(ssh); Emploje

n y key alous No'NOLL by delautt

Fove igr de culeu, NUL bH defauit.

h Vactoys dakadupex hat ave usee) NUME RIC cHAR(m), VAREN

}icdelete
INT mALINT t REAL

aemtro ctraato ake | 2 a SE

CItCK (DNO>O AND DNO ) > chen Se loced

Fa
the Netlcables ui
ev
Relo
 5 ALIAS ING
i t e ls each empoee,
For etveive 4he
4he tiGst and oame empeyee yt ard
ee 1n rCEult o his
Immediade utevvisor. lastname and
FrployeeFrome, Mi nit, Lrame, SShn,
PdaBe, Addhexs, sex,
Depot mentDam, DhumbeY, Employe alay,Y-sn,
sa

he a t i u Mar- n, Mgr- stoot date ) po)

Dpt-locaths umbe, Dlocation)
Suteviso
Boject Cpnae, pnumbon, plocation)
t0ls-O (Easn, pno, tours)
Depen dent Esan, Dependert- name, sca, pdade, Relotionship).
Aliasing Employee aa E
[Link]

s=0
CEmploy Employee
ESSN N LN S sA) SSSr FN LN ESS (ue ae doiq
1 Cros prduct on
select
the 2rme kkO
1oc Reame it axs

cotfor
SELECT [Link],LN, S
Frame, SLnarne

FRom EmployEE ASE,EMpLO YEE AS S

NHERE E, FsSSN S. SSSN;

TULES AND SET 0PERAT(ONS

to
eliminate
he duplicate
DISTINCT
CBe the beycmd

SELECT DISTINCr FName fnames oemplye

Atl the
FRom Employee ho
cwoK
Odept no (Wo

WHERE DNO=
dupli c a t o n s )
No, NOu

SELE CT DSTTNCT ton tmplojee HERE DNO-4 uN

St LtC ").
FHO Emplaee HtaE DrNO Jtohose
NO 4/10
No Repetetiong

UNION Eimi rabes duplicade3 SELECT DISTINCT

UNTONALL- esnot Eliminates dupli cadepStLtcT AL

>NOL i
R UNIDS RUNION ALL S

2 SELEC

Ro
a5

PATT ERN MACHING AND p TORS

The LIKE Uned fo Stsi Cvipa 9/amroning

Nou, 1 unnt to find all the
Empioyes udhose ranpei
8-ORDER By
ontaina Ravi
Retyeive aUst
The 2 opeado Uned ate
Ovdesec tyde
O
C Or move chatie ciav lastname,t
Single chane clab

FLECT Frame

Rom
Empotee
hame LIKE [Link]

. Ravi , eore Ravi thee Can ie OR

cchoote C4a.
Ajie Rai thre anbe
Tele c
r ro che tin
 No f ant Cmployee o o thid letter tn Frane ia 'vthe

he
AlLhap
ohone
name LIKE

z d chooe cloat
1s+ chave dar
Ate tha theve an beasqil

3rd cradteda
tefions.
FrameLname upu. Concceenated Narne.

No unnt to incvecose the olany o a the ermployees then

SELECT Frome, 1 SALARY

FRom EmpoVEE;
DETWE EN
SELECT *

FRom Enpojee
WHERE sALA RY BETuOEEN toyooo AND 20.000):

R toC0D SALARY s 20 t d

8-0RDER 8
Retveive aUst oemployee ard the project. they ae D i g 0 ,

Ovdeed b depcoitmert and cothin each depoutmen ordeed phaboaly

y lastname, then fut name.

p o e iihave
a
Reldion like Fitt ovcdes by dept [Link]
Ove ane then oder by

Laatraae and hen by

Odput
Ba C fiakrame
b
c

e D E R BY uned to Ode the Dutpa

in
the a t t i bates apfarg
de Cy clouse
on
Can be applied
c tsy
Pelert Gue
 SELeT I,.iu 11e, EFra, jct 1)e 1O NSER

he Cem
D
tpoyec Ai O u, noject P, Deratnent
I N SE RT
4ERE [Link] AND [Link] -32n AND [Link]= Pprn
-INSEP1

CRDtR 3Y
Doame,[Link], E-trame
e
deloult o CRDER By t
Azcendng Ase Nod,

vaues
Dercendinq desc= Nol deault

. ExAm pLE ON ORDER

A
B select ABc ERoM R ORDERBY [Link] L. DEET

NoD
then,

DELETE

DROP
UppTE
- SEtCT Ac FRom R ORDER BY A DE SC, B,C

12 DER
The tosle
e
AIOtL
 10 INSERT
The Conmrd crd Fecl
Uecl tD
to tnset a
tupde tn the mlaion 1
SERT INTO EmpOEE VALDE S
piOE pr (Ravi',Ra vdo '1236, 4

INIO
-INSERT

EmpLoyEFName, 'LN, ssN No) veLt

Ravi, 'Rav soa,1:34
NoD, 4 uant to imot the valsep, tn a tade in uhth he

vaues pre seht n trothen tae her

K
INSERT NTO E BE) SELECT CDE

EROm SCCOE) wHERE

Y ABC
. DELET E OpDAE
No aont to delele a 4he mplayeenams uooe lastrame ta Ravi

then,

DELE TE FRom EmpLoyEE WHERE LN-Ravi,

DROP AeLE EmpL OYtE

upopTE EpcyEE SET Saky Aalany l1, WHE RE DAIO 5:

pdates he Saloies all Fmpbyee hoc

DuO

2
DEALING KIT NUL VALUES
valus 1 e dont Kr h to intepvet
oblem with he NUlL
the tie
in ookon vaiaies
iE (aen CCmpoi espciey
e aOIe (np
NOLL values Can be TROEFALSE

have to do a

AND

UK F
n GL Cve NOLL Cnsidened t o he d i a t i n c t * to

tet) toith hern tuo re Cpeadi3. S" ANn "1SNOT ate

Trt odcn.

13 IN
tirnd od a4fe Frames Aldheme empode
fov dept 2,345

ÓN
SELECT tram Acddhes
ON
R
FnpioYtE
INHE RE Dno IN (1,2,4)

Fnd FNme
DANO: 2 Dio 2 v ONO: VONo
a l l empl-
Rod rum, Acdcs c employee uho wcx fn depzmtment tocation SELU CT

in Sttord FRON

WHLE
SELECTFmme, Adhen
Ro EmpveE

tAIE R DNO NsELECT DD

FRO EptoATOS
S NENTE
tlEPED TIo attad);
RE TRIEVE
F d DNO DNO Locati
A St4 he arme
D 3 Sotd

StLECTLD
FRCEA Eve
.ANY. ALL SomE
HERE
SE LECT DISTINCT En
FRCM LORKS-ON

Tho, Hecs)IN (SELECT ho tio uRS ROM lORES-ON

WiHERU
 Trianduced P he Sree Guemy i I give,
o
9 TO

(2o0)
A Cho 3010
1020 The Cudo Gueny praduces
The Guesy s
fnd out ail he
Enplorys e
6N SomE h o Fove ec cn C poject
ON ANy Oaohich Employee no-1o has uted
the ame o
no
Houn

d FNhme a|l employee whooAe alamp Ts qmeadon t i a n *e a

a empoyee in defuoment n o s

2ent locotion SELE CT FName

FROM EmploYEE
WHERE
SLDRY ALL SELCT SALAR
FRom fmpLoreE
OHERE DNO S)

5 NESTED COREL
RETRIEVE the fsame oeach empcyee who has a perdert wth

e me trume and is he e c as e employee

SELECTED (Ehamg
FROM EMpLOYEE AS E

AS D
NHERE [Link] NSELECT FROM DEpEN DET

INHE RE E. frome) e D. Detendent. rae

AND F Sex - . s e a );

h e
ON

'to) fer
 eperckrst
Employee Jnne Guery ouc
17. EIsST

hame SN Sex Dxp-derSe

Nome n Lint the

A
50- SELECT
B m50
50 2
503 FROM

ud enOueg pocduc ex AN

SELLCT
ExiS TS AND NoT ExiSTS FRo
SELECT ome NHER

FROM EHpLoveE AS E AND

HERE ExISIS (SELECT*

FPOM DEP4ENI AS D Erp

HERE E8n=D.Es8) AND

Een [Link] AND

EtameDDpatment rame

he c r a uesy ictnns sene vatue the ExI1S E

etnn TRUE ard Thhe intenal uer cteanot retun aogtng Retreive
heExist i teltnn ASE. Contolted

Reteive the tramex

SELECT
emoyee ho have no dependentg FPoM
SEIE CT ame
ERE
rriovtE

E ENI ExIS1SSCLECT FRa DtE NOENT nERE = Fson

h i s Guey ethms
Cme tra heG EXS

hiR uey inn Tothinn he, i t

Retns RUE
caces
11. Ex IsTS ExAm plE
Listthe Frames
omandgey h o have atlest one
dependent
SELECT rame

FROM EmployEE
WHERE EXISIS
EtEC FROM DE PARTME NT NHERE SSn=Fn)
AND

ExSS (SELECT* FROM DEPARIMENT NHERE Ssn =May-ssn

SELLCT Frame
PROM EMpLOYEE SSn: En)
HERE
(SEECTFROM DEPENDENT
NHERE ExISIS
FROM DE pRTIENT
WHERE ssn Mgrn
AND ExIS1S (SELEC

Deperdest fG-1on)
Depaseet

FN E f-ton)
f k ton

ExiSTS ExampiE 2
Retreive t hame
oing
ach
employee o uots on authe ror
Cortxolled depatment umbe 5.

SELECT rame
FROM Employee RITURNS Tu
HERE NOT EXISIS (CSEEC Trurnber
FRo PRAT ECT
NHERE Drum:5)ExcEpi (SELECT PoFRoM
r=Fssnis NoRKS-ON NHE RE SnESsn);
Enp Project
N o r E x s S I S

SSUFN Foum Drum PNo

Revi

fayi oit be printed

19 JOINS
2 0 AGGR
CELCT rre LNarne, Adhez 1Rot-1(E MJLit JOIN DE nRT mtNT

ON NODNtnbe)

Fmlo
t eE Tatnt ean h
Eno t o

T thane,Lrame Adohess TRo (EmpioyE NATutA Jol A pait

s
EpT (Drorne, Dno, Mn, MScdate))
e RE Ome estotih

ttect home AS Empe OYLt. rome LNcaroe AS

iA-0
FRot (Emptore e ASE LEFT OiER JotN
e
Cmpt EE AS ON [Link] n : S. san )

Naketai
SEILcT
RoS(Jon A ardc) S (Nadenal
C
A B NC Join) 4 SELLCT

ABD hin,

eect
s L t anjoin) ( (R der jon) AN.
data
SELcT
elec
 0 AGGRE GATE FUNCTIONS1
ENT A, min, max, tm, Count
ode funeiom
ane
e

Empayee
SE LcT Sum (Saloey) aq(akey)
Eno Ename Salay in FROM EmpLo YeE

Sumavg
D

SELLct sumnalany) As Tota,aug al)

aAverage fRom EmpLOVEE
TOId |Aveog
153

ile calucelatna the Avenaqe NULL Yalues ate not aonsider

)SELTCT naa(alany)-min Cralntn) as dit em Empoee

Ndunal
4 SELECT coUNT (x) as Total FRoM Empoyee
oin
Dalex
maz ae appliee oh NumbA, SEhga,
min,
Stect max(Name) FRom EnploE

XNot value
Seect Sum (name) FRorn Emplaje e
Nuneica ata nd n ny othen
ANg. Son coe applued only on

dota +yp ( 2 , 4 4,4)

SELECT CoUNT Sokng) FRaM Emplayee

,4)
Select couNT (Falany) FROM Employee
dishi nd (.2)
(2.u)
coUNT (alany+HA) FRo Employee (4,4
Seect
a )
H0)RCG Empae
Se'ec n a o y
a AGGREGAE PUNC loNNS 7. TRANS

ATan8acta

pial uniB
Tinolay) I

min(DSTINC SrnRY)1
hans
(la Son(tlaig) max(uny)
max (lsTINCT ShtPYl=4
UNT a l )

23GRoUp By
A

Le u Cc=

te , e
eect ern snop bu

RiA)-
455
3Bch A CouNT )t mR

eheve ouhave one atti bules n

tCep By clause ther) lhe 3houlcd

alutuy" OA)-
tlo 22
p i 3eled cause
rons acdion
each depet ment, Ret efve the Aori citr
no, the num
oh tmptoee
e
dent rnent and their
aveKYe tly
Cosi enc
-0, (outnt C), )
AYg{ala
Isdaio

e tae oil enln a 7epeale Gx, (y

Xah 1li