———————————— UNIT - IT Retatfomal Model: [Spite ON ee aHonal Model: Basée terms velaled to el asic tams velse SO eee Snformal Formal fable relation Gable abrae aelation name colarmn or fields or heade| Atte? butes Rows tuples valaes 2n cell omatns Example Jablename(es) afield . alain name ethers > ebules gait Sno | Sname | Address | phno val Orn) OO “O a os 3 Ba "on as |_>-r0105(0” feast | | Be rales——— ——________, | Begoce of relation She no-cf columns er attrtbutes In or aclation 23 known as degree of relation. ex fiom the aclation Example Sesree of relation =4. Cadi ally of relation oe No: of vows or taples na zelafon £3 called card ra ly of relaHon ts i éx; gn the Example Bardi natty of relation: s Not! values. dn the cell we doesn't enter any valacs ie O & blankspace (-) and leave the cell c0fthout enter any data $s known as Nail values She visage of thes 23 ab panticalar time 2f we dont know the values of particular cell: Shas by wstng reed! Vales enter chata $n.t0 cell when 26 getthe vale of pavtfealar cell. Par RSS. DESY BS eset from vatr geen attribetes, we can select an athShates as a hezgp ancl the cond ?tons are: the ke athSbutes Should not have dupltcaled dala IF the attr?brute $s not hey the dup Peation $8 allowed. tei dhe Example shesos the deball explanation. of hey. : Bee ye eatsaddvess | Phno_ a, Gnt_ _ Cpe SG. | cRL As4 cpt | | 10d ONG . 1 | ef ¢ select sno ae 4 key, + have Auplca ten poles there the above déagram then the enlire column should 70 of dala tf ath@butes we not key attr? can be duplication of data ve srollno- Conshabnts: s Constraint means coneltHon based on this conshatnts We say whether the table 28 waled 1 Snvaltd: constraints Hf the given table satisty the anvaltd there cten 9b $s galed table other w tse ane ~thvee types of constraints: 4 key conshaent: tere conshaint $4 formed based on tq. a [gato | S-r0lln0| Sname | address] 50) Siva Behe sor | Taig | US S04 ema | > vAE Jsiva | Ont ' 2 3 | sos | bhana| v5 A P: 05lee sname £3 aktey consbaint check whether the table 2s walfd er not - dhe give table $3 bnoal’d Df seréal number £3 a hey constraint check whethe the table €9 valéd ov not -. She table 2s val?d. 2 Dematn consratnt Ofere the vane of values that may be eeuns bere nd fn domain characters ete has a range vepresP constraint Et. | numbers 5 Yo 1000 | ~ ; veloL Characer a to 2 of vabtiele whether the gbven - -table 23 galfd 2 or not Sor ihe Seven omen constraint agin’ the tablets valid Sor gies ee a e411 canbe } tp 100" characetera to. 2 for thYs the géven fable does not valé 3. Refernttal Entegetly conbtraint :CREATE § ALIEe Gn table deserfption yon: create table tablename (column! dalalepe, colama2 clatalepe ~~. eokuma-n alolelypy &t! cereale table erople ee (Sel. rrember 6 10), evan com bert) varchar (20), salery numbertio), age 7 = cid eae | salary ae |g add colenn|s | syntar: alter table tablename add alum name dataleype;———————— gai filer -table Emoplagee ait adcbess varchar (20),:| | Gd. | enamts | Saleny *| age ez | o> a “il, remove colemmfs sypten aller table, tablename chop. colemn alumn name gx: alter table Emplogee drop column address 7 eld | enamel salmy | age i iii, ees coleunnfs of Aatat ty pe tan! alter table tablename mocktty alumnname datatype au aller table Emphogyee molt eld varcharte) Field name | __datatype.._ | eid varchar(o) say ad ename varchar (1) Lo salary nambecio) namberliod ageWw rmde fe) 2 of cfataty Pe; Seas | aller table tablename moclefy column | Sapkau: a nare datatype ; | ex: alley table enppbzee nudity etd ver chart) reg 7 p yerchayl20) varchar) ‘eimber(o) numbertw) Ye rename cthe colzmn syotae alter table tablename rename column colamnname 10 news éx: aller table Employee venarme cokimn age iD experFence ; G eid | ename salary exper tence Mi vename table name Sylar alley table tablename rename 10 nen tableray j en: aller dale: sroplogee venarme to teacher;Pee ee 5 select & from tenploryce table doesnot exes vil add conghietints vonstratint. Mears ke, allow ary dupltcales pr alle? belles « vy pian aller table tablename add pihaighey Seysty ded Ccolemn ner) éx: alley table eneplegee, sade rein hey 10 Teacher cond?tan ehteh Aoeerh pig? etd [ename| salng [experience] sya aller Enable tableware’ ohop constraint ‘ . eb sn coletenn, NAME ¢ éu alley Loble tenchanialrap primary keg let); stencher cfd | ename | saley | exprriee > & pm. kal Martpelaton Language 8 called bit Ihe coommeunals are} SSELFCT 2 INSERT 3+ DFIFIE Ay DPDNIE 4. SELECT: zised “Io zevkeve x to clap lengy feu) table, select’+ rom table name; EX! “select -¥ hom teacher: dala trom databas. eld cyame| salary |, cxpertence I ” 1000 as =) Le B 2000 10 3 ¢ 3000 2 4 [2 4000 | AD SYynhans Sor selec. panticalay altrfbriles * Selecl Agleemn name , colemmecfrom tablenams; ELS gelesk etd Bair rom teacher. etd | salary. ! 1000 2 2000 3 3000 4 4000Pt 4. UPBATBHY Soin ASM a | syntan: 4nsen t Sto Lablename valuesl file, ya | field valuefield value, ---) | ga: Snsert Ento teacher gales (Sy 'E;, 5000 +2 ei 5 ca | ename | salary [apaience 5 E 000 28 tablename valees £ tgatd'y Y tar! Gnsert nto g experience) Z ‘Rename’: bo psalang’ enter value of cid > value of ename eof salary enter value of experience Jj 70W created shane > Nowa pho { WE a 4 enter value ena a “enter Value: of salary enter value of experience, _ enter value of 4, you evedled dhe other Ensert command 6 0 Spaert multiple dala of records [10Ws - . syptan: updale pablersame Pek Aealemen games PINE. where condéton: wet . s1so00 where an: updale teacher set salary ename ='E' & v esiz. 3. DELETE: ypiae: Delele from dablena res. whe we £ cond? Hons 20 ft Delete from teacher wohere eld “Tretd [ename | salary | expertence = 5 keys fn DBMS si | peg-td | Sname | 6ravch code |° mail 4 | F101 | Rams CSE | @@ yz com 9. lese-2 | geoff SF b@ xyz 00” 3. |IT~-103 | grk IT C(O tyr C01 fcu fece-ion | abh? cE A @ tye em key ! keys 2n DBMS are Ht 9S an atyGbule whch doesnt allow duplécatton of dala: dhe dt fferent tapes ofmae: Ls geaper RPays A hey te also atid at wype hey: In our geven Page se pe kegs ae) hel » 1g fal, creat sid peg: Set email Shel 4 7eg. td Sld 4 email | deg tat email ie Seeper Keays are on. fegeawal anbtbele or _ Combearation of aihtbedes, a. Candedale : “a Ms dt fs motheng bit 2 normal sebset of super key he here nfinttmal > means as méngmum FF poss? ble. Sida regi temall, 4 Ue com on sida reh-74 100 combinaliont | sid 4 Cmail aefidd eral A ft ‘ is d Single alts? bules neg ontrtirnal email 3: premny, keg: A premeny key bs a canditols hey In the Given exaraple the canckidale keys cave std fade Ye onalt Oak ey ee three ch woe can chipre sae eS w va vassal——$<—<— See, as POG 45 the altfberle heh as “he Phroary key Fa aa RONG ann ate Gta Adie Aa hey ‘Jere Pad. have choosen i 1g Sel as penny hey. an 4 Alternale hea { ee Allernale kee means when yor choose pony ae comneltelale kez. Shea the vemarneng ae called Silernale ke; In bur exareple 7 allernale keys sve od andere oe Compost oh hey. on dt §s the combfnatton Fi fey atitboles, mm 620 example compostle kegs are Wincias dla t. aehtd 4 ematl peg-9d} ematl old sematl : Sed vrepitd “ 6. Foxéega key: ‘ ‘ Course CA) Student 68) ; ‘ on | exd | creme |... [Romo | awe | cra 0 | Java 101 4 '0 ee python 102 by | 30. SG AEA CHAS be is fe 0 3p | DBMB TS ole a : , | Ole 30 Tortegn key So used to establarh relation between tables-$n he ahage fable column eId"| |-2000 | 10 B | sooo | 20 4000.| 1000] 20 F [ecto] 20: olp: Eeltel. 821m (Sa Sfow Chops 1 executing the above Goes \itoe fill get he sam of al “the valees En the salary « | 4f 3 2ownt 0 Bet The sum of salary of penbeuley department we fave toperformed by Grouping Crouging was done by Grog by class | seppton': Select Golumnss’ | Feom (table name) . | [Where (cco nd€thon J— | Group Bz Ceoltumns) | opHenel [ flovin 6 Ccoluma) yf : Et: Select eptro, seutsal) fom emp Group Py cdeptho; (4 i deptno Sar (sal) 10 6000 20 6000" 30 6000, Vay gh Wiehe bs eo . Ex: we havea table cmid. table, yea ranking) By vorSting thes Sauaie insatiskey dose st éeleck year) count aR Sidon m 00% Group Bey or Wav: BAI eRe \ch ies ees, Be dans | 4 ‘| Select yer count contd Sra? movie. Group Py ea, Mavtng count (itd) BE0 chs | Tyan | eoxnt erie) 148 shah Cate eee | 2000 20, | - v4 ‘ 2002 i.) Ay y AS clause : f a Martane} student : [naive lage " gender ‘ | Fam 9 M Suresh 92) Mocfs koja ape F fawshé | Woe] F bhanw | 12>) & of joant 1p vename acelamn name we welAs? clause... |. tect Kio &: t cum n, yrtae eélect cblumnn name aai new, col 4 _ Tame —from, table name ; 2 Fr Tiree leaor) 4 Stra Asal Et: select Name as s Name rom student Rie a J| leney age ‘gencler [ ger 4 Mm 3G Stiemetie aperators: cere aie five types af Afthmetie eperatnre ahey Pe 4: pddtiow C4) be EDS 2 geibhaction C~) 3 Metplzeation C#) A Gesion c/) ce Module devesion (2) Sis gars select 5410: i olp: 15 | sal > select 1-S: | ole. 5 | Qus Select 04S lp: sD | Sqir select 10]6 5 Opes SY * gq ooakenys 2 sql» select o/s; a olp: o \ ofp: 1989-12-15 ee lh £4: select popome (19941-1201 interval 25 month), olp: 194) -2-20 an Ex geleel-a 0b bATE ("1AN4» 130) Snteroat 28 gree? Olp: zou —n-20 Carat) aan pal2 sugpare CD: il the | Shes method $6 wed to-sabtiac values For the | geen dale ‘ £4: Select subdate ('1984-N1-20', tnterval 25 009); lp: 1189-10-26 man); En gelect subdate Cine ne20,Snterval @ | } ol aa i a3). ‘ bdok Ci as yeas): £1: Seleck satel Ctiha9-u-20'fnteval re d . Olp: . 1964219426 «: 2 copparec): Tk gles ~the carrent dale of “the system: et. select conelale (', ofp: P0f4-o1-15 A CURTINAEC): St gives the current time of the system Ed: elect cuRTIMEC); olp: 8:12:10 Nemerte fenetfon Ss: 4. celle: : dhEs zetums she next Apher Entefer valve bape val é4: Select cE yt (55-40), sats Ay olp: 36 rfo 2- Flow oi Sh feanetfon relenns the lovee Jalue &1. gelect flour (85-40) 5 ofp: 86° 3: EXPL): bIS fanclon yetaros affseel 10 the poe? FA Spectsted namber. €x1 select Explsd 3 ofp: 148+ 4131591025966 Ay Pow): hie uncon retams ~he value of the ffambne aatsed to the power of another number’ fur select pow (24)5 ait olp: Dg 5. Greatest: IASs ferncBon retarns the greatest value among the Est of arguments. Et: Select Greatest ($,3,1,74, 1") 6/3,3/8D: ofp: 87 6 Least es: DALs fenclon returns -the least value of ~the 19st of argements i St: Select least (615,11 7H E14); ofp: |° : DAES 6500 | ca | V\doseph > | oslozlras3 + 6008 | CPN edward, |, oalofnay i) ¢-400 | cay || (Mather >) galoijini «| 4,009.) Tee | \ Tosegh asdodlinan, tio,o00 Lacan: || veg 04|nz|1998 10,000 TR | peter | asloulinas | 11/080 |) com Ermeson “l oajosliaay 10,500 | TCR | | bh select + Sroro Eroplgee; Select 10. char (Empsalarg:'4 44,999,999 stom Employes: Select 4 from employe: Eee > [emp Name |EMP\ BOB |EmPsniney, Emp Pince | | a ene ” oalosftaro $5000 | eRN 2 | Jam@b s\shoelouliay: | 46500.) can 3: | Jackson oafose | $6,500 | can a Bet osloslinag | ¢6:000 | can | 5 | edivard petoslinay | 4-600, can to. | Mathew aalotliaaa | #151000 | eR | 20-| Joseph oglosliaay | 410,000 | can Bo) yer7y oalostiang.; [4.19002 | Tee 8{0-| peter osloulmasy [41/000 cA so | erteso? olos}inay j$tosoo | Tereee Sag £4! gelect aos chav Cempaleb, ‘ow monl, ywyy)afrom, ‘ ‘ "employees i i wip SALAY | |i 6, | a8 oF mrayp inno] 4 S000 «) Gackob* | ogth apy taal | 46,600 ' | og? ai) 19a 6 S00 05!” Febs 1993) $ 61000 5, opp Janj1994| gs) F00° * oath sais, ao} 41510004 ‘) Jackson ‘| Joseph *Veduaaid Mather’ ‘| Joseph os feb) 190 | 10,000 Hee Mar, 1448'| $10,000 Terry pit peter... .| 0s? -pphr 199s | $111 000 exteson 24 mays 794 $10100% Saal %s a sfmple elemm, ‘wile wohSch' Psi created asa part of DBMS deftuzae Enstaltation- dual. consists. of ontz-one' colamn cohose clatatype Ss varehar2CV-dhe valee Sn cthe column 46 called! as Bie An % tls . Sa: Select + fiom dial! ‘ olp: x. cee ex. gelect * lta hots deals: tae olp: 8. eee ett tye ;| Sab datatype Sal Sala tape 4 yy — ; ~ Alamere Vo Sabe®. Toon) me ' chances _ time ‘ | | Sor storing 4. BEE s.char 4. dale Tson-file LS Bteter a.ncha >. goleime BE use ' dntege o s Ison dala (8. SeRmel o Bray 5. Time Lzype- do store| ‘A. Float 4 Varchar 4 go XML-ftle we (8: Louble use x mL dala | ‘or | Pasfe gat: SQL Stands for structured Buery Language It Ste 8s a standard langzage Sor ee anel markpalaling DB. All the RDBM, [Eke Mysalis nccess , oracle sobase, Thforme, postgres: and SAL servers. Users use S01 ab -the Standard database language what can a SO1 does $s as—followe:PS he =) Executes querng against a DB. = Reference dala—from a 0B - = Insert records In DB > cp data records 9 DB = Betete records fn Pb => creale grew PB 23' “Cyeale new tables.in DB. , = creale views #7? DB