Scribd
Upload
168 views223 pages

SQL Notes

The document provides an overview of SQL and database management systems, detailing key concepts such as primary and foreign keys, data types, and normalization. It discusses various types of DBMS, including relational and hierarchical systems, and emphasizes the importance of structured query language (SQL) for database interaction. Additionally, it covers constraints, attributes, and the organization of data within tables.

Uploaded by

vijaykumarsf00
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
168 views223 pages

SQL Notes

The document provides an overview of SQL and database management systems, detailing key concepts such as primary and foreign keys, data types, and normalization. It discusses various types of DBMS, including relational and hierarchical systems, and emphasizes the importance of structured query language (SQL) for database interaction. Additionally, it covers constraints, attributes, and the organization of data within tables.

Uploaded by

vijaykumarsf00
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
* Prirnaory Key 2 Fereign Key K Statements jn SQL (veven Cin) > PPL > DML —LInsent _ Update > BOL Cota topics) Ae Joins ‘ RK IANex Join F SAL Tom - Sut - Query Nloymals zoction _— Stagle Rous Functions (v.v.v-Serp) Loponum ~~ Co—velated Subaueny _ PL/sAL SS : il xe View ke Indox K Procedure a Fi ee cola ok * * at Pesewdo Columns , — + * — a 4 Co--rleted Sule- Query xr”? > Co-slated Sub -Guan4 is more efficient hey cub- Query ard Jens. : 2 SS ble Can find nth max and Mh min Salawe. > s \ 2» + a < | i \ t ype Lah =? wr 8k ) scpm 26] x, ee ek Tappan ase & Wee YP Of. -Pyplveching sas ate es, << ae See ie 5 Stand alone Application => pet, Exc) we nee > fb, iosta, eke ») E) avd sree cory atin sh te, ED) ena Samavory appli cation = ls @) Mowvile application. Thaw tc Yrvolement of sthird pert SD Latenent required pWhet 36 “Darabase 7 >? Data con be veceversd ay any ime ond ond whow. in ond device To Contact with glotebare Whe ned SQL. k Boi 5. Anigthieg which Kos dts aistence, A Attributes are nothings but — properties pon ase qoorgl /evuTQVe oS wee S QL (tou cturod Chery hongvogs) ) ‘Data r Data ig a ‘oud fact which describes | ies attyibutes of an orrti-ty. Detabose- Ft ts a plate whew dota will be Stored fn Systemetic and organised Monnen # Baste Operections — parformecl or database ane ) Create. 2) Read 2) Update a) Delete * Gpnerely Known as Peles . 6p - Dectalpase. 2s Asystematic 4+ Svaariaed +t ler * ae Steen (dp ms) 1S Softhoan.| ‘me OR ths “hoo Tepertant factors ant! ) Sacevity 8) Autherizotion > Query language is cated +0 comminani cate with dae DEMS. To Communicate with ahis svftwone We need ae oo Language calud Query Lanquoat > Different ky pes of DBMS Softusome — = =—_S= —" | )& Relotional DEMS CRoens) = mst used & pope, 7* Hiearchical DBMS y *€ Notwore PeMs 4 Object orianted DBMS 64 Graph DBNIS be Gaoepl— SRS ER modal PBMS X % Document DBMS RDBMS + It Stands fr Ratotional Database — Monooprrent System. 4 TE ts a type of OBMS Softuvans whew date's norl| le Stored in the. form Of yelahons or tables HF To Communicota with this Softuseas woe need Language. called Structuud Quem Language Csa >) —_ A ' C.F. Copp rules ee oe N PBMS+ RM => RpBMS > Creare [Insert > Road | Robriene sbrctoy > Update (modify < a > Delete | Drop Dedalnse cau Gay" Retotional Modal - pres *K dt ts a -Cmcept Which was dotigned by “EP Codd” who iS a data Sclentist. te Tn this we Can Steve —the data in the form of Velotions at tables, # We can also chue Meta data in the form of tables . *% Meta obta : Data aloout -the data DBMS \ Rutotional Model ——— > Tobles 4 Rarodional SBMS ——> Tatol es , x Be Any PRMIS thet follows Relational mode will becomes a Relational DEMS, Te to) ap ast? Ralatinsl model dato stirred in les and Some as in RPMS. Fasvefenmnce blr act Snel —__ — ——_—_ = 7 & RDBMS > Gt cant Store meta data | > we can Sture 14 ehod clato. 19 RDBMS, igre D Tho Sutatioship bho [> Thos is yElotionship bbe dott . * dvto . D> FO nted particuley lonquegs| > Ae naozd SAL to Carnmun'Care 0 Communicate worth RPBMIS, D> BA does rat folkeo Watiaal | T+ follevos. _ meds! i > FE does ret Hllno EFeadd ut] > Te Follows tan iS > Me Secunty g futheritalion|> Security Atheri2 ttm ted dst valne of data, | Unlimited Volume of dade, Aare, uae Task Se one =— @y vif blw DB and pems? vite bl pBMS ancl RDBMS 7 lalhat 1S velational model? Who is EF Cadd ? oO Dotokase + It is a collect of data." can be words yor Write on O piece of paper ara digital Fila. eees Databace Management System & (BBMY)~ DEMS is a | Pec of — Softoore —that Managps datoaleases , ase me Th Cn Create, edit, delete database thoty Kal and they data, @) Dens | RDBMS # In this dota is Steed [at Doka ctrred in a taoull as afk. #Asero te Relationship bles two f3lay [ak Ralartmchip to two tally ars maintained Propammad | os ily con be specitied tt ~colly, time af tala Creation, 4 Doms does not Support Clied ee DE s prerts Client sem Senvey architacutus, ' Orrehi tecture, HK De does not Support disturd.|* Te supports distribute ~uted architect - aychitectune. § ae (# Dems thew is no secu In this Ani are ryultiple oral j of dots. of Security , at loggirg level, at Corn ~and uel, at objact level Nemmaltzation is ret availa] ae Nov ion | ‘ ‘ . seen n malization iS awailable in ble BMS. @ Relational ede! Relational model vepresents I how aotan TS Stored WH mweladional dakatoases dota in the Arr) Relational database Stvres | | of Anbls. @ Ep cedd + Edgan Pan HK Me 1g ow Cempr0r Moe ‘Yelotional datoleasex modal, . | : | To toles + Table is a loxcol organization of dato which comsists of Rows and columns. Columns) =—_—_ = ' % Columns iS also refantied as Attribute ov eratds. * A Column IS used ty Vepresent one property of all Ane on titien, I | | { Ss Rows Rows is also Rafennad a& Records © Te, -& A ww is used t? "epretect all ~tha Poperties af Svat, entity. call Coll is the smattest Unit in table in which We Store deta, the inten cection of “rows and Columns qererate Colls , Table Stree we Attri butes/ columne|fpiolds Nome Nive cane Rohith MEch Mumia} 3 Rahul avil Renjar Rules of EF, Cobp d The data Stored jn the call must be a Strate Value data, 2 Gm RDBMS we stove euerytting Sin Ae -Fovn of alles Including Mate dota. (the dotoils about tH doro is mato dato). 3) According ap EF. Cedd we an Stove dato in mult) pe tables , Hf Weeded we Can establish Connection leo to tales using Koy Attributes. 4) We can Validate the dota entered “Mio the tole In two Steps a) By Assidivg hostachy pes %) By Assigning Costvaints, No te Here datatypes ane [Link] whawoas Syritox + Chor (size) ae bila gine — gt ic ased *0 datermina the no. of Chava —ctens thet we Can Stre. t we Alhanwe we mention chon datatype We have to mention sia fr te. The Max, sim thot we can Store ic 2000, tM lea tye Oe “Eto Ueagih memo alleen. Example - Chat ce) cenee usad rene pone f “ Mem: Disadvantage Wlastoae pf rem ts high . Gr ovdan =F? ovenceme this disadvantage. fn tbroducd Ov Chay. BF pecine = kK varcupR datatype Can accep chayarters Such ag , A=2", ‘a-z . ar] spectal Chortact ers (+ a 4 : ) LARCHER (axe) Syntax > Sizo> Te is used to determine the [Link] chy ~Yocters that we Can Shire . aw ‘ | henevey we mention Varchay datatype we have. tO mention Siza sy TL, KThkL Nox. zo that we Cap ctYe is aem— aA , 7 type of © Vow ialole emg th a allo catigg” K ctho’ ic no wastag of Memory Yo varCha, E®"O Verchan Ce) CITT TT] Y49 blocs ane Shred jn Memory location \lawchan 3: se 4 vgdatad vention / — a de is just wpm ection of the verdes ¢ TL con Store agin gooo to ras Syne he Marchex Giza’ O Somberr This doctacryre is used to Stove numenical values, re a TL Can accept two orrquments a) Precision 6) Scala | Syptaxs Number C Precision, Escats}) Pracision Pt te used to determing the digits bo we one qpirg to Sta to numenical Place Scaler re ts used to Oetermine the me. of —=— . = daqirs we ono ging to Store within the prectsion. in decimal place — & the max. Precision wa can Chre ic BE x The max. Scale we Can Stre i 127, = © oven Pes FIIVAAT : AAIVIF D7 sperma) * - 19.999 Numbey (4,0) 74449 Numben (5,2 + 794.949 Numben (42) ~---- 420 FIAWVWIA VF Numba Ce,y) ge 89999. A199 wal OS © oes © namber(w4) s---- £0,994 when PLS @ Numbem (=) -o LL + 0.00094 Stay Numben (2/7) 6 - [Link] TWr=¢ () Nomber Cue) . 0 -=- to.000q999 BS 4=y (9 nrumbey (a6, 2) (21-382 Assignment { Number (2,8) -- ~~ _u-- 4 0.0000094 8 a=6 @ Namber (va) -_ £ 99999.99 @ Ntumbor (82) ~2 Le [Link] & Neambey (5,5) +----- tea9990. © Number G8). Looe 4 © Numbo, (3.8) s-—-----~ + 0, 00000999» F-2=6 @ Number (e,3) mn £99999,.999 Obese Synrtax - DATE a ‘ two oracle Specified Date Formats amy a a) ‘ pp - ion - YY" b) “Do - Mon -Vyyy’ ok Date shoud alusays be enclosed vaithin single quotes, Example? spp mon-yy! “ig Tor - 20" (D> wp- mone vyyy’ ) S15 - AvG - 1944 © Lege Longe object Art a) ic henge Lag mules jeu Xx this is e used 4p Stwre the Characters upto uae of Size. Sy ntax — CLOB 5) Biney hawag chje ct (_BL08) : H This is used to Ste bmary numbers of = mages, Nidsos , filet etic... Opto UAB of Sizo., Syrtton = BLoB © DdiEl. Chan anal once ct Yen an Nencha) we [eames | Nerehw | a ) Tt con cxccegt chav ) Tt s also atepte che Ye —Ctens | -S. * ) Bt can steve upto 2) UW eon also Stove upty 20D. 2a. » Wastace of Memory 3) He nlo wastage of men is hq(h. Y) Syntax is Chow (size), Xu) symtar 6 Newcher (sto) @ Diff -Vanchar and Nave hen a9 SR Newco, can Stove ony \lavcboma Can ¢ ; GB “aesed tre the. "aneve “pto Yoo, - K Nlarchen 2 is a updete Norns of Nesthesn KK Syntax is Same -fy both , Thar ane Soma lis odvartage, of datatyye § Ke ote using Cumstraints , * % Suppose if we give numer datatype “to GD \e iy Mumbens ond Vt row be mplated, So inochn +o pot +0 Fepeat we AS | Oe | me aay t Neu \2Rurerig Us, wo %, was a” Comstyaints - Constraints cow the Cord ions hat ae assiqned tp particatorl Column to Nalidote the deta. Types of ComStraints » Oniqyr D Not Null Z Check © Uniques Unique is a Constvaint which iS assia wor —ned +o particule Column which Cannot eccept weprated oy Duplicate Naluos _ t NeT Nall 2 ntot null is a constraint which iS assigned to > parti calor column which Cannot be mull of which ans-Mandarsyy @ CHEE Check is a amstraint rhich is ascig lidacti = to a particulen Column fry extra va idartion. | # Check constraint ig assigned with a Condition, VE the condition ® true the value 9S ACeor tag else Yejected , Ea | check Changi Coine)= 6) Q. Chock Cale>o) aan ® Prinnany ee Prinmaony Koy is a Constraint Khich iS used to assign to a Colurnny + "edewtity a yecord uniquely Frm thse ‘table, Choractenictics of stasis ag We Can have ony ome Primes Key in atabe, UW Cannot OCcept repeated oy Duplicate values, Gt IS aH Combination of Unique and not null, © © © Te Cannot accept null. @ © Pricnary Rey “js not mandatory ut Yecemanded CASS t to have one Jn stalls . © Foreign Rey aa Sacmstimint which is vod +o establich the Connection between two 4 Characteristics of oP wg hay ) phe Cam Move “n’ nto. of ov eign “ ino table . 2) Te Can accept @ yepoated ot duplicate Values 2) Fk Can accept nal ord ret 4) Gt Xs not A Combination of unique null. 5) gt is present in loelongs, 4n Ravest table. 2 It is aleo vefawed as “Refenntial Trtigvity child table but octualy Com straint” note Orly Primary Kay can travel to anethen table vohen it gloss bavel rt becoma -forei ent yo Keg a NH Luanne | nntte | eres] eunio | SAL [pre [ere ale” an Ce ble Rioeny Sy D Ak is uged to aasiyn ty c& column td idenn 4 a vaca Laniqqualy yey the table. @ Only ene we can hove Primary iv any table © Te Cannot accept vepea ~ ted of dep WReote Valuy_ @® TH cannot accept null. © % is a p Duct © at igia Combi notion UNIAN and pet neatl, D TW carne travel te enorhey rable > * Prowment toy and Foregn Koy O To establish Canney, blvo two take © Ore Move than me woe Con have . St allows wrepeates os duplicate valusy ® ® @ SE Can ectep} neal, TEC child tl, TE WS not a Comba ati » of UNAD and nok nah. @ % coo tye) 40 Aantthen Avabl _) 2 Df Prinnomy rey Ee nique Congtaint 2)! SQL Statement < | Language’ ) Dota Defrnition Longuoge (ppt) —> create > Rename > ALRH A Truncate +3 Dre 2) Deta Manipulation Lonquese Comc) | > Insont = Updota > Delete 3 Pransaction Coren! Lanquase (Tet) > Commit = Save point > Rsblbace 4) Data, Crnrtvol hanquage (oct) D> Grant Ss Ravoks 5) Data Query Language (DP ary > Select : —) Pnjecton > select > Terns ow Darko, Query Lang woge. (var) “ye this Statement 1S Used to Wiviewe “the gy From dotaloase . K Tens cra 4 Stotaments ) Seloct 2) Projection 2) Selecten 4) Toins © Select» Wis Stectemont ts used 4d retrieve te data from datahase and disploy it, ® Projection: hie ctatemunt ie used to Vebriene, the date frm database by SAting only Column ® AN the volus in the Column will bo selected by de-foutt. ® Prejecka SeleRan » This. Stotement is used to vetvieve the dato frre detoabase toy Selo Ching bath Columns as welt as Records, O Teigs+ This statenent is used to relieve the data Frr muttiple table S tmnt tones Projector ‘ Dane This Stoterent is used to WWtrieve ae the dota From alatabase by selecting only Column x AU tho Values nM the Column will Le select by default Siyntinak’ Noorve | Expression {astas] Select */ [pietinet] Colom From Table —Name’ Ore den of erecection: ) From 2) select @ senate QO Erm Clouse Stet @ For from Clouse We Con poss arrqurnant @ the ‘joo of rem Clonse is Ao qo * “he database ond starch for “the Noble and put the toble undey sg “the execution salle name as %0 Crecurion. os) Select clouse onecutes of ton em clouse. she execution of g + d 6 For salect Clause we Can pass astenicy @) | Column name end EXPreSsion ag ay a © the Tob of seloct clause 4s to PP the ty, hich "Sunde erecection and Select the data and display. A Select ctauece “table. is “Yesponsi bla fig the vesute Q) LAlnte a Query to displey all the nawes { Studint -Freng Student table. Of he B—> seleck Snome 4 From Student + Sih | “(se ole of From We 4 Ans eatect gnome , PER From gtudant ) Pa WARTP — Name ard branch of all the * hy Pas Select Select * R from tabs T Name DEPT emp Booms Salgrode all the Tabtype — Clustentp Table Talla Tolle Tarlo a H TO desert an employee table SQL > Aric emp} Nome SQL > dese dept) Nu? Tyee EMP NO Not null Number) ENAME Varchar2 (10) varchar 2 (« 2) Tob Number Cy) Mae Tote Hive date Arbon G2) SAL Num ben (qn) comm stumbon (2) Dept mo K To dueribe an dert table tables present Dept no Net nut —- Number 6) D Naw chan 20) njarchon 2 (13) bec ¥ To Clea the Screen use Command > Clem Screen @) CL ser F In orden tp thevase size of Foges ond Ling “Se Command 5 «4 ony, nvasbon > Set Pops * tne g © winite a Query 40 display all the details “Bem the employer -taisle. RST = Select * From Emp + Neate Empno. EName 208 MGR Hirepate SAL Comm fut eu 20 1369, Smith Clore 729q 'I1-Dec80 © waerp Names of all the Employeur $i > Setect Enarre Frey EMP! | al Allan ward (BE) WA@TR Name and annual Salary given to au Me employers, kt Select Entame, Salary !> From Emp) WiAarr Employes Lp and Deperioees wo? a Select Emiene Pept no Pum EMP! Satemp and S2gry TE © waatr Enama and Howdae 7 Hae 20a pleyiass Soly Selack ENome, Sat, Salt sal*2ofte From EMP) fe) onary Ename and sal, Sl with deducta of inh fey at the Emp. Sally Solpct Enema, sol, Sel - Salt O12 from Emp’, KTace the Query “Mus explain the. press. oo’ “4 Ug Eapressieny ~ f\ Sta-lemory| btohich t weer . prem Var, TeSUIt To known ag onp * Exprewion Consists of 2 types D Operand > Operatws (+ 4, 1) & Operand Comsists of -tusp types 5 Columy Name » Citevale CDirect wales’) * L terale ee of hypo types QD Number — Gtera | >) Charactsy leteva) Dd Date Utere | oar Sol * fo Pevand Oporator Operand Colusa — name Go, 2) -tiXerale Career Values) Meter Chaetan (itexal ond data (itera Showa bem enclosed ty Within Sing Quotes, y> Ve ‘a Alias is an alternative Name Given te A Column wt An e@rpresion in the regu table a Alas nome canbe used with or wrtnot sing ~AS Keyroerel, HK Alas name Should oe Sirgle word oy A String enclosed within doute Quotes, Tost = » WAATD SAL as sarory , Hivepate a8 Drie of Towing ae Select Sol as Satory , Wreddte as “Date of ising From amp @ WaerD Nome, Sal of the exnpleyse atlong with “heiv Annuol Salary . fete Colct enane as Nome, Sal os ® cal of ahs empbe sol te as % Annual salory . Fron Emp |, @ WaAeTD Ename and ‘Toh -for al Aha employer Wah Aeiy half tex Solo. WS — Saleck Enama, Tob, sete a * Walt term sake Fer on! . a 4 ro @® waew an ke details of the employe, “y with @unmual beng of don, AWS Select Erare, Empno,Folb Map, Hoedate , Sal kiasrens = . ie Pept no Af “annual bonus comm , , e& select cates 2 Fri, emp: aa ® (Anan Alamo, Salary and Salany with OHiteog Joy ans : Solssits leet Frome as Abme ale as Salary, Same = OS ee toy." Even tmp! | WIRED Name and Salary Ans> toith dedue-ten OF ay, Select name! eS Nome * deduction acy.” ind © utaarn Nome and Salary vith Morty Hie of 9 + gynthl a Select Enome as Name , Sal+6o ag ie so Frem emp ' a » Sole Bako. Ge Puce @) WAGTD Alama lait arin) kell of toy, — Salen old ansi- Select Enome as Nlomne, Sola fo = my WH dedicty ~ deduction wy” tam emp, Note> Dent Provide alias fry Column name | don't ask and shed be prvidtd fw the | eAREON. compe |@ waar total Salary given to cach emnpleyte Cealsconey) POs Sole Gone = Select — Salamterrm “total calary” v emp Se G@) WRETD details of all “the employers along with annual Salary Ars seluct Eragme, Enawe,, Tob, MGR Hiredate , salts as Annual Sales "comm, pert no(A) Select Emp.®, Saba ta Fam emp! From emp ', (© WABTD Namo ard designation along with (60 Ponaly iq ansy Select Enana, Tob, Sal -ryp “Yerenal ty” a tOeEER SS peg Select emp. , Sal ¥1 From @emP | Past v Ox Select Emp. From emp) AD a Sols 200 “Annual Bonus” ctinet INET *= To temove vepented valuss of duslica ~ted values th vesult table we use distinct Clause > for distinct clause we can pass Column Name OF an expremim as an eorgaarnarst, 1 > Dictinct Clause ¢hould be used as -the fiyst argument in “the select Clause > we Can poss muti ple Columns Foy disney ¢ > Tt vemoveaa +the Combinaion i dbp li cate valuy fym all the Glumns, : WABIP Went fon Bay Sthident table Student fable SID | SName | Branch | Pen | ee 70 , A 2 & es ee 3 < Te a 4 DP cs xo s € Ec 40 & e me xe Avs- za Select distnet Pen From student ) ie ofp of select alg oe PER =~ 35 Eee 80 os (Ses t\ 8D (je %o ex @ waato diffoat branch and Pe , table. | ns s' getact distinct branch. Pers | fom Student ) Bonk P21 a ee eI) & es és a ¢@o es io ee 70 ue er , # TF branch is came it wont wemeve Aven 4 We wi also check vith Por Cen toge . SF both oe matched then only Te remo, 7) INAETD deffercat doptro frm emp table ar Select dictiact alapt no G Bron, emp | @ warp differnt Salory fom emp tole ae Select diskct sd Exon emp © wAErr ditfownt designations fren amp th ays? Select distinct gob wm zmp ' 4 @ WAATP Al) the crtails af emp’ along (4°) abductiom qn Sal. mss Select emp. ® , Salt cadtorty ‘as deduccton a Prem emp! @ yonatp dffeunt desiqnetion and sal From emp table} ans Gloct digtner ‘oe, ak “ from emp! & JOPATD SAL as Solory and quo os doslenoction along voy annual a Wr Selact Cataw exrSoleyy , Soo Dalynartion, aber “Annual Z salary” re omy | () WAQTD ENome TO BS Splocd — tname = emp ble. 2 From emp | AGTD Emam —frren emp table HF they ane voy = wing’ in pept 22: Ans Select Ename # p Fron emp where Deptinn =20} nvote pho Clause ic used -to filten “The. vecoy -de. | _— Ss i & wWAATD amly dif fount Sabres qiven “ty employers, ane Select distinct Lal = Fam emp! '® WAEID the Aiffount designations that ane Petey in emp +able. BMT eolect distinck Job Frm emp! WAGTD diffount dapt ro a well ac Selamieuthal\ F ‘A t Pe Present in the tbe. Ans Select distinct dept no, Sal @ Primo emp ee) arte 5 WAETD all ~the details of the - : employe ang With his annual alae a Select omp,®) alin * Annual salony ” Pesen emp G WAATP all the details of ampoyee Along toys AKe in the Salamy | arse solet emp F Sal+[Link] “Hire Za FM emp} | Selects im & yn * Cla, L i x fitter the veces whose Caster this is used to Fitton Note > % For whore clause we can pass filten Condition as On axqament . Se Whene Clause enccutes Row by Rows, ow H whore Clause atten the encciction of frm clause K we con pacs multiple condition for Whew clause nei logical operecters. Syotax> Seloct * /Cpctinct] Colwm-name! Expremion LAvas] Fyem Table Nome ohare |, Dvdr of enecection ) Free 2) whe 3d Select 6) WA ETD Ename -Frum emp table ff They are WovkKing vn Dept 20, on st Select Ename Ere amp Whoe dale =201 Emp ofp ef Fromm Rome True a0 F Qe =20-7 lo 220 & Bo S206 ro So0'T @ paard Sal of Emp whose name is Smith 2, pms Select Sal a From 2mP Sanne: whore Ename = sate ~ fa) WARTD The annual Salam of the eenploy2e Shos Name iS smith ans Select Sakkia ° ‘Anmua | salary” Fusm emp Where erame => SraiTHt’ © oTD name of the employees working oS clerk. fans Select ename = From, amp tohone Tob = ‘crepe’ + © WAQTD Salary of the employer who are eorking oS salesman WS Select Sal fwm emp whew Job = “saessapnt = (©) mAatD detaile of the emp coho Soh then 200d ss see eee” cotoct + Fomm emp : Whow cal > 2000) WAETD name and hivedate if thoy awe hired befire 19e 7. So Select ename , hiveoate ‘ em’ Fy P é or csant eI 4 whare hivedate < ¢qeer 2a) +g PEET BP - >) wagip Details of the enrployers working ms . calsct Des: From emp vdhere Tol = S MANAGER 1 (®) WKAGTD Name and Salar Gren —to an errploge via empleyee earns a Commission Of pers l4eD. ee Seloct enome , sal Fron emp Where comm = (od! \ @) waarv details of Greftoyen hoving Commision Move -than salary ‘ ANS select # Exon enp who Coun = * SAL” t r © WAGTD empro Of employes hived — beurre the Ws 83. AS Seloct empno * Fram erp Whore hivedate < “or-Tani- 877» 2fA)> yopec- | whore qo =~ ANALYST) ° (FA) WAEID Details of eines Hen Sg anova. Ateg i BODO UPL pan month, ons ‘ van aa Ger opesstecs > a - O/Aritamarte opermturs (4,-4.1) OSLO Operators (= ) i) (@/ Rolationa| Openectova (<> 2=,>5) O Logica operators Cano, of /NST) © concasdation OF" Cu) (5 Specket opraturs Cm, Nor tn, Between Net betoten Like ,NOT Lika is, ic not) opercchsrs Care PY, Exch , Not entsts) @ sean toga © pemertrrs A\od Opecohry Murti plication Binomy aay Yeturns sue tt oth tha A And “Dpeno conditime ore “true. A find yperotur should Oludea4s =—1 cmditions, ke used betwee OR Operactor” Binamy Addi ton * OR Cperatuy retuxns true if ae me of tha omdition is Satictied (+rue), HK OR ofevatsy shoutd Aucans Conditions. a be used between Not peat wy aah dt iS used nagation ‘lp Rogutt F T 71 eo QO wAATP EName H the emp is wortg in Deptt 20 ard garning cal move. then (50D Solect ename- \ Even emp vohone dap 20 AND Sol = igoD' ® waatp Enano if an emp is Working as Soles enon oF earning mon than (Sv. ii i Sect name Pum emp Who Tole = “Saleamon' oF Sot >10D) @ ward Crowe eAcept sho vis sworeing in Depnov0 sfc Select enome SO Fem ene vohine SLptne = 20! Logical epuetinrs Rs D vonerp potaile of he employees wortirg a: clave and fowning ley than 1foD Ang Seloct ¥* _— Pom emp y Whore Joba “CLERE AND Sol Blsop/ >) WAOTD Name and hivedate of the employes, wenetcd ac Manager tq “Pee dept 20 F Pwr seloct enome, hivedate | Fearn emp | Whee Tob = ‘manneer’ anp Depts 3.01 | 3) wnaTD Detaile of te emp along Lith annual | Salary of they working 16 Dept zo ac calesmg anh - theiy annual Seclomy Has WD le Spractey thon (UY eoo, a So * “ a Ags Select aR Eme.* sal% i “Annual Salany From emp : Where Dep 30 AND Job ="Sateymes! any souk . woAGTD all the details of the emp toov —King Iw dept 20 or as Analyt P Ceteck * Frm emp thaw dept = 320 oR Job = ANALYST!) wWAGTD Names and aloptno , Tob of empe weer as Cleve, tn dept 10 of 20. - Select ename, deptno oe 30b if Fur emp tonone Tob = “clare! And (depo or dopine=so) WoASTD Nome OF —the employees whos Salary is ley than Woo ard their designation is clewh? prs Select tname Exom emp < , , Wher sol =< 00 and @Yob= cLERE 7 WAGED Name and sal, Annual sal and opt no. deptnd is 90 caxning Enazds wood. ‘ 4 r Select oname, cal, Saleen “Annual Salary, daptno From emp more than leo and annual slang Wo dapt qo=20 and Sal> ito and Salary > p99, (B weary empno and eines Of the employees os “anager in apt a6 RovK soe Select empno, name Fron emp Whoo . ‘ TOb = “MANAGER and deptno = do! & ABTD retails of employsy icorking in Dept og oO 20. " ¢ & $e eloct From, emp tohere dept no = 20 or chptno = 30! ® PET Detait o¢ trnfloyers vooreing og ara fo ckept go. plas Select + Prec erop whoa Tok = “ANALYST” and doptno = 42) & AAT Detaile of employee rooking a gut ; with Salomy of P20 Yeo. sr Colect # P Fr amp where “To = “PRESIDENT! and sal = ovo! olp + No wwe selected ) wacitp drtails of employeu toeveing as, Clase UY ¢ fm or Manager {on dapt 10. © wloct * Frm emp ws = whos (% = ‘clove’ oy Job= Manager and doco: (BF wat Names ef employeu toasting ty dept 1,22, 30,40 Aysr Seloet ename a Fur np ot or : une deptno= 10 aa doptro=20 owed dopt no=30 NY) WOABIP Details of gzmployesd vith Empno 7902 1629 ans Colect # = Fremont ot ei pho empno = 1702 axed empno = 71639 ) Spe ctal Operators Tn Opaxetyy walued operate 4, || "In operatuy Ts a mutt ot Ruy Which we Can pass tt ple Wales accept multiple Values VL, Teh operat Can at RUS. *& In operarur votumes truce, UH ony ona o& + Condition Ts Satisfied. % Tn operatur allows the value present at Ms 40 be Compoed With all the values pretect ak RH ; . Syntan Column name) enpresi 7% un (428 tod iN (£0 ,Aod 1S,2e0) Kar (00 = DF {eo = WoT “True 160 SSD & {Ow = 26d F . ©) WeETD at’) Ho, 42 " A 2 Select *® Frys 2emp whore Deptno iw ( ©,20,50 NO, SO 60, 70) @ WAEID Ename and Job of erop's Who ano wave @ Hwy 4s Manogen oY Salesman . aes Select ename , Too From emp vere Tob INC Manager’, Ssatearnan) Not 19 Not gat operat Ts cdmilan te INO — . —tvy but se raqectS the value \nctead of satactng Syntax + Column —name | eaprenivn Nov IN (vi p2VO! Ear () WAATD Emps name ecluding “the emy's Loorking jn dopt to ov 20. Seletk enone in emnP r paper? Not tn (10, © . ‘ tp NoT IN (1928) (o cleT True wT. Pale to 220 F | r where Daptno « ((6,20,80 Mo, 60 jh, 79) @ WAGID Ename and job o-f erop's Who aro core. @ —wWg 4s Monogen oY Salesman . Select enorme , Tho From emp whan Job INC Monogr pesr IZ " soles man): to sdmilan * LN OP e1a \inctoad of Setocting H, 2,0)! Nott 1 Net ant operat py bot UH eraqect Ss the value Syntax + Column —nome [expronicn Not IN) (vu, be 3: v the em! Ea: () WAATD' Emps Name excluding rs wor King, jn dapt. © ov 20, pase Seletk enome Ens emP © wero Emp'c Nome tf they oe LEO" ing dept ao, 30 and Hived After 14o. fns> Select ename ee 10 edate > d Hix > Bhp, NOT IN an Ke Whore Ce ey @ wear petate of ample HF they awe Looreeiy aS president , Manager , Salumen Ans ZA Soloct *# Frum emp re Wher Job id (President, MANAGER, SA ®) WAETD Names and deptno, Iolo of emp) Lasrki rg as Cleve tn dept 10 or 20, Ans Select # oname , doptno , Jos From ena Where 68 Tob IW ‘eeee? Ano (Peptrenborb, WAATD dota ls et employees working as clvt see Monger tn ae, where Tob IW (cLERK, MANAGER) AnD Dept ne Oy a weaatD Name if . © ° s of employes wooreing ir) Adept to, 20, 30 ,4o Resi select nome r Frm emp whore deptnro IN (to, 20,30, 40) 3 ( WAATD Pertails of employees with empto 7902, 4139 ans'r Selock * -_ Pam 2p whan empno (1902,7889) ) 4 as mana * ’ (9 WoAQTD details of emnpleyees worring “7 of Saluuman oy clot . esr Select # Za Fam emp toe { whine Wb in Co Manogp , Salesman, of amployey Hintd after 1 one w ) clevie s (5 syepuaiene pAaTD fname , Sol emmy « one. earning lbetween 12480 and. acsv Solo stloct® erame prs { Feaeen Sot x-1a50 and Sal < 3600" whore tt betwee (12-05-2008) | a) ako. Sal betwen (26D and S602 Bet ween Ope wochry —_- = a 6 Between operator when euen W) is used hove Yoryes. 6 Betweur operat works including athe “vara tot , © “the Yonge cannot be 2 deny Sym@™ + Coleman rare |kxprasion Belocer lower-ronge ard Feqhen—¥* 5 . ‘ t rome anol sol and Comom tf emp @® wat, & n the Range cf 200 XD 1000 Carning Come i e Soloct erame, Sol , Comm Net between ¢ t tolay -to bet &k Not between KS O24 °F ets the \ue Instead * but TA ects No INSteo TA ye} of Keke, v4 7 Syo tan > Co\uren. name! Opresin Not lectern lousen-vange ond, fh, my, @ KAGTO Emp's Nava ard Sol if emps arg en, Sol lew thaw 120 and emis who aw cari Sol mere then Bon. ase Select enome, Sas From emp whore So) Not betwan 12.00 and 3qyp' | @ WASTP Erame , Come f they Ba Come lwythen Sep and omp's | ear ning Comen | Arbre thaiy ep ers Stlact enone, Comm emp gr x Yr en @ Whos Te TeNat betwee cop and 700! —_—_ Bb waet yet and be-fere 1984 Dp Erame, hivedate W wena hived after Ans Select ename, Wiredote Be For emP ' < OLN 1982 whore hivedote betwen 2 and ~a1- DEC-B6* %®) wAsTID ‘Erame who is net fonni og Commission, rs Seloct mame = Erm ep vohane comm is Na) Te Opeotry gs operator is used only “Te eee Compare vt ‘null: Syrtpa: — Coluran- rame ] exprenin is Nulls 0) WOAGTD ename ef Any oe not coring “ny Commission, a Selec 2name a From eh Comm {s Null + . | @ woe ename who loose ger ony uy, ) ae Select oname From emp Whene sal is Null ‘ Ts pot . 2 Operectuy - Ts oot operator is Serilion ‘ +0 Is Operotuy but Ht rejects the value Insta of Saleetng ft, Synte: ee [Link]|enpwision is mot mulls | Betuxong, Not bet voeen Assignment Questions ©) wre encom, Wvadate tcho were Aired a tate qn se ys aTAAG Seber fname | Hivedate Fron, 8. ol We bein ‘ol cynt-ar ord Sg iepec-&) \Z Ms @ WwAaTD ename, me hiveclate 2x Copt who w hived Ahe year 1980. gn pes Golect erome , WYevadate ve Exum &P Vohene Hiredate not between ‘oj gan-£0 and cgi pec %O! ome and hivedate tf ore cons BESSY: @) waar Dp Fe d Hive in the year —King 5 pept 10 OY 20 @n (Qzl. brs celect ename, hrvedate From emP Where aeptne io wt ev) ord eke >on TAN (10,26) ord bivedote between pec-&| uo eyedate whe wee hived f) pOAGTD Ename , H ag vndiiogin: wae wait befere 19¢F and voor e iva apy adi- monoge) Seloct opm Lyk cr salesman, Aes arate ee From en? where Giada yr ‘oa e'and = ana sod Cuserege Manoge ) 2 ade "-s mite | - My rey df baxvi fon Vs ret & 1S ajot 3 (1) wretr Eaame and Comm of tmp’c they ane tarning comm ANS Select fs enams | Comm From emp | Wheno comm te Mot null) © WAGTD dotaile of amps who are not "etext =tb ay Menogen Cmee) % Select Fyrom emp Whene MGR iS rast s | @ WASTD Ename and hivederte te the emp is bing Ia Ane yon lava. all . q. 1 oD Faas * Avs: Select ename > hivedate_ uns + Fron amp Wha peer bertoatn So “Tan - Lea and RE 1 haborird aye Lpec- #2"! , ® naan ante and hivedate tf the emp's a hi-ved — ue (981, ail © WAGTD empno of emp's vohose 2mpho at 1499 and ends at TIES, fnsr Salect empno Seve. From emp Whew empno between 1449 And gy, brna BSR Sipetin y. Lek pest TS used | | wen we need to ~ Merch the Rodtern | Syotox Coleunn-rone [expresion Lee ~perttern to ema, + To achioe the portern matching We. USA Speci al choractens such af ; ; om ' > ror D Pereemtile (xz) + at can carer ang. charac Any [Link] time oy 1 Character, | 2) Undensce (2) — de con “accept. oy Chae —tan bet only once. | @ wAATD enome only — A nn, ‘ennp's having oi [Link] ~ A’ a ‘ nat Cole ct from erp daw Qname Une * AAT 3 | @ LOAATD — Crome only tf the -eenp'c hoving cho — yacken SA’ in Aho first plae of their nome AOS Seteck name Byrom emp Ants colock enone = vor ENP a . ename Veo Swear! who

You might also like