@O REDMI NOTE 9 CO AIQUAD CAMERAa en Key can tove a null vale 1 3 an R NOTES Fore 1g d = 9 Ls CREATE TARE Department ( fee ieee 2 tx) Dept_LD NUMBER NET Ay y ; > {v 7 GU Depattment rane ARCA (26) ae NOT NULL, ] ove, eG , FD NUMBER noe Nl, E_Nlame VARCIHPR Cp) gi ae DEP_ 2D Number, F Cec ee CREATE TABLE Empl FeCRELGN kE Y(DEP_zD) REFERENCES Cy “CDEp ery) ° DB — by Frach a j a, way ‘LATA Nee sully psec EL Us ie Guan Cares) —oPhed Aergte character dete 2 suble Long tt character PAR CMe ee ee: VARCHAR ( Size ) a 7 NCI 8 ERG Di as) ane gee Sear Nebeeeri t Zk oF eC iE ion b> peel 2 ears iia oo , the nS Aa fe é C+ 2 J Lend ee a ° ued © Jilustratien 3 z/P Stored i TAS6IZ% «V7 NUMBER F4c% (2309 NUMBER (4) FF 4SbI7 = Pownded oft | NUCCRER (9, 2) eeieeram 22204 || a NU aie Ga 74-212 3.4 i | NUM RE 7) erm x 72) geen Omoft RELATIONAL LANGUAGE > Created bi Edgar Codd - > Procedural In Nature - elma operations on relation tp retrfeve Mani fuple mm @ relation based Bn 4et algebra. Dwe must explicret Provide & K£equence operate th to ibaa a dexired result. 7 °b , rs > Each operaction takes Ent oY More mbat and Sutputa a New relation- OPERATORS: 1). Select(~) 2). Projection(X) 3). Union (U) 4). Difference (=) 5). Interaction (nN) 6). Cartesian Product (xX) 7)- Totn (ot) ipulate relation ag os RELATIONAL ALGEBRA OPERATIONS EXTENDED/ DERIVED OPERATIONS BASIC OPERATIONS ° > Joly tie UNARY . efNary me OPERATION OPERATION > Intersection (a ) > Selection(=) Union (U) > Division C+) fection (X, inus Set (-) Us ole ea 2 » a opererce > Rereme (4) damterReetsonAANS conf Sire len AA’ pram, dept nam, story ) name = CFhy ce? t hy is BE WHERE clause @. Projection (n, Lett bbe tees ¥) Es orien pea a Bustrvte toy 3 SElecr zp, eae SavaRy FROM rig TRUeroR peer Lats & en [note : Duplicate, Gry ECP ohan, ,, we net allo se J “eS tote ue. oe ree Of rtw reloction w equal te : °E a we inkattrbute Lety iba: D-Renens (-P) _ Si aes eee explelty vena relatibrye at ibutes oy both . Se Soto en iB tg eae ond ea td bop STP ts aes a ae aan ee are AagtaQan|. R- velapen ay 4 17 cee wy ¢@ , SCA), Aa --- A) CR) 7 ee | j > ane Plat %,, tL. coerny ; tiple ey altr*butes og Cited.) tactng olf Sa eee tS

