top of page

Results found for ""

  • SQL Case Statement in Oracle

    SQL Case Statement in Oracle Oracle SQL CASE statement gives you the flexibility to use sql IF ELSE logic in a SELECT, WHERE and JOIN clause. Note: same CASE statement is used in PL/SQL blocks. Select CASE SQL For example, let us assume we need to count the number of employees whose salary is less than 2000, between 2000 & 4000 and above 4000. The below query helps counting such distinct values from a single column itself SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3 FROM employees; COUNT1 COUNT2 COUNT3 ---------- ---------- ---------- 0 43 64 With the output we can see that There are 0 employees whose salary is less than 2000 There are 43 employees whose salary is above 2000 and below 4000 There are 64 employees whose salary is grater than 4000 SQL Select CASE Example 1 For each customer in the sample customers table, the following sql query lists the credit limit as Low if it equals $100, High if it equals $5000, and Medium if it equals anything else SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END FROM customers; CUST_LAST_NAME CASECR -------------------- ------ ... Bogart Medium Nolte Medium Loren Medium Gueney Medium SQL Select CASE Example 2 We would like to output the name of the department for the corresponding department number. For all the departments from 10 to 40 have their department name values listed, the ELSE is used to deal with any other depart number other than listed (10 to 40) SELECT ename, empno, deptno (CASE deptno WHEN 10 THEN 'Accounting' WHEN 20 THEN 'Research' WHEN 30 THEN 'Sales' WHEN 40 THEN 'Operations' ELSE 'Unknown' END) department FROM emp ORDER BY ename; SQL Select CASE Example 3 The following sql query finds the average salary of the employees in the sample table employees, using $2000 as the lowest salary possible SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" FROM employees e; Average Salary -------------- 6461.68224 Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Add ISO image storage repository on XenServer

    Add ISO image storage repository on XenServer If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With Citrix XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Create repository Create ISO directory Access your XenServer via ssh and create a directory that will hold ISO images. mkdir /var/opt/ISO Use FTP client like WinSCP to copy ISO files to above directory. Create repository Now its time to register our new directory as storage repository with xenserver xe sr-create name-label=ISO_IMAGES_LOCAL type=iso device-config:location=/var/opt/ISO device-config:legacy_mode=true content-type=iso The new storage repository was created. To list your XenServer storage repository run: xe sr-list Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • NEO4j Basic Commands

    NEO4j Basic Commands CREATE Create a node We will use Cypher to generate a small social graph. CREATE (ee:Person {name: 'Emil', from: 'Sweden', kloutScore: 99}) () indicates the node. ee:Person – ee is the node variable and Person is the node label. {} contains the properties that describe the node. MATCH Find nodes Now, find the node representing Emil. MATCH (ee:Person) WHERE = 'Emil' RETURN ee; MATCH specifies a pattern of nodes and relationships. (ee:Person) is a single node pattern with label Person. It assigns matches to the variable ee. WHERE filters the query. = 'Emil' compares name property to the value Emil. RETURN returns particular results. Now, CREATE more data Nodes and relationships The CREATE clause can create many nodes and relationships at once. MATCH (ee:Person) WHERE = 'Emil' CREATE (js:Person { name: 'Johan', from: 'Sweden', learn: 'surfing' }), (ir:Person { name: 'Ian', from: 'England', title: 'author' }), (rvb:Person { name: 'Rik', from: 'Belgium', pet: 'Orval' }), (ally:Person { name: 'Allison', from: 'California', hobby: 'surfing' }), (ee)-[:KNOWS {since: 2001}]->(js),(ee)-[:KNOWS {rating: 5}]->(ir), (js)-[:KNOWS]->(ir),(js)-[:KNOWS]->(rvb), (ir)-[:KNOWS]->(js),(ir)-[:KNOWS]->(ally), (rvb)-[:KNOWS]->(ally) MATCH patterns Describe what to find in the graph For instance, a pattern can be used to find Emil's friends: MATCH (ee:Person)-[:KNOWS]-(friends) WHERE = 'Emil' RETURN ee, friends MATCH describes what nodes will be retrieved based upon the pattern. (ee) is the node reference that will be returned based upon the WHERE clause. -[:KNOWS]- matches the KNOWS relationships (in either direction) from ee. (friends) represents the nodes that are Emil's friends. RETURN returns the node, referenced here by (ee), and the related (friends) nodes found. More Example, Movie Graph Guide The Movie Graph is a mini graph application, containing actors and directors that are related through the movies they have collaborated on. This guide shows how to: Load: Insert movie data into the graph. Constrain: Create unique node property constraints. Index: Index nodes based on their labels. Find: Retrieve individual movies and actors. Query: Discover related actors and directors. Solve The Bacon Path. Create Create the movie graph Use the following code block to create the movie graph. It contains a single Cypher query statement composed of multiple CREATE clauses. NOTE: This guide assumes that you use an empty graph. If it contains data, see page 9 on how to clean it up. CREATE (TheMatrix:Movie {title:'The Matrix', released:1999, tagline:'Welcome to the Real World'}) CREATE (Keanu:Person {name:'Keanu Reeves', born:1964}) CREATE (Carrie:Person {name:'Carrie-Anne Moss', born:1967}) CREATE (Laurence:Person {name:'Laurence Fishburne', born:1961}) CREATE (Hugo:Person {name:'Hugo Weaving', born:1960}) CREATE (LillyW:Person {name:'Lilly Wachowski', born:1967}) CREATE (LanaW:Person {name:'Lana Wachowski', born:1965}) CREATE (JoelS:Person {name:'Joel Silver', born:1952}) CREATE (Keanu)-[:ACTED_IN {roles:['Neo']}]->(TheMatrix), (Carrie)-[:ACTED_IN {roles:['Trinity']}]->(TheMatrix), (Laurence)-[:ACTED_IN {roles:['Morpheus']}]->(TheMatrix), (Hugo)-[:ACTED_IN {roles:['Agent Smith']}]->(TheMatrix), (LillyW)-[:DIRECTED]->(TheMatrix), (LanaW)-[:DIRECTED]->(TheMatrix), (JoelS)-[:PRODUCED]->(TheMatrix) CREATE (Emil:Person {name:"Emil Eifrem", born:1978}) CREATE (Emil)-[:ACTED_IN {roles:["Emil"]}]->(TheMatrix) CREATE (TheMatrixReloaded:Movie {title:'The Matrix Reloaded', released:2003, tagline:'Free your mind'}) CREATE (Keanu)-[:ACTED_IN {roles:['Neo']}]->(TheMatrixReloaded), (Carrie)-[:ACTED_IN {roles:['Trinity']}]->(TheMatrixReloaded), (Laurence)-[:ACTED_IN {roles:['Morpheus']}]->(TheMatrixReloaded), (Hugo)-[:ACTED_IN {roles:['Agent Smith']}]->(TheMatrixReloaded), (LillyW)-[:DIRECTED]->(TheMatrixReloaded), (LanaW)-[:DIRECTED]->(TheMatrixReloaded), (JoelS)-[:PRODUCED]->(TheMatrixReloaded) CREATE (TheMatrixRevolutions:Movie {title:'The Matrix Revolutions', released:2003, tagline:'Everything that has a beginning has an end'}) CREATE (Keanu)-[:ACTED_IN {roles:['Neo']}]->(TheMatrixRevolutions), (Carrie)-[:ACTED_IN {roles:['Trinity']}]->(TheMatrixRevolutions), (Laurence)-[:ACTED_IN {roles:['Morpheus']}]->(TheMatrixRevolutions), (Hugo)-[:ACTED_IN {roles:['Agent Smith']}]->(TheMatrixRevolutions), (LillyW)-[:DIRECTED]->(TheMatrixRevolutions), (LanaW)-[:DIRECTED]->(TheMatrixRevolutions), (JoelS)-[:PRODUCED]->(TheMatrixRevolutions) CREATE (TheDevilsAdvocate:Movie {title:"The Devil's Advocate", released:1997, tagline:'Evil has its winning ways'}) CREATE (Charlize:Person {name:'Charlize Theron', born:1975}) CREATE (Al:Person {name:'Al Pacino', born:1940}) CREATE (Taylor:Person {name:'Taylor Hackford', born:1944}) CREATE (Keanu)-[:ACTED_IN {roles:['Kevin Lomax']}]->(TheDevilsAdvocate), (Charlize)-[:ACTED_IN {roles:['Mary Ann Lomax']}]->(TheDevilsAdvocate), (Al)-[:ACTED_IN {roles:['John Milton']}]->(TheDevilsAdvocate), (Taylor)-[:DIRECTED]->(TheDevilsAdvocate) CREATE (AFewGoodMen:Movie {title:"A Few Good Men", released:1992, tagline:"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth."}) CREATE (TomC:Person {name:'Tom Cruise', born:1962}) CREATE (JackN:Person {name:'Jack Nicholson', born:1937}) CREATE (DemiM:Person {name:'Demi Moore', born:1962}) CREATE (KevinB:Person {name:'Kevin Bacon', born:1958}) CREATE (KieferS:Person {name:'Kiefer Sutherland', born:1966}) CREATE (NoahW:Person {name:'Noah Wyle', born:1971}) CREATE (CubaG:Person {name:'Cuba Gooding Jr.', born:1968}) CREATE (KevinP:Person {name:'Kevin Pollak', born:1957}) CREATE (JTW:Person {name:'J.T. Walsh', born:1943}) CREATE (JamesM:Person {name:'James Marshall', born:1967}) CREATE (ChristopherG:Person {name:'Christopher Guest', born:1948}) CREATE (RobR:Person {name:'Rob Reiner', born:1947}) CREATE (AaronS:Person {name:'Aaron Sorkin', born:1961}) CREATE (TomC)-[:ACTED_IN {roles:['Lt. Daniel Kaffee']}]->(AFewGoodMen), (JackN)-[:ACTED_IN {roles:['Col. Nathan R. Jessup']}]->(AFewGoodMen), (DemiM)-[:ACTED_IN {roles:['Lt. Cdr. JoAnne Galloway']}]->(AFewGoodMen), (KevinB)-[:ACTED_IN {roles:['Capt. Jack Ross']}]->(AFewGoodMen), (KieferS)-[:ACTED_IN {roles:['Lt. Jonathan Kendrick']}]->(AFewGoodMen), (NoahW)-[:ACTED_IN {roles:['Cpl. Jeffrey Barnes']}]->(AFewGoodMen), (CubaG)-[:ACTED_IN {roles:['Cpl. Carl Hammaker']}]->(AFewGoodMen), (KevinP)-[:ACTED_IN {roles:['Lt. Sam Weinberg']}]->(AFewGoodMen), (JTW)-[:ACTED_IN {roles:['Lt. Col. Matthew Andrew Markinson']}]->(AFewGoodMen), (JamesM)-[:ACTED_IN {roles:['Pfc. Louden Downey']}]->(AFewGoodMen), (ChristopherG)-[:ACTED_IN {roles:['Dr. Stone']}]->(AFewGoodMen), (AaronS)-[:ACTED_IN {roles:['Man in Bar']}]->(AFewGoodMen), (RobR)-[:DIRECTED]->(AFewGoodMen), (AaronS)-[:WROTE]->(AFewGoodMen) CREATE (TopGun:Movie {title:"Top Gun", released:1986, tagline:'I feel the need, the need for speed.'}) CREATE (KellyM:Person {name:'Kelly McGillis', born:1957}) CREATE (ValK:Person {name:'Val Kilmer', born:1959}) CREATE (AnthonyE:Person {name:'Anthony Edwards', born:1962}) CREATE (TomS:Person {name:'Tom Skerritt', born:1933}) CREATE (MegR:Person {name:'Meg Ryan', born:1961}) CREATE (TonyS:Person {name:'Tony Scott', born:1944}) CREATE (JimC:Person {name:'Jim Cash', born:1941}) CREATE (TomC)-[:ACTED_IN {roles:['Maverick']}]->(TopGun), (KellyM)-[:ACTED_IN {roles:['Charlie']}]->(TopGun), (ValK)-[:ACTED_IN {roles:['Iceman']}]->(TopGun), (AnthonyE)-[:ACTED_IN {roles:['Goose']}]->(TopGun), (TomS)-[:ACTED_IN {roles:['Viper']}]->(TopGun), (MegR)-[:ACTED_IN {roles:['Carole']}]->(TopGun), (TonyS)-[:DIRECTED]->(TopGun), (JimC)-[:WROTE]->(TopGun) CREATE (JerryMaguire:Movie {title:'Jerry Maguire', released:2000, tagline:'The rest of his life begins now.'}) CREATE (ReneeZ:Person {name:'Renee Zellweger', born:1969}) CREATE (KellyP:Person {name:'Kelly Preston', born:1962}) CREATE (JerryO:Person {name:"Jerry O'Connell", born:1974}) CREATE (JayM:Person {name:'Jay Mohr', born:1970}) CREATE (BonnieH:Person {name:'Bonnie Hunt', born:1961}) CREATE (ReginaK:Person {name:'Regina King', born:1971}) CREATE (JonathanL:Person {name:'Jonathan Lipnicki', born:1996}) CREATE (CameronC:Person {name:'Cameron Crowe', born:1957}) CREATE (TomC)-[:ACTED_IN {roles:['Jerry Maguire']}]->(JerryMaguire), (CubaG)-[:ACTED_IN {roles:['Rod Tidwell']}]->(JerryMaguire), (ReneeZ)-[:ACTED_IN {roles:['Dorothy Boyd']}]->(JerryMaguire), (KellyP)-[:ACTED_IN {roles:['Avery Bishop']}]->(JerryMaguire), (JerryO)-[:ACTED_IN {roles:['Frank Cushman']}]->(JerryMaguire), (JayM)-[:ACTED_IN {roles:['Bob Sugar']}]->(JerryMaguire), (BonnieH)-[:ACTED_IN {roles:['Laurel Boyd']}]->(JerryMaguire), (ReginaK)-[:ACTED_IN {roles:['Marcee Tidwell']}]->(JerryMaguire), (JonathanL)-[:ACTED_IN {roles:['Ray Boyd']}]->(JerryMaguire), (CameronC)-[:DIRECTED]->(JerryMaguire), (CameronC)-[:PRODUCED]->(JerryMaguire), (CameronC)-[:WROTE]->(JerryMaguire) CREATE (StandByMe:Movie {title:"Stand By Me", released:1986, tagline:"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of."}) CREATE (RiverP:Person {name:'River Phoenix', born:1970}) CREATE (CoreyF:Person {name:'Corey Feldman', born:1971}) CREATE (WilW:Person {name:'Wil Wheaton', born:1972}) CREATE (JohnC:Person {name:'John Cusack', born:1966}) CREATE (MarshallB:Person {name:'Marshall Bell', born:1942}) CREATE (WilW)-[:ACTED_IN {roles:['Gordie Lachance']}]->(StandByMe), (RiverP)-[:ACTED_IN {roles:['Chris Chambers']}]->(StandByMe), (JerryO)-[:ACTED_IN {roles:['Vern Tessio']}]->(StandByMe), (CoreyF)-[:ACTED_IN {roles:['Teddy Duchamp']}]->(StandByMe), (JohnC)-[:ACTED_IN {roles:['Denny Lachance']}]->(StandByMe), (KieferS)-[:ACTED_IN {roles:['Ace Merrill']}]->(StandByMe), (MarshallB)-[:ACTED_IN {roles:['Mr. Lachance']}]->(StandByMe), (RobR)-[:DIRECTED]->(StandByMe) CREATE (AsGoodAsItGets:Movie {title:'As Good as It Gets', released:1997, tagline:'A comedy from the heart that goes for the throat.'}) CREATE (HelenH:Person {name:'Helen Hunt', born:1963}) CREATE (GregK:Person {name:'Greg Kinnear', born:1963}) CREATE (JamesB:Person {name:'James L. Brooks', born:1940}) CREATE (JackN)-[:ACTED_IN {roles:['Melvin Udall']}]->(AsGoodAsItGets), (HelenH)-[:ACTED_IN {roles:['Carol Connelly']}]->(AsGoodAsItGets), (GregK)-[:ACTED_IN {roles:['Simon Bishop']}]->(AsGoodAsItGets), (CubaG)-[:ACTED_IN {roles:['Frank Sachs']}]->(AsGoodAsItGets), (JamesB)-[:DIRECTED]->(AsGoodAsItGets) CREATE (WhatDreamsMayCome:Movie {title:'What Dreams May Come', released:1998, tagline:'After life there is more. The end is just the beginning.'}) CREATE (AnnabellaS:Person {name:'Annabella Sciorra', born:1960}) CREATE (MaxS:Person {name:'Max von Sydow', born:1929}) CREATE (WernerH:Person {name:'Werner Herzog', born:1942}) CREATE (Robin:Person {name:'Robin Williams', born:1951}) CREATE (VincentW:Person {name:'Vincent Ward', born:1956}) CREATE (Robin)-[:ACTED_IN {roles:['Chris Nielsen']}]->(WhatDreamsMayCome), (CubaG)-[:ACTED_IN {roles:['Albert Lewis']}]->(WhatDreamsMayCome), (AnnabellaS)-[:ACTED_IN {roles:['Annie Collins-Nielsen']}]->(WhatDreamsMayCome), (MaxS)-[:ACTED_IN {roles:['The Tracker']}]->(WhatDreamsMayCome), (WernerH)-[:ACTED_IN {roles:['The Face']}]->(WhatDreamsMayCome), (VincentW)-[:DIRECTED]->(WhatDreamsMayCome) CREATE (SnowFallingonCedars:Movie {title:'Snow Falling on Cedars', released:1999, tagline:'First loves last. Forever.'}) CREATE (EthanH:Person {name:'Ethan Hawke', born:1970}) CREATE (RickY:Person {name:'Rick Yune', born:1971}) CREATE (JamesC:Person {name:'James Cromwell', born:1940}) CREATE (ScottH:Person {name:'Scott Hicks', born:1953}) CREATE (EthanH)-[:ACTED_IN {roles:['Ishmael Chambers']}]->(SnowFallingonCedars), (RickY)-[:ACTED_IN {roles:['Kazuo Miyamoto']}]->(SnowFallingonCedars), (MaxS)-[:ACTED_IN {roles:['Nels Gudmundsson']}]->(SnowFallingonCedars), (JamesC)-[:ACTED_IN {roles:['Judge Fielding']}]->(SnowFallingonCedars), (ScottH)-[:DIRECTED]->(SnowFallingonCedars) CREATE (YouveGotMail:Movie {title:"You've Got Mail", released:1998, tagline:'At odds in life... in love on-line.'}) CREATE (ParkerP:Person {name:'Parker Posey', born:1968}) CREATE (DaveC:Person {name:'Dave Chappelle', born:1973}) CREATE (SteveZ:Person {name:'Steve Zahn', born:1967}) CREATE (TomH:Person {name:'Tom Hanks', born:1956}) CREATE (NoraE:Person {name:'Nora Ephron', born:1941}) CREATE (TomH)-[:ACTED_IN {roles:['Joe Fox']}]->(YouveGotMail), (MegR)-[:ACTED_IN {roles:['Kathleen Kelly']}]->(YouveGotMail), (GregK)-[:ACTED_IN {roles:['Frank Navasky']}]->(YouveGotMail), (ParkerP)-[:ACTED_IN {roles:['Patricia Eden']}]->(YouveGotMail), (DaveC)-[:ACTED_IN {roles:['Kevin Jackson']}]->(YouveGotMail), (SteveZ)-[:ACTED_IN {roles:['George Pappas']}]->(YouveGotMail), (NoraE)-[:DIRECTED]->(YouveGotMail) CREATE (SleeplessInSeattle:Movie {title:'Sleepless in Seattle', released:1993, tagline:'What if someone you never met, someone you never saw, someone you never knew was the only someone for you?'}) CREATE (RitaW:Person {name:'Rita Wilson', born:1956}) CREATE (BillPull:Person {name:'Bill Pullman', born:1953}) CREATE (VictorG:Person {name:'Victor Garber', born:1949}) CREATE (RosieO:Person {name:"Rosie O'Donnell", born:1962}) CREATE (TomH)-[:ACTED_IN {roles:['Sam Baldwin']}]->(SleeplessInSeattle), (MegR)-[:ACTED_IN {roles:['Annie Reed']}]->(SleeplessInSeattle), (RitaW)-[:ACTED_IN {roles:['Suzy']}]->(SleeplessInSeattle), (BillPull)-[:ACTED_IN {roles:['Walter']}]->(SleeplessInSeattle), (VictorG)-[:ACTED_IN {roles:['Greg']}]->(SleeplessInSeattle), (RosieO)-[:ACTED_IN {roles:['Becky']}]->(SleeplessInSeattle), (NoraE)-[:DIRECTED]->(SleeplessInSeattle) CREATE (JoeVersustheVolcano:Movie {title:'Joe Versus the Volcano', released:1990, tagline:'A story of love, lava and burning desire.'}) CREATE (JohnS:Person {name:'John Patrick Stanley', born:1950}) CREATE (Nathan:Person {name:'Nathan Lane', born:1956}) CREATE (TomH)-[:ACTED_IN {roles:['Joe Banks']}]->(JoeVersustheVolcano), (MegR)-[:ACTED_IN {roles:['DeDe', 'Angelica Graynamore', 'Patricia Graynamore']}]->(JoeVersustheVolcano), (Nathan)-[:ACTED_IN {roles:['Baw']}]->(JoeVersustheVolcano), (JohnS)-[:DIRECTED]->(JoeVersustheVolcano) CREATE (WhenHarryMetSally:Movie {title:'When Harry Met Sally', released:1998, tagline:'Can two friends sleep together and still love each other in the morning?'}) CREATE (BillyC:Person {name:'Billy Crystal', born:1948}) CREATE (CarrieF:Person {name:'Carrie Fisher', born:1956}) CREATE (BrunoK:Person {name:'Bruno Kirby', born:1949}) CREATE (BillyC)-[:ACTED_IN {roles:['Harry Burns']}]->(WhenHarryMetSally), (MegR)-[:ACTED_IN {roles:['Sally Albright']}]->(WhenHarryMetSally), (CarrieF)-[:ACTED_IN {roles:['Marie']}]->(WhenHarryMetSally), (BrunoK)-[:ACTED_IN {roles:['Jess']}]->(WhenHarryMetSally), (RobR)-[:DIRECTED]->(WhenHarryMetSally), (RobR)-[:PRODUCED]->(WhenHarryMetSally), (NoraE)-[:PRODUCED]->(WhenHarryMetSally), (NoraE)-[:WROTE]->(WhenHarryMetSally) CREATE (ThatThingYouDo:Movie {title:'That Thing You Do', released:1996, tagline:'In every life there comes a time when that thing you dream becomes that thing you do'}) CREATE (LivT:Person {name:'Liv Tyler', born:1977}) CREATE (TomH)-[:ACTED_IN {roles:['Mr. White']}]->(ThatThingYouDo), (LivT)-[:ACTED_IN {roles:['Faye Dolan']}]->(ThatThingYouDo), (Charlize)-[:ACTED_IN {roles:['Tina']}]->(ThatThingYouDo), (TomH)-[:DIRECTED]->(ThatThingYouDo) CREATE (TheReplacements:Movie {title:'The Replacements', released:2000, tagline:'Pain heals, Chicks dig scars... Glory lasts forever'}) CREATE (Brooke:Person {name:'Brooke Langton', born:1970}) CREATE (Gene:Person {name:'Gene Hackman', born:1930}) CREATE (Orlando:Person {name:'Orlando Jones', born:1968}) CREATE (Howard:Person {name:'Howard Deutch', born:1950}) CREATE (Keanu)-[:ACTED_IN {roles:['Shane Falco']}]->(TheReplacements), (Brooke)-[:ACTED_IN {roles:['Annabelle Farrell']}]->(TheReplacements), (Gene)-[:ACTED_IN {roles:['Jimmy McGinty']}]->(TheReplacements), (Orlando)-[:ACTED_IN {roles:['Clifford Franklin']}]->(TheReplacements), (Howard)-[:DIRECTED]->(TheReplacements) CREATE (RescueDawn:Movie {title:'RescueDawn', released:2006, tagline:"Based on the extraordinary true story of one man's fight for freedom"}) CREATE (ChristianB:Person {name:'Christian Bale', born:1974}) CREATE (ZachG:Person {name:'Zach Grenier', born:1954}) CREATE (MarshallB)-[:ACTED_IN {roles:['Admiral']}]->(RescueDawn), (ChristianB)-[:ACTED_IN {roles:['Dieter Dengler']}]->(RescueDawn), (ZachG)-[:ACTED_IN {roles:['Squad Leader']}]->(RescueDawn), (SteveZ)-[:ACTED_IN {roles:['Duane']}]->(RescueDawn), (WernerH)-[:DIRECTED]->(RescueDawn) CREATE (TheBirdcage:Movie {title:'The Birdcage', released:1996, tagline:'Come as you are'}) CREATE (MikeN:Person {name:'Mike Nichols', born:1931}) CREATE (Robin)-[:ACTED_IN {roles:['Armand Goldman']}]->(TheBirdcage), (Nathan)-[:ACTED_IN {roles:['Albert Goldman']}]->(TheBirdcage), (Gene)-[:ACTED_IN {roles:['Sen. Kevin Keeley']}]->(TheBirdcage), (MikeN)-[:DIRECTED]->(TheBirdcage) CREATE (Unforgiven:Movie {title:'Unforgiven', released:1992, tagline:"It's a hell of a thing, killing a man"}) CREATE (RichardH:Person {name:'Richard Harris', born:1930}) CREATE (ClintE:Person {name:'Clint Eastwood', born:1930}) CREATE (RichardH)-[:ACTED_IN {roles:['English Bob']}]->(Unforgiven), (ClintE)-[:ACTED_IN {roles:['Bill Munny']}]->(Unforgiven), (Gene)-[:ACTED_IN {roles:['Little Bill Daggett']}]->(Unforgiven), (ClintE)-[:DIRECTED]->(Unforgiven) CREATE (JohnnyMnemonic:Movie {title:'Johnny Mnemonic', released:1995, tagline:'The hottest data on earth. In the coolest head in town'}) CREATE (Takeshi:Person {name:'Takeshi Kitano', born:1947}) CREATE (Dina:Person {name:'Dina Meyer', born:1968}) CREATE (IceT:Person {name:'Ice-T', born:1958}) CREATE (RobertL:Person {name:'Robert Longo', born:1953}) CREATE (Keanu)-[:ACTED_IN {roles:['Johnny Mnemonic']}]->(JohnnyMnemonic), (Takeshi)-[:ACTED_IN {roles:['Takahashi']}]->(JohnnyMnemonic), (Dina)-[:ACTED_IN {roles:['Jane']}]->(JohnnyMnemonic), (IceT)-[:ACTED_IN {roles:['J-Bone']}]->(JohnnyMnemonic), (RobertL)-[:DIRECTED]->(JohnnyMnemonic) CREATE (CloudAtlas:Movie {title:'Cloud Atlas', released:2012, tagline:'Everything is connected'}) CREATE (HalleB:Person {name:'Halle Berry', born:1966}) CREATE (JimB:Person {name:'Jim Broadbent', born:1949}) CREATE (TomT:Person {name:'Tom Tykwer', born:1965}) CREATE (DavidMitchell:Person {name:'David Mitchell', born:1969}) CREATE (StefanArndt:Person {name:'Stefan Arndt', born:1961}) CREATE (TomH)-[:ACTED_IN {roles:['Zachry', 'Dr. Henry Goose', 'Isaac Sachs', 'Dermot Hoggins']}]->(CloudAtlas), (Hugo)-[:ACTED_IN {roles:['Bill Smoke', 'Haskell Moore', 'Tadeusz Kesselring', 'Nurse Noakes', 'Boardman Mephi', 'Old Georgie']}]->(CloudAtlas), (HalleB)-[:ACTED_IN {roles:['Luisa Rey', 'Jocasta Ayrs', 'Ovid', 'Meronym']}]->(CloudAtlas), (JimB)-[:ACTED_IN {roles:['Vyvyan Ayrs', 'Captain Molyneux', 'Timothy Cavendish']}]->(CloudAtlas), (TomT)-[:DIRECTED]->(CloudAtlas), (LillyW)-[:DIRECTED]->(CloudAtlas), (LanaW)-[:DIRECTED]->(CloudAtlas), (DavidMitchell)-[:WROTE]->(CloudAtlas), (StefanArndt)-[:PRODUCED]->(CloudAtlas) CREATE (TheDaVinciCode:Movie {title:'The Da Vinci Code', released:2006, tagline:'Break The Codes'}) CREATE (IanM:Person {name:'Ian McKellen', born:1939}) CREATE (AudreyT:Person {name:'Audrey Tautou', born:1976}) CREATE (PaulB:Person {name:'Paul Bettany', born:1971}) CREATE (RonH:Person {name:'Ron Howard', born:1954}) CREATE (TomH)-[:ACTED_IN {roles:['Dr. Robert Langdon']}]->(TheDaVinciCode), (IanM)-[:ACTED_IN {roles:['Sir Leight Teabing']}]->(TheDaVinciCode), (AudreyT)-[:ACTED_IN {roles:['Sophie Neveu']}]->(TheDaVinciCode), (PaulB)-[:ACTED_IN {roles:['Silas']}]->(TheDaVinciCode), (RonH)-[:DIRECTED]->(TheDaVinciCode) CREATE (VforVendetta:Movie {title:'V for Vendetta', released:2006, tagline:'Freedom! Forever!'}) CREATE (NatalieP:Person {name:'Natalie Portman', born:1981}) CREATE (StephenR:Person {name:'Stephen Rea', born:1946}) CREATE (JohnH:Person {name:'John Hurt', born:1940}) CREATE (BenM:Person {name: 'Ben Miles', born:1967}) CREATE (Hugo)-[:ACTED_IN {roles:['V']}]->(VforVendetta), (NatalieP)-[:ACTED_IN {roles:['Evey Hammond']}]->(VforVendetta), (StephenR)-[:ACTED_IN {roles:['Eric Finch']}]->(VforVendetta), (JohnH)-[:ACTED_IN {roles:['High Chancellor Adam Sutler']}]->(VforVendetta), (BenM)-[:ACTED_IN {roles:['Dascomb']}]->(VforVendetta), (JamesM)-[:DIRECTED]->(VforVendetta), (LillyW)-[:PRODUCED]->(VforVendetta), (LanaW)-[:PRODUCED]->(VforVendetta), (JoelS)-[:PRODUCED]->(VforVendetta), (LillyW)-[:WROTE]->(VforVendetta), (LanaW)-[:WROTE]->(VforVendetta) CREATE (SpeedRacer:Movie {title:'Speed Racer', released:2008, tagline:'Speed has no limits'}) CREATE (EmileH:Person {name:'Emile Hirsch', born:1985}) CREATE (JohnG:Person {name:'John Goodman', born:1960}) CREATE (SusanS:Person {name:'Susan Sarandon', born:1946}) CREATE (MatthewF:Person {name:'Matthew Fox', born:1966}) CREATE (ChristinaR:Person {name:'Christina Ricci', born:1980}) CREATE (Rain:Person {name:'Rain', born:1982}) CREATE (EmileH)-[:ACTED_IN {roles:['Speed Racer']}]->(SpeedRacer), (JohnG)-[:ACTED_IN {roles:['Pops']}]->(SpeedRacer), (SusanS)-[:ACTED_IN {roles:['Mom']}]->(SpeedRacer), (MatthewF)-[:ACTED_IN {roles:['Racer X']}]->(SpeedRacer), (ChristinaR)-[:ACTED_IN {roles:['Trixie']}]->(SpeedRacer), (Rain)-[:ACTED_IN {roles:['Taejo Togokahn']}]->(SpeedRacer), (BenM)-[:ACTED_IN {roles:['Cass Jones']}]->(SpeedRacer), (LillyW)-[:DIRECTED]->(SpeedRacer), (LanaW)-[:DIRECTED]->(SpeedRacer), (LillyW)-[:WROTE]->(SpeedRacer), (LanaW)-[:WROTE]->(SpeedRacer), (JoelS)-[:PRODUCED]->(SpeedRacer) CREATE (NinjaAssassin:Movie {title:'Ninja Assassin', released:2009, tagline:'Prepare to enter a secret world of assassins'}) CREATE (NaomieH:Person {name:'Naomie Harris'}) CREATE (Rain)-[:ACTED_IN {roles:['Raizo']}]->(NinjaAssassin), (NaomieH)-[:ACTED_IN {roles:['Mika Coretti']}]->(NinjaAssassin), (RickY)-[:ACTED_IN {roles:['Takeshi']}]->(NinjaAssassin), (BenM)-[:ACTED_IN {roles:['Ryan Maslow']}]->(NinjaAssassin), (JamesM)-[:DIRECTED]->(NinjaAssassin), (LillyW)-[:PRODUCED]->(NinjaAssassin), (LanaW)-[:PRODUCED]->(NinjaAssassin), (JoelS)-[:PRODUCED]->(NinjaAssassin) CREATE (TheGreenMile:Movie {title:'The Green Mile', released:1999, tagline:"Walk a mile you'll never forget."}) CREATE (MichaelD:Person {name:'Michael Clarke Duncan', born:1957}) CREATE (DavidM:Person {name:'David Morse', born:1953}) CREATE (SamR:Person {name:'Sam Rockwell', born:1968}) CREATE (GaryS:Person {name:'Gary Sinise', born:1955}) CREATE (PatriciaC:Person {name:'Patricia Clarkson', born:1959}) CREATE (FrankD:Person {name:'Frank Darabont', born:1959}) CREATE (TomH)-[:ACTED_IN {roles:['Paul Edgecomb']}]->(TheGreenMile), (MichaelD)-[:ACTED_IN {roles:['John Coffey']}]->(TheGreenMile), (DavidM)-[:ACTED_IN {roles:['Brutus "Brutal" Howell']}]->(TheGreenMile), (BonnieH)-[:ACTED_IN {roles:['Jan Edgecomb']}]->(TheGreenMile), (JamesC)-[:ACTED_IN {roles:['Warden Hal Moores']}]->(TheGreenMile), (SamR)-[:ACTED_IN {roles:['"Wild Bill" Wharton']}]->(TheGreenMile), (GaryS)-[:ACTED_IN {roles:['Burt Hammersmith']}]->(TheGreenMile), (PatriciaC)-[:ACTED_IN {roles:['Melinda Moores']}]->(TheGreenMile), (FrankD)-[:DIRECTED]->(TheGreenMile) CREATE (FrostNixon:Movie {title:'Frost/Nixon', released:2008, tagline:'400 million people were waiting for the truth.'}) CREATE (FrankL:Person {name:'Frank Langella', born:1938}) CREATE (MichaelS:Person {name:'Michael Sheen', born:1969}) CREATE (OliverP:Person {name:'Oliver Platt', born:1960}) CREATE (FrankL)-[:ACTED_IN {roles:['Richard Nixon']}]->(FrostNixon), (MichaelS)-[:ACTED_IN {roles:['David Frost']}]->(FrostNixon), (KevinB)-[:ACTED_IN {roles:['Jack Brennan']}]->(FrostNixon), (OliverP)-[:ACTED_IN {roles:['Bob Zelnick']}]->(FrostNixon), (SamR)-[:ACTED_IN {roles:['James Reston, Jr.']}]->(FrostNixon), (RonH)-[:DIRECTED]->(FrostNixon) CREATE (Hoffa:Movie {title:'Hoffa', released:1992, tagline:"He didn't want law. He wanted justice."}) CREATE (DannyD:Person {name:'Danny DeVito', born:1944}) CREATE (JohnR:Person {name:'John C. Reilly', born:1965}) CREATE (JackN)-[:ACTED_IN {roles:['Hoffa']}]->(Hoffa), (DannyD)-[:ACTED_IN {roles:['Robert "Bobby" Ciaro']}]->(Hoffa), (JTW)-[:ACTED_IN {roles:['Frank Fitzsimmons']}]->(Hoffa), (JohnR)-[:ACTED_IN {roles:['Peter "Pete" Connelly']}]->(Hoffa), (DannyD)-[:DIRECTED]->(Hoffa) CREATE (Apollo13:Movie {title:'Apollo 13', released:1995, tagline:'Houston, we have a problem.'}) CREATE (EdH:Person {name:'Ed Harris', born:1950}) CREATE (BillPax:Person {name:'Bill Paxton', born:1955}) CREATE (TomH)-[:ACTED_IN {roles:['Jim Lovell']}]->(Apollo13), (KevinB)-[:ACTED_IN {roles:['Jack Swigert']}]->(Apollo13), (EdH)-[:ACTED_IN {roles:['Gene Kranz']}]->(Apollo13), (BillPax)-[:ACTED_IN {roles:['Fred Haise']}]->(Apollo13), (GaryS)-[:ACTED_IN {roles:['Ken Mattingly']}]->(Apollo13), (RonH)-[:DIRECTED]->(Apollo13) CREATE (Twister:Movie {title:'Twister', released:1996, tagline:"Don't Breathe. Don't Look Back."}) CREATE (PhilipH:Person {name:'Philip Seymour Hoffman', born:1967}) CREATE (JanB:Person {name:'Jan de Bont', born:1943}) CREATE (BillPax)-[:ACTED_IN {roles:['Bill Harding']}]->(Twister), (HelenH)-[:ACTED_IN {roles:['Dr. Jo Harding']}]->(Twister), (ZachG)-[:ACTED_IN {roles:['Eddie']}]->(Twister), (PhilipH)-[:ACTED_IN {roles:['Dustin "Dusty" Davis']}]->(Twister), (JanB)-[:DIRECTED]->(Twister) CREATE (CastAway:Movie {title:'Cast Away', released:2000, tagline:'At the edge of the world, his journey begins.'}) CREATE (RobertZ:Person {name:'Robert Zemeckis', born:1951}) CREATE (TomH)-[:ACTED_IN {roles:['Chuck Noland']}]->(CastAway), (HelenH)-[:ACTED_IN {roles:['Kelly Frears']}]->(CastAway), (RobertZ)-[:DIRECTED]->(CastAway) CREATE (OneFlewOvertheCuckoosNest:Movie {title:"One Flew Over the Cuckoo's Nest", released:1975, tagline:"If he's crazy, what does that make you?"}) CREATE (MilosF:Person {name:'Milos Forman', born:1932}) CREATE (JackN)-[:ACTED_IN {roles:['Randle McMurphy']}]->(OneFlewOvertheCuckoosNest), (DannyD)-[:ACTED_IN {roles:['Martini']}]->(OneFlewOvertheCuckoosNest), (MilosF)-[:DIRECTED]->(OneFlewOvertheCuckoosNest) CREATE (SomethingsGottaGive:Movie {title:"Something's Gotta Give", released:2003}) CREATE (DianeK:Person {name:'Diane Keaton', born:1946}) CREATE (NancyM:Person {name:'Nancy Meyers', born:1949}) CREATE (JackN)-[:ACTED_IN {roles:['Harry Sanborn']}]->(SomethingsGottaGive), (DianeK)-[:ACTED_IN {roles:['Erica Barry']}]->(SomethingsGottaGive), (Keanu)-[:ACTED_IN {roles:['Julian Mercer']}]->(SomethingsGottaGive), (NancyM)-[:DIRECTED]->(SomethingsGottaGive), (NancyM)-[:PRODUCED]->(SomethingsGottaGive), (NancyM)-[:WROTE]->(SomethingsGottaGive) CREATE (BicentennialMan:Movie {title:'Bicentennial Man', released:1999, tagline:"One robot's 200 year journey to become an ordinary man."}) CREATE (ChrisC:Person {name:'Chris Columbus', born:1958}) CREATE (Robin)-[:ACTED_IN {roles:['Andrew Marin']}]->(BicentennialMan), (OliverP)-[:ACTED_IN {roles:['Rupert Burns']}]->(BicentennialMan), (ChrisC)-[:DIRECTED]->(BicentennialMan) CREATE (CharlieWilsonsWar:Movie {title:"Charlie Wilson's War", released:2007, tagline:"A stiff drink. A little mascara. A lot of nerve. Who said they couldn't bring down the Soviet empire."}) CREATE (JuliaR:Person {name:'Julia Roberts', born:1967}) CREATE (TomH)-[:ACTED_IN {roles:['Rep. Charlie Wilson']}]->(CharlieWilsonsWar), (JuliaR)-[:ACTED_IN {roles:['Joanne Herring']}]->(CharlieWilsonsWar), (PhilipH)-[:ACTED_IN {roles:['Gust Avrakotos']}]->(CharlieWilsonsWar), (MikeN)-[:DIRECTED]->(CharlieWilsonsWar) CREATE (ThePolarExpress:Movie {title:'The Polar Express', released:2004, tagline:'This Holiday Season... Believe'}) CREATE (TomH)-[:ACTED_IN {roles:['Hero Boy', 'Father', 'Conductor', 'Hobo', 'Scrooge', 'Santa Claus']}]->(ThePolarExpress), (RobertZ)-[:DIRECTED]->(ThePolarExpress) CREATE (ALeagueofTheirOwn:Movie {title:'A League of Their Own', released:1992, tagline:'Once in a lifetime you get a chance to do something different.'}) CREATE (Madonna:Person {name:'Madonna', born:1954}) CREATE (GeenaD:Person {name:'Geena Davis', born:1956}) CREATE (LoriP:Person {name:'Lori Petty', born:1963}) CREATE (PennyM:Person {name:'Penny Marshall', born:1943}) CREATE (TomH)-[:ACTED_IN {roles:['Jimmy Dugan']}]->(ALeagueofTheirOwn), (GeenaD)-[:ACTED_IN {roles:['Dottie Hinson']}]->(ALeagueofTheirOwn), (LoriP)-[:ACTED_IN {roles:['Kit Keller']}]->(ALeagueofTheirOwn), (RosieO)-[:ACTED_IN {roles:['Doris Murphy']}]->(ALeagueofTheirOwn), (Madonna)-[:ACTED_IN {roles:['"All the Way" Mae Mordabito']}]->(ALeagueofTheirOwn), (BillPax)-[:ACTED_IN {roles:['Bob Hinson']}]->(ALeagueofTheirOwn), (PennyM)-[:DIRECTED]->(ALeagueofTheirOwn) CREATE (PaulBlythe:Person {name:'Paul Blythe'}) CREATE (AngelaScope:Person {name:'Angela Scope'}) CREATE (JessicaThompson:Person {name:'Jessica Thompson'}) CREATE (JamesThompson:Person {name:'James Thompson'}) CREATE (JamesThompson)-[:FOLLOWS]->(JessicaThompson), (AngelaScope)-[:FOLLOWS]->(JessicaThompson), (PaulBlythe)-[:FOLLOWS]->(AngelaScope) CREATE (JessicaThompson)-[:REVIEWED {summary:'An amazing journey', rating:95}]->(CloudAtlas), (JessicaThompson)-[:REVIEWED {summary:'Silly, but fun', rating:65}]->(TheReplacements), (JamesThompson)-[:REVIEWED {summary:'The coolest football movie ever', rating:100}]->(TheReplacements), (AngelaScope)-[:REVIEWED {summary:'Pretty funny at times', rating:62}]->(TheReplacements), (JessicaThompson)-[:REVIEWED {summary:'Dark, but compelling', rating:85}]->(Unforgiven), (JessicaThompson)-[:REVIEWED {summary:"Slapstick redeemed only by the Robin Williams and Gene Hackman's stellar performances", rating:45}]->(TheBirdcage), (JessicaThompson)-[:REVIEWED {summary:'A solid romp', rating:68}]->(TheDaVinciCode), (JamesThompson)-[:REVIEWED {summary:'Fun, but a little far fetched', rating:65}]->(TheDaVinciCode), (JessicaThompson)-[:REVIEWED {summary:'You had me at Jerry', rating:92}]->(JerryMaguire) WITH TomH as a MATCH (a)-[:ACTED_IN]->(m)<-[:DIRECTED]-(d) RETURN a,m,d LIMIT 10; Create constraints Unique node property constraints Create unique node property constraints to ensure that property values are unique for all nodes with a specific label. Adding the unique constraint implicitly adds an index on that property. CREATE CONSTRAINT ON (n:Movie) ASSERT (n.title) IS UNIQUE CREATE CONSTRAINT ON (n:Person) ASSERT ( IS UNIQUE Index nodes Create indexes on one or more properties for all nodes that have a given label. Indexes are used to increase search performance. CREATE INDEX FOR (m:Movie) ON (m.released) Find Find individual nodes Run any of the following query examples. Notice the syntax pattern. Try looking for other movies or actors. Find the actor named "Tom Hanks": MATCH (tom:Person {name: "Tom Hanks"}) RETURN tom Find the movie with the title "Cloud Atlas": MATCH (cloudAtlas:Movie {title: "Cloud Atlas"}) RETURN cloudAtlas Find 10 people and return their names: MATCH (people:Person) RETURN LIMIT 10 Find movies released in the 1990s and return their titles. MATCH (nineties:Movie) WHERE nineties.released >= 1990 AND nineties.released < 2000 RETURN nineties.title Query Find patterns Use the type of the relationship to find patterns within the graph, for example, ACTED_IN or DIRECTED. What other relationships exist? What movies did Tom Hanks act in? MATCH (tom:Person {name: "Tom Hanks"})-[:ACTED_IN]->(tomHanksMovies) RETURN tom,tomHanksMovies Who directed "Cloud Atlas"? MATCH (cloudAtlas:Movie {title: "Cloud Atlas"})<-[:DIRECTED]-(directors) RETURN Who were Tom Hanks' co-actors? MATCH (tom:Person {name:"Tom Hanks"})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors) RETURN DISTINCT How people are related to "Cloud Atlas"? MATCH (people:Person)-[relatedTo]-(:Movie {title: "Cloud Atlas"}) RETURN, Type(relatedTo), relatedTo.roles Solve Six Degrees of Kevin Bacon You might have heard of the classic "Six Degrees of Kevin Bacon". That is simply the shortest path between two nodes, called the "Bacon Path". Use variable length patterns to find movies and actors up to 4 "hops" away from Kevin Bacon. MATCH (bacon:Person {name:"Kevin Bacon"})-[*1..4]-(hollywood) RETURN DISTINCT hollywood Use the built-in shortestPath() algorithm to find the "Bacon Path" to Meg Ryan. MATCH p=shortestPath( (bacon:Person {name:"Kevin Bacon"})-[*]-(meg:Person {name:"Meg Ryan"}) ) RETURN p Recommend Recommend new co-actors Let's recommend new co-actors for Tom Hanks. A basic recommendation approach is to find connections past an immediate neighborhood that are themselves well connected. For Tom Hanks, that means: Extend Tom Hanks co-actors to find co-co-actors who have nоt worked with Tom Hanks. MATCH (tom:Person {name:"Tom Hanks"})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors), (coActors)-[:ACTED_IN]->(m2)<-[:ACTED_IN]-(cocoActors) WHERE NOT (tom)-[:ACTED_IN]->()<-[:ACTED_IN]-(cocoActors) AND tom <> cocoActors RETURN AS Recommended, count(*) AS Strength ORDER BY Strength DESC Find someone who can introduce Tom Hanks to his potential co-actor, in this case, Tom Cruise. MATCH (tom:Person {name:"Tom Hanks"})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors), (coActors)-[:ACTED_IN]->(m2)<-[:ACTED_IN]-(cruise:Person {name:"Tom Cruise"}) RETURN tom, m, coActors, m2, cruise Clean up Remove the movie data set When you are done experimenting, you can clean up your graph. NOTE: Nodes cannot be deleted if they have relationships, so you need to detach the nodes to delete them. Delete all Movie and Person nodes, and their relationships. MATCH (n) DETACH DELETE n Verify that the Movie Graph has been removed. MATCH (n) RETURN n Example: Northwind Graph Guide From RDBMS to Graph using a classic dataset The Northwind Graph demonstrates how to migrate from a relational database to Neo4j. The transformation is iterative and deliberate, emphasizing the conceptual shift from relational tables to nodes and relationships. This guide shows how to: Load: Load data from external CSV files. Index: Index nodes based on their labels. Relate: Transform foreign key references into data relationships. Promote: Transform join records into relationships. Load product catalog Load the product catalog data from external CSV files Northwind sells food products in a few categories provided by suppliers. Let's start by loading the product catalog tables. The load statements to the right require public internet access.LOAD CSV retrieves a CSV file from a valid URL by applying a Cypher statement to each row using a named map. This example uses the name row. LOAD CSV WITH HEADERS FROM "" AS row CREATE (n:Product) SET n = row, n.unitPrice = toFloat(row.unitPrice), n.unitsInStock = toInteger(row.unitsInStock), n.unitsOnOrder = toInteger(row.unitsOnOrder), n.reorderLevel = toInteger(row.reorderLevel), n.discontinued = (row.discontinued <> "0") Load category data. LOAD CSV WITH HEADERS FROM "" AS row CREATE (n:Category) SET n = row Load supplier data. LOAD CSV WITH HEADERS FROM "" AS row CREATE (n:Supplier) SET n = row Index product catalog data Create node indexes based on labels CREATE INDEX FOR (p:Product) ON (p.productID) CREATE INDEX FOR (p:Product) ON (p.productName) CREATE INDEX FOR (c:Category) ON (c.categoryID) CREATE INDEX FOR (s:Supplier) ON (s.supplierID) Relate product catalog data Transform foreign key references into data relationships The products, categories, and suppliers are related through foreign key references. Let's promote those to data relationships to realize the graph. MATCH (p:Product),(c:Category) WHERE p.categoryID = c.categoryID CREATE (p)-[:PART_OF]->(c) MATCH (p:Product),(s:Supplier) WHERE p.supplierID = s.supplierID CREATE (s)-[:SUPPLIES]->(p) Load customer orders Load customer orders data from external CSV files Northwind customers place orders, which may detail multiple products. LOAD CSV WITH HEADERS FROM "" AS row CREATE (n:Customer) SET n = row Load order data. LOAD CSV WITH HEADERS FROM "" AS row CREATE (n:Order) SET n = row Index customer orders data Create node indexes based on label CREATE INDEX FOR (n:Customer) ON (n.customerID) CREATE INDEX FOR (o:Order) ON (o.orderID) Relate customer orders data Create releationships between customers and orders MATCH (n:Customer),(o:Order) WHERE n.customerID = o.customerID CREATE (n)-[:PURCHASED]->(o) Promote customer orders data Transform join records into relationships Notice that Order Details are always part of an order and that they relate the Order to a Product — they are a join table. Join tables are always a sign of a data relationship, indicating shared information between two other records. Here, you directly promote each OrderDetail record into a relationship in the graph. LOAD CSV WITH HEADERS FROM "" AS row MATCH (p:Product), (o:Order) WHERE p.productID = row.productID AND o.orderID = row.orderID CREATE (o)-[details:ORDERS]->(p) SET details = row, details.quantity = toInteger(row.quantity) Query the Northwind graph Query patterns How many products did each customer purchase? MATCH (cust:Customer)-[:PURCHASED]->(:Order)-[o:ORDERS]->(p:Product), (p)-[:PART_OF]->(c:Category {categoryName:"Produce"}) RETURN DISTINCT cust.contactName as CustomerName, SUM(o.quantity) AS TotalProductsPurchased Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Echo Command in Linux

    Echo Command in Linux One of the highly used yet simple linux commands to print simple output is echo. Linux echo command allows you to print Linux environment variables , create new files, add contents to an existing file and much more. Print Linux Variable Value Print Text Containing Double Quotes Print Text Containing Single Quotes Print Text in Multiple Lines Print Special Characters Create New File or Append Print Output in Specific Colour Print Linux Variable Value To print stored value of a Linux environment variables echo $SHELL echo $HOSTNAME To print variable value with attached characters, use { } export TITLE=MR. echo "$TITLEjohn" --> No output echo "${TITLE}john" Mr.john --> output To print output of a command, enclose command in ( ) echo "Today's date is $(date)" Print Text Containing Double Quotes To print a double quote, enclose it within single quotes or use backslash echo 'This is "Linux"' or echo "This is \"Linux\"" Print Text Containing Single Quotes To print a single quote, enclose it within double quotes echo "This is 'Linux'" Print Text in Multiple Lines To print text in multiple lines, use \n character with -e option echo -e "Dear Sir \n Welcome" Print Special Characters Let's take you would like to actually print $NAME with echo command echo "Use \$NAME variable" Use $NAME variable --> output The backslash (\) will print $ as is and will not consider $NAME as a variable Create New File or Append Use > to create a new file (or overwrite existing file) echo "These are the file contents." > new_file.out Use >> to append at the end of an existing file echo "This is second line in the file." >> new_file.out Print Output in Specific Colour To print output in specific colour in Linux use below commands echo -e "\033[1;37mThis is WHITE colour" echo -e "\033[0;34mThis is BLUE colour" echo -e "\033[0;32mThis is GREEN colour" echo -e "\033[0;31mThis is RED colour" echo -e "\033[0;33mThis is YELLOW colour" echo -e "\033[1;30mThis is GRAY colour" Enjoy! 🏀🏀🏀 Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Automate RMAN Backup using Shell Script

    Automate RMAN Backup using Shell Script In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article, we will look at RMAN backup automation using shell scripts. Create directory structure Create RMAN backup script file Schedule Backup Under Crontab Crontab tool Create directory structure On your database server, create a directory structure to hold RMAN backups and all related files. All the RMAN backups, logs and backup scripts are kept in one directory. If you want to have a different directory structure, its complete up to your environment requirements mkdir -p /u01/rman Create RMAN backup script file Let us create the RMAN backup script file to trigger DB FULL backup vi /u01/rman/ #!/bin/bash . /home/oracle/.bash_profile export ORACLE_SID=proddb export ORACLE_HOME=/u01/app/oracle/product/ export DATE=$(date +%y-%m-%d_%H%M%S) rman target / log=/u01/rman/proddb_${DATE}.log << EOF run { allocate channel ch1 device type disk format '/u01/rman/proddb_full_bkp_%u'; allocate channel ch2 device type disk format '/u01/rman/proddb_full_bkp_%u'; crosscheck backup; delete noprompt obsolete; backup database; backup archivelog all delete input; release channel ch1; release channel ch2; } EOF Schedule Backup Under Crontab Give execute permissions on the shell script chmod 775 /u01/rman/ Now you can go ahead and schedule the backup under the crontab. For example, we are scheduling backup to trigger at 10 am and 4 pm everyday crontab -e 00 10,16 * * * /u01/rman/ Crontab tool Here is a crontab handy tool that I use to accurately define when crontab will execute next. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • How to Check Oracle Instance Size

    How to Check Oracle Instance Size The first step is to understand how instance size is configured. There are two main configurations: One Instance Size Separate SGA and PGA One Instance Size Oracle MEMORY_TARGET parameter defines the total instance size. Example, If MEMORY_TARGET is set to 5 GB, oracle will manage PGA + SGA within 5 GB Show parameter memory_target; If you see memory_target value blank, then separate SGA and PGA are configured The MEMORY_MAX_TARGET parameter defines the maximum RAM that Oracle can use in case of heavy workloads. Example, if MEMORY_TARGET is set to 5 GB and MEMORY_MAX_TARGET is set to 8 GB, then during heavy workloads, Oracle instance can max take upto 8 GB RAM show parameter memory_max_target; Separate SGA and PGA Some databases require separate SGA and PGA configuration for application to work smoothly. The SGA_TARGET defines the SGA size and SGA_MAX_SIZE defines the maximum RAM SGA can take during heavy workloads. Example, if SGA_TARGET is set to 3 GB and SGA_MAX_SIZE is set to 5 GB, then during heavy workloads, SGA can max take upto 5 GB of RAM show parameter sga_target; show parameter sga_max_size; The PGA_AGGREGATE_TARGET defines the PGA size and PGA_AGGREGATE_LIMIT is set to (2 * PGA_AGGREGATE_TARGET ). Example, if PGA_AGGREGATE_TARGET is set to 4GB, then set PGA_AGGREGATE_LIMIT to 8GB (2 * PGA_AGGREGATE_TARGET ) show parameter pga_aggregate_target; show parameter pga_aggregate_limit; Hence, Instance Size= SGA_TARGET+PGA_AGGREGATE_TARGET Further Read: How to increase MEMORY_TARGET Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Loops in Linux Shell Scripts

    Loops in Linux Shell Scripts Loops allow you to execute a set of statements n number of times. Generally a loop re-runs a series of statements until a situation or condition is true. For Loop in Linux Break Loop For Loop in Linux A simple FOR loop defines how many times you would like to run piece of code #!/bin/bash for i in {1..5} do echo "Hello World" done The above FOR LOOP will execute for 5 times. Here i is just a variable (you can use any variable) and {1..5} defines how many times we would like to execute code between do and done. Break Loop To break a loop in-between and exit out, we use BREAK command. The below loop will exit when i is 2 #!/bin/bash for i in {1..5} do if [ $i -eq 2 ]; then break else echo "Hello World" fi done 🐼🐼🐼 Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Oracle 18c to 19c Data Guard Upgrade

    Oracle 18c to 19c Data Guard Upgrade During oracle data guard upgrade, the standby database(s) can be upgraded implicitly via the redo from the primary database, and there is no need to rebuild the standby database after upgrade. Before Upgrade Upgrade Primary Database Install Oracle 19c on Primary Start DBUA on Primary Post Upgrade Enable Redo Log Transport and Apply Install the new Oracle Home on both primary and standby Before Upgrade Stop Data Guard: On primary database, defer the redo log transport to the standby On Primary: =========== SQL> show parameter log_archive_dest_2 SQL> alter system set log_archive_dest_state_2='defer' scope=both; Cancel redolog apply on standby and shutdown the database On Standby: =========== SQL> alter database recover managed standby database cancel; SQL> Shut immediate; Upgrade Primary Database Install the 19c pre-install package on both primary & standby server On Primary & Standby: ===================== yum install -y oracle-database-preinstall-19c Install Oracle 19c on Primary Unzip the 19c software under 19c home and start the runInstaller cd /u01/app/oracle/product/19.3/db_home unzip ./runinstaller Purge the RECYCLEBIN and also check the pre-upgrade summary Create a guaranteed restore point Start DBUA on Primary We wills tart DBUA from 19c home to upgrade 18c database to 19c version Post Oracle Data Guard Upgrade Install new home in standby, Update the listener on the standby host. Be sure to update the Oracle Home information in the listener.ora vi $ORACLE_HOME/network/admin/listener.ora lsnrctl reload export OLD_HOME=/u01/app/oracle/product/18.3/db_home export NEW_HOME=/u01/app/oracle/product/19.3/db_home export ORACLE_HOME=$NEW_HOME export ORACLE_SID=prod #Set ORACLE_UNQNAME to DB_UNIQUE_NAME export ORACLE_UNQNAME=standby Copy TNS #Back up files cp $NEW_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin/tnsnames.ora.backup #Copy from old to new home cp $OLD_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin Now, you can edit /etc/oratab and update the information about the Oracle Home to match the new Oracle Home vi /etc/oratab Copy SPFile and password file to the new Oracle Home cp $OLD_HOME/dbs/orapw $ORACLE_SID $ORACLE_HOME/dbs cp $OLD_HOME/dbs/spfile $ORACLE_SID.ora $ORACLE_HOME/dbs Start the database SQL> startup mount Enable Redo Log Transport and Apply On the primary database re-enable redo log transport to standby database SQL> alter system set log_archive_dest_state_2='enable' scope=both; On the standby database restart redo apply SQL> alter database recover managed standby database disconnect from session; Thank You!! Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Analyze Redo Log With Oracle LogMiner

    Analyze Redo Log With Oracle LogMiner Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against the database. Hence Oracle LogMiner ! Oracle LogMiner is used to read the contents of Redo / Archive log files. In this article we will understand Oracle LogMiner configuration and query the contents of Redo / Archive log files. Enable Supplemental Logging Add Log Files Start LogMiner Query V$LOGMNR_CONTENTS End LogMiner Filtering LogMiner Contents You can run LogMiner to read the contents of Redo / Archive log files on the same database or on a completely separate database! Enable Supplemental Logging The supplemental logging records additional information regarding each transaction into redo log files. You must enable supplemental logging before generating the redo log files that will be analysed by Oracle LogMiner ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; Add Log Files Oracle LogMiner can mine both Redo / Archive log files. Let us assume we would like to analyse all the redo logs inside the database Notice the DBMS_LOGMNR.NEW parameter specifies the first log file to be analysed. The subsequent log files are defined with DBMS_LOGMNR.ADDFILE option EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/u01/db_files/testdb/redo01.log', - OPTIONS => DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/u01/db_files/testdb/redo01.log', - OPTIONS => DBMS_LOGMNR.ADDFILE); EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/u01/db_files/testdb/redo01.log', - OPTIONS => DBMS_LOGMNR.ADDFILE); Alternatively, you can directly give the name of the archive log file EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/u01/FRA/TESTDB/archivelog/o1_mf_1_3027_k6dcc33y_.arc', - OPTIONS => DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/u01/FRA/TESTDB/archivelog/o1_mf_1_3028_k6dcc33y_.arc', - OPTIONS => DBMS_LOGMNR.ADDFILE); EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/u01/FRA/TESTDB/archivelog/o1_mf_1_3029_k6dcc33y_.arc', - OPTIONS => DBMS_LOGMNR.ADDFILE); Start LogMiner If you are starting LogMiner on the same source database, then simply issue EXECUTE DBMS_LOGMNR.START_LOGMNR( - OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); Now you are ready to query V$LOGMNR_CONTENTS view that allows you to see the contents of Redo / Archive log files. Query V$LOGMNR_CONTENTS Let us check redo log files for any queries run against EMPLOYEES table SELECT username, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE seg_owner = 'HR' and seg_name like 'EMPLOYEES'; Every time you query V$LOGMNR_CONTENTS view, Oracle LogMiner must be running to present the data. The data is not stored anywhere inside the database when you query V$LOGMNR_CONTENTS. End LogMiner Oracle LogMiner takes system resources and it does not release those resources until you stop it EXECUTE DBMS_LOGMNR.END_LOGMNR Filtering LogMiner Contents When you add log files and start LogMiner, you can view all the contents of the log files. If the log files are huge, then it's a good idea to use some filters to find out specific transactions. Filter with SCN Number You can filter the log file contents between particular SCN numbers (if you know 😜) EXECUTE DBMS_LOGMNR.START_LOGMNR( - STARTSCN => 280389, - ENDSCN => 351390, - OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); Filter with Date & Time You can filter the log file contents between particular date & time ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; EXECUTE DBMS_LOGMNR.START_LOGMNR( - STARTTIME => '23-Nov-2001 11:23:00', - ENDTIME => '23-Nov-2001 11:43:00'- OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); Showing Only Committed Transactions By default, LogMiner will show both committed and non-committed transactions from the log files. Just in case you would like to filter and see only committed transactions EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => - DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + - DBMS_LOGMNR.COMMITTED_DATA_ONLY); Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Linux Project - Monitor Server Disk Space

    Linux Project - Monitor Server Disk Space In this Linux project we will write a shell script that will monitor the disk space and put the info in a file every 20 mints. Setting up Oracle Linux 7 on Virtual Box How to find disk free Script to log disk space Schedule Script via crontab Setting up Oracle Linux 7 on Virtual Box Follow these detailed steps for the exact process we consistently use to create a virtual machine (VM) and practice Oracle on Oracle VirtualBox. Step-by-Step Guide: Setting Up Oracle Linux 7 on Oracle VirtualBox How to Find Disk Free Space? We use the below command to find the disk free space on Linux df -h Script to Log Disk Space Creating a script to log disk space simplifies monitoring and helps manage storage effectively, ensuring timely updates on disk utilization. Create Scripts Folder: to store and organise various scripts mkdir /tmp/scripts Storage Space log: This file will store storage information touch /tmp/scripts/storage_space.log Permission for Log File: chmod 777 /tmp/scripts/storage_space.log Shell script executing 'df -h' to retrieve and log free disk space information, saving results in storage_space.log with timestamps for each entry #! /bin/bash # To find the free disk space and save it in a log file echo "********************************************" >> /tmp/scripts/storage_space.log date >> /tmp/scripts/storage_space.log echo "********************************************" >> /tmp/scripts/storage_space.log df -h >> /tmp/scripts/storage_space.log # To insert a space between each log entry echo >> /tmp/scripts/storage_space.log Permission for Shell Script: chmod 777 /tmp/scripts/ Schedule Script via crontab Creation of crontab: Automate script execution at 20-minute intervals crontab -e */20 * * * * /tmp/scripts/ Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Generate Execution Plan in Oracle

    Generate Execution Plan in Oracle While working on SQL performance tuning, you must know what execution plan optimiser is generating. This execution plan defines how the SQL statement will be executed. There are multiple methods to generate an SQL plan. You can choose the best method that suits you. AUTOTRACE – easy option EXPLAIN PLAN AUTOTRACE – easy option The autotrace option is used when you want to display the sql execution plan on your screen. The only problem with this option is that the sql query will execute first and then display the execution plan. This might typically be a problem if a SQL statement is taking longer to execute. You need wait until the poor performing sql completes and then will be able to look at the execution plan. set autotrace on; select * from emp; EXPLAIN PLAN The explain plan method does not require query to execute first. It will display the optimiser execution plan without even executing the sql statement explain plan for select * from emp; Run below to show the explain plan on the screen SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Oracle Transportable Tablespace

    Oracle Transportable Tablespace Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Right! Transportable Tablespace (TTS) TTS Activity Copy Files to Target Server On Target Server Limitation of TTS Let us add some complexity – What if IQS schema size is 200 GB and all of IQS schema objects reside under dedicated tablespace IQS_Tbs? If you again go with data pump method, the steps involved are Perform schema export from production server Copy the dump files to test server Perform schema import on test server These steps will take lot of time. Instead, you can use transportable tablespace method to move tablespace from one database to another database. You can even move tablespaces between databases residing on different operating system platforms. Transportable Tablespace (TTS) Ultimately, you can move large amounts of data between databases just simply by moving data file from one database to another database. There are lot of real time context where TTS is used Archiving historical data into separate database Consolidate data from multiple databases into one database Moving data from OLTP database into data warehouse Schema replication when entire schema objects are contained in one single tablespace TTS Activity For activity purpose, I am creating a new tablespace and user on source database CREATE TABLESPACE IQS_TBS DATAFILE '/u01/app/oracle/oradata/proddb/test_data01.dbf' SIZE 1M; CREATE USER IQS IDENTIFIED BY iqs DEFAULT TABLESPACE IQS_TBS TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON IQS_TBS; GRANT CREATE SESSION, CREATE TABLE TO IQS; GRANT SELECT ON HR.EMPLOYEES TO IQS; CONN iqs/iqs CREATE TABLE TEST_EMP AS SELECT * FROM HR.EMPLOYEES; We are going to perform IQS_TBS tablespace transport from prod server to test server. But before transporting, we need to check for any transport violations. Execute below procedure and then query TRANSPORT_SET_VIOLATIONS view for any violations EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'IQS_TBS', incl_constraints => TRUE); PL/SQL procedure successfully completed. SELECT * FROM transport_set_violations; no rows selected Make sure there are no violations. If found, fix it and then proceed by switching tablespace to read only mode SQL> ALTER TABLESPACE IQS_TBS READ ONLY; Tablespace altered. As we are going to use data pump for performing tablespace transport, create export directory SQL> CREATE OR REPLACE DIRECTORY TTS_DIR AS '/tmp'; Export the tablespace metadata using EXPDP. This will only export tablespace meta information into dumpfile expdp directory=tts_dir transport_tablespaces=IQS_TBS dumpfile=IQS_data.dmp logfile=IQS_data.log Once export is done, switch the tablespace back to READ/WRITE mode ALTER TABLESPACE IQS_TBS READ WRITE; Copy Files to Target Server Make sure you are copying below files on test server Copy the IQS_TBS tablespace data file /u01/app/oracle/oradata/proddb/test_data01.dbf on target server /u01/app/oracle/oradata/testdb/test_data01.dbf Copy the dump file IQS_data.dmp to test server /tmp location On Target Server Create a new user on test server which will own all the objects inside the tablespace that we are transporting. Create user only if its not already available on test server. CREATE USER IQS IDENTIFIED BY iqs; GRANT CREATE SESSION, CREATE TABLE TO IQS; Create import directory inside database CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/'; Now import the tablespace metadata using IMPDP impdp directory=temp_dir dumpfile=IQS_data.dmp logfile=import.log transport_datafiles='/u01/app/oracle/oradata/testdb/test_data01.dbf'; Switch the new tablespace into read/write mode on test server ALTER TABLESPACE IQS_TBS READ WRITE; Your tablespace is now available on test server as well!! SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='IQS_TBS'; Limitation of TTS Cannot transport System, Sysaux, Temp or Undo tablespaces Source and target must have same character set If ASM is used on source or on target, use RMAN to transport / convert the tablespace Cannot transport if you have tablespace with same name on target You must have same schema name on target as on source. If you are transporting tablespace owned by IQS schema on source, so you must have IQS schema on target. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Search Results

bottom of page