Retrieving Data from RCRAInfo Basic SQL Training 2005

Retrieving Data from RCRAInfo: Basic SQL Training 2005 RCRAInfo National Users Conference August 2 – 5, 2005 Nashville, TN

DEFINITIONS SQL: Structured Query Language (SQL) – Standard language for interacting with Relational Databases. SQL*Plus: Oracle tool that recognizes and submits SQL commands. TABLE: Structure which holds data and is comprised of columns and rows. COLUMN: Field representing one kind of data in a table. ROW: One set of fields. VALUE: Data represented by the intersection of a row and column. 2

How to log on to the RCRAInfo testing/development database 3

HOW DO YOU ENTER COMMANDS IN SQL*PLUS? • Typing from the keyboard • Copy and paste (Ctrl-c, Ctrl-v) from/to Windows clipboard • Use the edit command to manipulate the current SQL query 4

LIST OF USEFUL SQL*PLUS COMMANDS spool filename – Records your session in a file. spool off – Closes the spool file. desc tablename – Enables you to view the structure of a table. start filename. ext – Run/executes a previously saved file. @filename. ext – Run/executes a previously saved file. run – Displays and runs the current SQL statement in the buffer. get filename. ext – Gets a SQL file and places it into the buffer. / – Runs command in the SQL buffer. BUFFER EDITING COMMANDS L[ist] – Lists contents of buffer. The * is the current line. You must select the line to modify. C[hange]/old string/newstring – Change string on the current line. I[NPUT] text – Adds one or more lines after the current line. Enter a carriage return on a line by itself or semicolon to terminate input mode. DEL – Deletes the current line. ED or EDIT – Invokes host text editor. 5

SELECT DEFAULT FOLDER/WORKING LIBRARY 1. From SQL*PLUS logon 2. Select File/Open 3. Select file to set link 6

BASIC QUERY SELECT … column name(s) FROM … table name(s) WHERE … (condition) GROUP BY … (grouping multiple rows into one row) ORDER BY … (specific order) Whenever you have a SELECT, you must also have a FROM clause 7

TABLE STRUCTURE DESCRIBE command: SQL> desc all_objects; Name Null? Type ---------------- ---- OWNER NOT NULL VARCHAR 2(30) OBJECT_NAME NOT NULL VARCHAR 2(30) SUBOBJECT_NAME OBJECT_ID VARCHAR 2(30) NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR 2(15) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR 2(19) STATUS VARCHAR 2(7) TEMPORARY VARCHAR 2(1) GENERATED VARCHAR 2(1) 8

List all objects (tables, views, synonyms, indexes, triggers, etc) SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE OWNER = 'RCRA' AND OBJECT_TYPE = 'TABLE'; OBJECT_NAME OBJECT_TYPE --------------- AAREA TABLE ACA_AUTHORITY TABLE AEVENT TABLE ALN_AREA_EVENT TABLE ALN_EVENT_AUTHORITY TABLE ASTATUTE_CITATION TABLE BARRT_COLUMN_INFORMATION TABLE BREPORT_CYCLE TABLE C 6002 FED 3 TABLE CCITATION 3 TABLE CCOMMITMENT 3 TABLE CDX_CONTACTS TABLE CDX_PERMISSIONS TABLE CENFORCEMENT 3 TABLE CEVALUATION_AREA TABLE CLN_COMPLIANCE_SCHEDULE TABLE CLN_EVALUATION_COMMITMENT 3 TABLE CLN_EVALUATION_VIOLATION TABLE 9

SELECT SPECIFIC ROW(S) WHERE condition COMPARISON OPERATORS: = [NOT] IN (list) != or <> [NOT] LIKE string pattern (Wildcards % _ ) > [NOT] BETWEEN x and y < IS [NOT] NULL >= <= LOGICAL OPERATORS: AND OR NOT 10

select * from lu_violation_type; OW VIOLATION_ U A VIOLATION_TYPE_DESC -- ----- - - ----------------------------------------USERID LAST_CHAN --------HELP_NOTES --------------------------------------------------HQ 260. C 1 Y HW Management System: Rulemaking Petitions CONV 3 12 -APR-05 HQ 261. A 1 Y ID and Listing of HW: General CONV 3 12 -APR-05 HQ 261. B 1 Y ID and Listing of HW: Criteria for Identifying the Characteristics of HW and for CONV 3 12 -APR-05 HQ 261. C 1 Y ID and Listing of HW: Characteristics of HW CONV 3 12 -APR-05 select count(* ) from lu_violation_type; COUNT(*) -------109 11