Union Compadt biDfty Sp Pateenity (Patter, corla) Mate ( se Peter at child | Mapa pepemeen |S Sh bet 5| Juices ecaaC Pedernit,) () CMatomity ) ° Pastnt, Chyly ) Z Op, YZ Arent, chi ty ) ae LZ FOR SET OPERATIONS A,¥ a=) ° eeLONS >Schema should be Unfon Combatfbie - Rand S must have gchema wrth ‘dertical set atbstbutes and tht domain for each attrtbute must be ple >The columns Rand g MUSE be ordered 40 th 7 St eyes SHR OR Aame yer both. ee a > When a relation Aas sane number \ a " athTbhutes but worth different names for that Santee ee . UsE TeERaNnL operection . ie >SET DIFFERENCE (—) : oo ee ee e-g- CR-S) S(A,8) > SELECT * FRom R MINUS SELECT * FROM § eg. R(A,8, 6) SCD,e,F) Re Peas, gD)a hare esac ae > Selectiz - aie. stan CCourataideyiectil man ceter, building , »™M—No , timeslot) all Conger taught tr Odd 20LF Aerester ot fh Even acto tenthier. TKeoume.id (“semester =toaa?? (Sel ection) ) A ean = 2017 Teowuc id (“eenuster= “Even? (Selection) ) A yean=2019 IN SQL? select couueld From selection nohou Semester = “odd ? AND Year = 20187 MINUS SELECT cowed From selection , WHERE Yeast = 2 : Question Find the slot of all the courses es Prboth ~ odd avit and even tot@ Senusters~ | 019 AND Seauster = “Even” (2 Remmeta ( enater ated. (Selection) aan A gone coun id ( Tbenuster= Keven? A (Selection) 7 oa 2019 1 IN SQL: Select court From «election WHERE yean=2017 and semister = “Odd ?? INTERSECTION ‘ Select cowmusefd From “cliction fe | and Sowster = “Bey?” Kiz m i pee ee F 25/09/20? cavsina 1° oy covigo"" 1 { Saeed 2 R28 yee = My R= (Aras fy, Am, By, bo. By) ; Ge eee ty | Pina of O28 ke ie ) GAR oA) 7 5RCA 46, c) $e) | | Cc, Ti = R/g, | S2Ce) (eee) A= 8 ‘ eee ; S2e) ae a} e ; < 3 = R/s, Cg - oevs 6 tion R 6 S— Taken ath butes value into considera ° TENNER Tot: based On RE Meactthes tuple from both the tables ‘ the fol predicate And xeturn matched rew Perey RS Black Product We | Produed NATURAL, TolN t6 dssociytive RragMT =Roals mz) Maven ( 5 (@xs) ) implied cendttfen T depantment = S+ dear treat Bru Mow Mow i 2 AMATIRAL TOWN, 2s conmurative RAS =Spap‘ at JOINS R(aacd) S(&DE) TA RB, CRD 4 ie. cin (rxs)) RAD =o. Y Cue) THETA Joins Studentsa (S-id ,f&name . L_name,d Members ( C_id ae 4) = eae &. Frnd ea toho cow eee of donee ?élusb « _td =m_id e Orel er TaN. & Ovbes Joln \ Z Lote outer Toin Id RWS Ce Right. Ourer Ion pC R WS? Fuld QUECY oe ip ee Ss DC y __ @& Departmot| Head Y Hy | Zz a ci NI % = Enpley ec] parimed Head A is NUT + |r | L Nuce SELECT From R [erp ecqurpreee] oie TOSNERG ie Er ee oa ae |Ectended Retatroal Algebra Pp ercationy 7 C Poyection) extended #0 pofom Osu Hmnp Operation € BUN ane Apt—nam , salary $12 a, randety sale Cinsteucéo ) by = Can (Salary ) Crmstou ctor) Gate T- dicirce Cn) ( Sa en Cterchus)) SOME Sehary ) CiraencarS? ie 8G eh aa) , ) plo ’ ae yi . ) &. Customer ( c.1 d >» Crame, rating ) , y ) ’ , ) dept—name is aes type? Cvde ' D- Fad posal afc nb okite ly bore Srdiveg 11) >and yom Cetra, Be Ce a eee Lop-tep OR AASB: a liye Za Cxstoncoy sho tave erat reel oa top EIv, ~RTev rion Cavcus > Relactional Calculus fs a declarative Lon ye ace ces ae Algebra Ps procedural Language > We Specify what to retrieve and ushat not *o retiveve. > Fire order Predicate Calculus ae > Express he power oF both % Kdentiral > Wwe ont. gots “bo oberate on tuple DP Vesvable sa tuble Pra velateen (4) 4 Uae Setof Tuple predicate that “zotife Peer > Taples votre department =CSe ef Empl Lt/* E Employee A €[ dept] = “cse st (eg: J “dept ="*Cce? CEmployec)) g » Last _yowe At nae en blegee ushose af ee Aalosuy > 40000 a 2 ii ool “Ey ee) Ne ee ee ee = ——ipa: ee Vedndken Coc ata! | ao ‘ > x Students Cs.idy ‘eo haweso ee nad, abe ( eget > } 7 Somes co th auteet | Cua b g. Rs eee stores) | = (sridete Oy ea § Clube Da ges mtd 1 | ’ an 2 Memner } } } ‘Jie pe Gis a Gwe Usk Ste sd) ests! does) mem bead “4 a4 Clb . ate cape +e ae afta by = he ( atuden SA Ce mack A a tie! x brs : Can Car, epee ) ; gow ( foet , Ror. pared 7 Car Coy Man PED Kent pee ‘a @O REDMI NOTE 9Students ¢ Sha; Jonas \-nanre ¢ dept ; capa) GnsolicinGe cd | etc od) ee Ust Bd studerts eunsitlad (05 50d] ( Studenh AY. = cea] a Sb Studewd id = Corl! Maw, Lo nore, MeN cid =" lees Ol a L Levech Employes D epartocent Mes FROM =! paTusal gon GD soe Tein (re so Te compare rE Fe Y Ep ine ¢ Eid eRe ea ) \oo\ A NULL a & \od) ong S eo {Se b (Os E Nisa) ) 622 is of the eumplaget aud saa BAP Gs Ges (eorbleres) fn C Employ)SELECT From EMpioyee E (NNER jyord EMPLOYEE 4 oN € mg rid oM eth + Outer Jetn . There ano 8 types Yo Suto joie lad outer fern 0G avs BO) Right outer fein R PES CSBed op _——— fl: coeds Cc unter (A oviclen) ) ) —— } Aa leer pM onder )) | geet : ol ovder) —- alahteh( Guntome¥ » 2 \ Ate ah ( al Ayhe eNetonttoh"| [sok ° Bounces! varable vain sil Be Vase ety ( CC reat stem fer Ceo) = sf ieahany 4 S(seels 4 cose) b oc ir Relosioned Calanen A Frame, | Uroien, alan, Dace oe Mae nawe auc Hlee 9 gins ng Beals Ulett Aor Spore Bployee C cutee 1 1 peparmmer’ Lhve Bo gran: eh gE bles “ plus C El bse) ae peed ses Empltye Ct ee “(sttt(tan 2 = ate—9. ( Cake Ay pan > ellceedam a Cotdind = ies u eA 5 OF, cox ) geen fe ln Voy P Dem lont te R= ( Em ine (Sober tte = Debat -£hs) A Ash. mowe : 1 1 aids ete ) pupabe «lanl :tee aes A Relatenal Cate Clleulus Oe CD Re)? Deomatn, blea— Range over single value fom WD rain, ) ene betes? i< x) Xe, As Xt, ts>] Pex 10 ‘ > X4 re) ee, d 2. Ly Instructor C#D , mame, dept_name, Belang yy oe E Frttructors A € > covey] ‘5 866 Klay eae le a Department ( de wg Name, beurldin 5 Crag) tet) °F | feleaee rice nod,g>€ eae AW b,a(€ ferchus Add stn, yO Peete ctee- ay | 0 ae & oie ’a 5 eS oe) Bare Plas > > 5 = % teats ie Stetien A 2 ="ogy>? a = 2-43) sl becrz [Poie/eee? J AC squ- structured Query Language 5 ee ee t;[ANeI £80 standard » Sat Dost Ty pes) | > char(n) g DATE: YYYY-MM-DD ? Varchay(n) 2? TIME: Hirmisss 2 = ine TS PATETIME? YVYy—miM-DD tent ¢s > small ine TIMESTAMP: Seconda Petsed xince | ZNumene(p,4) >? Float(n) Ly (S8u Literals 2 - Literal 16 an o Ore Nurasue Boolean value os represens? ¢ > S00, AaHay G 3p Ses. 9 Character Btan or’ db dee 4 an dent Pe > | Giovrny |) ‘tor? —choractey Uiterak pames 4 d= Numeric biter > ($9r Command, ODt Date Depa tte ae os ae “qe b deleSp ie ee ee ae Va ok Tages Cigeere Vaiveroene P euthortty from a) Uuger> @TCL— Transaction, Control! lanacea. Ls Manage. dif ferent SiN) Occurin’ within database ‘DATA on Language 3 ene Tey ae databoue ®- SU cpemtors : Arithmetic operator > +5, x FP Compasixen ope rators—> oe ee oe 5G? Spemtord SAN ee ene Steere Operator, —» nee 9 Trterxcecton, D pherence DISELECT Curae FROM Customer » ETEM , ORDER WHERE CUSTOMER. CED =ORDER.CID AND TTEM.ID = ORDERSID AND DYPE = laptop? ©. pene ey CNAME M CUSTOMER ee INNER Torn ORDER ON (CUSTOMER. CLD =ORDER- CLD ) ENNER JOIN TTEM ON ( LTEM.IID =ORDER.(d ) WHERE Type=* Laptop”? @ + Alfters SCP eCrm as CNAME FROM customer o TENNER SOIN paneR ON(C-crp = 00rd) NNER TOLN rrem ON ( Tr. UD = O+ltD) WHERE TYPE = & Laptop?) ~~yy 03/10/2022 ‘View? NX n tables Ly \Prtual relation derived from pene OY more ‘ called base tabled led L, Views Logically represents subsets of ter Pav Ola or more Aabless . : BS Lo pames ctores the definttion of views « SS > create [or Replace] view view-namv AS Subqu ere D> CREATE vrEW EMPS_vu8o AS SELEcT employ ee-id 4 fost narne, salary FRom Employees WHERE Depaxtment_id = 80 SELECT emps_vwBo > Types 0 Views nee OF Nk aa data Prom ote table cai ®- Complex view Ceutaind n0 funciona er groups of data : Can perform OML ie Uso: Aimble views insert, delete, update BEPCGESIILILSL © £4" CREATE OR REPLACE VIEW sal_wu5o (*2, NAME, ANNUAL SALARY ) 4S Setecr erployee_ fd, Frratnane |] ¢ "|| Hast nome, Salary *19 FROM EMPLOYEE WHERE dep arrtrant—_d =50 SELECT # FRoM Sel_vuSo DESCRIBE Sak_vuso ® -~-es99 ead Se: AT Views Bae nn ae - Compex, View ba py ety ‘eal dade, LDertres data from mary tables Meee ena Pometrene os groups Of cake Ly Does not alkvaye allow. Dart Operation «te Hy bhren view en Ls eg: @ CREATE OR REPLACE VIEW dept. sum AS SELECT dedepartment_namne, MIN (e. sale FROM employees € Sale MAK (Chala) Movecey, Torn depordim emt_ id =de departments 1) d GRoup BY _d-depoaortment name 3 Advantages OF Views > Vitws restricts access to tte data > Views makes complex Quuscres Casier > Views present different views Of Some data MATER AUZED View ie L> Views tortie phy sic data ‘qeturned by i ee ae i Ls Whe 10s freqpectly access, thre data ) @ for cad. vu Ls tf relations, used in, qytteruy cou sf dated, materialized vrews result WR Out of- ; date. Me. rez in base tables cou not Cte meet. veflected Mn modesto liKed Vviewsr vt ‘ DICREATE MATERTALSZED VrEW Vre®-nome AS (Subyury° ere LsTo ob tte Changea In the mater salized ES can refresh te mote ols eq vireo periodically ° REFRESH MATERIALIZED VIEW view _name Notes Views cet rarely used ' ec teres Vidndex Ps a Structure Con a disk) associated with a ) table/relationship-that speed« retrial Of tHe » yews Prom tte table or view, yb Index simproves tu performance of the Quarutes \ Index use B-Tree to Atore Seanch teayy ae loca tia ) Cf the record in p KICol dotabare | (Balanced Tree Structww) LyIndex fs Adetrbase obs, ' improve performance of vere 4, aes te 2rd fatto ial creat ty seve tin ae ws sone nies Wescc » Fable 't indexes. ea ear tr 2 Dt 16 malntatned automat i DML operations ase perf vstuch fndex fs ef ned td Rerver (whi re Meee rar iar. (eee ° 4 © pee | Reig drreot & put ACCEK to WHRWA MH & Ef you do not have an tndey on tte column Cin WHERE clause), Hur a full table ACOn. OCCUNML* Ly Tndey Con BO Cr Bae Odi) fore ppied deh Ei tableSe AM Imaty WShtr a estuemn Contam. wide range vhs Column Comtahs a tas ee number of NULLS -fPrequnctly - Used toget eer in WHERE Clause Oy JOIN Cond tien + LICREATE INDEX fax name ON table_name(Colunnsnams [Becertcn, va {ill )+ Ene or more Columns ade Ls|DRop INDEX tdx_name LS | CReaTte INDEX, empcal. ON Emplo ees (Salas ds & CREATE INDEX Emp-nanre_idx ON Emplovees (firstname, Lastname) “+ [Deep EMRE enpreata] | H Auigulady tdx ls @ yer verthin another Ra Auby: ay 9 Suby) Can be Insite SELECT, FROM WHER © ; INSERT, UPDATE or DELETE SELECT fiat. Ey Fon emp narell 7 > SYNTAX: main/outer Que SELECT Select. be FROM -table WHERE salary >(see cr | WHERE Condition ) ] dnneyr CXeCmuteA OCG 2 ee malice wb query fi LUsed 9 Ree oh asta foe Sub/fyney QueryYes Or Sua Sveetes pbs Sing le “Kow Subyurtes ( Scalar Aubyuery ) Operator Can be =)>,<, <=, 5— SELECT Lastname, yob-id, Salary FRoM empl eek WHERE Selooy = ( LECT nazn(gul ) FRorn4 employees ) ° 6/10/2022 ert es ae IN » Nor IN, ANY , ALL SELECT fast_name , gala FRom EMPLOYEES WHERE Salary IN( Select cata Feom emblordex 0 WHERE ih Neve: IN = Any i a 3 NOT IN=Nor AL ae d epartment_iq Cee ee Fe ek a me CORRELATED ° Sve Queries ™ donner oer THOTE tp Column ote bute 1 thy a Outer “ew sthen Ty result depends on ty a mon /Gute¥ Query. ie é = > SELECT demp| lou ee _id ety Frat Hontectiuey depooctment_ if tl > hare , mS) From embloyees “et MO) Were aa > (Select Aver(sabary- | FRom emb lo ees e2 | WHERE Saee. 13S epartinenbtd sab) pandnner Saat Spica cee en SoC Cle oye ero le oo Nesred QusQueny > SELECT job-id, AVE (salary) FROM EMPLOYEES GRouP @yY gob_fd HAVING AvG(Csala: ») Getecy jae (MELmin_salary )) FROM Toes WHERE yos_tp IN CS8ELect yos_tp FRom JOB_HISToRy WHERE department_id BETWEEN 50 AND top Group BY ‘ToB_I) F } SELECT yoR-ID, RACES) FROM EMPLOY GES GRourp BY JoR_xID Ne AVG( saloo )< Cr output from oa aaa (.., output from nested Subyuoy ») = dD SRL CONSTRAINTS ° 5 Conthoints enforce yules at table Level ‘SNOT NULL? Ensure « thot null values cow not permitted fr the Apect Pred column no Ls UNTQUE: a column Im whith each volte 4 different for each oo, fr tte, table - L, PRIMARY KEY: It Kanruxrt to hove pene thot ae aa) yew Mn +e eerhorce simple ville, such thot Ly CHECK + Used to fo column must be within valu entered in eco iee specifi ed vera e oh “SFoRETGN KEY . 4 we can create Conatroints erther ee IEICE ticcinte aare ak table 44 Crea BO ii). After the table 1 created (Use Alter Atatemunct) - eM at e Ls crete a ns he pee che Aerve x Ot Ly We can de pine Corsham ot column Level or at table Level >| Create TABLE ( empl p ogeetd NUMBER (Cg 5 At nog VARCHAR (28 Last rrame VARCHAR Sala NUMBERS ,2), hireG ote DATE COnsTRATA+ empid Nor Nou emarlid VARCHARS (26) m er_id NUMBER (6), CONSTRAINT Emp_td pk PRIMaRy Key (em CONS TRAT pT CON STRATA CONSTRAINTa Foreicn Key ag Bo a 4p ecifren a column fn child table that Corresb onda Peete ey prt mary a; of ty pount table. b Deletin a ew fn be parent terble uxt) ive ero 4 there 11 AYOW refern’ go de na A Id. table « IEG Wee eee default behaviour Ly Te chowe default behowioun we Can creche a couthaint corth ON DELETE CASCADE ee oN DELETE SET NULL: 7/10/2022 L> ON DELETE CAS: means that if a row mr parert is deleted, orack server will search rews i'n iceman for oll matching. wews and ddbete tthim- PK Deleted [feo] OSE PELETE SET NULL meena Bae HE ce iy deltted mn partnt sable, eracle server wl Atonch reuon fn child cietelGee ren 7 valuss and Aet then +o NULL)“te Ser Orergrions WN 9QL 4° @©4sevect Pirat_name, Jtast_name , emaik y IFRoM EMPLOYEES UNION SELECT first_name , Lastname, emai h FROM CONTACTS -|SELEcT Last_name FROM CONTACTS MINUS SELECT last name FRoM EMPLoyees ORDER By Last name DML, Operations tN Sorte |) DELETE « FROM EMPLOYEES WHERE department_id =50 wwwwe » Giodecete x Feom eEmeioyees » Sldepate EmpLoyees , SET firstname =e Kr?thna” WHERE emplouee_jy =10 LJINseer Into INSTRU CID wan “4 See 3 Ey dept_id, Salary 3 Values to20, “anuy? a 10, loop.eh Th/AQL Ta 4 Procedural Lenguage extengions to $§t Loft & a block KtMeLuned forquape are en different Aectiong, ¢ Reader, declartoctio, 9 exCcuitiong and exception cece == FL/sau Block ke Te zs DECLARATION SEcTion BEGIN Exception i EXCEPTION HANDLING, a oe END; ee Mt-name vars, AR2 (25), V-t-nome varge HAR2 C45)» Firat_nome , Last. name EEN ON Canyee Ry pes FRom student ? oa WHERE Studeut—id I ee‘Dams—_ovrPur. PUT_LINE ( “Shea pene "|| v-ferame || ° l eer) EXcepTion WHEN NO DATA FOUND THEN i : DBMS_OUTPUT.PUT-LINE ("No ktudernt Cae rd). Notes aa aca PL/SQL Block ¢ PL/squ PRocEDURE: D Procedure x a nell — contarre meant to do some Apecthic task. > Procedure i MEANE OY named PL/SQL Block > TRey can be yeused and stored Qs detailer objet ‘dd. prrg usta) wean eh fy >Ttdoes not return any voller, CREATE OR REPLACE PROCEDURE Yaise_dept. sal ( d_-num IN Numeeg, iS Tpet IN NUMBER ) VPPATE EmpLo aaa YEEs = Salary + Salary #(_Tpee ) WHERE department_ = a rei END; TO execute, EXECUTE Yaixe ept_sal ( 9510) or ExecMe PARAMETER TYPES: CN > IN > OUT > INouT e PL/SQL CURSORS : > Cursor 1 an obfect reberenctn Onc ere ! Called context arwa which (prion he aoe yetreved decta from ty database Of §8L Atatement) and alte iin Py cursor 14 an obf ect which allows, + he yOUwK of er resust Kel. ( Clive se 1 4 result Set { ( ‘ PTYPES OF CURSOR: =; ©: Explictt Cursor q @ $rplicit Cuxvpor ° ° ene. Execution Life Gicle of Explicit Cursor > Declare a Cursor > Open @ Cursor ! > Fetch from a Cuno y Clon a cwoucy© EXPLICIT Cursor : Se et ' > Explicte CUursorts User debined Cursors f > They give ou more PPPGram matic Corrtrel O-DEcLARE a cursor CURSoR CUursorna ©: op EN ' OPEN Cwusor_ name a , (associated statement 24 Executed ) ®- Fetes} i mo LS Select_stat; FETCH CUotk0r_nanye INT ®. CLOSE CLOSE CLevK,0¥_nomy 0 vasable_lixe ce SET SERVEROUT PUT 9, DECLARE 4 Viname VARCHAR? C30) ,° CURSOR Cur tert 19 SELECT Pe oa -——~—27 @ rat_name From employees NREREerobipt eda mews F BEGIN OPEN Cux_test ; Hy Loop A FETCH Cur_tet INTO V_name Fs - DBMS_oUuTPUT, PUTLINE (v-rame) y EXIT WHEN cutest +) Nor Foun + END Loop, CLOSE cus_test; | j END;Je CURSOR ATTRIBUTES 3 Thy return Papormatiion aso. Cx of DMtor DDL Atatement; , > Cursor_name 7. FOOND—TRuE, if record 1% fetched Buco eng ed eter bly, > cursor_nane ZF NOT FOUND — Tove A Crone: ae ance Anat AMM rd doy 99 tehed Arccers 5 fe > cusername 7. ROWCOUNT nun, ber 0 bly betebrg from cur, Crono. > cusn01 nome S. ees OREN TRUE SP ele reais ie COBEN) cme 15 oper, esis] PROTECT. he @List Functionacirfes ; @E-R Diacran , @ USE case Diagrams FOREACH FUNCTIONALITIES @ E-R DIAGRAM TO TABLES IN DaTARACE ) © Normacization ' © ImMevemen tation : ; @ RUNNING PROTECT REVIEW © SPIRAL Bin» PRovEcT REPORT ; Xmpcicir, Cursor . > Cursor 75 Creted and manag ed bY Oracke Auer, i ( ( _ PY You do not tawe access to E- > Cursor P46 Created for all DML, SELECT Statement, that do not hove explicit cwu6er askocfocted woith -Uwm - cumsor. CONTEXT_AREA Actve SetVOR EN CRE Ft ca a ll 0 ewer ) > Ovacle d ov attributes to Lest Lhe outcome >You can access curs of SL statement ' ’ 1 Ly S@L+/ FOUND ~ Boolean attribute that evaluates to ) tut +4 mokt recent S8L Statement returns atteast p One vow: y »& SQ. Not FOUND — Boolean attribute that evaluates | to TRUE “f most recent SQL statement does not yeturn even ONE ew: 3 SQL. Rowtount— An Mn-teger value ME represents numbery of yews offected bg mat recent SQL Statements - 2 SET, SERVER OUTPUT. ON BEGIN UPDATE Emplo ees SET SALARY = SALARY +200, WHERE department fd = 20 5 TF SQLY. FOUND THEN DBMS_ourpur. Po Lene (“ ee Update for” || TO_cHAR( SQL. ROW Coun) ne I * Employee fh Dept 20%) D&M, . \ r ; IS_ OUTPUT. PUT. LINE (‘No employees in Dept 20) END rF \ END;[> SET SERVEROUTPUT ON a CREATE OR REPLACE PROCEDURE List-emp 1s v_empno emplo, ee « Employ eid +1. TYPE A , v-Pnamt emplo ceb, firstname J. TYPE ° v_dname Emplogees + Lastname +), Type * CURSOR emp_cuy IS ; SELECT €rmployee_id , Fvst—name, Jast_nany | FROM employees ORDER By employeeid 3 BEGIN OPEN emp_cu 5 | DEMS ouTeT. PUT_LTNE(EMPND — ENAME?) 5 \ DBMS— OUTPUT. PUT_LENE(* ae ed ( LooP FETCH emp-cwt INTO Wempno, vfram, Vtrany » EXIT WHEN €mp.cu71/. NOTFOUND ? i) t) DBME_OUTPUT. PUT_LINE (v_empno ||‘ \v-fram | ( [I ?]f vbrane) 5 END LooP ‘ f D@MS_oUTPUT PU T_LENE( TO.CHAR (emp. curr Reto) I “number ef enployees preeested”) ; | CLOSE Emp-Cut ¢ 2 4 EXECUTION: | aeanele EXECUTE List_emp; { or EXEC Lipt_emps { 2 | (= ve PL/SQL TRIGGER ¢ ee j > Trigger fs a name, PL/ S8t untt that ts stored fy d -Hn database. p >You cannot explicithy >d4tis automatically Prvol ane weaponse “ko Occurmnte [ypes OF IriGGeRs INSTEAD OF Trig ger On Views, (i-esnon- eazy Views) U fnvoke TE+ wed or executed oy databaxe of Aorme event Comgges fs 4 fired). START- DOWN se CA model. Poe. Event — Condition Action > Triggers ave baked on F ~~ rye wee wPweUTweHR TWO VowPP HEADER CREATE /OR REPLACE / TRIGGER trfgername 7 f BeroRe /APTERT Egg evere ON table namny [ror EACH Row J [Fottows IREcEDEs and “Trigger J [ ENABLE /orsneLe) [WHEN ConDI tron ] Body DECLARE declaration statements BEGIN exeeution Atatements ! EXCEPTION \ exception ah Btatement ; END; { ( D CREATE OR REPLACE TRIGGER dept—check time ' BEFORE INSERT OR OPDATE OR DELETE ON DEPARTMENTS, | BEGIN Se \ TF TO_NUMBER (TO-CHAR (SYSDATE) 9° HH24? ) ( NOTRETWEEN 9 AND 16 THEN ( raise — opp Ucation_errmr C 20000, “pe operation ( not allowed new); ' END 1Fy ! END; ‘ { DELETE FROM DEPARTMENTS WHERE depantmad ro 107)When We ULE eoe5 Level triggers 2 OLD; Column_na PA NEW # cobimm name mere | CREATE OR REPLICATE dié p_sal_che BEPORE DELETE on FoR EAcu row ) DECLARE Selb. diff NUMBER( ©, 2) y BEGIN Self ait bz SiMe tall =i OLD Seek 5 ‘ D&Ms-ourpur: pur_ LINE ( Old Salary = /) * OLD. calony, DBM S8_ovTouT. PU T_ LINE (‘New a7 271] | NEN gala a DBM6_ OUTPUT. PUT_LINE Csala Uf Ofer Ei ‘Il Sahay). C& INSERT OR Upp, TE ON mploy cox END 5) INSERT = : NEW. eae UPDATE = +01: Bala rg de NEW sad DELETE =; orn “Saloon wt ' , b , b 5 , ’ ’ s ° 5 , y ' ' 's11 top He FUNCTIONS 4x Lion TATA Aub Pr0g7am thot ae > et eifit. taak onlt tormpuctel ava >) peeban & suturning Choure Lat retuens av > CREATE FUNCTION function_name (parameter Siz) RETURN return — datatype (Declare Statements) Executable statement RETURN (veturn value) Exception handling statements] D CREATE OR REPLACE FUNCTION emp-. RETURN NUMBER cilia 5 ESI in Sal NUMBER (9, 2) 5 Ynax Sob NUMBER( 8,2) 2 dea VARCHAR 2095" NOMBER (QJ SELECT Job_id, salary INTO V_fob-id, V_sal FROM embiyees Si WHERE fob-id =empid ; SELECT mn ( Scary MAK( Salasy ) INTD V-rn.cal Vimay_ hab >> J - FROM Employees WHERE ob-id = sob_Pd> a dies Belee (v-4al —y_ min_sal ) [Cvimraraat = Vimin_ sal) SL Veen ng (enpit Nina2 > ~--wwwerwerve [f/ © CALLING PL/SQL BLock SET SERVEROUTPUT ON; emp—id NUMBER = 1695 made wat eine G Meer of ’|I TO-CHAR (onp.) [| RounD(emp_ol_ranke 4 (empia dz) J Find the manus of the students who are Betsy me Tas OMA eo eee Roito ( Tbranch= tose? eae) , Name V Branch ~¢ ry?