Introduction to Computer Science Databases and SQL Essentials

Introduction to Computer Science Databases and SQL Lecture c This material (Comp 4 Unit 5) was developed by Oregon Health & Science University, funded by the Department of Health and Human Services, Office of the National Coordinator for Health Information Technology under Award Number 90WT0001. This work is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. To view a copy of this license, visit http://creativecommons.org/licenses/by-nc-sa/4.0/.

Databases and SQL Learning Objectives - 1 Define and describe the purpose of databases (Lecture a) Define a relational database (Lecture a) Describe data modeling and normalization (Lecture b) Describe the structured query language (SQL) (Lecture c) 2

Databases and SQL Learning Objectives - 2 Define the basic data operations for relational databases and how to implement them in SQL (Lecture c) Design a simple relational database and create corresponding SQL commands (Lecture c) Examine the structure of a health care database component (Lecture d) 3

SQL Used to manage and access database information ANSI, ISO, and IEC standard DBMS have custom extensions! Extensive language We will look at a few basic commands 4

Example Make a space for the tables by creating the database create database <name> sql> create database contacts To remove drop database <name> sql> drop database contacts 5

Create the Tables Create table <name> (<column information>); Column information consists of column names and data types Tables also have extra information To remove: drop table <name> 6

SQL Basic Data Types Data Type integer or int float date time char (length) varchar (length) Description Whole numbers Floating point number Date Time Fixed number of characters Variable number of characters Table 1. Basic data types and their descriptions in SQL. 7

Create Company Table sql> create table company (id integer auto_increment, -> name varchar(50), -> address varchar(50), -> city varchar(50), -> state char(2), -> primary key(id)); 8

Create Person Table sql> create table person(id integer auto_increment, -> first_name varchar(50), -> last_name varchar(50), -> company_id integer, -> primary key(id), -> foreign key(company_id) references company(id)); 9

View Tables 10

View Table Columns 11

Same Basic Operations Add an entry Retrieve an entry Delete an entry Modify an entry 12

Add an Entry Insert into <table> (columns) values (values); First add company entries: sql> insert into company (name, address, city, state) -> values ('Community Hospital, Inc.', '1312 Main', 'Portland', 'OR'); sql> insert into company (name, address, city, state) -> values ('Oakland Providers LLC', '14 12th St.', 'Oakland', 'CA'); 13

Add Persons Now add people and companies associated with them: sql> insert into person (first_name, last_name, company_id) -> values ( Sriveni , Sharma , 1); sql> insert into person (first_name, last_name, company_id) -> values ( Walter , Chen , 2); sql> insert into person (first_name, last_name, company_id) -> values ( Rachel , Cohen ', 2); sql> insert into person (first_name, last_name, company_id) -> values ( Karthik , Subramanian , 1); sql> insert into person (first_name, last_name, company_id) -> values ( Kelly , David , 2); 14

Retrieve an Entry Select <columns> from <table>; Get company information: 15

Add Sorting Get people, sorted by last name: sql> select * from person order by last_name; 16

Add Selectivity Get names of people who work for Community Hospital: sql> select first_name, last_name from where company_id = 1; 17

Retrieve from Multiple Tables Select <columns> from <table 1> join <table 2> on <table 1 foreign key> = <table 2 primary key>; sql> select * from person join company on person.company_id = companyid; 18

Create a Complex SQL Statement sql> select first_name, last_name, company.name from person join company on person.company_id = company.id order by last_name; 19

Delete an Entry Delete from <table> where <constraints>; Remove Rachel from contact list: sql> delete from person where first_name= Rachel and last_name= Cohen ; 1 row affected 20

Modify an Entry update <table> set <column>=<data> where <constraints>; 21

New Company Name - 1 Our example where Community Hospital, Inc. becomes Community General Get the id sql> select * from company; Update the row data sql> update company set name Community General where id=1; 22

New Company Name - 2 Just for us, verify the change sql> select * from company; 23

Verify Again sql> select first_name, last_name, company.name from person join company on person.company_id company.id order by last_name; 24

Databases and SQL Summary Lecture c SQL is a language for creating, accessing and updating databases Create tables Insert data Update data Retrieve (select) data Delete data 25

Databases and SQL References Lecture c References Chen, P. P. (1976). The Entity-Relationship Model - Toward a Unified View of Data. ACM Transactions on Database Systems, 1(1). International Organization for Standardization. (2008). Information technology Database languages SQL (No. ISO/IEC 9075-(1-4,9-11,13,14)). Kent, W. (1983). A simple guide to five normal forms in relational database theory. Communications of the ACM, 26(2). 26

Introduction to Computer Science Databases and SQL Lecture c This material was developed by Oregon Health & Science University, funded by the Department of Health and Human Services, Office of the National Coordinator for Health Information Technology under Award Number 90WT0001. 27