EXAMPLE OF COMPARISON AND LOGICAL OPERATORS spool equal. txt select handler_id, violation_type, former_citation, determined_date, notes from cviolation 3 where handler_id = 'FLR 000050369' / spool off HANDLER_ID VIOLATION_ ------ FORMER_CITATION -------------- DETERMINE NOTES ----------------------------- FLR 000050369 279. C FR - 279. 22 C 21 -JAN-05 LABELLING- Former Owner and Type: FL UOR FLR 000050369 279. C FR - 279. 22 D 21 -JAN-05 SMALL DISCHARGES- Former Owner and Type: FL UOR FLR 000050369 279. C FR - 279. 46 21 -JAN-05 ACCEPTANCE RECORD INFORMATION INCOMPLETE- Former O FLR 000050369 279. C FR - 279. 72 21 -JAN-05 NO ANALYSIS- Former Owner and Type: FL UOR spool and. txt select handler_id, violation_type, former_citation, determined_date from cviolation 3 where violation_type = '279. C' and former_citation = 'FR - 40 CFR 279. 22 D' / spool off HANDLER_ID VIOLATION_ FORMER_CITATION DETERMINE ------------------------ FLR 000062208 279. C FR - 40 CFR 279. 22 D 18 -OCT-04 FLD 982138117 279. C FR - 40 CFR 279. 22 D 30 -JUN-04 FL 9170024260 279. C FR - 40 CFR 279. 22 D 23 -JUN-04 FLR 000113662 279. C FR - 40 CFR 279. 22 D 27 -OCT-04 FLR 000113159 279. C FR - 40 CFR 279. 22 D 30 -SEP-04 FLR 000113266 279. C FR - 40 CFR 279. 22 D 15 -OCT-04 FLR 000110668 279. C FR - 40 CFR 279. 22 D 29 -JUL-04 ----- 12

LIKE string pattern (wildcard) spool like. txt select handler_id, violation_type, former_citation, determined_date from cviolation 3 where handler_id like 'FL%‘ / spool off HANDLER_ID VIOLATION_ FORMER_CITATION DETERMINE ------------------------ FL 0000001735 279. C SR - 62 -710. 500 1 A 28 -APR-04 FL 0000001735 262. A SR - 62 -730. 150 5 F. A. C 08 -JAN-96 FL 0000002527 262. A FR - 265. 173 A 18 -NOV-98 FL 0000002527 262. A FR - 262. 34 D 5 18 -NOV-98 FL 0000002535 262. A SS - 376. 3078 7 A 17 -MAR-98 FL 0000002535 262. A SR - 62 -730. 150 7 17 -MAR-98 FL 0000002584 262. A FS - 262. 34 A 3 12 -AUG-04 FL 0000002584 262. A FS - 262. 34 D 4 12 -AUG-04 ----- FL 0000013938 263. A 09 -AUG-95 FL 0000013938 263. B 09 -AUG-95 FL 0000013938 263. A 09 -AUG-95 FL 0000013938 279. C FR - 40 CFR 279. 45 E 24 -MAR-97 FL 0000014555 262. A FR - 262. 34 A 3 02 -JUL-01 FL 0000014555 262. A FR - 262. 34 C 2 02 -JUL-01 FL 0000014555 262. A FR - 265. 54 D 02 -JUL-01 FL 0000014555 279. C FR - 279. 22 D 3 02 -JUL-01 FL 0000014555 279. C FR - 279. 22 C 1 13 -JUN-97 FL 0000014555 262. A FR - 40 CFR 262. 11 06 -JUL-93 13

IN [list] spool in. txt select owner, penalty_type, active_status, penalty_desc from lu_penalty_type where usage in ('0', '1', '3', '5', '7', '9') / spool off OW PEN A PENALTY_DESC -- - ---------------------------------------- HQ PA N Proposed Monetary Penalty HQ FA N Final Monetary Penalty HQ FC N Final SEP Cost HQ CR N Final SEP Credit HQ SCR Y SEP CREDIT HQ FMP Y FINAL MONETARY PENALTY HQ FSC Y FINAL SEP COST HQ PMP Y PROPOSED MONETARY PENALTY HQ ZAP Y ZERO ABILITY TO PAY 14

NOT IN [list] spool not_in. txt select handler_id, enforcement_date, agency, enforcement_type from cenforcement 3 where enforcement_type not in ('105', '310', '410') and handler_id like 'FL%‘ / spool off HANDLER_ID ENFORCEME A ENF ------ - --- FL 0000001735 08 -JAN-96 S 110 FL 0000001735 03 -MAY-04 S 110 FL 0000002535 13 -MAY-98 S 115 FL 0000002535 27 -APR-98 S 125 FL 0000002584 12 -AUG-04 S 110 FL 0000013938 17 -SEP-97 S 312 FL 0000013938 11 -SEP-96 S 415 FL 0000013938 26 -SEP-95 S 115 FL 0000013938 09 -AUG-95 S 125 FL 0000014555 13 -JUN-97 S 110 FL 0000014555 27 -OCT-95 S 125 FL 0000014555 07 -OCT-93 S 312 FL 0000014555 02 -SEP-93 S 115 FL 0000014555 07 -JUL-93 S 125 FL 0000014555 02 -JUL-01 S 110 FL 0000015008 21 -OCT-97 S 110 FL 0000015016 10 -DEC-97 S 110 FL 0000015321 16 -OCT-03 S 115 FL 0000015321 22 -SEP-03 S 125 FL 0000015321 23 -MAR-04 S 312 15

