HMP 669: Db Mgmt & Internet Apps in HC, Kai Zheng
gender gender CHAR(1) CHAR(1) NOT NULL CHEC CHECK K (gen (gende der r = ’F’ ’F’ OR gend gender er = ’M’) ’M’), , dob DATE NOT NULL, NULL, ssn CHAR(11) CHAR(11) UNIQUE UNIQUE );
Oracle 10g Cheat Sheet 1
ER Mode odel
1.1
Logic Logical al Model Model
3.1
IsPCPOf
0..*
NUMBER NUMBER (5,2) VARCHAR2(50) CHAR(5) DATE
0..1
Patient patientId {PK} pcpId {FK} nameFirst nameLast
Appointment AssignedTo
Has 1..1
0..*
1..1
appointmentId {PK} patientId {FK} physicianId {FK} appointmentDateTime reasonForVisit
Physician
AssignedTo
0..*
1..1
physicianId {PK} nameFirst nameLast
4
Has
4.1
0..3
2.1
Max. Max. size 38 38 digit digitss Max. Max. size size 4000 4000 Max. Max. size size 2000 2000 -4712–9999
Work with with Existi Existing ng Tabl Tables es Modif Modify y Table abless
ALTER TABLE physician physician MODIFY MODIFY salary salary NUMBER(10, NUMBER(10,2); 2);
Work ork with with Oracle Oracle via via SQL* SQL*Plu Pluss
DROP TABLE TABLE patient; patient;
Connec Connectt using using Instan Instantt Clien Clientt
sqlplus username/password@se username/password@server/database rver/database
2.2
Numeri Numericc (e.g. (e.g. 999.99) 999.99) Variabl ariablee le lengt ngth h strin string g Fixe Fixed d leng length th stri string ng Date/time
ALTER TABLE physician physician DROP COLUMN COLUMN salary; salary; ALTER TABLE physician physician ADD salary salary NUMBER(8,2 NUMBER(8,2); );
PatientPhoneNumber uniqueId {PK} patientId {FK} phoneNumber phoneType
2
Comm Co mmon on Data Data Types ypes
DROP TABLE TABLE patient patient CASCADE CASCADE CONSTRAINT CONSTRAINTS; S; -- remove remove even even if refere reference nced d by other other tables tables
Change Change Passw Password ord
ALTER USER kzheng kzheng IDENTIFIED IDENTIFIED BY hmp669; hmp669;
2.3
PURGE RECYCLEBIN;
Load Load Scr Scrip iptt from from File File
START h:\script.sql
2.4
4.2
Reco Record rd Sessi Sessions ons
ALTER TABLE patient patient DISABLE DISABLE CONSTRAIN CONSTRAINT T pat_phy_f pat_phy_fk; k; ALTER TABLE patient patient ENABLE ENABLE CONSTRAINT CONSTRAINT pat_phy_fk; pat_phy_fk;
SPOOL h:\log.tx h:\log.txt t ... SPOOL OFF
2.5
Modify Modify Constr Constrain aints ts
ALTER TABLE patient patient DROP CONSTRAINT CONSTRAINT pat_phy_fk pat_phy_fk; ;
Miscell Miscellane aneous ous
SET CONSTRAINT CONSTRAINTS S ALL DEFERRED; DEFERRED; SET CONSTRAINT CONSTRAINTS S ALL IMMEDIATE; IMMEDIATE;
DESC patient; patient; -- describe describe table structure structure SELECT SELECT table_name table_name FROM user_table user_tables; s; -- list list of all all tabl tables es
ALTER TABLE patient patient ADD CONSTRAINT CONSTRAINT pat_phy_fk pat_phy_fk FOREIGN FOREIGN KEY (pcp_id) REFERENCES physician(physicia physician(physician_id) n_id) ON DELETE DELETE CASCADE; CASCADE;
SELECT constraint_name, constraint_type, table_name FROM user_constraints; -- list list of all constrai constraints nts
5
3
Inse Insert rt an and d Updat Update e Reco Record rdss
INSERT INSERT INTO physician physician VALUES (’S01’,’Robert’,’Jones’, ’M’,98191.77);
Create Create Tables ables and and Const Constrai raint ntss
CREATE CREATE TABLE physician physician ( physician_ physician_id id VARCHAR2(1 VARCHAR2(10) 0) PRIMARY PRIMARY KEY, name_first name_first VARCHAR2( VARCHAR2(20) 20) NOT NULL, name_last name_last VARCHAR2(2 VARCHAR2(20) 0) NOT NULL, NULL, gender gender CHAR(1) CHAR(1) NOT NULL CHECK (gender (gender IN(’F’,’M IN(’F’,’M’)), ’)), salary NUMBER(7,2) );
INSERT INSERT INTO physician physician (salary, (salary, name_first name_first, , name_last name_last, , gender, gender, physician_ physician_id) id) VALUES VALUES (83351.28, (83351.28, ’Mary’,’J ’Mary’,’Jones’ ones’, , ’F’,’P02’ ’F’,’P02’); ); INSERT INSERT INTO patient VALUES (’389029113’,NULL, (’389029113’,NULL,’John’,’Smith’, ’John’,’Smith’, ’M’,’12-MAY-1983’,’419-29-4892’); -- DD-MON DD-MON-YY -YYYY YY is the defaul default t date date format format
CREATE CREATE TABLE TABLE patien patient t ( patient_id patient_id VARCHAR2( VARCHAR2(10) 10) PRIMARY PRIMARY KEY, pcp_id VARCHAR2(10) CONSTRAINT pat_phy_fk REFERENCES physician(physician_id) physician(physician_id) ON DELETE DELETE CASCADE, CASCADE, name_first name_first VARCHAR2( VARCHAR2(20) 20) NOT NULL, name_last name_last VARCHAR2(2 VARCHAR2(20) 0) NOT NULL, NULL,
INSERT INSERT INTO patient VALUES (’389029114’,’S01’ (’389029114’,’S01’,’Rebecca’,’Lee’, ,’Rebecca’,’Lee’, ’F’,TO_DATE(’04/01/1981’,’mm/dd/yyyy’), ’148-23-7326’); COMMIT; /* make uncommitt uncommitted ed changes changes permanent permanent, , only apply in record record manipulati manipulation*/ on*/
1
8.4 UPDATE physician SET salary = salary*1.2 WHERE physician_id = ’S01’;
Common String Functions
INITCAP() LENGTH() UPPER() LOWER() SUBSTR() TRIM()
ROLLBACK; -- undo uncommitted changes
Capitalize the first letter
e.g. SUBSTR(zip,1,5) Remove leading and trailing spaces
Example:
5.1
Delete Records
DELETE patient WHERE patient_id = ’389029113’;
SELECT LENGTH(TRIM(’ count me ’)) FROM dual; -- 8
DELETE physician;
8.5
6
TO_NUMBER() TO_CHAR(input,format) TO_DATE(input,format)
Work with Views
CREATE OR REPLACE VIEW rich_physician AS SELECT * FROM physician WHERE salary > 100000;
8.6
Work with Sequences
CREATE SEQUENCE seq_appt_id START WITH 1000000000 MAXVALUE 9999999999 INCREMENT BY 1;
9
8.2
-- average physician salary, rounded to 1 decimal SELECT ROUND(AVG(salary),1) FROM physician;
Comparison Operators
-- full name of those born in April -- || is used to concatenate strings SELECT name_last || ’, ’ || name_first FROM patient WHERE TO_CHAR(dob,’MON’) = ’APR’;
Not equal to
Pattern match in strings
-- dob ordered by last name then by first name SELECT TO_CHAR(dob,’DD/MON/YYYY’) FROM patient ORDER BY name_last, name_first;
Basic Arithmetic Operators
+, -, *, /
8.3
Common Number Functions
COUNT() SUM() AVG() MAX() MIN() ABS() ROUND(a,b) POWER(a,b)
Query a Single Table
-- how many female patients have PCP? SELECT COUNT(*) FROM patient WHERE gender = ’F’ AND pcp_id IS NOT NULL;
Data Manipulation
=, >, <, >=, <= <> IS NULL IS NOT NULL LIKE
01-JAN-2007 01/01/2007 SUNDAY, JANUARY 04TH SUN, January 04th 01:30:23 PM 13:30
SELECT TO_CHAR(sysdate,’MM/DD/YYYY HH:MI:SS A.M.’) FROM dual; -- 03/05/2007 01:31:17 A.M.
DROP SEQUENCE seq_appt_id;
8.1
e.g. e.g. e.g. e.g. e.g. e.g.
Example:
INSERT INTO appointment VALUES (seq_appt_id.NEXTVAL, ...);
8
Common Date Format
DD-MON-YYYY (default) MM/DD/YYYY DAY, MONTH DDTH DY, Month DDth HH:MI:SS AM HH24:MI
SELECT * FROM rich_physician; DROP VIEW rich_physician;
7
Common Conversion Functions
/*number of distinct patient last names of those born after 1979*/ SELECT COUNT(DISTINCT name_last) FROM patient WHERE dob >= ’01-JAN-1980’;
# of matched records Average
9.1
Absolute e.g. ROUND(129.29,1) = 129.3 e.g. POWER(5,2) = 25
Pattern Match in Strings (LIKE)
-- last name contains ’mm’ SELECT name_last FROM patient WHERE name_last LIKE ’%mm%’;
Example:
-- last name contains ’mm’, ’Mm’, ’mM’, or ’MM’ SELECT name_last FROM patient WHERE UPPER(name_last) LIKE ’%MM%’;
SELECT ABS(ROUND(-1.237,2)) FROM dual; -- 1.24
2
FROM appointment GROUP BY patient_id )
-- first name starts with ’A’ SELECT name_first FROM patient WHERE name_first LIKE ’A%’;
);
-- first name ends with ’a’ SELECT name_first FROM patient WHERE name_first LIKE ’%a’; /*first name contains two i’s separated by exactly one letter*/ SELECT name_first FROM patient WHERE name_first LIKE ’%i_i%’;
9.2
GROUPING
-- average physician salary by gender SELECT gender, AVG(salary) FROM physician GROUP BY gender; -- which gender group’s average salary is over 90k? SELECT gender, AVG(salary) FROM physician GROUP BY gender HAVING AVG(salary) > 90000;
10
Query from Multiple Tables
-- patient last name and their PCP last name SELECT patient.name_last, physician.name_last FROM patient, physician WHERE pcp_id = physician_id; -- include patients who don’t have PCP (outer join) -- alias may be used to speed up coding SELECT p1.name_last, p2.name_last FROM patient p1, physician p2 WHERE pcp_id = physician_id(+); -- include physicians who are not PCP of any patients SELECT p1.name_last, p2.name_last FROM patient p1, physician p2 WHERE pcp_id(+) = physician_id; -- patient last name(s) with more than 1 appointment SELECT name_last FROM patient WHERE patient_id IN ( SELECT patient_id FROM appointment GROUP BY patient_id HAVING COUNT(*) >= 1 ); -- patient last name(s) who visited most often SELECT name_last FROM patient WHERE patient_id IN ( SELECT patient_id FROM appointment GROUP BY patient_id HAVING COUNT(*) = ( SELECT MAX(COUNT(*))
3
