Even more SQL NURS 6803 Clinical DB Design

Even more SQL… NURS 6803 Clinical DB Design Katherine Sward, Ph. D, RN 1

Union queries • Up to this point we joined tables horizontally, with additional columns select demog. pt_id, name, rbc, wbc from demog, blood where demog. pt_id = blood. pt_id name rbc wbc 10 Brown, Mike 5. 62 9. 4 102 Costner, Kevin 3. 32 16 102 Costner, Kevin 3. 55 12 103 Cole, Nat-King 4. 39 5. 8 103 Cole, Nat-King 3. 36 3. 7 104 Mason, Perry 4. 05 7. 5 105 Go, King-Fung 5 7. 5 109 Bond, James 4. 24 12 2

Union queries student (unid, lastname, firstname, email, major) faculty (unid, lastname, firstname, email, department) Get a list of unid, name, and email for all students and faculty Process: create a query for each table, then paste the results together vertically into a single list that is a UNION query 3

Union queries SELECT unid, lastname, firstname email, ‘student’ as Person. Type FROM students UNION SELECT unid, lastname, firstname, email, ‘faculty’ as Person. Type FROM faculty; unid lastname firstname email Person. Type u 00123456789 Smith Sam sam@somewhere. com student u 00123455555 Jones Joe joe@somewhere. com student u 00123457777 Anders Sally sally@somewhere. com student u 00123458888 Williams Greg greg@somewhere. com faculty u 00123456789 Dodge Kathy kathy@somewhere. com faculty 4

Union queries • Queries must be “Union compatible” – return same structure – same number of columns, matching data types (convert if necessary) – columns should conceptually match 5

Self join Recursive relationship: there is a relationship between different rows in the same table 6

Table data employee. ID name date. Of. Hire manager. ID u 0000001 Bossman, Bob 1/1/2005 u 0000002 Overseer, Susie 1/2/2005 u 0000001 u 0000003 Worker, William 2/1/2005 u 0000001 u 0000004 Smith, Sam 2/1/2005 u 0000002 u 0000005 Jones, Jenny 8/15/2010 u 0000002 u 0000006 Little, Larry 8/21/2011 u 0000007 Mann, Macho 7/17/2006 u 0000001 u 0000008 Pipsqueak, Pippy 4/15/2007 u 0000002 u 0000009 Doer, Danny 6/6/2006 u 0000002 u 0000010 Jones, Jeff 2/8/2009 u 0000001 7

Self join • Who are Susie’s employees? or • Show the ID and name for an employee, and the name of the employee’s manager 8

employee. ID name date. Of. Hire manager. ID u 0000001 Bossman, Bob 1/1/2005 u 0000002 Overseer, Susie 1/2/2005 u 0000001 u 0000003 Worker, William 2/1/2005 u 0000001 u 0000004 Smith, Sam 2/1/2005 u 0000002 u 0000005 Jones, Jenny 8/15/2010 u 0000002 u 0000006 Little, Larry 8/21/2011 u 0000007 Mann, Macho 7/17/2006 u 0000001 u 0000008 Pipsqueak, Pippy 4/15/2007 u 0000002 u 0000009 Doer, Danny 6/6/2006 u 0000002 u 0000010 Jones, Jeff 2/8/2009 u 0000001 9

Self join • Process: pretend there are two copies of the table – Use the table name twice – Alias at least one of the copies – Join the tables (manager. ID as FOREIGN KEY) Employee (Copy 1: Emp) Employee (Copy 2: Mgr) employee. ID name date. Of. Hire manager. ID M 1 10

Self join SELECT EMP. employee. ID, EMP. name, MGR. name FROM employee as EMP, employee as MGR WHERE EMP. manager. ID = MGR. employee. ID EMP. name MGR. name u 0000002 Overseer, Susie Bossman, Bob u 0000003 Worker, William Bossman, Bob u 0000006 Little, Larry Bossman, Bob u 0000007 Mann, Macho Bossman, Bob u 0000010 Jones, Jeff Bossman, Bob u 0000004 Smith, Sam Overseer, Susie u 0000005 Jones, Jenny Overseer, Susie u 0000008 Pipsqueak, Pippy Overseer, Susie u 0000009 Doer, Danny Overseer, Susie 11

Multiple relationships • Normally there is ONE relationship between two tables (that goes both directions) • Occasionally there is actually two relationships between the tables – Different “roles” 12

Multiple relationships • Suppose you had a database tracking baseball games • You have a “Teams” table and a “games” table • A team can participate in a game as the “home” team, OR a team can participate in a game as the “away” team (but never both at the same time). • These are two separate relationships 13

Table structure • Team (Team. ID, Team. Name, Mascot) • Ball. Game (Game. ID, Game. Date, Location, Home. Team. ID, Home. Score, Away. Team. ID, Away. Score) • To find the names for teams participating in a game, you need to get one row of data from the Teams table to get the name of the home team, and a different row of data in the teams table to get the name of the away team. 14

Process • Like with did with self join • Pretend like the Team table exists twice 15

Team. ID Team. Name Mascot Ballgame Game. ID Game. Date Location Home. Team. ID Home. Score Away. Team. ID Away. Score 16

The query Select Game. ID, Game. Date, H. Team. Name As Home. Team, A. Team. Name as Away. Team From Ball. Game AS G Team AS H Team AS A Where G. Home. Team. ID = H. Team. ID and G. Away. Team. ID = A. Team. ID; link the home copy link the away copy 17

Multiple functions • Let’s look at the demog table in the SNDB. It’s structure is • Demog (pt_id, name, zip, gender, race). • We need to know what’s the highest pt_id in the table. However, every column in this table was created as a TEXT field. So we can’t just sort the table and scan for the last entry – the data will sort alphabetically, so 1001 will show up early in the list, but 999 will show up late in the list. 18

Multiple functions • We can use a data type conversion function to pretend that the pt_id is a number. (Like we did with sorting data). Cint(pt_id) or to_number(pt_id) Access Oracle 19

Multiple functions • Then once we have it as a number, we can find the maximum value using the max() function • Those functions can be nested together. Just be careful to do the parentheses correctly. SELECT max(cint(pt_id)) from demog; • If I run this, I see that the highest pt_id in my table is 1255 (your copy may differ slightly). 20

finally • READ the description of other DML query types • Insert (add row of data) • Update (change existing row) • Delete (get rid of a row of data) 21