GROUP BY spool groupby. txt select activity_location, agency, count(handler_id) from cevaluation 3 where evaluation_start_date >= '01 -Jan-2003' group by activity_location, agency / spool off AC A COUNT(*) -- - ----- AK E 92 AL C 1 AL E 4 AL S 762 AL X 18 AR E 1 AR S 222 AZ E 18 AZ S 176 CA B 779 CA C 1 CA E 149 CA S 324 CA X 4 The GROUP BY statement groups all rows with the same column value. The function “count” returns the number of rows that satisfy the condition in the WHERE clause GROUP BY also acts like the ORDER BY clause 16

SPECIFIC ORDER Order by COLUMN asc [desc] Specify the order in which the results of the query are to be displayed. spool orderby. txt select activity_location, handler_id, agency, evaluation_start_date from cevaluation 3 where activity_location = 'AZ' and evaluation_start_date >= '01 -Jan-2004' and agency = 'E' order by handler_id, evaluation_start_date / spool off AC -AZ AZ AZ AZ AZ HANDLER_ID ------AZ 0000337360 AZ 0000962530 AZD 000819615 AZD 009015389 AZD 037612702 AZD 980735179 AZD 980818090 AZD 981421449 AZD 982014631 AZD 982418204 AZD 982441263 AZD 983481813 AZR 000000943 AZR 000030452 AZR 000031856 AZR 000042184 AZR 000500355 A E E E E EVALUATIO ----27 -OCT-04 17 -JUN-04 22 -JUN-04 14 -APR-04 21 -MAY-04 23 -JUN-04 16 -JUN-04 30 -NOV-04 24 -JUN-04 29 -NOV-04 02 -DEC-04 12 -FEB-04 29 -JUN-04 21 -JUN-04 30 -JUN-04 25 -JUN-04 28 -JUN-04 01 -DEC-04 17

SOME TIPS Only ask for what you need, and know your data. Know the characteristics (size, number of records) of your tables. Different queries can return the same result. Experiment for best performance. Style is important. If it is readable, it will be reusable. 18

Editing Commands Command Description APPEND text Adds text to the end of the current line. CHANGE /old / new / Changes old text to new in the current line. DEL Deletes current line. INPUT Inserts an indefinite number of lines. INPUT text Inserts a line consisting of text. LIST Lists all lines in the SQL buffer. N Specifies the line to make the current line. 0 text Inserts a line before line 1. File Commands Command Description SAVE filename. ext Save contents of SQL buffer to a file. If no file name is specified, the buffer is saved to afied. buf. START filename. ext Runs a previously saved file. "@filename. ext" Runs a previously saved file RUN Displays and runs the current SQL statement in the buffer. EDIT filename. ext Invokes the editor to edit contents of a saved file. If no file name is specified, the contents of the SQL buffer is saved and then edited in file afiedt. buf. Environment Commands Command Description SET ECHO {ON|OFF} Display SQL statement along with SQL statement results. SET FEEDBACK {ON|OFF} Display the number of records returned by a query. SET HEADING {ON|OFF} Display column headings with SQL statement results. SET LINESIZE n Set the number characters per line to n. SET PAGESIZE n Set the number of lines per page to n. SET TERMOUT {ON|OFF} Display output on screen SET VERIFY {ON|OFF} Display the text of a command before and after it replaces a substitution variable with values. 19

Input/Output Commands Command Description SPOOL filename. ext Send output to a file. SPOOL {OFF|OUT} OFF closes the SPOOL file. OUT closes the spool file and sends the output to the system printer. &user_variable If user_variable does not exist, SQL*Plus will prompt for a value. SQL*Plus immediately discards it when used. &&user_variable value for reuse. If user_variable does not exist, SQL*Plus will prompt for a value only once and will store the DEFINE Display all user variables. DEFINE variable = value Creates a CHAR type user variable and assigns the value to it. ACCEPT variable {PROMPT text} Prompts the user with text then accepts the user input and assigns the value to variable. UNDEFINE variable Discards the specified variable. VARIABLE variable datatype Creates a variable of the type datatype. PRINT variable Displays the value of variable. Format Commands Command Description COLUMN Displays settings for all columns. COLUMN column CLEAR Clears all settings for the specified column. COLUMN column {HEADING text} Sets the column heading and justification. Specifies a format. {JUSTIFY align} {FORMAT format} for the column results and text to be displayed in place of null values. {NULL text} TTITLE text Specifies text as a header to be placed at the top of each page. BTITLE text Specifies text as a footer to appear at the bottom of each page. BREAK Displays breaks for all columns. CLEAR BREAK Clears all breaks. BREAK {ON report_element} Set break to suppress display of duplicate values for the specified report_element. Other Commands Command Description 20

QUESTIONS? 21