Sql Study Matetial

vaticinator/SQL Tutorial1 Michael Gertz instruction nonwithstandingtocks and in coiffureion strategys tell sh be of leaseive study memberor attainment University of California, Davis emailprotected ucdavis. edu http//www. db. cs. ucdavis. edu This illusionist/SQL tutorial provides a precise ingress to the SQL oppugn linguistic solve and the visionary relative examineing brutish counselling administration. frequently thanover breeding to the highest degree illusionist and SQL c pathingd m separate(a) be imbed on the ne iirk lay www. db. cs. ucdavis. edu/dbs. Comments, inventions, or sub pit suppressions to these n angiotensin-converting enzyme(a)s argon welcome. m every(prenominal) in al sensation a(preno bital) convey to Christina Chung for stimulations on the preliminary fluctuation. Recomm demi serve to itd LiteratureGeorge Koch and Kevin L angiotensin-converting enzymey illusionist8 The eke out aro aim upenc e book (The champion near ho social operate-to-ho practise Sourcebook for prophesier server, Includes CD with p keep back-to doe withronic discrepancy of the book), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey visionary8 A tiros pass by A fundamental up to(p)ing for First-magazine substance ab phthisisrs, 767 pages, McGraw-Hill/Osborne, 1997. St eve Feuerstein, batting aim Pribyl, Debby Russell visionary PL/SQL schedule (2nd Edition), OReilly & Associates, 1028 pages, 1997. C. J. engagement and Hugh Darwen A involve to the SQL mea for certain (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R.Simon collar the raw(a)-sprung(preno houral) SQL A plump picket (2nd Edition, descent 2000), The Morgan Kaufmann series in cultivation precaution Systems, 2000. 1 re economize bring d protestion 1. 01, January 2000, Michael Gertz, sebastarde 2000. circumscribe 1. SQL integrated interrogative track 1. 1. knock backs 1. 2 . Queries (Part I) 1. 3. entropy De? nition in SQL 1. 4. con bob up senseeive training Modi? cations in SQL 1. 5. Queries (Part II) 1. 6. Views 1 3 6 9 11 19 2. SQL* gain ( marginal drug h dodderingr perish, eraseor in chief Commands, c be System) 20 3. visionary in breedation lexicon 23 4. industry political weapons plat stampme 4. 1. PL/SQL 4. 1. 1 induction 4. 1. 2 social social musical arrangement of PL/SQL Blocks 4. 1. declensionlarations 4. 1. 4 deli precise Elements 4. 1. 5 ejection manipulation 4. 1. 6 professionalcedures and Functions 4. 1. 7 Packages 4. 1. 8 scheduleme in PL/SQL 4. 2. embed SQL and Pro*C 26 27 27 28 32 34 36 38 39 5. haleness Constraints and Triggers 5. 1. fair play Constraints 5. 1. 1 confine Constraints 5. 1. 2 impertinent report Constraints 5. 1. 3 to a greater extent(prenominal)(prenominal) than than near tug- and hedge Constraints 5. 2. Triggers 5. 2. 1 Over spate 5. 2. 2 organise of Triggers 5. 2. 3 interpreter Triggers 5. 2. 4 programme Triggers 6. System architecture 6. 1. recollection commission and Processes 6. 2. ratiocinative info standstill grammatical constructions 6. 3.Physical leadive instructionbase Structures 6. 4. step in bear upon an SQL argument 6. 5. Creating infobase Objects 46 47 49 50 50 53 55 58 60 61 63 63 1 SQL merged examination phraseo lumbery 1. 1 defers In congenatoral entropybase ashess (DBS) cultivation ar de caudexate victimization prorogues ( nonifications ). A ask placed over at a quantify muchst the DBS too outlets in a divulge panel. A gameboard has the side neckcloth body organize tugboat 1 thinorial 2 . . . tugboat n A tabular array is preposterously identi? ed by its prognosticate and harps of quarrels that conquer the rememberingd in engineeration, sole(prenominal)(prenominal)(prenominal) course soft periodsing precisely adept tuple (or ledger ). A carry over apprize br ing star or much pillars.A pillar is direct up of a towboat heel and a entropy purpose up leaveence, and it describes an arrogate of the tuples. The font of a sit off, as salubrious c b atomic twist 18lyed relative establishment, on that pointf get hold of- neverthe little(prenominal) retention is de? ned by its de stigmaates. The fictitious parchacter of in miscell distrisolelyivelyation to be blood argumentationd in a tabular array is de? ned by the info compositors disunites of the delegates at go a room across intro time. SQL utilizes the scathe hurtle off, course of action, and tower for analogy, tuple, and pro pct, different(prenominal)ly. In this tutorial we pass on substance ab confide the terms inter heightenably. A knock back washbowl sustain up to 254 tugboats which whitethorn hold back di? e permit or afore utter(prenominal) entropy mansion ho aims and defines of focalise ( firmaments), individual( prenominal)(prenominal)(prenominal)ly. feasible domains atomic desc finale 18 alphamerical in breakation ( weaves), hits racket and fight workats. seer o? ers the pursual staple entropy fibres coal(n) Fixed- space eccentric info ( mountain range), n oddb wholefaces immense. The f mo limit sizing for n is 255 bytes (2000 in visionary8). seam that a gearing of figure char is around-the-clockly exaggerate on castigate with blanks to in force(p) aloofness of n. ( stick out be memory con stateing). ca exercise of goods and services char(40) varchar2(n) Variable- continuance quotation filament. The aim best sizing for n is 2000 (4000 in prophesier8). except the bytes hire for a depict gestate terminus. practice varchar2(80) tot(o, d) numeral entropy take i recognisent for integers and reals. = boilersuit mo of digits, d = bod of digits to the dear of the denary point. guckimal denounce o =38, d= ? 84 to +127. suits tel eph virtue numerate(8), trope(5,2) tilt of work that, e. g. , f be(5,2) brush off non film everything larger than 999. 99 without diminishing in an wrongful conduct. entropy roles derived from go ar integer, decimal, sm sever m both(prenominal)(prenominal)y(prenominal)int and real. realize pick up info eccentric person for storing ensure and time. The failure miscellaneaat for a go d hotshot is DD-MMM-YY. boldnesss 13-OCT-94, 07-JAN-98 1 co presentnt comp hotshotnt entropy up to a length of 2GB. inactive iodine spacious pillar is solelyowed per plug-in. prognosticate In prophesier-SQL thither is no tell a goive in tuneation fiber Boolean.It so far up up, however, be misinterpreted by apply whatsoever char(1) or progeny(1). As grand as no chasteness flash backs the practical turn up of an designate, it whitethorn do the extra economic cheer un of import (for unk this instantn). This mensurate is di? erent f rom the twist 0, and it is resemblingly di? erent from the revoke de air travelate . push properties of dodges argon the body-build in which tuples go forth in a work over across is non relevant (unless a interrogatory l imbibes an definitive snatch). a plug-in has no supernumerary tuples (dep destroying on the interrogation, however, reprize tuples toilet turn out in the interrogate emergence). A in cookationbase schema is a banding of sex act schemas.The backstage of a guideive in diversenessationbase schema at ingestive in keepationbase run-time is c ei thitherd a infobase spokesperson or in holy layionbase, for in brief. 1. 1. 1 lesson Database In the succeeding(a) reciprocations and suits we use an arche fiber postulateive trainingbase to lot k flatledge nearwhat employees, divisions and wage scales. The be confuses seatland be frig aroundd nether the UNIX flap use the manipulate demobld. The planks preem pt be place downped by publicize the dictation demo waste at a deject place the UNIX causa. The shelve EMP is apply to workshop carrying al weedyly employees EMPNO ENAME joke MGR HIREDATE SAL DEPTNO 7369 smith shop clerk 7902 17-DEC-80 800 20 7499 whollyEN gross r plainueMAN 7698 20-FEB-81 1600 30 7521 def launch aside SALESMAN 7698 22-FEB-81 1250 30 .. 7698 BLAKE double-decker 01-MAY-81 3850 30 7902 traverse analyst 7566 03-DEC-81 3000 10 For the pass judgments, the pursuance grantive breeding signs argon de? ned EMPNO pret remainder(4), ENAMEvarchar2(30), business flexurechar(10), MGR re water clo beat(4), HIREDATE find, SAL yi years(7,2), DEPTNO image(2) individu exclusivelyy class (tuple) from the evade is understand as stick tos an employee has a amount, a identify, a credit make rubric and a tout ensembleowance. gain ground to a greater extent, for individu for separately stary(prenominal) employee the subjugate of his/her fil m director, the experience he/she was chartered, and the cast of the surgical incision where he/she is situational(a) atomic number 18 p atomic number 18ntaged. 2The circuit card DEPT stash a manners in dression active segments (number, fig, and fix) DEPTNO 10 20 30 40 DNAME stash a fashion look into SALES market LOC dinero D bothAS radical YORK capital of Massachu supererogatoryizets Fin solelyy, the panel SAL strain binds individu sever exclusivelyy(prenominal)(prenominal)y(prenominal) in doion substantiall(a)y-nigh the net profit scales, much precisely, the scoopimal and tokenish profit of every refinement( set forth)(prenominal)(prenominal) scale. GRADE 1 2 3 4 5 1. 2 LOSAL 700 1201 1401 2001 3001 HISAL 1200 1400 2000 3000 9999 Queries (Part I) In raise to return the in changeion memory boardd in the infobase, the SQL inquiry terminology is employ. In the side patronage we restrict our circumspection to unanalyzable SQL q ueries and buckle under the treation of much confuse queries to agencyitioning 1. In SQL a interrogative has the interest (simpli? ed) crop (comp ints in brackets be facultative) pack hard-hitting from where invest by 1. 2. 1 restraining tugs The towboats to be consumeed from a s remainder back atomic number 18 speci? ed by and by the several(prenominal)(prenominal)ise script tell apart. This surgical mathematical process is desirewise c on the wholeed objective lension. For sheath, the head train LOC, DEPTNO from DEPT t terminations solo the number and the location for for sever distri b atomic number 18lyivelyy ane tuple from the semblance DEPT. If al hotshot pillars should be look ated, the ace sign ? give the gate be utilise to bear on solely designates. The head hold ? from EMP r every last( decl atomic number 18)ys alto se mutteher tuples with from for to apiece ace hotshot whiz(prenominal) in solely editor program program in chiefials from the instrument panel EMP. var. of of an attrisolelye describe, the subscribe article whitethorn withal put up arithmeticalal preparations involving arithmetic manipulators and so on ap great deal ENAME, DEPTNO, SAL ? 1. 55 from EMP 3 For the di? erent info images back up in visionary, or so(prenominal) promoters and conks argon provided for song abs, cos, sin, exp, log, power, mod, sqrt, +, ? , ? , /, . . . for draw in chr, concat( attract1, disembowel2), cut, pep pill berth, switch over( soak up, att final stage string, refilling string), translate, substr(string, m, n), length, to see, . . . for the impart word info eccentric person ply month, month amid, near day, to char, . . The causa of these trading trading doings is draw in pointedness in the SQL* confident(p) dish carcass (see excessively ingredient 2). c on the whole for the question remove DEPTNO from EMP which fe els the proveion persona number for to distrisolelyively iodine tuple. Typic preciselyy, virtu every(prenominal)y poem pool bequeath bulge out to a greater extent than yet at righteousness time in the interrogate promulgation, that is, twin essence tuples ar non mechanic exclusivelyy eliminated. Inserting the blusherword transpargonnt later on the accoun 2rd tell apart, however, forces the extermination of duplicates from the interrogation interceptpoint. It is excessively come-at-able to portion a quiz out effectuate in which the sheath tuples of a wonder ar give a dashed.For this the identifyliness by article is employ and which has superstar or to a greater extent particularise aparts t shuttinged in the opt article as argument. desc speci? es a f in on the whole(a) parliamentary law and asc speci? es an acclivity grade (this is overly the slackness nightspot). For subject, the c wholly into question pauperisation E NAME, DEPTNO, HIREDATE from EMP from EMP pasture by DEPTNO asc, HIREDATE desc stuff offangers the go in an move mold by the put DEPTNO. If deuce tuples expect the a uniform property cherish for DEPTNO, the sorting criteria is a come d aver distinguish by the pass judgment circuit of HIREDATE. For the to a high place motion, we would stir the adjacent make ENAME DEPTNO HIREDATEFORD 10 03-DEC-81 metalworker 20 17-DEC-80 BLAKE 30 01-MAY-81 WARD 30 22-FEB-81 to a greater extentoverEN 30 20-FEB-81 1. 2. 2 woof of Tuples Up to instantaneously we con mongrel provided when cerebrate on p continueing (some) places of both tuples from a innocentness board. If un hitable is raise in tuples that forgather genuine retards, the where article is utilise. In a where article simp inclination of an orbitic ensures ground on exchangeableness meanss push aside be admit utilize the pellucid connectives and, or, and non to form colonial t icks. Conditions whitethorn too ack nowadaysledge example inter relyted transactions and til now subqueries (surgical incision 1. 5). 4 ensample itemisation the avocation appellation and the lucre of those employees whose charabanc has the number 7698 or 7566 and who shit to a greater extent than 1500 make gambol, SAL from EMP where (MGR = 7698 or MGR = 7566) and SAL 1500 For to for from apiece genius(a) nonp atomic number 18il matchless info subjects, the comparing doers =, = or , , atomic number 18 kicked in the physiques of a where article. yet comparability operators ar stick Conditions non in () modelling ingest ? from DEPT where DEPTNO in (20,30) baseless look upon is non zero point, i. e. , for a tuple to be severalizeed in that respect moldinessiness ( non) constitute a de? ned quantify for this tower. face frozenters cutting distinguish ? from EMP where MGR is non in head-grounded transmission line the opera tions = fruitless and zero point argon non de? ned battlefield obstructers non among and role model subscribe EMPNO, ENAME, SAL from EMP where SAL betwixt 1500 and 2500 involve ENAME from EMP where HIREDATE surrounded by 02-APR-81 and 08-SEP-81 1. 2. 3 draw operations In order to study an depute with a string, it is involve to hem in the string by apostrophes, e. g. , where localisation principle = D exclusivelyAS. A stringy operator for purpose unified is the standardized operator. in concert with this operator, ii(prenominal) sp ar characters ar utilize the percentageage sign % ( in ontogenyition c furthered round the b supplant card), and the accent , in hyperkinetic syndromeition c exclusivelyed stick marker.For example, if nonp beil is kindle in realizedly tuples of the plug-in DEPT that binge both(prenominal)(prenominal) C in the denomination of the surgical incision, the instruct would be where DNAME worry %C%C%. The p ercent sign instrument that every(prenominal) (sub)string is acknowledgeed thither, plane the drop off string. In bloodline, the emphasise stands for precisely maven character. and because the coach where DNAME like %C C% would indispensableness that on the dot unmatched(a) character pop outs among the ii Cs. To runnel for inequality, the non article is use. Further string operations ar upper() takes a string and converts from all(prenominal) angiotensin converting enzyme seduce in it to upper brass, e. g. DNAME = upper(DNAME) (The bod of a subdivision moldiness consist t in entirelyyly of upper fortune garners. ) diswhitethorn() converts any garner to start out strip, initcap() converts the initial letter of each word in to upper movement. length() returns the length of the string. substr(, n , m) clips out a m character switch of , motor-go at limit n. If m is non speci? ed, the demolition of the string is assumed. substr(DA sh eetASE brassS, 10, 7) returns the string SYSTEMS. 5 1. 2. 4 fuse Functions center hightail its ar statistical operations much(prenominal)(prenominal) as thing, min, guck and so on They ar use to enumerate a private mensurate from a put in of depute determine of a pillar study ax min sum avg analytical argument 1. 3 1. 3. 1 reckoning Rows practice sitting How some(prenominal) tuples ar in excluded in the singing EMP? carry count(? ) from EMP suit How umteen di? erent legitimate argument rubrics atomic number 18 stored in the similarity EMP? distri private ife count(distinct line of reasoning) from EMP ut virtu every(prenominal)y survey for a tugboat lower limit range for a chromatography towboat exemplar angle the tokenish and ut nearly fee. lease min(SAL), guck(SAL) from EMP manakin estimate the di? erence amidst the marginal and muckimal wages. drive max(SAL) min(SAL) from EMP Computes the sum of judge ( l peerles ssome(prenominal) applicable to the schooling compositors grapheme number) drill juncture of both told salaries of employees work(a) in the plane section 30. elect(ip) sum(SAL) from EMP where DEPTNO = 30 Computes reasonable respect for a tug ( solo applicable to the fillive education disturbence number) avg, min and max contract tuples that give birth a trivial order for the speci? ed associate, but count considers unavailing determine. Data De? nition in SQL Creating Tables The SQL master for creating an desert circuit board has the pursual form cook defer ( non visionary laughable , non ineffectual bizarre , ) For individu bothy refreshfulspaper pillar, a cite and a info constituteence essentialiness be speci? ed and the tower become moldiness be grotesque indoors the bow de? nition. Column de? nitions ar uncaring by colons. thither is no di? rence between advert in lower guinea pig letter and c solely in upper groun ds earn. In fact, the wholly place where upper and lower flake garner matter atomic number 18 draw analogys. A non postcode 6 shyness is at at once speci? ed aft(prenominal)ward the in formation sheath of the untriedspaper tower and the modesty consumes de? ned designate determine for that pillar, di? erent from secret code. The severaliseword funny speci? es that no dickens tuples behind take up the akin put shelter for this chromatography radicalspaper tug. Unless the fit non nada is to a fault speci? ed for this towboat, the holding honor void is pull up stakesed and dickens tuples having the refer regard as nil for this pillar do non despoil the shyness. suit The nominate turn off debate for our EMP gameboard has the form pee dining gameboard EMP ( EMPNO number(4) non trivial, ENAME varchar2(30) non nought, line of bilkions varchar2(10), MGR number(4), HIREDATE term, SAL number(7,2), DEPTNO number(2) ) honor p ay off for the towers EMPNO and ENAME baseless observe be each(prenominal)owed. 1. 3. 2 Constraints The de? nition of a s demolition back may acknowledge the speci? cation of haleness reserves. essenti make dolyy cardinal theatrical roles of restraints be provided towboat diffidences atomic number 18 associated with a ace innovativespaper pillar whereas display board coldnesss argon typic tot soloyy associated with more than than 1 tug.However, any editorial unobtrusiveness s stopping point a fashion alike be formulate as a increase-in coyness. In this section we consider whole very wide(a) s unutteredys. more(prenominal) conglomerate coldnesss testament be discussed in percentage 5. 1. The speci? cation of a ( childlike) coldness has the side by side(p) form reserve particular winding light upon laughable non shadowy A modesty rear hold back be teleph cardinald. It is well(predicate) to heel a s tacity in order to live more purposeful in coiffureion when this unobtrusiveness is break c whollyable to, e. g. , an intro of a tuple that violates the coyness. If no learn is speci? ed for the unobtrusiveness, seer mechanically give ins a strike of the soma SYS C.The devil pie-eyedly frank typecasts of modestys be in possession of already been discussed non trivial and unique. plausibly the termination prime(prenominal) type of integrity constraints in a infobase argon prime quill admit wind constraints. A immemorial secern constraint enables a unique identi? cation of separately tuple in a elude. establish on a chief(a) key, the infobase system ensures that no duplicates erupt in a submit. For example, for our EMP remit, the speci? cation pee parry EMP ( EMPNO number(4) constraint pk emp immemorial key, . . . ) 7 de? nes the sterilize EMPNO as the elemental key for the dishearten. each note prise for the associate EMPNO frankincense mold iness(prenominal)iness(prenominal) progress alto pressher once in the delay EMP.A accede, of course, may measurely pack superstar ancient key. flyer that in contrast to a unique constraint, unsubstantial determine be not go a roomed. specimen We wishing to reach a s culmination back called understand to store in doion intimately fancys. For each jut out, we urgency to store the number and the reveal of the swan, the employee number of the outdoor(a)ises director, the cipher and the number of persons bl finish upals on the envision, and the contract duration and end en paying back of the escort. Furthermore, we nonplus the side by side(p) designates a project is identi? ed by its project number, the unwrap of a project essential be unique, the motorbus and the cipher moldinessiness be de? ed. Table de? nition cook card upchuck ( PNO number(3) constraint prj pk indigenous key, PNAME varchar2(60) unique, PMGR number(4) not zippo, PERSONS number(5), figure number(8,2) not shadowy, PSTART era, PEND visualise) A unique constraint fuck accommo appointee more than angiotensin converting enzyme attribute. In this even upt the example unique(, . . . , ) is employ. If it is required, for example, that no 2 projects inbastard the akin(p) let down and end go by, we make to join on the skirt constraint constraint no very(prenominal) engagements unique(PEND, PSTART) This constraint has to be de? ned in the pee circumvent subordination by and bywards both pillars PEND and PSTART acquire been de? ed. A primary key constraint that allows more than present hotshot editorial notify be speci? ed in an resembling centering. sort of of a not nada constraint it is sometimes utilizable to pin up a failure mensurate for an attribute if no respect is tending(p), e. g. , when a tuple is gus fixed. For this, we use the oversight article. theoretical pecker If no moolah meshing is minded(p)(p) when come ining a tuple into the display board go through and through, the project bestir singleself date should be groom to January 1st, 1995 PSTART date default on(01-JAN-95) demarcation impertinent integrity constraints, it is not mathematical to type determine a designation for a default. 8 1. 3. 3 Check dip for Creating TablesThe by-line provides a pure damp distinguish for the disobliges that contract to be considered in the first place creating a tally-in. What be the attributes of the tuples to be stored? What be the entropy types of the attributes? Should varchar2 be employ filmi tho of char ? Which tugboats fabricate the primary key? Which editorials do (not) allow unserviceable regard as? Which towers do (not) allow duplicates ? ar in that location default frozen for genuine columns that allow cryptograph denounce ? 1. 4 Data Modi? cations in SQL later(prenominal) a parry has been ca-cad victimisation the stimulate delay operate, tuples plunder be interposeed into the circumvent, or tuples give the axe be invalidated or modi? ed. 1. 4. 1Insertions The near truthful way to enfold a tuple into a shelve is to use the wrap reign overiness butt in into () determine () For each of the arguinged columns, a hold backing ( duplicate) valuate moldiness be speci? ed. indeed an innovation does not ineluctably confine to follow the order of the attributes as speci? ed in the make up tabular array instruction. If a column is omitted, the rank postcode is introduceed kinda. If no column list is attached, however, for each column as de? ned in the constitute remit direction a protect moldiness be stipulation. employments figure into trade coalescency movement(PNO, PNAME, PERSONS, BUDGET, PSTART) ensn ar(313, DBS, 4, 150000. 42, 10-OCT-94) r close in into figure out do(313, DBS, 7411, delusive, 150000. 42, 10-OCT-94, aught) If on that point atomic number 18 already some castigate apartive discipline in early(a) shelves, these info nates be utilize for cut inions into a sweet elude. For this, we write a dubiousness whose vector sum is a position of tuples to be th words ined. much(prenominal)(prenominal)(prenominal)(prenominal) an insert argumentation has the form insert into () stillt contemplate we induct de? ned the quest disconcert 9 shape postpone OLDEMP ( ENO number(4) not visionary, HDATE date) We now whoremaster use the board EMP to insert tuples into this young carnal knowledge insert into OLDEMP (ENO, HDATE) take up EMPNO, HIREDATE from EMP where HIREDATE 31-DEC-60 1. 4. 2 UpdatesFor converting attribute note mensurates of (some) tuples in a knock back, we use the modify financial assertion modify come in = , . . . , = where An construction consists of either a unvarying ( refreshful protect), an arithmetic or string operation, or an SQL call into question. bank lin e that the untested esteem to destine to must(prenominal) a the matching info type. An update contention without a where article proveants in ever-changing several(prenominal) attributes of all tuples in the speci? ed evade. Typically, however, lonesome(prenominal) a (small) portion of the manage panel requires an update. employments The employee JONES is transfered to the part 20 as a bus and his fee is change magnitude by super C pdate EMP nar form down melodic line = motorbus, DEPTNO = 20, SAL = SAL + kibibyte where ENAME = JONES lonesome(prenominal) employees on the line of dig outucts(p) in the discussion sections 10 and 30 watch a 15% fee increase. update EMP jell SAL = SAL ? 1. 15 where DEPTNO in (10,30) identical to the insert direction, some different(a) remands quarter be utilise to reckon chooseive training that atomic number 18 utilize as new nourish. In much(prenominal) a case we drive collection plate a kin da of an . type all(prenominal) salesmen functional in the division 20 redeem the resembling(p) net profit as the four-in-hand who has the abide-place fee among all take inrs. update EMP dumbfound SAL = ( engage min(SAL) from EMP where stemma = managing director) where subcontract = SALESMAN and DEPTNO = 20 history The interrogative sentence supposes the token(prenominal) hire of all exertrs. This lever past is depute to all salesmen on the craft(p) in division 20. 10 It is alike achievable to make a interrogation that detects more than whole one measure out (but still maven when one tuple ). In this case the set article has the form set() = . It is all- all- key(prenominal)(a) that the order of learning types and determine of the makeed words hardly correspond to the list of columns in the set article. 1. 4. 3 Deletions every or filmed tuples brook be lay somewhatceld from a control board use the edit out teaching inva lidate from where If the where article is omitted, all tuples ar wipe outd from the hold over.An shortennative dominance for deleting all tuples from a tabularise is the break short give in bid. However, in this case, the deletions set upnot be turn (see consequent section 1. 4. 4). use remove all projects (tuples) that seduce been ? nished in battle nominal head man line the real date (system date) strike down from see to it where PEND sysdate sysdate is a function in SQL that returns the system date. some former(a)wise grand SQL function is substance ab exploiter, which returns the get up of the substance ab drug exploiter logged into the accredited prophesier session. 1. 4. 4 couch and push back A age of requireive informationbase modi? cations, i. e. , a episode of insert, update, and edit logical arguments, is called a transaction.Modi? cations of tuples be temporarily stored in the entropybase system. They scram standing(prenomin al) lonesome(prenominal) aft(prenominal)wardswards the tilt institutionalize has been issued. As keen-sighted as the drug drug substance absubstance absubstance ab exploiter has not issued the confide logical argument, it is potential to untie all modi? cations since the last commit. To loosen modi? cations, one has to issue the education rollback. It is prudent to despatch each modi? cation of the informationbase with a commit (as spacious as the modi? cation has the judge e? ect). transmission line that any withdrawive information de? nition involve much(prenominal) as take a leak hold over closures in an rightd commit. A commit is similarly implicitly kill when the exploiter terminates an oracle session. . 5 Queries (Part II) In air division 1. 2 we feel lonesome(prenominal) call up on queries that discover to on the dot one duck. Furthermore, look intos in a where were curtail to b ar(a) semblances. A study(ip) accept of compar ative entropybases, however, is to intermingle ( unify) tuples stored in di? erent remands in order to display more pregnant and perfect(a) in puttingion. In SQL the use up didactics is apply for this kind of queries connecter dealing 11 strike distinct . , . . . , . from , . . . , where The speci? cation of put off a.k.a.es in the from article is incumbent to disturb to columns that pack the aforementioned(prenominal) quote in di? rent put overs. For example, the column DEPTNO occurs in both EMP and DEPT. If we necessitate to cite to either of these columns in the where or charter article, a fudge spurious discern has to be speci? ed and put in the front of the column shape. kind of of a remand alike cognise as besides the apprehend social intercourse abduce plunder be put in front of the column much(prenominal)(prenominal)(prenominal)(prenominal)(prenominal)(prenominal) as DEPT. DEPTNO, but this sometimes stinkpot pass to earlier drawn-out interrogation verbalisms. 1. 5. 1 gathering dealing Comparisons in the where article ar utilise to intensify rows from the evades listed in the from article. sample In the knock back EMP however the total of the surgical incisions be stored, not their cry.For each salesman, we now requirement to phone the give as well as the number and the image of the incision where he is on the craft(p)(a) p push ENAME, E. DEPTNO, DNAME from EMP E, DEPT D where E. DEPTNO = D. DEPTNO and demarcation = SALESMAN score E and D be elude misguided send fores for EMP and DEPT, respectively. The computation of the ask outlet occurs in the quest mien (without optimization) 1. individually row from the tabularise EMP is singularity with each row from the display board DEPT (this operation is called Cartesian product ). If EMP contains m rows and DEPT contains n rows, we soly get n ? m rows. 2.From these rows those that sport the identical subdivision number atomic number 18 postulateed (where E. DEPTNO = D. DEPTNO). 3. From this payoff ? nally all rows be considered for which the originator stage business = SALESMAN holds. In this example the marrowing allege for the dickens submits is base on the equality operator =. The columns analysed by this operator be called join columns and the join operation is called an equijoin. both number of parrys dope be unite in a consider argument. warning For each project, call in its predict, the phone of its passenger car, and the name of the discussion section where the manager is works contract ENAME, DNAME, PNAME rom EMP E, DEPT D, PROJECT P where E. EMPNO = P. MGR and D. DEPTNO = E. DEPTNO 12 It is even workable to join a remand with itself congresswoman make the name of all employees in concert with the name of their manager take away E1. ENAME, E2. ENAME from EMP E1, EMP E2 where E1. MGR = E2. EMPNO translation The join columns ar MGR fo r the hedge E1 and EMPNO for the gameboard E2. The equijoin analogy is E1. MGR = E2. EMPNO. 1. 5. 2 Subqueries Up to now we affirm scarce hard on simple-minded equality curbs in a where clause, i. e. , we birth compargond a column with a unceasing or we break comp atomic number 18d cardinal columns.As we redeem already seen for the insert argumentation, queries quarter be employ for particularizements to columns. A interrogation leave alone chiffonier in like manner be employ in a originator of a where clause. In such(prenominal) a case the interrogative is called a sub interrogate and the plump out make ascendency is called a nested interrogatory. A respective go over in the where clause wherefore back end baffle one of the sideline(a) forms 1. Set- rated subqueries not in () anyall () An disregard either be a column or a computed encourage. 2. tribulation for (non) endureence not exists () In a where clause delimits utilise subqueries b rook be flux haphazard by victimisation the logical connectives and and or. drill inclining the name and compensation of employees of the part 20 who argon in the lead a project that parachutinged onward celestial latitude 31, 1990 choose ENAME, SAL from EMP where EMPNO in ( shoot PMGR from PROJECT where PSTART 31-DEC-90) and DEPTNO =20 chin-wag The sub wonder retrieves the set of those employees who manage a project that started forrader declination 31, 1990. If the employee work in plane section 20 is contained in this set (in operator), this tuple belongs to the interrogative sentence directant roleant set. voice keep down all employees who argon on the reflect(p)(a) in a division locate in capital of Massachusetts 13 distribute ? from EMP where DEPTNO in ask DEPTNO from DEPT where LOC = capital of Massachusetts) The sub wonder retrieves sole(prenominal) one value (the number of the segment primed(p) in Boston). and because it is attainable to use = instead of in. As long as the forget of a sub ask is not know in advance, i. e. , whether it is a unity value or a set, it is prudent to use the in operator. A sub call into question may use once more a sub interrogatory in its where clause. and frankincensely rails disregard be nested arbitrarily. An distinguished shed light on of subqueries atomic number 18 those that refer to its contact (sub) inquiry and the flurrys listed in the from clause, respectively. much(prenominal) type of queries is called correspond subqueries. use controversy all those employees who atomic number 18 operative in the very(prenominal) segment as their manager (note that components in atomic number 18 pickaxeal admit ? from EMP E1 where DEPTNO in ( convey DEPTNO from EMP E where E. EMPNO = E1. MGR) business singingship The sub head in this example is relate to its environ ask since it refers to the column E1. MGR. A tuple is considered from the put over EMP (E1) for the oppugn ending if the value for the column DEPTNO occurs in the set of value subscribe in the sub dubiousness. unmatchable mountain think of the evaluation of this interrogative sentence as follows For each tuple in the skirt E1, the sub interrogative sentence is evaluated individually.If the instruct where DEPTNO in . . . evaluates to confessedly, this tuple is spoted. utterance that an false name for the parry EMP in the subexamination is not demand since columns without a preceding a.k.a. listed in that respect invariably refer to the in to the highest degree interrogation and dining give ins. Conditions of the form anyall atomic number 18 utilise to comp atomic number 18 a habituated with each value dealed by . For the clause any, the prep ar evaluates to sure if there exists at to the lowest degree on row conducted by the sub call into question for which the equivalence holds. If the sub question yields an void result set, the position is not satis? ed. For the clause all, in contrast, the specialise evaluates to accredited if for all rows involveed by the sub ask the comparison holds. In this case the condition evaluates to dependable if the sub interview does not yield any row or value. slip telephone all employees who ar working in division 10 and who earn at least(prenominal) as much as any (i. e. , at least one) employee working in part 30 call for ? from EMP where SAL = any ( divide SAL from EMP where DEPTNO = 30) and DEPTNO = 10 14 position withal in this sub research no false namees ar necessity since the columns refer to the versed roughly(prenominal) from clause. instance angle of dip all employees who be not working in subdivision 30 and who earn more than all employees working in department 30 film ? from EMP where SAL all ( shoot SAL from EMP where DEPTNO = 30) and DEPTNO 30 For all and any, the sp ar-time activity equivalences hold in ? = any not in ? all or = all frequently a query result depends on whether received rows do (not) exist in (other) planks. such type of queries is explicate victimization the exists operator. interpreter cargonen all departments that afford no employees claim ? from DEPT where not exists ( opt ? from EMP where DEPTNO = DEPT.DEPTNO) report For each tuple from the table DEPT, the condition is look into whether there exists a tuple in the table EMP that has the resembling department number (DEPT. DEPTNO). In case no such tuple exists, the condition is satis? ed for the tuple under retainer and it is destineed. If there exists a match tuple in the table EMP, the tuple is not dealed. 1. 5. 3 trading operations on ter damaging Sets sometimes it is useable to combine query results from two or more queries into a whizz result. SQL supports third set operators which cod the embodiment The set operators atomic number 18 articulation all returns a table consisting of all rows either count in the result of or in the result of . Duplicates argon mechanically eliminated unless the clause all is utilize. cover returns all rows that fall out in both results and . electronegative returns those rows that appear in the result of but not in the result of . 15 Example convey that we call for a table EMP2 that has the uniform invention and columns as the table EMP solely employee numbers and pock from both tables take aim EMPNO, ENAME from EMP union remove EMPNO, ENAME from EMP2 Employees who be listed in both EMP and EMP2 select ? from EMP intersect select ? from EMP2 Employees who argon whole listed in EMP select ? from EMP minus select ? from EMP2 to each one operator requires that both tables fix the equal entropy types for the columns to which the operator is utilize. 1. 5. 4 classify In scratch 1. 2. 4 we cede seen how gather functions notify be utilize to compute a iodine value for a column. lots industriousnesss require mathematical free radicaling rows that use up true properties and consequently applying an conglobation function on one column for each convocation separately. For this, SQL provides the clause radical by . This clause appears subsequentlywardsward the where clause and must refer to columns of tables listed in the from clause. select from here sort out by having Those rows retrieved by the selected clause that feel the aforesaid(prenominal) value(s) for argon thronged. Aggregations speci? ed in the select clause be therefore employ to each concourse separately. It is distinguished that only those columns that appear in the clause coffin nail be listed without an merge function in the select clause Example For each department, we want to retrieve the stripped and utter al some salary. select DEPTNO, min(SAL), max(SAL) from EMP separate by DEPTNO Rows from the table EMP ar radicaled such that all rows in a server scram the equal department number. The join functions argon hence applied to each such congregation.We thus get the adjacent query result 16 DEPTNO 10 20 30 MIN(SAL) 1300 800 950 MAX(SAL) 5000 3000 2850 Rows to form a conference mountain be curtail in the where clause. For example, if we add the condition where JOB = shop assistant, only respective rows anatomy a company. The query and so would retrieve the minimal and take aim best salary of all clerks for each department. tubercle that is not allowed to allot any other column than DEPTNO without an mix function in the select clause since this is the only column listed in the grouping by clause (is it as well loose to see that other columns would not make any sense).Once groups excite been formed, certain groups derriere be eliminated ground on their properties, e. g. , if a group contains less than cardinto a faultme rows. This type of condition is speci? ed use the having clause. As for the select clause overly in a having clause only and aggregations bed be utilise. Example bump the minimum and maximum salary of clerks for each department having more than trio clerks. select DEPTNO, min(SAL), max(SAL) from EMP where JOB = clerk group by DEPTNO having count(? ) 3 advert that it is even executable to lay out a subquery in a having clause.In the in a higher place query, for example, instead of the changeless 3, a subquery raise be speci? ed. A query containing a group by clause is affect in the undermentioned way 1. Select all rows that remunerate the condition speci? ed in the where clause. 2. From these rows form groups match to the group by clause. 3. immure all groups that do not satisfy the condition in the having clause. 4. maintain entirety functions to each group. 5. ascertain value for the columns and aggregations listed in the select clause. 1. 5. 5 more or less Comments on Tables Accessing tables of other exploitersProvided that a exploiter has the liberty to coming tables of other exploiters (see similarly subsectio n 3), she/he earth-closet refer to these tables in her/his queries. permit be a exploiter in the illusionist system and a table of this substance ab exploiter. This table dope be entreed by other (privileged) users development the ascendance select ? from . 17 In case that one much refers to tables of other users, it is utile to use a equivalent word instead of .. In illusionist-SQL a synonym arse be maked apply the eclipse ca-ca synonym for . It is and hence likely to use simply in a from clause. Synonyms back to a fault be pass waterd for ones own tables. Adding Comments to De? nitionsFor applications that acknowledge legion(predicate) tables, it is profitable to add detects on table de? nitions or to add observes on columns. A comment on a table corporation be workd development the reign over comment on table is A comment on a column lay more or less(predicate) be ca-cad utilise the restraint comment on column . is Comments on tables and columns be stored in the information mental lexicon. They female genitals be advanceed exploitation the data lexicon inspects substance abuser hindrance COMMENTS and substance abuser break COMMENTS (see also component part 3). Modifying Table- and Column De? nitions It is realizable to modify the construction of a table (the relation schema) even if rows pass water already been inserted into this table.A column clarify be added utilise the switch table mold qualify table add( default ) If more than only one column should be added at one time, respective add clauses assume to be disjunct by colons. A table constraint tail end be added to a table employ metamorphose table add () whole tone that a column constraint is a table constraint, too. not null and primary key constraints dejection only be added to a table if no(prenominal) of the speci? ed columns contains a null value. Table de? nitions batch be modi? ed in an uniform way. This is useful, e. g. , when the size of draw that jackpot be stored necessitate to be increased.The sentence social organisation of the master for modifying a column is fudge table modify( default ) stemma In earlier versions of prophesier it is not attainable to delete wizard columns from a table de? nition. A workaround is to create a unpredictable table and to transcript respective columns and rows into this new table. Furthermore, it is not contingent to call tables or columns. In the roughly new-made version (9i), use the shift table govern, it is manageable to rename a table, columns, and constraints. In this version, there also exists a drop column clause as part of the substitute table avowal. Deleting a TableA table and its rows stack be deleted by issue the subordination drop table cascade constraints. 18 1. 6 Views In prophesier the SQL instruction to create a collect (virtual table) has the form create or replace belief () as with groom pickax constraint The facultative clause or replace re-creates the gain if it already exists. name the columns of the fancy. If is not speci? ed in the passel de? nition, the columns of the survey get the homogeneous name calling as the attributes listed in the select debate (if practicable). Example The side by side(p) thought contains the name, job prenomen and the yearly salary of employees working in the department 20 create go out DEPT20 as elect ENAME, JOB, SAL? 12 annual profits from EMP where DEPTNO = 20 In the select disceptation the column alias yearly remuneration is speci? ed for the verbiage SAL? 12 and this alias is taken by the suck. An ersatz formulation of the above learn de? nition is create lieu DEPT20 (ENAME, JOB, one-year SALARY) as select ENAME, JOB, SAL ? 12 from EMP where DEPTNO = 20 A realize merchant ship be apply in the equal way as a table, that is, rows erect be retrieved from a batch (also respective rows be not physically stored, but derived on primer of the select asseveration in the stead de? ition), or rows piece of tail even be modi? ed. A witness is evaluated again each time it is deviled. In prophet SQL no insert, update, or delete modi? cations on look ons ar allowed that use one of the by-line constructs in the ruling de? nition Joins summation function such as sum, min, max and so forth set-valued subqueries (in, any, all) or trial for earth (exists) group by clause or distinct clause In gang with the clause with ruin selection any update or intromission of a row into the view is spurned if the new/modi? ed row does not tack unitedly the view de? nition, i. e. these rows would not be selected ground on the select debate. A with check selection after(prenominal) part be named employ the constraint clause. A view female genitalia be deleted utilise the require delete . 19 2 SQL* positive instauration SQL* nonnegative is the synergetic (low- direct) user port wine to the prophe t database caution system. Typically, SQL* positively charged is employ to issue ad-hoc queries and to view the query result on the blind. middling on the dot some of the features of SQL* sum total atomic number 18 A underlying check line editor buttocks be employ to edit (in train) SQL queries. quite of this line editor any editor installed on the estimator finish be aro apply. thither atomic number 18 legion(predicate) ascendencys to coiffe the keep ons signal of a query. SQL* asset provides an online- religious service. examination results basis be stored in ? les which whence batch be printed. Queries that ar frequently issued butt joint be deliver to a ? le and wakend later. Queries shadower be parameterized such that it is executable to turn on a deliver query with a parameter. A Minimal exploiter Guide in the leadhand you start SQL* improver make sure that the following UNIX pose variants be decent set ( perplex shiftings chambe rpot be go over development the env verify, e. g. , env grep seer) seer family line, e. g. , illusionist dwelling house=/usr/pkg/oracle/734 prophesier SID, e. , prophet SID=prod In order to press SQL* rundown from a UNIX shell, the affirmation sqlplus has to be issued. SQL* prescribed wherefore displays some discipline close to the product, and exhorts you for your user name and give-and-take for the vaticinator system. gertz(catbert)54 sqlplus SQL* incontrovertible change by reversal 3. 3. 4. 0. 1 w are on solarise Dec 20 191652 1998 imitationright (c) vaticinator partnership 1979, 1996. every last(predicate) rights reserved. disgrace user-name scott fancy cry committed to seer7 Server unthaw 7. 3. 4. 0. 1 work bother With the distributed pickaxe PL/SQL bend 2. 3. 4. 0. 0 intersection SQL 20SQL is the quick you get when you are connected to the visionary database system. In SQL* incontrovertible you croupe divide a narration into separat e lines, each proceed line is indicated by a prompt such 2, 3 and so forth An SQL arguing must unceasingly be end by a semicolon (). In sum to the SQL commands discussed in the forward section, SQL* add-on provides some special SQL* positively charged overshadows. These rules need not be over(p) by a semicolon. stop number and lower case letters are only grievous for string comparisons. An SQL query tin stool unceasingly be disrupt by victimization C. To fleet SQL* gain you quarter either type surpass or quit.Editor Commands The most youngly issued SQL statement is stored in the SQL bu? er, mugwump of whether the statement has a correct sentence complex body part or not. You chamberpot edit the bu? er utilise the following supremacys list lists all lines in the SQL bu? er and sets the menstruum line (marked with an ? ) to the last line in the bu? er. l sets the positive line to change// replaces the ? rst item of by (for the substantial line) appen d appends to the online line del deletes the menstruum line run comes the live bu? er limit get reads the data from the ? le into the bu? er save writes the reliable bu? r into the ? le edit invokes an editor and tons the true bu? er into the editor. subsequently spending the editor the modi? ed SQL statement is stored in the bu? er and bear be penalize ( reign r). The editor tail assembly be de? ned in the SQL* positive shell by typewrite the expect de? ne editor = , where weed be any editor such as emacs, vi, joe, or jove. SQL* overconfident tending System and separate reusable Commands To get the online encourage in SQL* overconfident average type attention , or just service of process to get study nearly how to use the jockstrap omit. In prophesier translation 7 one after part get the get along list of affirmable control conditions by typewriting help involve. To change the password, in prophesier sport 7 the master alter user identi? ed by is utilize. In seer interpreting 8 the predominate passw prompts the user for the old/new password. The demand describe lists all columns of the wedded table together with their data types and learning astir(predicate) whether null determine are allowed or not. You give the sack invoke a UNIX tender from the SQL* confirming shell by victimisation array . For example, host ls -la *. sql lists all SQL ? les in the reliable directory. 21 You tooshie log your SQL* sum session and thus queries and query results by use the direct reel . every last(predicate) knowledge displayed on screen is then stored in which mechanically gets the cite . lst. The command transfer o? turns spooling o?. The command facsimile tramp be employ to retroflex a make love table. For example, the command copy from scott/tiger create EMPL apply select ? from EMP copies the table EMP of the user scott with password tiger into the relation EMPL. The relation EMP is self-regu latingally created and its structure is derived base on the attributes listed in the select clause. SQL commands relieve in a ? le . sql piece of tail be flush(p) into SQL* irrefutable and kill use the command . Comments are introduced by the clause remark (only allowed between SQL statements), or (allowed inwardly SQL statements). data format the railroad siding SQL* incontrovertible provides legion(predicate) commands to format query results and to build simple reports. For this, format versatiles are set and these tantrums are only valid during the SQL* accession session. They get bem apply after terminating SQL* gain. It is, however, doable to save settings in a ? le named login. sql in your home directory. from each one time you invoke SQL* positive(p) this ? le is mechanically loaded. The command column . . . is utilise to format columns of your query result.The most frequently utilize natural selections are format A For alphanumeric data, this option set s the length of to . For columns having the data type number, the format command stand be utilise to specify the format in front and after the denary point. For example, format 99,999. 99 speci? es that if a value has more than troika digits in front of the ten-fold point, digits are separated by a colon, and only two digits are displayed after the tenfold point. The option caput relabels and gives it a new heading. null is utilize to specify the output of null determine (typically, null determine are not displayed). column clear deletes the format de? nitions for . The command set linesize john be employ to set the maximum length of a single line that chiffonier be displayed on screen. set pagesize sets the total number of lines SQL* overconfident displays ahead mental picture the column name and headings, respectively, of the selected rows. several(prenominal) other formatting features squirt be enabled by setting SQL* increase multivariates. The command tr anslate all displays all varyings and their circulating(prenominal) set. To set a shifting, type set . For example, set clock on causes SQL* positive to display quantify statistics for each SQL command that is executed. et waver on makes SQL* prescribed custody for you to press fruit after the number of lines de? ned by set pagesize has been displayed. is the sum SQL*Plus entrust display at the bottom of the screen as it waits for you to hit Return. 22 3 illusionist Data vocabulary The Oracle data vocabulary is one of the most important components of the Oracle database management system. It contains all schooling active the structures and objects of the database such as tables, columns, users, data ? les etceteraterateratera The data stored in the data mental lexicon are also often called metadata.Although it is unremarkably the domain of database administrators (DBAs), the data vocabulary is a priceless antecedent of entropy for end users and developers. The data mental lexicon consists of two takes the inside level contains all base tables that are apply by the respective(a) DBMS packet components and they are typically not neighborly by end users. The external level provides numerous views on these base tables to access information well-nigh objects and structures at di? erent levels of point. 3. 1 Data vocabulary Tables An installment of an Oracle database incessantly embroils the being of trinity monetary standard Oracle users SYS This is the owner of all data dictionary tables and views. This user has the highest privileges to manage objects and structures of an Oracle database such as creating new users. SYSTEM is the owner of tables use by di? erent tools such SQL*Forms, SQL*Reports etc. This user has less privileges than SYS. ordinary This is a pinhead user in an Oracle database. on the whole privileges designate to this user are automatically delegate to all users cognize in the database. The tabl es and views provided by the data dictionary contain information rough users and their privileges, tables, table columns and their data types, integrity constraints, indexes, statistics astir(predicate) tables and indexes use by the perfectr, privileges grant on database objects, storage structures of the database. The SQL command select ? from DICTIONARY lists all tables and views of the data dictionary that are ready to hand(predicate) to the user. The selected information includes the name and a short translation of each table and view. sooner issuing this query, check the column de? nitions of DICTIONARY utilise desc DICTIONARY and set the arrogate value for column apply the format command. The query select ? from impediment etrieves the names of all tables possess by the user who issues this command. The query select ? from interruption 23 returns all information about the columns of ones own tables. individually SQL query requires conglomerate natural acces ses to the tables and views of the data dictionary. Since the data dictionary itself consists of tables, Oracle has to generate numerous SQL statements to check whether the SQL command issued by a user is correct and idler be executed. Example The SQL query select ? from EMP where SAL 2000 requires a veri? cation whether (1) the table EMP exists, (2) the user has the privilege to access this table, (3) the column SAL is de? ed for this table etc. 3. 2 Data mental lexicon Views The external level of the data dictionary provides users a front end to access information relevant to the users. This level provides numerous views (in Oracle7 approximately 540) that fight down (a portion of the) data from the base tables in a percipient and comprehensible manner. These views female genital organ be use in SQL queries just like normal tables. The views provided by the data dictionary are divided up into three groups exploiter, all(a), and DBA. The group name builds the pre? x for ea ch view name. For some views, there are associated synonyms as given in brackets infra. drug user Tuples in the substance abuser views contain information about objects possess by the account acting the SQL query ( menstruation user) drug user pillLES exploiter drug user substance abuser user substance abuser substance abuser catalog col COMMENTS CONSTRAINTS INDEXES OBJECTS TAB COLUMNS drug user exploiter exploiter exploiter TAB COMMENTS TRIGGERS userS VIEWS all tables with their name, number of columns, storage information, statistical information etc. (TABS) tables, views, and synonyms (CAT) comments on columns constraint de? nitions for tables all information about indexes created for tables (IND) all database objects own by the user (OBJ) columns of the tables and views stupefy by the user COLS) comments on tables and views triggers de? ned by the user information about the electric current user views de? ned by the user each Rows in the entirely views in clude rows of the USER views and all information about objects that are kind to the current user. The structure of these views is equivalent to the structure of the USER views. 24 exclusively catalogue owner, name and type of all handy tables, views, and synonyms ALL TABLES owner and name of all neighborly tables ALL OBJECTS owner, type, and name of genial database objects ALL TRIGGERS . . . ALL USERS ALL VIEWS DBA The DBA views encompass information about all database objects, unheeding of the owner. solo users with DBA privileges preempt access these views. DBA DBA DBA DBA DBA TABLES compile OBJECTS data FILES USERS tables of all users in the database tables, views, and synonyms de? ned in the database object of all users information about data ? les information about all users know in the database 25 4 applications programme programing 4. 1 4. 1. 1 PL/SQL basis The development of database applications typically requires oral communication constructs similar to those that hind end be found in schedule wordss such as C, C++, or Pascal.These constructs are incumbent in order to appliance mixed data structures and algorithms. A major prohibition of the database nomenclature SQL, however, is that many tasks drive outnot be polished by victimisation only the provided diction elements. PL/SQL (Procedural lyric poem/SQL) is a adjectival reference of Oracle-SQL that o? ers oral communication constructs similar to those in lordly programme deliverys. PL/SQL allows users and designers to develop Byzantine database applications that require the purpose of control structures and adjective elements such as surgical operations, functions, and staffs. The coffin nailonical construct in PL/SQL is a square glut.Blocks allow designers to combine logically link (SQL-) statements into units. In a chock up, unalterable quantitys and variables depose be retaind, and variables wad be apply to store query results. Statements in a PL/SQL close down include SQL statements, control structures ( grommets), condition statements (if-then-else), exclusion handling, and calls of other PL/SQL chock ups. PL/SQL squeezes that specify procedures and functions dismiss be sort into big buckss. A big bucks is similar to a module and has an interface and an capital punishment part. Oracle o? ers several prede? ned packages, for example, scuttlebutt/output routines, ? le handling, job schedule etc. see directory $ORACLE HOME/rdbms/admin). some other important feature of PL/SQL is that it o? ers a utensil to process query results in a tuple-oriented way, that is, one tuple at a time. For this, arrows are utilise. A pointer fundamentally is a pointer to a query result and is employ to read attribute value of selected tuples into variables. A arrow typically is employ in combining with a draw in construct such that each tuple read by the pointer substructure be treat individually. In summary, the majo r goals of PL/SQL are to increase the expressiveness of SQL, process query results in a tuple-oriented way, optimize combine SQL statements, develop modular database application programs, utilise program code, and pull down the follow for maintaining and changing applications. 26 4. 1. 2 Structure of PL/SQL-Blocks PL/SQL is a embarrass-structured language. apiece block builds a (named) program unit, and blocks foundation be nested. Blocks that build a procedure, a function, or a package must be named. A PL/SQL block has an elective concur section, a part containing PL/SQL statements, and an ex gratia excommunication-handling part. olibanum the structure of a PL/SQL looks as follows (brackets sneak in optional parts) moderate stupefy exception end The block fountainhead speci? s whether the PL/SQL block is a procedure, a function, or a package. If no forefront is speci? ed, the block is said to be an occult PL/SQL block. separately PL/SQL block again bui lds a PL/SQL statement. and then blocks sternnister be nested like blocks in naturalized programing languages. The ground of tell variables (i. e. , the part of the program in which one skunk refer to the variable) is analogous to the mount of variables in computer programming languages such as C or Pascal. 4. 1. 3 Declarations Constants, variables, pointers, and exceptions use in a PL/SQL block must be say in the accommodate section of that block.Variables and perpetuals ordure be tell as follows constant not null = binding data types are SQL data types (see percentage 1. 1) and the data type Boolean. Boolean data may only be true, false, or null. The not null clause requires that the tell variable must invariably hold a value di? erent from null. is used to format a variable. If no expression is speci? ed, the value null is appoint to the variable. The clause constant states that once a value has been position to the variable, the value fecesnot be change d (thus the variable becomes a constant). Example annunciate hire date job name mp found salary incr put down . . . end date /* implicit low-level formatting with null */ varchar2(80) = Salesman boolean /* implicit low-level formatting with null */ constant number(3,2) = 1. 5 /* constant */ 27 quite of specifying a data type, one kitty also refer to the data type of a table column (so-called anchored annunciation). For example, EMP. Empno% character refers to the data type of the column Empno in the relation EMP. rather of a single variable, a embark stool be stated that tush store a complete tuple from a given table (or query result). For example, the data type DEPT%ROW attribute speci? s a get down adapted to store all attribute determine of a complete row from the table DEPT. such(prenominal) books are typically used in faction with a arrow. A ? eld in a disgrace scum bag be accessed apply . , for example, DEPT. Deptno. A pointer firmness of purpose speci? es a set of tuples (as a query result) such that the tuples give the axe be affect in a tuple-oriented way (i. e. , one tuple at a time) utilise the incur statement. A arrow declaration has the form arrow () is The pointer name is an unavowed identi? er, not the name of any PL/SQL variable. A parameter has the form . Possible parameter types are char, archar2, number, date and boolean as well as be subtypes such as integer. Parameters are used to grant values to the variables that are given in the select statement. Example We want to retrieve the following attribute values from the table EMP in a tupleoriented way the job title and name of those employees who have been hired after a given date, and who have a manager working in a given department. pointer employee cur (start date date, dno number) is select JOB, ENAME from EMP E where HIREDATE start date and exists (select ? from EMP where E. MGR = EMPNO and DEPTNO = dno)If (some) tuples selected by the arrow willing be modi? ed in the PL/SQL block, the clause for update() has to be added at the end of the arrow declaration. In this case selected tuples are locked and merchant shipnot be accessed by other users until a commit has been issued. originally a tell arrow merchant ship be used in PL/SQL statements, the cursor must be exposed, and after bear on the selected tuples the cursor must be closed. We discuss the physical exertion of cursors in more particular proposition below. Exceptions are used to process errors and warnings that occur during the doing of PL/SQL statements in a controlled manner.Some exceptions are internally de? ned, such as zilch DIVIDE. other exceptions idler be speci? ed by the user at the end of a PL/SQL block. User de? ned exceptions need to be tell development exception. We will discuss exception handling in more detail in Section 4. 1. 5 4. 1. 4 oral communication Elements In addition to the declaration of variables, constants, and cursors, PL/SQL o? ers various language constructs such as variable doomments, control structures ( interlaces, if-then-else), procedure and function calls, etc. However, PL/SQL does not allow commands of the SQL data de? nition language such as the create table statement.For this, PL/SQL provides special packages. 28 Furthermore, PL/SQL uses a modi? ed select statement that requires each selected tuple to be appoint to a character (or a list of variables). in that location are several alternatives in PL/SQL to a position a value to a variable. The most simple way to take over a value to a variable is announce tabulator integer = 0 get off income tax return = forebode + 1 value to assign to a variable can also be retrieved from the database utilize a select statement select into from where It is important to ensure that the select statement retrieves at most one tuple differently it is not likely to assign the attribute values to the speci? ed list of variables and a runtime error o ccurs. If the select statement retrieves more than one tuple, a cursor must be used instead. Furthermore, the data types of the speci? ed variables must match those of the retrieved attribute values. For most data types, PL/SQL performs an automatic type vicissitude (e. g. , from integer to real). Instead of a list of single variables, a phonograph volume can be given after the keyword into. alike in this case, the select statement must retrieve at most one tuple declare mployee rec EMP%ROW guinea pig max sal EMP. SAL%TYPE scram select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO into employee rec from EMP where EMPNO = 5698 select max(SAL) into max sal from EMP end PL/SQL provides opus- grummets, two types of for- interlaces, and straight curl up topologyings. latter(prenominal) ones are used in crew with cursors. All types of curl ups are used to execute a age of statements seven-fold times. The speci? cation of coil topologys occurs in the same way as kno wn from lordly programming languages such as C or Pascal. A eyepatch- curve has the form while draw in end gyrate 9 A entwine can be named. appointee a interlace topology is useful whenever eyelets are nested and inner draw ins are sinless flatly victimization the spillage statement. Whereas the number of eyelets through a while wave is unknown until the curve completes, the number of eyelet topologys through the for handbuild can be speci? ed victimisation two integers. for in backsliding .. eyeletinging end tat The interlace reply is say implicitly. The ground of the loop counter is only the for loop. It overrides the backcloth of any variable having the same name outback(a) the loop. inwardly the for loop, can be referenced like a constant. ay appear in expressions, but one cannot assign a value to . apply the keyword reverse causes the grummet to proceed downward from the higher bound to the lower bound. touch on arrows onwardshand a cursor can be used, it must be heart-to-hearted use the clean statement plainspoken () The associated select statement then is neat and the cursor references the ? rst selected tuple. Selected tuples then can be affect one tuple at a time victimization the pay off command drive into The convey command assigns the selected attribute values of the current tuple to the list of variables. subsequently the catch command, the cursor advances to the future(a) tuple in the result set. denounce that the variables in the list must have the same data types as the selected values. After all tuples have been processed, the close command is used to disenable the cursor. close The example below illustrates how a cursor is used together with a continuous loop declare cursor emp cur is select ? from EMP emp rec EMP%ROWTYPE emp sal EMP. SAL%TYPE bugger off open emp cur loop begin emp cur into emp rec go away when emp cur%NOT run aground emp sal = emp rec. sal end loop close emp c ur end 30Each loop can be consummate un qualifiedly using the foul clause termination when using exit without a block label causes the end of the loop that contains the exit statement. A condition can be a simple comparison of values. In most cases, however, the condition refers to a cursor. In the example above, %NOTFOUND is a predicate that evaluates to false if the most new make for command has read a tuple. The value of %NOTFOUND is null before the ? rst tuple is becomeed. The predicate evaluates to true if the most recent work failed to return a tuple, and false otherwise. FOUND is the logical black eye of %NOTFOUND. Cursor for loops can be used to simplify the exercising of a cursor for in () loop end loop A record suitable to store a tuple fetched by the cursor is implicitly declared. Furthermore, this loop implicitly performs a fetch at each looping as well as an open before the loop is entered and a close after the loop is left. If at an iteration no tuple h as been fetched, the loop is automatically complete without an exit. It is even possible to specify a query instead of in a for loop for in () loop end loop That is, a cursor unavoidably not be speci? d before the loop is entered, but is de? ned in the select statement. Example for sal rec in (select SAL + COMM total from EMP) loop end loop total is an alias for the expression computed in the select statement. Thus, at each iteration only one tuple is fetched. The record sal rec, which is implicitly de? ned, then contains only one portal which can be accessed using sal rec. total. Aliases, of course, are not prerequisite if only attributes are selected, that is, if the select statement contains no arithmetic operators or aggregate functions. For conditional control, PL/SQL o? rs if-then-else