Download Introduction to SQL: Creating Tables, Constraints, and Queries and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!
INTRO DUCT ION TO SL
SGL fs^ hased^ on^ Reiatino^ Ngebra,^ 3. REFE RE TRC SE &EL Seuenhal^ tmlzh^ Gueny^ lanquoe^ whee xo
CREATE eHEMA)^ Cnn (^) A0TlOIenTio
CREATING TABE AND CONST RAINS 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
ny key alous No'NOLL by delautt
Fove igr de culeu, NUL bH defauit. h (^) Vactoys (^) dakadupex hat (^) ave (^) usee) (^) NUME RIC (^) cHAR(m), VAREN INT }icdelete mALINT t REAL aemtro (^) ctraato (^) ake (^) |2 (^) a SE CItCK (DNO>O AND DNO ) > chen Se^ loced the Netlcables ui Fa ev Relo
5 ALIAS ING it e ls (^) For each (^) empoee, (^) etveive (^) 4he 4he tiGst^ and^ oame (^) o his (^) Immediade utevvisor. ee 1n rCEult empeyee^ yt^ ard^ lastname (^) and FrployeeFrome, Mi^ nit,^ Lrame, (^) SShn, (^) PdaBe, Depot mentDam, DhumbeY, Addhexs,^ sex,^ saalay,Y-sn, he atiu (^) Mar-Employe (^) n, (^) Mgr- stoot (^) date ) Sutevisopo)
Dpt-locaths umbe, Dlocation)
Boject (^) Cpnae, (^) pnumbon, (^) plocation) t0ls-O (Easn,^ pno, (^) tours) Depen (^) dent Esan, (^) Dependert- (^) name, (^) sca, (^) pdade, Relotionship). Aliasing Employee^ aa^ E
E.oNo
s=0CEmploy^ Employee ESSN N LN S sA) (^) SSSr FN LN ESS (ue^ ae^ doiq select^1 Cros^ prduct^ on 1oc theReame^ 2rme^ kkO it axs cotfor SELECT (^) E.FN,LN, S (^) Frame, SLnarne FRom EmployEE ASE,EMpLO YEE AS S NHERE E, FsSSN S. SSSN; TULES AND^ SET^ 0PERAT(ONS CBe the^ beycmd^ DISTINCT^ to^
eliminate he^ duplicate
SELECT (^) DISTINCr FName Atl the fnames oemplye FRom (^) Employee (^) ho cwoK Odept no^ (Wo WHERE (^) DNO= dupli^ catons)
No f ant Cmployee oo thid letter tn Frane ia 'vthe
AlLhap Ate^ tha^ theve^ an^ hebeasqil
ohone
name LIKE
1s+ chave zddar^ chooe^ cloat
tefions. 3rd^ cradteda
FrameLname upu. Concceenated Narne.
No unnt to incvecose the olany o a the ermployees then
SELECT Frome, 1^ SALARY
FRom EmpoVEE;
DETWE EN
SELECT *
FRomWHERE EnpojeesALA RY BETuOEEN toyooo AND 20.000):
R toC0D^ SALARY^ s^20 td
8-0RDER 8
Retveive aUst oemployee ard the project. they ae Dig 0, Ovdeed b depcoitmert and cothin each depoutmen ordeed phaboaly
y lastname, then fut name.
poe iihave^ a^ Reldion^ like Fitt^ ovcdes^ by^ Ovedept ane^ naTe.hey then oder by
Odput Laatraae^ and^ hen^ by
b Ba^ C fiakrame c eDER BY uned to^ Ode the^ Dutpa
c tsy^ clouse^ Can^ be^ applied^ on^ the^
de Cy atti^ bates^ apfarg^ in
Pelert Gue
SELeT (^) I,.iu 11e, EFra, jct 1)e 1O NSER he Cem tpoyec Ai O u,^ noject^ P,^ Deratnent^ D 4ERE (^) DrumbeE.Dno AND E.an -32n AND W.NS= Pprn IN^ SE^ RT -INSEP CRDtR 3Y Doame,E.Lname, (^) E-trame e (^) deloult o CRDER (^) By t (^) Azcendng (^) Ase Nod, Dercendinq desc=^ Nol vaues deault
. (^) ExAm (^) pLE (^) ON (^) ORDER AB select ABc (^) ERoM R ORDERBY A.BC L. DEET NoD then, DELETE DROP
- SEtCT Ac UppTE FRom R (^) ORDER BY A DE SC, B,C
12 DER The tosle e
AIOtL
n GL Cve NOLL Cnsidened to he diatinct * to tet) toith hern tuo re Cpeadi3. S"^ ANn^ "1SNOT^ ate^ Trt odcn.
13 IN tirnd od a4fe Frames Aldheme empode
fov dept 2,
SELECT (^) tram Acddhes ÓN R ON
FnpioYtE
INHE RE (^) Dno IN (1,2,4)
DANO: 2 Dio 2 v ONO: VONo Fnd^ FNme
all empl-
Rod rum, Acdcs c employee uho wcx fn depzmtment tocation SELU CT in Sttord FRON SELECTFmme, Adhen WHLE
Ro EmpveE
tAIE R DNO^ NsELECT^ DD
FRO EptoATOS S NENTE
tlEPED TIo attad);
DNO Locati RE^ TRIEVE St
F d DNO he arme
A
D 3 Sotd StLECTLD FRCEA (^) Eve .ANY.SE LECT ALLDISTINCT SomE En HERE
FRCM LORKS-ON
Tho, Hecs)IN (SELECT ho tio uRS ROM lORES-ON
WiHERU
Trianduced P^ he^ Sree^ Guemy (^) iI (^) give, 9 o TO A Cho (2o0)^3010 (^1020) The Cudo Gueny praduces The (^) Guesy s (^) fnd out ail^ he^ Enplorys e 6N SomE (^) ho (^) Fove (^) ec cn (^) C (^) poject ON ANy (^) Oaohich Employee no-1o has uted the (^) ame no (^) o (^) Houn d FNhme a|l employee whooAe alamp Ts qmeadon tian *e a a empoyee in defuoment no 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
NHERE E.SSn NSELECT FROM DEpEN DET^ AS^ D
INHE RE^ E. frome) e^ D.^ Detendent. rae AND F Sex - .sea ); ON h^ e 'to) fer
caces 11.^ Ex^ IsTS^ ExAm^ plE Listthe Frames (^) omandgey ho (^) 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
NHERE (^) ExISIS (SEECTFROM^ DEPENDENT^ HERE^ SSn: (^) En) AND ExIS1S (SELEC FROM^ DE^ pRTIENT^ WHERE^ ssn^ Mgrn Deperdest (^) Depaseet fG-1on) FN E^ f-ton)f k ton ExiSTS ExampiE 2 oing RetreiveCortxolled^ t^ depatmenthame^ achumbe^ employee 5.^ o^ uots^ on^ authe^ ror 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 NorExsSIS SSUFN Foum Drum PNo
Revi
fayi (^) oit be (^) printed
19 JOINS (^20) AGGR
CELCT rre LNarne, Adhez 1Rot-1(E MJLit JOIN DE nRT mtNT
ON NODNtnbe)
t eE^ Tatnt Fmlo
ean h (^) Eno to 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- FRot (^) (Emptore e ASE (^) LEFT OiER (^) JotN Cmpt EE AS ON E.Se n : S. san ) e Naketai SEILcT
CRoS(Jon A^ ardc)^ S^ (Nadenal
A B NC Join)^4 SELLCT
ABD (^) hin,
eect sLt anjoin) (^ (R^ der^ jon) AN. data SELcT elec
a AGGREGAE PUNC^ loNNS 7.^ TRANS
ATan8acta Tinolay) I pial^ uniB min(DSTINC SrnRY)1 (^) hans (la (^) Son(tlaig) max(uny) UNT al) max^ (lsTINCT^ ShtPYl=
23GRoUp By
A Le u Cc= te , e eect (^) ern snop bu
RiA)-
3Bch 455
A CouNT )t mR
eheve ouhave one^ atti bules n
tCep (^) By clause (^) ther) (^) lhe 3houlcd (^) alutuy" p (^) i 3eled (^) cause OA)- tlo (^22) rons acdion each (^) depet ment, Ret (^) efve (^) the (^) no, the (^) num (^) oh (^) tmptoee Aori citr Cosi enc
e (^) dent (^) rnent and (^) their (^) aveKYe (^) tly ) Isdaio
-0, (^) (outnt (^) C), (^) A (^) Yg{ala
e (^) tae (^) oil (^) enln a (^) 7epeale Gx, (^) (y Xah^ 1li
