Creating Tables Inserting Values Using SQL Farrokh Alemi
Creating Tables & Inserting Values Using SQL Farrokh Alemi, Ph. D HEALTH INFORMATICS PROGRAM HI. GMU. EDU
SQL Can Create Tables HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
CREATE TABLE table_name ( column 1 datatype, column 2 datatype, column 3 datatype, . . ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
CREATE TABLE table_name ( column 1 datatype, column 2 datatype, column 3 datatype, . . ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
CREATE TABLE table_name ( column 1 datatype, column 2 datatype, column 3 datatype, . . ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
CREATE TABLE table_name ( column 1 datatype, column 2 datatype, column 3 datatype, . . ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
CAST and CONVERT commands can change data from one type to another HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
Patient Table First Name Larry George Jill Last Name Kim Smith HEALTH INFORMATICS PROGRAM Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
HEALTH INFORMATICS PROGRAM Str ing ing Str First Name Larry George Jill Last Name Kim Smith Patient Table Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
First Name Larry George Jill Last Name Kim Smith HEALTH INFORMATICS PROGRAM Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA te Da Int ege r Patient Table Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
Patient Table First Name Larry George Jill Last Name Kim Smith HEALTH INFORMATICS PROGRAM Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
Patient Table First Name Larry George Jill Last Name Kim Smith HEALTH INFORMATICS PROGRAM Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
Patient Table First Name Larry George Jill Last Name Kim Smith HEALTH INFORMATICS PROGRAM Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
Patient Table First Name Larry George Jill Last Name Kim Smith HEALTH INFORMATICS PROGRAM Zip Code 22101 22102 City Mclean Mc. Lean State DC Virginia VA Date of Birth 08 -Jan-54 09 -Sep-60 01 -Aug-89 Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 GEORGE MASON UNIVERSITY
Patient Table First Name Larry George Jill Last Name Kim Smith Zip Code 22101 22102 City Mclean Mc. Lean Date of State Birth DC 08 -Jan-54 Virginia 09 -Sep-60 Remove VA 01 -Aug-89 Variability in Email email@test. edu email@tes. com test@test. com Telephone of the Patient 703 -9934226 (703) 8884545 703 993 4226 Data Entry HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street]](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-17.jpg "CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street]")
CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street] Text, [Zip Code] Int, [Birth Date] Date, [Email] text, [State] Text, [Phone Number] Text, [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), In [ ] [Street](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-18.jpg "CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), In [ ] [Street")
CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), In [ ] [Street Number] Int, [Street] Text, [Zip Code] Int, [Birth Date] Date, [Email] text, [State] Text, [Phone Number] Text, [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![# CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int,](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-19.jpg "# CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int,")
# CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street] Text, [Zip Code] Int, [Birth Date] Date, [Email] text, [State] Text, [Phone Number] Text, [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street]](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-20.jpg "CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street]")
CREATE TABLE #Patient ( [First Name] char(20), [Last Name] char(50), [Street Number] Int, [Street] Text, [Zip Code] Int, [Birth Date] Date, [Email] text, [State] Text, [Phone Number] Text, [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ) HEALTH INFORMATICS PROGRAM Primary Key GEORGE MASON UNIVERSITY
Provider Table First Name Last Name Jim Jill George Jones Smith John HEALTH INFORMATICS PROGRAM Board Certified Yes No Yes Email Telephone jl@w. com js@w. com g@w. com 3456714545 3454561234 3104561234 Employee ID 452310 454545 456734 GEORGE MASON UNIVERSITY
First Name Last Name Jim Jill George Jones Smith John HEALTH INFORMATICS PROGRAM Board Certified Yes No Yes Pri ma Ke ry y Provider Table Email Telephone jl@w. com js@w. com g@w. com 3456714545 3454561234 3104561234 Employee ID 452310 454545 456734 GEORGE MASON UNIVERSITY
Provider Table First Name Last Name Jim Jill George Jones Smith John HEALTH INFORMATICS PROGRAM Board Certified Yes No Yes Email Telephone jl@w. com js@w. com g@w. com 3456714545 3454561234 3104561234 Employee ID 452310 454545 456734 GEORGE MASON UNIVERSITY
Provider Table First Name Last Name Jim Jill George Jones Smith John HEALTH INFORMATICS PROGRAM Board Certified Yes No Yes Email Telephone jl@w. com js@w. com g@w. com 3456714545 3454561234 3104561234 Employee ID 452310 454545 456734 GEORGE MASON UNIVERSITY
![CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), [Board Certified] bit, [Date](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-25.jpg "CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), [Board Certified] bit, [Date")
CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), [Board Certified] bit, [Date of Hire] Date, [Phone] Text, [Email] char(75), [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![Pri ma Ke ry y CREATE TABLE #Provider ( [First Name] char(20), [Last Name]](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-26.jpg "Pri ma Ke ry y CREATE TABLE #Provider ( [First Name] char(20), [Last Name]")
Pri ma Ke ry y CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), [Board Certified] bit, [Date of Hire] Date, [Phone] Text, [Email] char(75), [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), 1, 0, or [Board](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-27.jpg "CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), 1, 0, or [Board")
CREATE TABLE #Provider ( [First Name] char(20), [Last Name] char(50), 1, 0, or [Board Certified] bit, Null [Date of Hire] Date, [Phone] Text, [Email] char(75), [Patient ID] int IDENTITY(1, 1) PRIMARY KEY ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
Encounter Table ID Patient ID Provider ID 1 2 3 4 5 1 1 2 3 1 452310 454545 HEALTH INFORMATICS PROGRAM Date of Encounter 10 -Jan-04 17 -Jan-04 10 -Jan-04 Diagnosis Treatment Hypertension Heart Failure Null Hypertension Asthma Assessment Monitoring Assessment Education GEORGE MASON UNIVERSITY
Encounter Table ID Patient ID Provider ID 1 2 3 4 5 1 1 2 3 1 452310 454545 HEALTH INFORMATICS PROGRAM Date of Encounter 10 -Jan-04 17 -Jan-04 10 -Jan-04 Diagnosis Treatment Hypertension Heart Failure Null Hypertension Asthma Assessment Monitoring Assessment Education GEORGE MASON UNIVERSITY
Encounter Table ID Patient ID Provider ID 1 2 3 4 5 1 1 2 3 1 452310 454545 HEALTH INFORMATICS PROGRAM Date of Encounter 10 -Jan-04 17 -Jan-04 10 -Jan-04 Diagnosis Treatment Hypertension Heart Failure Null Hypertension Asthma Assessment Monitoring Assessment Education GEORGE MASON UNIVERSITY
Encounter Table ID Patient ID Provider ID 1 2 3 4 5 1 1 2 3 1 452310 454545 HEALTH INFORMATICS PROGRAM Date of Encounter 10 -Jan-04 17 -Jan-04 10 -Jan-04 Diagnosis Treatment Hypertension Heart Failure Null Hypertension Asthma Assessment Monitoring Assessment Education GEORGE MASON UNIVERSITY
HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Encounter ( [Patient ID] Int, [Provider ID] Int, [Diagnoses] char(50), [Treatment] char(50),](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-33.jpg "CREATE TABLE #Encounter ( [Patient ID] Int, [Provider ID] Int, [Diagnoses] char(50), [Treatment] char(50),")
CREATE TABLE #Encounter ( [Patient ID] Int, [Provider ID] Int, [Diagnoses] char(50), [Treatment] char(50), [Date of Encounter] Date, [Encounter ID] int IDENTITY(1, 1) PRIMARY KEY ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Encounter ( Connects to Patient [Patient ID] Int, Table [Provider ID] Int,](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-34.jpg "CREATE TABLE #Encounter ( Connects to Patient [Patient ID] Int, Table [Provider ID] Int,")
CREATE TABLE #Encounter ( Connects to Patient [Patient ID] Int, Table [Provider ID] Int, [Diagnoses] char(50), [Treatment] char(50), [Date of Encounter] Date, [Encounter ID] int IDENTITY(1, 1) PRIMARY KEY ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![CREATE TABLE #Encounter ( [Patient ID] Int, Connects to [Provider ID] Int, Provider [Diagnoses]](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-35.jpg "CREATE TABLE #Encounter ( [Patient ID] Int, Connects to [Provider ID] Int, Provider [Diagnoses]")
CREATE TABLE #Encounter ( [Patient ID] Int, Connects to [Provider ID] Int, Provider [Diagnoses] char(50), [Treatment] char(50), [Date of Encounter] Date, [Encounter ID] int IDENTITY(1, 1) PRIMARY KEY ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
any One to M HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
any One to M HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); Reserved Words HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); able Existing T HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); Re se W rve or d d HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); Se pa Co rate m d m by a HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date],](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-44.jpg "INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date],")
INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date], [Email], [State], [Phone Number]) VALUES ('Farrokh', 'Alemi', Null, 22101, '08/01/1954', 'Test 2@gmu. edu', Null, '7039934226'), ('George', 'Smith', Null, 22102, '09/09/1960', 't@tes. com', Null, '7038884545'), ('Jill', 'Smith', Null, 22103, '01/08/1989', 'test@test. com', Null, '7039934226'); Ea ch R in ow Pa Sep of re ar D nt at at he e a sis HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date],](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-45.jpg "INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date],")
INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date], [Email], [State], [Phone Number]) VALUES ('Farrokh', 'Alemi', Null, 22101, '08/01/1954', 'Test 2@gmu. edu', Null, '7039934226'), ('George', 'Smith', Null, 22102, '09/09/1960', 't@tes. com', Null, '7038884545'), ('Jill', 'Smith', Null, 22103, '01/08/1989', 'test@test. com', Null, '7039934226'); r fo es Is alu ta ll V Da Nu hen sing W Mis HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![D I t n atie P o N INSERT INTO #Patient ([First Name], [Last](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-46.jpg "D I t n atie P o N INSERT INTO #Patient ([First Name], [Last")
D I t n atie P o N INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date], [Email], [State], [Phone Number]) VALUES ('Farrokh', 'Alemi', Null, 22101, '08/01/1954', 'Test 2@gmu. edu', Null, '7039934226'), ('George', 'Smith', Null, 22102, '09/09/1960', 't@tes. com', Null, '7038884545'), ('Jill', 'Smith', Null, 22103, '01/08/1989', 'test@test. com', Null, '7039934226'); HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
![INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date],](http://slidetodoc.com/presentation_image/5cd5885c01bc7acb9303c86fde5d94ac/image-47.jpg "INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date],")
INSERT INTO #Patient ([First Name], [Last Name], [Street Number], [Street], [Zip Code], [Birth Date], [Email], [State], [Phone Number]) VALUES ('Farrokh', 'Alemi', Null, 22101, '08/01/1954', 'Test 2@gmu. edu', Null, '7039934226'), ('George', 'Smith', Null, 22102, '09/09/1960', 't@tes. com', Null, '7038884545'), ('Jill', 'Smith', Null, 22103, '01/08/1989', 'test@test. com', Null, '7039934226'); HEALTH INFORMATICS PROGRAM Te En xt te Va re lu d i e n Q s a uo re te s GEORGE MASON UNIVERSITY
Patient • Create Table • Insert Values Provider • Create Table • Insert Values • Create Table Encounter • Insert Values HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
Relational Database HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
DROP TABLE table-name HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
Create Table & Insert Values
- Slides: 51