Scribd
Upload
11 views287 pages

ETL Testing Notes

The document provides an overview of data management concepts, including databases, data warehouses, and ETL processes. It explains the differences between transactional systems and analytical systems, as well as the importance of data normalization and denormalization. Additionally, it discusses various database management systems and reporting tools used in data warehousing and business intelligence.

Uploaded by

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

ETL Testing Notes

The document provides an overview of data management concepts, including databases, data warehouses, and ETL processes. It explains the differences between transactional systems and analytical systems, as well as the importance of data normalization and denormalization. Additionally, it discusses various database management systems and reporting tools used in data warehousing and business intelligence.

Uploaded by

kandukurikeerthishivanand
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
Name [Link]. Date Page Ho. ee /o7/at 2] o«]ai ie foslay Sou SGL_ commands character functions Aggregate functions pier Date functions conditions operators au _operatere constraints Sub-quesies Kor more copies contact : pagemaker 9886600993 Teasars so, | ste Title ese ‘Sin marks - Back-up table Toad fro _ ETL testing _ Introduction Data transformation tests eX pression | Router __| Lookup 8 Joiner vy vata quality scheduling tool Overvied Computer is used to store data im it tn_the table format. * || Tt is Hardaare. % || Softciare ill be installed in et, 7 %|[Gie store date in computer in Two format D Files _ 2) Data Base 7 -» | Data Base? data base is an organized ‘collection of data, Generally stored and accessed elecatyonic~ Lally from ce computer system x |/In Dora bese we Call a 1) Table = Entity — Dock — : ®) Column = Attribute ~Daa wi) Rew = Recayd — wekackio ~ x || Anything we store in table is called Data, - Doctor Entity Attribute (Data) ; ID: NAME |. AREA - SPECIALIZATION J | foot RAM erm Nem 2. E 1908 | Rat | wp nagar | 1oo3 | s RANI Jayanagas REKHA Mar road |_ Metadata :Meta data ‘is data that provides wformation about other data oR Data cbout data is the meta data AM, RAT, BTM ,1GO1 eta — Meta data sbeet a : = Table Name| Column Name|Data type Comments Doctos ID Number In this colum_ F _ we Gril be stor De fc tp J Doctay Name __ thavactey Storing Dactor _ Name ane Doctor ica character storing Doctors / : staying area 7 Doatay Nm-@ Ghovacter| stoving Doctor's _ speaibi zation. —_j[Flat Féle iA flat file includes a table with one.” record per line [Altalte 15 Coflerlie. Lich only Contains Pfeie eat] | *||The different columns in a record are delimeted by a _comma.or Tab to sepayate the fields. * |The data hich has no structure ie called a ' flat cate, : * || The file in cahich data stored is flot is called flat file : ee po, - csv, + Ts0n ety # |The file aihich has no structure ie called as flet_file. csv 3 Gomma Separated value. (Ddiwerer's which are used in fiat file Gonna Gs) nee [Salawtor dhe uo lenS, Ovo Lomclries Semi-Colon (5) Wo Ws fas Single pipe (1) - Double pipe (tl) _ = - Space : _ - Tab i oe Front-End : Front-end refers to the énterface on application that enables accessing tabular’ structure oR Front-end development manages everything that users visually see fivst ip theiy browser or opplic- | ation, (uctrsrwiePce ES cause Fens) — Front-End programming langauges G,Ctt, Java, VB, cH, HTML y PHP, XML Python. Note : Here End means Uses Back-end i The back-end also called the sexver “|[stde, consists of the server cihich provides data lon request, the application. which channels it, and _ the database esbich organize the information. Ex i Glhen a cestomery brousses shoes on a ebsite, they are interacting with the ‘front- end and the Seayching process which: works | behind the scenes formation to [Devewree a: I _Back- end pragsamming databases Oracle MS- SQL-Server , 08-2) HANA 4 Nethaxa 7 ‘My SQL, Tevvadata . etc. Seem leceares sas Gerec ee Intelligence Tools) Qk Reporting Tools. Company- _ | Céystar Report t sap 2) Bussiness objects (80) | 3)| Sses _ Microsoft 9 | Tableau _ —_ Tableau ae 5) ]] Click ~view . a Glick. view : ” S)|MstR (Micro - Stvat egy Company) ete. a ")|[Goqaos i : > rem z 8) lOBTEE Coracle’ Bussiness — + Oracle + inteltigence Enterprize Edétion) Z iy —+ There are two typés of Database. 4) ||D8Ms * Data Base Management System Tt ts the software that handles the | storage, retrieval and updating of data ima ie computer system. 2 RDBMS : Relational pata Base Management System. _Tt is based on the relational model as tovented by Dr. E.F. Godd RpemMs isa DBMS designed speciathy ‘ 7 relational: database, Z ' ee r——C Tr fS rules). out of that 6 rules are used coost ly. Nore: ‘BM company gooms —. Rooms _ _ tang@uge’ : SEQUEL ( S6L) : Database : R-systen 4 = later it is named as ‘0B-9 [Company Name Langauge __clisenate Database Name a | rem [Ee , SEQUEL ,SteScne”) R~ system — (DB-a ANSI ~ SQL _ SQL - PLUS Oracle Microsoft 4) Transaction sQL Ms -s@l oR T~SQL server PR Trans S@b Netteza Gin Se@L _ Netteza 6 | my sev - My SQL % Note 3 ANST s@L is standard langauge -and alt the — tangauges are 60 7. similar to ANSE sQl only 20% features are different. % ||,DaiH ? Data wars house ts a Godown in which past data is’ stored; which we are not usi may use in’ futare ous but Ale No ‘2021 —» 100000) BO~ T= Boat = too 000|_ [6 months date Transaction Systero (Source “Sysfec) rt is also called’ as Online Transaction processing systec CoLTP system) Teunsaction systems are database that record a company’s daily transactions. a) Data-Giare House System (Target System) It_is atso called as Qaling Anal y:tical rocessing System (OLAP - syst Data Gerehouse systems are database that store aggregated, Histovical datu in eultidiemensiooal scheroas, || To develop DGIH we need mininuro “THREE” softaaies - * [From source to target the data is loading = thvough ETL mechanism. — || STL t—eETL stonds for Extract , Trensforrn and © Load A 4 [Te is the. process (mechanism) of loading the. cota’ “fron transaction system (ouTP system) to DGH system (COLAP system) . This ETL mechanism can be EYL ‘Tools ) | Pofermatica poses Centre —+ Infosmatica Campany a | Abinitio + Abinitio Company 3 ||Datastage Talend Sac! ~ DATA GIAREHOUSE (DWH) —~ A Data cmarehouse is a database used fow Business reporting: A DWH is a subject oviented, integrated, time variant aod non-volatile collection of a [acta in support of management's decision peocess. {Characteristics of DH} ||Susrect ORteNTED Aba aan be used to analyse subject area xi Sales can be particular subject: Infosys - eroployee [Link]. | NAME }DEPT. _ 1 | Nisha Schema [2 Ram BHR Laxmi + 4 | Anusha Schema. s | xivti | rT posales i a i | 10,000 : Tt takes less _ Et takes more time Schema 2 A ee database objects. Detabase objects “example able z 6) Package Procedurg 1) Constraint — View __8) Index Funetion Trigger Oz Partition in haed disc ts called Drive lly Pastition in DWH is called Schema: &_|lEvery Schema ill have tables. & |[Enteqrateo 7 A’ data warehouse itegrates Data aa — et | BE tooo Lt IMG woad Rel. fresh stor | Intelligence i -people ase | the pwH data 2300 for reporting © | oe Al sis. & +e 500 Source A and Source 8 may have different esays of identi fy?n _@_ prod. abet ina cata ware house there will be only a single way ef identifying @ prodect, | olascnate Time Variant }_ Ait ~_kept in a DOH, Ex i One can retvieve data “from 3 months, 6 months, or, even oldex deta. from detaw hous Non - Voti tite Once data is in the DGIH, it will not change So, Historical data in a DWH should never be : otterect. Employee (Transactional system) -. DH system Employee eid | uame | Loc T[eia- | mame Br LOC Dn | Hawi | Banglore /chennay/eun| |p Havi |, Banglore af rani | Hyderabad | | a> Roni Hyderabes T 3) | shiva °° Pune 3) ‘Shiva Pane __| | Heri | -Chenn | Hox Pune i | [| Hox Noida _ __=» [Difference between DWH and Gleb Application project Life cycle, O|| Traditional projects stexts with requirement and end with data. & ||Data warehousing projects stoxts with data and _ _ end with requirements, re - 3 AU date warehouses ate dotabases, but not all _____|lastabases Gre date warehouses. : 4 | Basically a database is any system usbich keeps _ _ date in o table format. _ ___8 |/A_dataworehouse is a specially setup database : | designed to hold lterge amounts of data for _ reporting puspos “Types Or DATA Bases ‘There are Two types of Databases. Normal DATABASIS t Novmal Databases is optim for tvansactional activity’ fer keeping a smayl data, amount of data. DATA GQAREHOUSE : A data warehouse will be optimized for lavqe scale reporting. : Giithin a@ DaH data from several systems will typically merged togethey to present a Global enterprise vieur DAH will also typically beep a very long history - from several years to the entire life of the company so that very long term trends cen be viewed . ||Bez_histovicel data is the backbone of any business for mission DIFFERENCE — Normal’ Database Data warehouse Used fox Online Trangacti- 1) Used for Ooline Analytice -on Processing (OLTP), [the user for histor records the data from Processing CO LAP). This records the His tovical data for the users for business decisions. 2) The teblesand joins are gomplex sioce they are The tables and joins art | Simple since they are normalized. This is done de-normelized. This is to veduce redundant data. done to reduce the respo. and +o save storage space. ~nse time for Analytical queries . 3 _| Eatity~ Relational (Ee) 2S Dimension - Modeling modeling techniques are techniques arq used for used for database desiqn. | the ‘Data caretouse cles} 4 | Optimized for covite Optimized for read operation. operation . Performance is low for | High performance for analysis quevies analytical queries. Normalization 1— A_normalization is_a technique of database desi redundancy data which Qe to avoid repeated data ox is used: to reduce to avoid duplicate date, mployee Details Denormalization example . E,Name |[Link]. [[Link]] Dept. | Loa. | 1 [Megha [50,000 [to ET | Banglr . Ha ixti__ | 20.000 20 HR Hyd [3 Neelam | 45000 10 It - Bangly a 4 Preeti | 60000 to IT Beng Ss asika | {S000 20 HR Hyd | o ' ‘ ' t ' — tee p \op00} 5 ‘ . Normalization Employee Details [Link]| [Link] [dept | |i |megha | 50000 | ‘to "| “Le Kirti “20006. | 20 _ _ 3 Neelam {4000 lo | 4 |Preeti |soooo | to | - Ss [Rasika |rsvoo | ab | _ a = 7 i ‘ Tao0e oe | - ~ - Nore ¢ D [Normalization data splits Into many different tables 2)|Each toble represents a separate Entity of the data : 2 [Normalization data ensures the darabase takes up minimal disk space and so it is memory efficient “a the goal of normalization is to reduce and even _|eliminate data redundancy i.e stoving the same __| piece of data more than once. —< | Data Glarehouse Avchitecture 7 exbrack _METADATA Push/Pull ‘Suramar Aggreget at a “Trans formation > Summevi zation agreqarion : sod|e ces staging pata pata Repowti nx Layer Giarehouse mats Layer a s -— erg }—pon|—Te sere SS femme (STE Racemest| 2s net 1 _ _ - 3 s || a DoH | —+ _ «l/s [om Fs ]oan —| 8 J— stg |—+])bay R —— SOURCE staging DOH Repoit ~ Godawe Garrat : al 7 nuh Poiate | Extraction| [#4 Loading [Jno LJ | ety code i transformation — —t a Dara MART Ghat ts Data Mavt 2? A_ Data Mart (DM) is a specific, subject oviented , repository of data designed to answev specific questions for a specific set of users, So am organiz ation could have multiple data marts serving the needs of sates, Masketing etc. A data mart usually is organized as one dimensional model @s a star~Scheme (OLAP cube) made of o fact table and muttiple dimensi on tgble. Ex 2 Finan ——+ DUH {multiple reports we OLTP can generate } sales, sales | HR Franance, (—E Te SL | Enterprise DoH Markt, HR _ maak Fey a ae &e 83 “| sates . |——+ Dato- Mart . {specific repoyts we can generate 1 ED®H Stores abl assaciated with organization. DM Data Maxt stores only one subject in formation, classmate, Types OF DATAMART / APPROACHES = D_| Top-Down Approach 2) || Bottom~Up Approach _ - 2) [Tp—Down Approach :To this the Dot will be on the top of the Data Mart ___»¥ [Tn top-Dowo approach the Data Mart cranes the Data, from DOH _ a) | pottore—Up Approach : In thig the aH is z fon the bottom of Data ‘Mart. 2 %* | ro this approach the Data Mort dvaurs the data from orp. Ghat are the types of Data Mavt ? There are Talo types of Data Mart i & | Dependent Data Mart a> |[Tndependent Data Mast Uy DEPENDENT Dara MART = "Dependent data mart is built by drawing the data from DMN, that already exists ~ 4h | - M[Mavk et INDEPENDENT DATA MART Independent data mart is built by drawing the Data from. GLTP. Ti uses Bottom up, approach . Ih this the Dall Zs built after Datamare , OLTP, Gibat 8s the need of Data Mask ? oR ~ inate g& : Reasons fer creating @ Data Mart ? Need of Datamart 1) || easy access to frequently needed data. a> |[Tmproves end users response time , 8> || Ease of creation. 4> || Lowes cost than implementing a full DAH D> |Time vequired to build Data —mart is less then baw - __ DATA MART DATA WAREHOUSE || A_DM_ stores Depaytment | A_DwH stoves Entevpri- - | data. (A single subject) -xe data (Integration |, oviented) of multiple sources, | #) |[Data mart is designed | pan is designed for} _ fer middle management. Top management. access; = 2 3 Holds only ome subject | Holds multiple subject area, area. 4 fom holds more summari- | DW holds move detailed | ~zed data | daca i : Size is MB to GB Lsixe is G6 to Te ! | a i — [6 |[Desiqning is Easy ' Designing is Difficult. 1 ' + : i i —+ || O_rP LOoling Transaction Processing System. Features of OLTP end operations of the bussiness, OLTP systems stove , update and eebviVe Operational _ Data . Operational data is the data that runs the_buséness, OLTP systems are Accounting system , Banking Application, Payooll. = [es ae Order Management System, etc. _ _ COMS) , aivline resexvation sy stem OLTP Peoperty¥ DOH Response | Sub seconds to Seconds to minutes Time seconds Operations { DML (Data manipul- Primal Read ~ation Langauge) only, Date goes | Paka goes ~ in out 80-60 days or anaTES Fore Tere [i year - 2 years . Current it Historical T pata Application Subject » Time Organ ization, size Smail_to loxge Large to vesy large IES Fea MB to GB Few GB te TB Processes Analysis Activities No. of, One record. at a Thousends to we cords time millions of records DATA MODELING ¢ A peta Modeling is a ‘process Data Base with a set of tables. " =| tyres Or para MODELING os i 5 7 || Entity - Relationship (E-R) Modeling i | Dimenstonal Modeling © ws7[Difference bet? E-& modeling and Dimensional reodeling E-R Modelin Dimensional Medeling | cs ar ¢ Used OLTP. jon |!) Used for OLAP ui - > for epexcttion |1) se fe: appli - | Application. = cation. iT c &) | ze represents logical | zt consists of Facts flocs’ of Entities /objects aad ‘Dimensions, | graphical { poe 3) zt is Novmelized Lb contains Denormati_ ‘ -zed data . : 7 a a —+ ||A van is designed with following types of Scheoas ___D | Ster schema 2D _|| Snow Flake Schema . # | A daicbose architecture (or) Dato Modeler is _ : a ScHeEMA Stay Scheme is a database which cont a centrally located “Fact?! table, which is Surroun- ~ded by, “DIMENSION” tables. boving a relationship of primary "Key and foreign _key betaseen tables | It is Denovmealized , STORE Time LL omension Z pimension | FACT TABLE Prooucr ae DIMENSION CusTOMER DIMENSION * || Dimension Table 2 A table ashich. stores detail data _ is called ‘Dimension Table’ __* [Fact Teble 1A table which stores summarized data is called “Fact table’, * A_stox schema ¢an be simple of complex “Simple Stay Scheenct Simple stay schema only QNE FACT TABLE, Complex Stas Schema 1 Tt consist mouitiple fact tables. —+ | Simple Star Schema Store - D@reension ‘Time — Dimension [Link] | toc? pay | Month | year | _ oe | BrM ag or TAN L020 ~ [mee : oa | saw | -20ap Fact Tale \ Product -sole Fack : : | [etd [t-date frac. sale | bty too; | I-1-20 | s00000 /6 : toag | t-t-20 | 169000 | 4 : too; | 1-20] ;s0000)\2 Z (aoa -28] 80060 Customer, Transaction Dimension Product Dimensom ord] sad] Pad |aty | Price fr-vobe [rad [pname | Price 5e [or [loot | 4. [s0000 loot | rv {sdo00 slo: [10g [3 | 40000 1002 | Laprep | 40000 | 52 Tot {roo | 2 | soooo : | s3 ol 1002 t 40000 ‘ Sy . . 1 y . | - at opp = a _ “ [ts flog [tor [3 fe0000 Co Ses eee : “Tis: ffoa | too | + | spa00 — [5 Tea | ro0al 1 | 4oao - _ Complex Stay Schema Store ;Dimension Table Time-Dimension Table sid Lec? pay {MONTH | YEAR or BTM ot Tan | a02p 02 MGR 0g vAN { acap FACT TABLE (Product sale Fact e \- —_ sizd[ pad |t-date | [Link], |/ eo ti-ae | spqpa0 | oF ‘1-20 | 160000 | - aa mra6 | 180000 |"\ = acs _ A 0g t-1-20| go000] Z 7 —_1\ “Stove. Tots sale / \ \[e-2¢ | [Link] [tor cate. \ oO) | st-1-2020} 1, 60.600 { \ Jee [20 | 8130,0001 i t \ - / _\\ pid Jaty] Price |r-date [Link] |[Link]| price tool | ¢ fp.000 fj. -20 toor [| Tv | S0000 | _ toda | Laptop|40000 | The teble witich Stres Debwled pato Tek Called — Otmarton Tobby ENSTION + inaust 7 2 Mortal 8 y0.¢h A Dimension is a descriptive data_ushich describes the key The Dimensions are organized in a table called Dimension tables Dimension Tables are De-normalized, Each dimension. is represented as _a_single table The primary key in each dimension table is related to a foreign key in the fact table. Ex i Customer, Product —lFacT ke tobe otic, shares. oe # || Ze. Da FAcTS are numeviC. ~Yek 4 ect Pack + # |[Facts are summarized or aggregated , * | Fact is a combination of dimension tables | + ||Facts are business measures ashich used to evaluate the performance of an Bussiness. # [The purpose of this table is to record the sales amount fer each product in each stove eon a daily basis Seles amount jis the fact _ * ex —#||Fact-less Fact Table A_Fact ‘table without any” FACTS. oS -FACT-Less-FAcT TABLES, Ex ; To determine: the promated products 4 that didnit sell. NOTE {Fact table contains cheracteys but Fact value never be a chavactey Fact’ teble aloays created foro Dimension table. Feet table never fact table Suoos FLAK Ke Screma Tt is: cimilay to the ster schema bub snow flake Schema is Normalized into multiple dimension [Link] as star Schema is denormatized .» oR The snow flake schema is an. extension — of the stay schema, where each point of the star explod joto move points. Ex Year —>+ Month — Day store” Time ; — Dimension] : Dimension -f F Fact — __ TABLE customer |/ * Product Dimension ‘Dimension ry Ge, will -have % tables in the above snowflake schema déaqram. A_ table for YEAR, A table for “MONTH and | a table for DAY. is connected to Month, aahich is then = Hybsid Schema i The _combinati D a of $ Star Gilby: coe-use more often stay Schema. 2) The star schema is en important special _ case of the snowflake schema, and it ts - more effective far handling simpler queries. rt is extremely simple to understand ana _ | build. Accessing data fs faster. _ 3) aby cant we use snowflake schema Bex, If uses Normalization .eshich makes performance poor. Nore : Ge can construct dimension tables without fact table. . Gie can’t construnt fact table without ctmension Tables. classmate SOL stands for Stwuctured Query Lonqaage (i974) sau is an ANST.(Americen National Standards Insti standard, but there ere many different versions of the sq. language . (196) SQL is a computer language for storing , mantpulatine aod retvieving data -stored in relation! database, |: is the standard language for Relation Database system . All relational database _management systems like My SQL, Ms access, Oracle , S@L-server use SQL _as standard. language. Glby Ge Use SQL ? 7 Allows users to access [Link] relational database management systems. Allows users to “describe the data. Allows users to create and drop databases aod tables users ‘to create View, stored procedure, functions in a database. —+ ||Dara Types IN SQL ‘) | NumBer (Numeric Data type) * || Numeric data types are number stored in Database columns - _ - ye || The Number data stores Zero, Positive and a neqative aumbers. _ _ * || Exact niimevic types ;-values-where the precision & scale |[peede to be preserved and_the—scole—con—be- floating. | P — Precision is total length. _ . 5 — Scale ts digit’ after decimal . Fhe exact numeric types are Tnteger, 8 Decimal, Numeric... Number’ and Money. * Approximate numeric type : Values where the precision needs to be preserved and the scale can be floating. Ex Double Precision, Float and real. «x |[Fox Number data type size is not mandatory, 7 @cz it takes 38 size by default.) x a) VARCHAR “DATA TYPE - * || rt allows character ancl number and setters, re length data type * Vaschara ses the. amount pecessory to store rn the actual text . - maximum deta size is Gooo bytes Size is mandatory Better for storage space. Saves p place by taking only entered our q_back remaining places of places and returni to database, 3) || Cyan DATA Type *# || Te allows Number., letters and special chavactess, * | Tt is fixed length character data type. * Size 1s not mandatory, bez it takes 1 by fe defacil Maxirourn data size ‘is 2000 byte The size of cher column ig byte-based not choracter based. RULES FoR CREATING TABLE _ Table name should be always character. Table is a combination of number, characters ; spac will pot be access or allowed, = Ex 1 Customey Details: x ~ Cust—Details —~ “ customer Details\— Cust. vetails 3 Dex! 2 customer x Customer -— Caustomer, x 6B.s-1 X Be-si — Length will be minimum 4 character and maximum 30 characters . i Sgt is not a case sensitive, You can use both upper case .and lower case. Feoo table names cant be same. L—is—oot. _ensiti Table name should not contain space .Instead @e cen put underscore '(_) oy double string Ex i customer , cust_details , “customer details” __*) || ta ¢ seme database table, two columns names cant be same, #) || Data base is case sensitive but sql is not, DATE DATA TyPE date data type stores the | year, menth anc day ,as well as hour, minute aod sectnds. Ex : To. chav, Te-date, Months. Beteen etc The standard sQlL commands to interact with relational databases CREATE , SELECT , INSERT, UppaTr , DELETE and [Link] commands can be classified wto groups based on theiv nature. DDL ¢ Data Definition Language Command Description. CREATE creates a New Tabte ALTER Modifies an existing databasel object such as Table . DROP __Deletes an entire table , Foom. the database. RENAME Ge can rename column , i table name, DML ~ Data Manipulation Language - Command Deseviption. Creates a record INSERT _ - . UppaTe Modifies @ record E i feecere _Deletes o reco rc. SELECT ; z= es “pisplay paspose classmate Tel ~ Transaction Control Language Commend Description __|| commit Permanently’ save any transaction. into the databace , ee Rollback Restores the-catabase to last commited . __4 | bet ~ bata Control Lanquage Gommand L Description _ _|[ Grant Gives a previlege ‘to User - Revoke ‘Takes back privileges «grantec i - from user CREATE COMMAND : Create Command is used for creating objects tp the data base. | Tt creates New table, = || Syntex * CREATE TABLE (. 2pate typed , , cote set 5 ENTER = | Exareple _ D | sat > create table customer (10 numbes (10), Name _ varchara (10) ,' cit rehay @Cio); Enter _2)|| sat > create table Samsung (Pid number Cio), - ame vavchar 2 Cio), et model varchar (10)) 5 a) |f sac > “create table Castomer & Co number Cro), IFrame varchar a Cte), thame varchar a (ao) , Date varchar @ 9)) 5 Points to be Remember + ‘|| To cleay the screen, we use | SQL Y CL SCR 2) || To see the table, Column, size and datatype used SQL > DESC SeLECT % FROM TAB! ys de existing tables will display.,* means att the columns, +: Once we create table ,ov alter 4 Data Diationar _ or any other command is applied , then aft _ the created datubases will be saved. here automatically. - fa 5 — | eerors » | revalid Table Name | Any of the rules for table’ name is ‘broken. @ | zdentiftes is toa long 1 2f name o¥ column nome ma exceeds more than 30 characteys. ++ | Note - - - : _ D3 Csemicolen) + Used to end to statement. ~ ~ a) [5 Coomme) = Continuat OF statement ay once you save create the table it will automatically. Duplicate column pame ¢ column ame , | Acter Commano = By using Alter command we can modify the-structure of table such as Add pea column. to existing table, Change date type of any column gor: to modify it - 9 || To rename ex any existing column Size ®) Te Srop or remove existing column. from the table : 9) | Add New Colume to existing Table _ SYNTAX ALTER TABLE ¢TABLE NAMES App NEGO "COLUMN NAMES 2DATA TYPES ) 5 Here # |_ nove D |Gihitle Adding one column, wilt elerays bracket is optionel and comma is aot acceptable Dl The newly added column will always appear as = last column in table, an 3) || Gihile adding more than one columns, bracket ig | + mandatory “and comma (>) is used as’ usual, a) Order of colemns cant be changed but-in Fepork, @e san change the display order of columns ALTER TABLE table—name Aop column nam es kchataty pesgsizey> 5 Ex i ALTER TABLE student _Aop (Address varchave ~t00))3 6) classmate it’s size Change Dota type © “To mod if) Sywrax 2: For single Calumn ALTER TABLE CTABLE NAME> MODIFY ( y s -t-y- 7s Ex: Alter table Customer ‘rodify" C20 number lo), mob pumber Wumber (10) grail varchar? (o)); the size Te increase ov Decrease SYNTAX 2 ALTER TABLE Mopiny ( cnsert te into customer values ! vour created SQL? select # from custamer ED NAME cxty q Manjula BNG “Sharada RwEL CH ULL) Ev 2 €QL>Insert into customer values (3, ‘suathi’, ‘HYD’, *ENDEA’) | #& Tt wont accept extra values. = |e: sat > Insevt into customer values (FD> Name) be SQt>—~Tosert—into—custemerwvatres—Cot, Rarody # || te _cill_ accept and third column will be remain empty . #t is useful when ae dont knoe the informatio ee SQL> Tnsert into customer values (b> NAME) values (4,‘RAIU’) 5 classmate Note : Before saving data it can be undo but t can hot undo. 1 —lloracte DATABASE - 7 : 7 a Redolog File _ - a Dd shiva BNG r{ >> Megha = HYD : ' 74 7 cael —— = = Per manant’ st anit —p] Dota type — [+ Permanant storage . TT 1 Space iti _——— | — L. : i = Burree 3; Buffer is a physical memmoxy storage [used to stove the dato temporary data while a it is being moved from one place 4 another TEL > Teansactian Control language Transaction Control Lanquage Ctct) is used to into logical tvansactions . cammands are ) Commit 2) Rollback . CGonmit t- Commit command is used to save the data from Redoleq File (Suffer File) to data file Cpermanant). Once we commit data coe can -not Rollback * || syntax $ say commrt y 2)|| Rottpack: * | Rollback-restore is a command that causes all data ____|[ebanges since the last begin work . wilt can not undo data from ‘data ‘file’ but it can : undo data from ‘Redolog Féle’ bee ib is not saved, i. + || Gthen we use select * from étable Hame>, % ! shows al records in redolog file ond data file | but the data saved in Redolog file is nok saved, + Once oe close database ee loose data present in e. Redolag fe SYNTAX 2 SQL> ROLLBACK 5 Select STATEMENT SELECT Statements are used to select and view the specifie record ‘present in table. SYNTAX 7°SQLY SELECT SAI “k FROM cahere ZCOLUMN NAME> = >= ZCOLUMN BATAY Note :‘WHERE’ is a-clause which is used to filter the [Link] a table. SQu>. Select * from the SELECT * FROM EMP_NO., EMP NAME xt will Select only Emp_no. and EMP NAME, i 2) || Savy SELECT! * FROM EM P_LNO, WHERE: x TOR 2 ‘MANAGER’. 5* ‘ : Diisql'> serect *« FROM EMP WHERE [Link] = 20 5 on . + * SQL> Setect FROM EMP WHERE SALAey | 5 |/s@u> serect-k FROM EMP OHERE SALARY Z 3000 3 Il — oe _ | 6 |S@L >SELECT # FROM EMP GHERE commission is NULL | Nere 3 Here comméssion wis wot possible, | _ _ _ — 4 7 ||S@L > cetect * FROM EMP COHERE comMissioN i is NoT NULL — _ — — + ~ —t SQt > SELECT % FROM EMP GlitER ==) |NoTE i Ge can not covite EMP No 27445, 7S 2) Gorrect statement TSELECT € FROM EMP WHERE 7 EMP NO IN (7499, 75a!) + NOTE “IN” is a@ function to display more than two values.
SYNTAK ] SELECT K FROM EMP COHERE CCOLUMN NAME _ =N CDATA!? » {DATA 2? 3 a |For ex 3: stow yoss OF MANAGER AND CLEARK SYNTAX { Select ® fworm EMP oyhere JOB in Cimanager’, ftleark’) 5 3) || Lepate “Stat EMENT =~ , _* |lUpdate cratement is used to Uppare the vecovds : present in the table. | [sy using update we can modify the data tn the | table - : 7 | | NOTE : vifferende bet® alter command aad Update | statement [= =| By using ALTER command owe can modi ite the | Structure of table Wher as using UPDATE a L Statement we can “modify, “the data of the table. + | Syntax of Update “statement, SQL> UPDATE CTABLE NAME > SET ¢COLUMN NAME> = Het ups Ex |f sac > update gastamer set Ft selects whole city column. —» | IF we want ‘to UPDATE particular YOu then BQ@L > UPDATE {TAGLE NAME> SET 7 ON> Fr SYNTAK 7 geo, WaME > =) CNEW VALUE> WHERE Update EMP set city = SPune’ where 1D =25 | a |sau > Update customey set Wame o*shiva’ where ro=5; | - | 3) [sou > Update costamer set city ee - city = §BNG’ 5 . . - —L —» || To remove data : —+ JAMED SET fl SYNTAX | SQL > UPDATE Update Customer set céty eNULl where iD=5} ae —» |[to Update multiple fields 5 : : SYNTAX 2°SQL7 UPDATE
aQEW VALU Sau * Update customer set Name efHare? > ciby 2eNg . where id= 4) 4-.'|| DELETE ‘COMMAND + oe By using DELETE cormmand we can delete dota From the table ahd table structure. remain: same. —> || Te delete table Gyuvax i squ>perete 5 - ex?|| Sar > Delete from customer where 1D=3 5 « ae dethi! 5 Gal peleve From customer where city classe, —> | TRuncate Commann aw” Terncate Command will delete the data from the table and the table structure remains the sam SYNTAX 7 S@L > TRUNCATE TABLE 5 Trancate Command -ts a combination ef. ‘Drop’ And ‘cReare’? command, it dvops and recreate the table which is faster than deleting’ vow one by one. Truncate Command is Dp. ar bML 2 Truncate Command is DDL command Bcx tt actua dvops and recreate ‘the table ‘and resets the tables metadata . Difference Between -PEmTE and TRUNCATE . DELETE TRUNCATE Tt will delete whole data DN Truncate ill delete from the table and: table whole data from the tabl structure will remain same] and table structure will remain same,” Once we delete the data fa) Once we delete the dat we can rollback. we can not rollback 3) Ge e dan delete specific 3) Ge can not delete spe- |] sclective data by using|-ciftc selective data ben GIHERE clause, burpcate will wot suppowt @HERE clause. Delete vow by ¥ocw 5) Delete whole data at Low Performance ® High Performance ence, a I. de- allocates @ ||Why data can vollback in DELETE but Not in | TRUNCATE ¢ é In case of DELETE, SQL ‘server removes att the rours from table and records them [n a Redolog féle therefore it can be rollback. But incase of TRUNCATE, Sat server @Slocates Se the data files the table and secords\delocation) | OF the data- filed , therefore it can act vollback. @ ||Gthy Truncate command will __ not support ‘GIHERE” clause 2 : i Ans;|| Bez: truncate command ‘delete tohole date 6 And i we know that Here clause is used to delete t selective ‘data Heoce Troncate Command will not suppost ‘WHERE’ clause Q llothy petere command has Low - performance £ ‘ ‘Abs:|| Delete command ueletes the data voor by your : so it takes: more time Hence it has Low ¢ per fermance. ‘ GQ ||G@by Truncate ~ Command bas High performance 2 TevneaTe command deletes whole data at once | or ata time so it takes ‘very less time hence. 1 TRUNCATE command has High’ performance. Gao we drop cmultiple caturmns S oF PI nop S Yee we can dvop d SVNTAX 1 Albey' table (Table Name> Drop Cotes, col, a i Ex 7 Alter table Emp Dvop (JOB, SALy es) __shessnate CHARACTER FuNcriong By using character function oe Gao convert the data from one format to another format. ENITCAP C) t~ Initial Capital Letter. Tt will change initial letter to capital letter and yemaining as small Letter. TABLE NAN SYNTAX 2 SELECT =NITCAP (ENAME) | From select EmMpNo,ZNITCAP (ENAME) FROM EMP ; a) SELECT empwoO 4s Ip, ENAME AS NAME, SAL OS = Coane, _ 8 Qt Basic from emp; Gatias name _ Note ; oracle ill support’ the query with ov ae without ‘as’ also, i Ex] Select EmPNO, TD. ENAMB EMPLOYEE, SAL BASIC ' From EMP 3 ; : .! = & || uretM () % tert TRIM . fe * || Removes the space from left side of ouv stving. = # | Lrreem ¢) _funetio con be used with constant, 4 |[wentable. ov column of either character oy binay data. ‘ tt - se _ _. Ex SELECT EMPNO, LTRIM (ENAaME)}AS NAME FROM EMP, T é = : = — ue oe ee _ = hs |Irretm() 5 }! SELECT EMPNO, lererm (urrrm() [ LTREMCRTREM C)) 7 “Oo =i : @e can use any one of the above Used“to remove both left and vight side. SYNTAX 1 SELECT LTRIM (RTREM ( } [Link], TeEM C ENAME) AS NAME FROM EMPL 3- Upper FuNcTION () i Tt converts au LOWER cases Into UPPER cases and if it is in alyeady UPPER case, it will, display as it is. SYNTAX 4 SELECT EMPNO, UPPER C) AS £NEG) COL. NAME> FROM FROM
> EMPNO, LOWER CENAME ) 4S NAME FRomM EMPL 5 ) ee NVLC) ¢ NvL is NULL value by using ANVL, ig cae can replace NULL values by some othey values. SYNTAX 7 SELECT EMPNO Neco VALUE) AS FROM § Ex {SELECT EMPNO ,NVL Ceomm,!00) AS comM FRow EMP 5 Ex | SELECT EMPNO, ENAME, NVL(JOB , ‘NO T0B" As s06 FROM EMP 3 : - FeoM EMP’; . 8 ||INvL2 C)t Ge can pass Talo parameters. _ ok By using NVL2 function we can pass Two, parameters . One parameter replaces. the Nutt values and another ee pavametey replaces NOT NULL values. SYNTAX | SELECT EMIPHO, ENAME , NVL2 ( FROM Z2TABLE NAME> ex:||SeLecT EMPNO, ENAME , NVL2 Ccomm,200, 100) As comm From || Seteer “LENGTH ( ‘TeEsTING’ *) Feom ua 5 —» 7 TABLE NAME — CUSTOMER TD | Name |GeNveR - | Manjula FE _ 2 | Darsh ™ 3 Kiran ™ _ 4 | Shweta EF SQL? Seveer ED 4 NAME, LENGTH (NAME) Ag NAME_LE ‘ GENDER » LENGTH (GENDER) AS GEN-LEN FROM customer ro _|_ name NAME-LEN | GENDER | Gen-LEN! 1 Manjula i i e 10 2 Darsh s M 10 3 Kiran. s M 10 & Shweta 6 E 10 _ [Here we have given gender char Con size Te _ to so it is ehowing 10 CGen-lea) bex WRKT chay as is’ fixed type of [Link] tt can't change, _ _ Ext Customes Name warchar2 (10) , Seaden charQo) - a __|ze T Name [Mame ten [Gender | [Genter | _ {| Manjula | 7 j to ef a |_Dawsh _s _ | xian 7 | PeounounGe as instving rNste ©) By. using INSTR function we can get a position of character fram @ st ex:ilsececr rucTR (‘NER TECHNOLOGIES; E” 3) FROM DUAL 5 RESULT 7? ENSTR Cinserectinovost es! ae _ = 6 Xt counts space also bez space is also a character co. NSR_ TEICHNOLOGE ES Ts 3456 band from 3 we have to count bex of cond” Exi2 |Sececr xrusre (‘nse TECHNOLOGLES’, €',7) FROM DUAL 3 RESULT 3 NSR TECHNOLOGIES fom 7 TES EPC TE Teer BAST =15 Bafsecect InsTe (‘NER TECHMOLOGTES", “E7) FROM DUAI RESULT ~ ZNSTR (‘NERTECHNOLOGFES’,“E') © : 2. % af Yau dont mention. from there we have: to calculate it ull Starés frm. staring. —> [NeTE : DUAL table is a empty table which, contains £ rou « || cihatever position they mentioned we have to _ start from’ thet posicion but we have to INSTR FOR COLUMNS SQL> SELECT Tp,NAME ,ENSTR CNAME, ‘A’, 3) as NAME FROM CUSTOMER 5 : = ID NAME 4 It is showing the — 1 Ia ai letter. ‘a in. the fa o Name from 34 3 - 4 posit ionpaee ae ee 4 | Shweta 6 a SUBSTR, C) < By Using substring we can get a substring of @ string oF we can get a piece of characters from a string « Ex Q SElect sugsTR ((NSR TECHNOLOGIES” 5, 4) FRO 2 DUAL 5 7 ete Here — starts from 5* position. ie Pi 4 = Takes 4 characters from 5 positian, RESULT 3 SUBS _ ee TEGH 2) SELECT SuBsTR (“NSR, TECHNOLOGIES’, 5) FROM DUAL 5 — SUBSTR ({NGRT : TECHNOLOG TES %* |[SuestR FoR COLUMNS _ : 7 SyNTAK : SQL> SELECT E'ED,NAME , SUBSTR GNAME, & FROM TO cuT, vPTO cUT ) FROM SELECT. Ip, NAME, SUBSTR (NAME , EMPL} customer 2) FROM peal “GENDER, J i ~ . / _ _ | | eee i anes _ Dealleacaas oe 4 Poe SQL> SELEGT ED, NAME, SUSTRING CNAME, From CUSTOMER } su we SGL > SevecT Zd, MAME, SORSTR (Name, 3)FROM CUSTOMER 3 ID. NAME {suBSTR CN From 3° pasition j —_ : , selects, aU Letters i J DECODE FUNCTION By using decode we can decode values from one format fo another format (Short format to tog forroot) [syntax : Select 1D, NAME (pecove (Z cot. NAMED, [<‘oLp NaME!s , £/NEG NAME’> , ¢*0LD NAME! ). - ~~ -- 9 AS_ FROM | Megha Bangalove — 3 sharada | chennad 4 Shikha. Hyderabad 5 Keerthé Ledéa ( Heve Pune aod oul _ 6 [Neelam [| tnedia — |f veplaced sith indi: QL» SELECr Tp, NAME, DECODE (CTY, “ANG |, “BANGALOR cuyo!, “Hyper aBad’, © cHN', “CHENNAT', MYL (city, ¢rmora’)) AS crtTyY eeom customer 5 T 3 _ zp | NAME ferty t { _ ' Manjula Hyderabad | WL condition — 2 Megha Bang lore | replaces the null 3 shavada Chennai | Value by todia. __ 4 gbi kha Hyde rabad | ace > "Keertbl PUN yr le - Neelam | ENDIA : : u Ascii value of pull (¢ Sa J a) reo ts 13 ASCII FUNCTION CO) Asczt function returns the ASCIL of the character value SYNTAX . SELECT ASCII CetaracteRr ) FROM DUALS NOTE? S@_ is NOT case sensétive but dato in table és a case sensitive. IF data is luppex case tn sar:query clso write in upper case othevatse tt will applies in, ‘show erro and same in tower cases. : AGGRIGATE FUNCTIONS ()i . Ab_aggtigate function. performs a [Link] a sét of values, and ereturns a single value . Expect for count Ce), aggregate funetions iqoore null values. Aggregate f* are often. used with the GROUP BY ‘clause of the SELECT statement, COUNT () sum C) Max CD Aggregate Function. MIN C ) tAVG CD ROUND ( ) Count function can’’ count nuut values, dl cous ©: * * Count function count each and ever column . SYNTAX ¢ SELECT couNT ( 5 3 Count row \2 Joalculace totat vows from selected column nam , - 2) I|selegt count C#) From Emp 5 count # = 12 falways take xb column. fér accurate count: - @ "ap there is no mandatory column then, go 2) [select count Cempowo) From wher dept =20; ~ || Select count ([Link]) feorm emP where DEPT=20 count [Link]) =S 2 || SUM (_) ¢ Gives counts. sum , SYNTAX 1 SELECT sum (écoL. NAME>) FROM SYNTAX { SELEGT MA (COL. NAMES) FROM CTACLE classmate 5) ats Qs ex-Oll serecr max CsA) FROM EMP % = 5000 CRESUIT From EMP tabh) 2) |secect max Csat) FROM EMP HERE DEPT = 20 5 4& || Mxn C) : Gives _mintmum value - SYNTAX | SELECT MEN Cécou NAMES) FROM CTABLE - _NAME > ne = SELECT min CSAL) FROM eBwiP WHERE DEPT 2305 5 lavac 1 Gives average value. wk |i re may give output én decimal value y [re is better to use vound of before average, to get a wound of value, SYNTAX 2 SELECT AMG (¢COL NAMES) FROM
5 Ex ?SELECT AVG ‘(aad FRom EMP * = 2073-214 29 Round function rounds of decimal 6 [ROUND C) values . SYNTAX 7 Sevect ‘ROUND ¢sar) FROM EMP 5" ROUND (2073. 21429) — = ao7g Gig went can exicute rut Jone quer SQL> SELECT “COUNT, Cen NO) AS “ToT EMP 5 [sum CsAl) AS “TOTAL sal”, max (SAL) AS bE HxGHst, MIN-CsaL) Ag Low- SAL FROM EMP TOT-SAL HIGH =SAL TLO@-SAL AVG. SAL 2goas “S006 “800

You might also like