Over the past two years, I've been getting quite familiar with a drug NLM nomenclature called RxNorm.  If you aren't familiar with it, you can read about it here.  Here, I am just going to share some of the sample MySql queries I've written to illustrate the different tables in RxNorm and what you can do with them.  

What follows is written in a way that you should be able to copy and paste it directly into any MySql query editor and run the queries.   It includes my comments which refer to a tool called RxNav.  RxNav is a free, open-source tool that you can learn about here.  Ok, here are the queries:

USE RXNORM;

-- ----------------------------------------
--                CONCEPTS
-- ----------------------------------------

-- get an rxcui for a drug INGREDIENT name

SELECT DISTINCT RXCUI FROM RXNCONSO
WHERE STR LIKE '%AMOXICILLIN%'
AND TTY='IN'
AND SAB='RXNORM';

-- look at RxNav string=Amoxicillin
-- let's look at all the fields from RXNCONSO
-- for Amoxicillin (tty=in) and Amoxicillin 200 mg/5 ml Oral Suspension (tty=scd)

SELECT * FROM RXNCONSO
WHERE RXCUI IN ('723','313850');

-- what is column TS and value P? can find this out in RXNDOC, we'll look at that later

-- fields that have been most useful for me (and what I know something about)

SELECT `RXCUI`,`SAB`,`CODE`,`STR`,`TTY`
FROM RXNCONSO
WHERE RXCUI IN ('723','313850');

-- notice two rxcuis mapped to a wide variety of different SABs, CODEs and STRs
-- also notice different types (TTYs) in the same RXNCONSO table

-- ----------------------------------------
--                RELATIONSHIPS
-- ----------------------------------------

-- first look at RxNav String="Amoxicillin" and the relationships there represented by arrows

-- get all concepts that have 723 AS AN INGREDIENT

SELECT * FROM RXNREL
WHERE RXCUI1 = '723'
AND `SAB`='RXNORM'
AND `RELA`='has_ingredient';

-- can't really tell this from the data, but I am getting BOTH tty=scdc and scdf's
-- because they are both "have_ingredient" of 723
-- if I just want the scdc's, I need to join to rxnconso

SELECT REL.RXCUI1, REL.RELA, REL.RXCUI2, CON.STR
FROM RXNREL AS REL, RXNCONSO AS CON
WHERE REL.RXCUI1 = '723'
AND REL.`RELA`='has_ingredient'
AND REl.`SAB`='RXNORM'
AND REL.RXCUI2 = CON.RXCUI
AND CON.TTY='SCDC'
ORDER BY CON.STR;

-- look at the list of STR, it matches the SCDC window in RxNav

-- at this point, to go further in my crosswalking, i am working with a set - the results of my last query - that
-- I will need to ITERATE through
-- goto RxNorm Overview, III. RxNorm in Use
-- http://www.nlm.nih.gov/research/umls/rxnorm/overview.html
-- so to give you a snapshot of just ONE PASS/ITERATION, I cheated and used RxNav, so I know that the tty=SCDC
-- between 723 and 313850 is 329101 ("Amoxicillin 40 MG/ML")

-- find all the direct discendants of tty=in of 723 (Amoxicillin)

SELECT `RXCUI2`,`RELA`,`RXCUI1` FROM RXNREL
WHERE RXCUI1 = '329101'
AND SAB='RXNORM'
AND `RELA`='consists_of';

-- to replicate this in RxNav, just double click on Amoxicillin 40 MG/ML in SCDC window
-- tie it to RXNCONSO so we can see what these are

SELECT REL.`RXCUI2`, CON.`STR`, CON.`TTY`, REL.`RELA`, REL.`RXCUI1`
FROM RXNREL as REL, RXNCONSO as CON
WHERE REL.`RXCUI1` = '329101'
AND REL.`SAB`='RXNORM'
AND REL.`RELA`='consists_of'
AND REL.`RXCUI2` = CON.`RXCUI`
AND CON.`SAB`='RXNORM'
AND CON.`TTY`!='SY' -- filtering synonymns out
ORDER BY CON.`TTY` DESC;

-- this query will yield 6 results because there are 2 SCDCs and 4 SBDCs
-- descentant from 329101, RXCUI=329101 in RxNav for comparison

-- REMEMBER: this was just ONE PASS of an iteration using 329101, to get all the SCDs and SBDs, I would have to
-- repeat this query for each SCDC that I got back in my earlier query

-- but we don't HAVE TO iterate, we can use "self-joins"
-- i'm going to start simple to illustrate the idea
-- this query gets all the relationships to concepts which consist of SCDCs that
-- have 723 as an ingredient; basically 2 steps at once in the crosswalk

