Truncate “Thole empotee: “> Truncal® fs mot tworting with: “udHeRe' Condition, ' TE we ration “WIREAE™ Condit — Ayla sil Ahr 09 evr | D vets Wha Te “Traneet DOL Command ? | “5 Truncakt wth Loughe data tote ine and duds entice — fab tnclading Stiucliw= & DML Commands % —— TO perform — amy Lind of operakren en ‘data Level ee Cy Pp with DML Commands : \) Tnseat ii) Lppate iW) DELETE iLINseet \- tte de tig i We use “TNSERT CAumna OF “tale ~ Sxgottan = — FINscar mnro TableNarve yacves Cvaluct, Velusty — ) Axangles— TEnsear \n® ‘emp’ Volusss (101, stwe’,' gg —30-a002!, 1o208) LATTo Read the Table doln —— — Seleck & Prom Emp- a ong UE Reed Ho mention) > While inserting — roulhiple ous ab ov afl “The paranthescs and Thin Conic 0% Aame- Tuseer NT Emp values Cloly'stuw’, "Ago 2099" 1000) 9 ‘(102 siwatornan’ ,12-to-22211, 10000), oo) Ctos,'-sive Komna’,' 12-86 -2024!, 10000) » xe we tant ndert the data tte Apedt tte éaluumnas only Tom vert wall toe Nite THR {omen (nro —trotentame Cedumttanae,-—-) valyaal values, Values, --—)} LNSES-® Viale ever Tee Ovdig manttmad tr TE Cautions tn ame. ony deka will be Interted. ° “ a menHoning We column name be consul & eid erro, . . : @ zn -wy Cae VW we \epe tg . with cub Volury y dafaadte face “ WWE" calumny name, te oot—* UPDATE # -_ updating Tht table —) exinled — taformakim We Con gy, With ak a “Uponte’ command + i Syalans- ; { = pence’ taaicname ‘ser’ coLvawname a'valus’ “uHGRe’ CONDITION » update Gnp’ SEr Salay 90000 WHERE mp2 lo L D> we con update’ raltple Columns ab a-tirne ing Tha wHEre’ ‘upoaTe’ commends - ey. poste “emp” SET salarys 3000, DeT-) 124-200! woneae enane swat —_—<* Delete +— eee ead To haleting «THR tabi «doa trtermakim UE Can He ‘DeLetE! Command . i Te ue event mentimed Ta ‘wheres’ Conditim , tha We wil) olkditc Whe fabk — ingermakton - Syntan ~ . DELETE FRom TARLENWme WHERE CONDITION a BeLete FRom ‘employe! WHERE erpnio 4 toi! FE ae ue wank As delet craig pot fee 8 tebe Wan DELETE Fram | Empey! UMERE,. emprto ain( Lol) (03) 6" DELETE From ‘Employee’ WHERE — ementosioyor empNo= loa + > Delecbr estl) Leck wow wise “tote duleting onc loy anc turorl,, ont — J tant Seed Volue)- TR Column ok whine ae , gine Te wr} daside Starling Twymiee, of Colman. iw * Ddentty ¥ RE wy oe Tdlenkity Column loutcalty dspencls stu valeas Ye 1) Seed HW) fncremental vale.> OH Athol Telentity calumn Con be dott “inl datatype only. @ > mostly Tduaiity column » Creabd ok Td-no" Cdumn only. synten Create able Studint Cétutd tok dantity (4) sla_nara — lanchan( 50) ~~ dake ingestion — - — > Tosat tts gluduat (shinand Value (‘Yayo’) {°5ita!), C rarra) — Read rable data — - - Selest % from Stiles Nalz'-@ iq clacve Ao Ue can Slorerve Wak “she td ‘ coleumn To autamakieally — filling wit number’ laked on giten Seed § tncrementad Volize + HE @ while wmrg Tdenkty cdumo., 4 wf pecforen DELETE’ commend Win dake wil be dubeted Temporarily, Balk (hee ule “Trunthke ' TR atx wil be daleled permanently mods * — 4 DOL som DSeLecri— Select Cormmmomd ao be ted do vetiteviag the “eon Valle and aa tae) 0 we Can do Caltulatrns otlio + System cfox_tetrieaig data + — Select 4 from SWident syton fre _Coledabions +> select tost2 | ‘ gelect (oor? , Selecr cuvleSA sNolhs- 7! will Yepresnt — aba all Column? fom gq + Fable - BD yrite ascleck shatement v2 Arfherent — worn? Select # from stadent Select & fam Wee} Cadet), RANTS select tefrom ete Crew bateh2r) «Cdlee). Cstadent) que Sdleck k-feom — [Reco—lootch 22) - [stedante] NOE copy TE Gmp and dep and bonus, Salary tees | Syne Grom — eexe qooges | ®S write a Quey to Sekh Name, emp Tob, emp salan dorm emp tabk? a ad Seleck empName, empTob, emp salary ° rors emp: D wrikea Quen fe Feth eName & salon Gorm Gene Ferdi. who all ane Working Chern = to?" Sele eName, Salary + from Emp where sob) Chew '. alee. ® writ a gusty qo etch Gmp Tfermation fore Emme tah Mio? 2D whe all ane Working duptnoiio’ ! seleck, See Name fron ene yohure, duph nor \o (oO Selene * Lom Emp wn Nites. lene Te! remtioned Lecauye fo Scleet all ere Deptho=lo. Cdumnt feo a table -® Wet 6 Guy -to fetch enlorme, Soh frora Emp lab puke 1R “taking Sallany a -3000" eetewoe Sa Select “stom me — whow — Salon = 2o00- oo Select entame, sal from Emp where — salony = 3000+ ® Wee a quay to Airday enlame perpLo, sola trom emp table , Whose Saleny & more Wan \S007 Seleak Nara, emePo, sal from Emp Wier Sol Suswo > Wile © Quay to fetch Emp mnformebien form | Emme tab who Faking — Salary “ps Wan 20007 Select emp there Salant “= 2000- eo : Select # emp here Galanjeseoo of Sdlaky> Zoco- No — “AND” —Sunceton werle wont tdatle Uday WARE RE conditten QD Wire a Quay to felch eeprom dagen, emp salary “Hom Emp tab who (0 Aonisy salary OO Akan (sco and tex Than > Soh y weee” salve Cisod Bees) + en Bele — eNarra, SRFNO, Sol fron Emp hens Sal $isoo | and Sah 4 2000 «D Wile 0 Gacy te felch — emplayer infuvinaficn — Salon, bebwen (S00 and S000" Select fam emp Where Sal>=tse0 and Sal C= tone oo Sela # Leen Emp shure Sal leckean (Soe and Joeo ® UtviE a Query to disday ernplejer Frformetien SA CepE tho tr towing Selay a% 20007 Seleck % from Emp WHEae Sal <> 2000 * * trom Emp WIKERE Sal |= goo @ Uke a Gury te fetch employer trformaticn who ane toorking tn duptnintio or depk 20 4 Seleee € from Emp WHERE laptNonto or dupttto- a9. Notes - soe See Ta ne Wlenever ue om tang make valiuad fro ne Single Celearen nd UME TUM OR OF We a a a UIN ope’: Select « dtron Ere WHeae DepiNo tn (10/28) D Write a Guay to fdeh — omplazee information ~fsore Emp tatLe expt Sabsrnon and — Clert Job — thfermation 7 pike Select from Emp Where Tob fe\ salsamona! oni! Clare! ov) ted & from Emp WHERE ob Not wCUtere!, \elumad! Seled- ww Seer & fron Emp whee spe L>ckern md Job <> slagmana> wei erp Nome, Aopurkrrent ard Saleny Laon employer Veble > & Query +n felch tno VS etCReg On, dept No enor then 2.0007 SP) No @® 20 Towing Salonga Select ename, dpi) SAL From emp WHERE — deprenlo IN (to12e) and Solem >2000. aE ey) : Sin veal time Quay | Stuchire t= =" Selech — entame ay cLptnte Sat From Gene : WHEE dleptNo INCI020) and SaL>200 ® write a quay to Aitplay omplsjer rformnatitn — tehoate Commisaion f@ NULL D Seleet otros Emp KHER, Cornmatniat'hS nolL" Note NULL tH Undafteed Valun So) We? Conte UNE = \ Compauizion symlosl8 « | WWrtlr a Que te dangly enloter tnformakin — whoote | Commission 18 Nok Nu? Seleat x fron Emp WHERE Comm TSNev Snow’, sha ® wie a Quay fetch —enlame .depel© prom enp table who ang Working — eithun clip Ne 20 @D30 Not working may Saluwan Job) . Sele # eName, deprNo. From emp. ; , WHERE daptNo tn (20,30) and ECD “Salama , : 6g Seleck x from Credboth ia, + CEme) ; ctw dos ‘repracnta — Sthuma of Qteny.BD Wie a Query to Fuch employee Name Whoke 0 sleds with *s'? => The Kind of Infermakion Called ad Pattern informakion + Tr Ths Scenario we have cto Use LIKE" operakey i i | } { Seleck «Crom Emp , WHERE ENAME Whe ‘sh! } ® Wile a Query to Fetch empltame fore onde Whese mame ends sity “9 ENAME \ ; Seleck @ from EMp Where ENAME Like | %8 DS Writ a Quey fo felch emaatee trfermakion from employes Talo. ulhate — nawe and (eter 8 eat syed Sele & From Emp WEEE EName Wee | NH! DW 4 gag do tebe emeayen / tndormnakn -Whete Vase aNd lettex «having “e 7) Seek ~ From Emp Where Name Ute =! JH ®D wri a Quiy tm -felth edlome Fron emp table Name Contata Naty 80) rlgdil F wh Jor ad Select ename -from Emp WHEE ENAme Live Zax! a tT toile Q QUAY do Fetch eName from Erp “aol whade Vee aS Ta Weer. OP Sdeck Etihne From EMP WHERE ENAme LIKE “Cavestioie) 7 bb ey ch ARH tamer, i ee thee Stats with ‘pam? u } t 7 Selek ENAME Drom me WAEAG ENtme LIKE (a-ryyy!ate Uion _Aapeget , Furcions | We Agaregeuts — pumchions ceed somo) Min ©) mero) Avg 0) Count ©) am calzguitedd inte 6 typ Note :- fay any ~Reanctina starting, ending Parortheris am Compalroy Fe ‘Ot ——» SUMO x—— To -fetthing «Te total volte of WR Cam go Letty “Sum fumekion . Pankitate —columo, SUM C caumn Warne) D Write 9 Query cto Hfakth’ "étal “Salaay teem!» Ene tabl 9 4 a edeider A 2 tol Gap eee cleo. } ae Sadek Sum sal) from Emp. [eo with. altay Nome) + Seled- . sum(sal) os Total Salon strom. Emp. , D vrie a gusy to febch teat Salary tom Er p Tobole wit tho an working ty dlapo ‘lo! ? : Ske BETTE MP Selects sum (sal) 0 Toad — Salody feorn GNP Where depNoz lo - i BE a Quan, 4% fetch ~Folad SoleY From Gmp table Who one doing ‘ colerron! “wad oy) Clan! gdeie Suintset) aa telah Sakany Aro Eine WERE —wo= Saleman’ ef — Job = Clare!MIN © TO felching “Whe mimenum — Value of Panttcalon, Column we Can Qo With “PUN | -funcktoa + Sql AAtn Ceaturmnttarne) « @ Wilk a Quay to felch — Minimurn Scllany Proce employes hed? Seleck mMin(sat) from Emp. D vile a Query to fetch — minimum Salauy Fiore emilee fel: Ge QR NOK Loring aL manager, Clerk 5 Analy Sele — min (sald Fron Eme Tae Re — FOR Nor INS manage’, ‘Chern’, Smakyet” ) ——F MAO - Vo fetching the Maximum Value af antic clam , we Can go Utth ‘man’ efeinetion, Syke MAK Cocturnn Mime) « ® Wee as Quay Fo fan Madimum Comrdizaten Fre empleyee bebe) 7 Salede — abel comin) od MAL tommy tran Emp: | D WHE 9 Quy Ao felch mattiewm — Salony Ero Emp Kable Who onc working I dagrNy to OH Fe Buk Wek Waving OR momager - clk! : i Seleck — MpaCsal) Os Max Sal Fron Emp duprto \who,20) Gnd Tob ror in Cranage) {Qua WHEREPa es ww AYO Te ., > gctting The cwerage Value Of pantieal Column; we Can Go IAG MVereege feanchten + Sgnten' AN Ccalumatiarre) QM WE a ery AO eid Average calary from Eve tau? [see Avg(cal) from EME t SS Write a Quay te felch Average salon strom Grpleyer able who ot in 2520 departmedts ond who ome Werking sa ANAGES ase worting Seluk Avg (sal) oS ‘yy _sel com Era Where depo NraTIN( 20/85) AND Jog = manag! Di writ a quay to gr Avermgye. Selany torn GMP toby os ane sorting Pn dept nos to and enone ty Av a manager who ane weenting ty ro ape and working er chene? Seleck ANG (Sal) Fromm Eme (here Coegenes to AND TBS | mMentAGce’) AND (oegenesre ano Tee! CLeee!). 5 oe De ue alserve — aloavs — @uamy ue eve AND’ Be two Conditions + So th “thak condition 3X ht Anas wrong oops ’ DB Meaning, Fer Queny bom perm 18 weerlng tr Dept Nozto wo reonaget oie weretg 0d s daRh Ty cape Ne 20 + Hare Shae pores Can do aootn debt So SK SA Aho eee “your's fe te Wat Se tstend of “ANDY we Tee Ae uo’, adedk A6gsa) From EK y reac Cpeptnoz to AnD de MANE?) OC Coegrtte-re Arto pact ctor’)mote) TE We te AND’ in dylan ik Means both the, Conditions — whalrever Given ik Mud te Sabir ty Mowe UN SOR iy Sytasn “than ok PCNA Fk wee 4, to Ant fy any Oe of The condition + Count ©) TF we Ore COOWTLE) From table name, thon ik Coil veleny Tota) no-of Your Count — in a Tolols « Sgutan- Select, Count’) rors Grape Te we oils — coord catumnblame) thin Jr vil) Fetare | To-of Valuth th a Column Here tt wont Conaidn “poLe’ Values- ~®© scledk —Coonte Cements) tom Benf oudpees— \ty* leecowe no- nul) Valu Se DB shar Counr( comm) from Eme ‘qubput) Ip Fecaue Tae Calum Mneuing Nou value DB Write a Qty to felch mintrurs salon, , Monlimuum Salanj Average salou toto Salamy , NOok emplayen fron emp table? Selec Sum(SAL) a1 Total Solo, , MARCSAL, MIN(SAL, AUASAL) , : cout (&) From Emp, ay * Saeck — Som(SL) a» Total Sal meaCSAl) ay MOR Saly MICS) oy Mutt Cale Aug(SaL) ey Avg Sol Coont ) Fiery Emp see TSE © a ie We E Bo GUNTUemntlannd) and courts) wr ow te die Bio Wheee Clouse ond Having chanel (» (2) How te fetch — dah vise tolat Solawy fm Grroep Oy, Chae jw—-* Cyroop BY, CLauae i To getting the aoe ogqagreqated Valin, Ue Cam ge vi Od a * Gop O17 Cloaste ARO we can cote ott WHERE’ Clore ® Wile a Query Ae fulth — dapentrnak mowite NOO% Empl? | i ~aebeet enone Catepr tis) a5 teorof rnp tramp Seledk * Dept No, countCx) From Gmp. , | Group By PeprNo 7 ow Sele aprNo, Covnir(#) os NO-EmR Keon Eme From Eee Group 8” dag NS- D write 2 quay te felch gob wide fete colany’ “Whe ona working eifhan 16/201 30 dapoakenint? oe. Som : Raed APRS, ewe (Solan) on total Solony From Eme WHEee dugeNo Tal (10,20: 20) Group Oy DOM pide Ne Calta News ehould cepa fr Same Syren ob uize , daph wtae ‘ madimam > wriz a aug te fateh une ona, ering he Salim, Salon Beem tener Tab daar and manager ? ; geleak Joo, Dep NO, MMCSAL) Ero Emp: WHERE — Sob ti ('salsamnon’, Scere! \ maraiget) Grove Sy Joe, Beene + ovo selec soo, Depttlo, mAxLIAD — From EME janeee oR INC! Salatmmon’," Clare’; Smanages ) Group &Y Fok, Dept No-—* Hovng Clause — Ado 1k we wonk ty qe with axa Mowing Clauy a On kp of agg gees apely fly Tin ue Can > Atte — qrespay we cen warty [URE a a Howtng Clouzy, > For Having Clause We Group By SNakammnt mondatery - 4 B® wrk a guy to fetch lepine vette total salary 1... Employee tale wahich ef Tre Gaganttruné 18 Prove Than Gogg Scleok SumCsar) ay teal Saley from Emp Group Bepk Nos Having SumSalan) > Ieee + ae Note Rowing wih wme on Agyesgats funckens only, oul OD write a Quy to Lekch Geb star, chapttlo the Ao-oF empties eAtapt — Salman Jeb peop, whith of | TS Feb i | % having mere Tan 2 employes? t ae Sele ob, dagtlo , @ CoonrC&rpno) WMERe dob |= 'Sabesmam'! Grroop By Fob, depts Jrmuing gam cont (EmPN >ue . i Ib wide, Aapeno whe — NO-of- D> writ query to Fetch J | we Nagrand working a8 a empoyes — urhoae fine de Yeon Saluman , Clerk, manecye? . Seed Job, Pept) counrCemens) Frm Emp sd 4 C SR REO 08 WC Salamed ! chant; Tmanaga’) AND ee rere Grup oy Tob, Pep NO. — TY No Ned & Having Vecange “2d olin ts Ret TAKS, a——# orver BY 4 Aww nee jo getting the det, ctMun we Con go Sn Orne Bored on Colum Aacencting ar Dercending oxdan ey Clone, ORvER BY calumaName Ase | Desc. TE we Urile Simply — ORDER PH CalumaNarme oy te an AaCending Ordans depanlh te od) Yave » Writ a Query 40 daaploy, ernpyer inferenation booted on | Salany fn Deacendig Orden? : i | Seleck % Erom Emp sole DESC: ORDER OD Wk a Query to Airplay — EName pSoleny , Aeptho, Fb trom yew Tolle, Rateple rmonager deb for The Yemoaining intern eken, diagey Ascanding odin bared en Eme No! qlee ettome, dagene , Job, salary &t trom EMR: WMERE Feb <>*monsqur! GeoER By Emprlo. ge tap “Close *— rm aaa cto fate. THE aoe Nvecards heer a Noloke te can We” Top Close”. “ quay te foeh TOP Nalole: o> waite 4 BS eecerds trom Empl Selede | “TopCS) & Loom EMP ‘ . ya ws 3S Whak tx Te exegution orn Of Clause. / erm > ers —S/"Grwovr 21 > @RAVNG, Topclomse:, &<— ORDERS £—selter B wthot OR Quay wri Hing Orde of” tek Clansen? “Saeet” > tr => Cr WERE, > Geoup ey Sawing + a Oenee: ey”BET) Wits Quay dn fetch Laake S TeCerde from Em tetole D Ne DME quay to felch 6 to ih vecard ftom table! ~ —* ConstRminrs &—— ; Aaa RR : Constiaint 18 nothing = bub Rule or a Condition Constraints we Com tmoplement ab “We Aree of tak Creation > We Conttrarats ane Cakagoriseh _ tnt 6 types 1) Not NOLL , WY Unitaue © UT) Peimagy KEY jv) FOREIGN Key v) Caede vi) DEEALLT : 4 Nokes hile we creak & table we Com declare “tot Calum Shold nok be NULL. | Ga Gahile Uking NOT NULL: eck “ cretz table SWidankit staid fat Roe NOLL, Rte-name — Vandhan (20) d sees eglure? > TE once Not NOLL = Given in sat “Ahold not be Yelane | NULL: > wile inserkag the Ada Bakatexpes oh Calurans cout Ye Aare. i a “> We can . tates values © Sadividubh esturans alse By Sddecking Certain Calumnaby : 4 D> While WBertag — data Tots tndividash calumng alae Net Noll Gaur Should be filed, He cadet be NLL] @LANE, \—* _UNIQue f——— Th we gie unique Then te wee atlas rhe duplicabs | Valius and alas ie wit allow 8 Ory One NOLL Value, > Whin ever ,we creat The Tunigu, Key" otkind OF wil) Qenerake tk od, Non- clustered index ee rh ote 40 Creakt multide — UNNgs ans . > Bi Uniqs Ky Conatrainé = Narne €eCommendad to slants catty “ug : eae Ha ee fo ce GORE Comsat Vays —— pebtoaye Name —> “table, Nome —> Keay he ‘“ —_——_—* ——_—___* Syrtee — —fov_Untque Fey | Conttraint Cotta ame Conahraint type Catumotared as combrasnt = UQe Sodas Sha-1d Bdquel( rete td), We Crcatres felole_O ost unique WENT Creat: able stadt (staid tah) gh-nawe Vandnon(2) conibroinle VQ —Steaduct dy SEA unravel stad) ) 1 Primaay KEY 4 seen oes 7 When ever We dadlane Tr OS prirnom, Fey thes Wok panttculey Calum + wall Mot allow huplteals anformetiny and NOLL Valuer. , a “Whenever we Creakr primam “Fey batkind tk dll geecok tr os a“ cluitereg Index" , on fp ot Tabu we Can Oe ‘Creat OMy ore pimomy Yea» wh becomte of © Ginantver Ge Create TK piinony Yay Hatten ak od) Stee. Tre dots in Serting — Ordur the primary Key © Crestveini’ Name Fecomrmendid te Slant an pK”