SELECT REL2.`RXCUI2`,REL2.`RELA`,REL2.`RXCUI1`
FROM RXNREL as REL1, RXNREL as REL2
WHERE REL1.RXCUI1 = '723'
AND REL1.SAB='RXNORM'
AND REL1.`RELA`='has_ingredient'
AND REL1.RXCUI2 = REL2.RXCUI1
AND REL2.SAB='RXNORM'
AND REL2.`RELA`='consists_of';

-- keep in mind, it's a longer result set because these are all the
-- even better, tie it to rxnconso; and get just the SCDs descendant from 723

SELECT CON.`RXCUI`, CON.`STR`
FROM RXNREL as REL1, RXNREL as REL2, RXNCONSO AS CON
WHERE REL1.RXCUI1 = '723'
AND REL1.SAB='RXNORM'
AND REL1.`RELA`='has_ingredient'
AND REL1.RXCUI2 = REL2.RXCUI1
AND REL2.SAB='RXNORM'
AND REL2.`RELA`='consists_of'
AND CON.RXCUI=REL2.RXCUI2
AND CON.SAB='RXNORM'
AND CON.`TTY`='SCD'
AND CON.`SUPPRESS`='N' -- i don't think you have to do this in latest version of RxNorm
ORDER BY CON.`TTY` DESC;

-- I get 50, RxNav gets 51, have to check but I think it's because I'm using an older version of RxNorm

-- ----------------------------------------
--                ATTRIBUTES
-- ----------------------------------------

-- --------------------
--    rxnsat table
-- --------------------

-- "Simple Concept and Atom Attributes"

-- get ndcs for Amoxicillin 200 mg/5 ml Oral Susp

select `ATV` from rxnsat
where rxcui='313850'
and `ATN`='NDC';

-- get FDA Uni for Amoxicillin, note these are always associated with RxNorm TTY=IN concepts

select `ATV` from rxnsat
where rxcui='723'
and `ATN`='UNII_CODE';

-- cases where NDC codes associated with same RxNorm form carry multiple meanings
-- see RxNorm Release Documenation for more information:
-- http://www.nlm.nih.gov/research/umls/rxnorm/docs/2009/rxnorm_doco_full12072009.html#s9_0

select `rxcui`, `rxaui`, `code`, `atv`
from rxnsat
where `ATN`='AMBIGUITY_FLAG'
and `ATV`='Base';

-- cases where SCD/SBD forms are further specified by time, ie. 24 HR Extended Release Tablets
-- see RxNorm Release Documenation for more information:
-- http://www.nlm.nih.gov/research/umls/rxnorm/docs/2009/rxnorm_doco_full12072009.html#s10_0

select `rxcui`, `rxaui`, `code`, `atv`
from rxnsat
where `ATN`='RXN_QUANTITY';

-- --------------------
--     rxnsty table
-- --------------------

-- semantic types related to rxcuis

select distinct tui,sty, count(sty) as items
from rxnsty
group by tui,sty
order by items desc;

-- relates to stype columns in tables rxnrel and rxnsat

-- ----------------------------------------
--                DOCUMENTATION
-- ----------------------------------------
-- --------------------
--     rxndoc table
-- --------------------

-- provides documentation for abbreviated columns and values in them

SELECT * FROM RXNDOC;

-- breakdown of the keys and how many values each has

select distinct dockey, count(*)
from rxndoc
group by dockey;

-- example: get ingredient Amoxicillin (rxcui=723) with documentation for "TTY" value

SELECT con.`RXCUI`, con.`STR`, con.`SAB`, con.`CODE`, con.`TTY`, doc.`type`, doc.`expl`
FROM rxndoc as doc, rxnconso as con
WHERE con.`RXCUI`='723'
AND doc.`dockey`='TTY'
AND doc.`VALUE`=con.`TTY`;

-- --------------------
--     rxnsab table
-- --------------------

SELECT * FROM RXNSAB;

-- relates to SAB column in RXNCONSO
-- using a query we i did ealier to get all the concepts in RXNCONSO for 723 and 313850 rxcuis (Amoxicillin and
-- Amoxicillin 200 mg/5 mL), but this time I am adding Source information from RXNSAB by joining to it

SELECT CON.`RXCUI`,CON.`CODE`, CON.`STR`,`TTY`,CON.`SAB`, SAB.`SON`, SAB.`SLC`
FROM RXNCONSO as CON, RXNSAB as SAB
WHERE CON.RXCUI IN ('723','313850')
AND CON.`SAB`=SAB.`RSAB`;