1 / 21

2005 RCRAInfo National Users Conference August 2 – 5, 2005 Nashville, TN

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.

bkliebert
Download Presentation

2005 RCRAInfo National Users Conference August 2 – 5, 2005 Nashville, TN

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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.

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

  4. 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

  5. LIST OF USEFUL SQL*PLUS COMMANDS spoolfilename – Records your session in a file. spool off – Closes the spool file. desctablename – Enables you to view the structure of a table. startfilename.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. getfilename.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.

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

  7. 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

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

  9. 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 C6002FED3 TABLE CCITATION3 TABLE CCOMMITMENT3 TABLE CDX_CONTACTS TABLE CDX_PERMISSIONS TABLE CENFORCEMENT TABLE CENFORCEMENT3 TABLE CEVALUATION TABLE CEVALUATION3 TABLE CEVALUATION_AREA TABLE CLN_COMPLIANCE_SCHEDULE TABLE CLN_EVALUATION_COMMITMENT3 TABLE CLN_EVALUATION_VIOLATION TABLE

  10. 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

  11. 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 CONV3 12-APR-05 HQ 261.A 1 Y ID and Listing of HW: General CONV3 12-APR-05 HQ 261.B 1 Y ID and Listing of HW: Criteria for Identifying the Characteristics of HW and for CONV3 12-APR-05 HQ 261.C 1 Y ID and Listing of HW: Characteristics of HW CONV3 12-APR-05 select count(* ) from lu_violation_type; COUNT(*) -------------- 109

  12. EXAMPLE OF COMPARISON AND LOGICAL OPERATORS spool equal.txt select handler_id, violation_type, former_citation, determined_date, notes from cviolation3 where handler_id = 'FLR000050369' / spool off HANDLER_ID VIOLATION_ FORMER_CITATION DETERMINE NOTES ------------ ---------- --------------------------- -------- -------------------------------------------------- FLR000050369 279.C FR - 279.22 C 21-JAN-05 LABELLING- Former Owner and Type: FL UOR FLR000050369 279.C FR - 279.22 D 21-JAN-05 SMALL DISCHARGES- Former Owner and Type: FL UOR FLR000050369 279.C FR - 279.46 21-JAN-05 ACCEPTANCE RECORD INFORMATION INCOMPLETE- Former O FLR000050369 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 cviolation3 where violation_type = '279.C' and former_citation = 'FR - 40 CFR 279.22 D' / spool off HANDLER_ID VIOLATION_ FORMER_CITATION DETERMINE ------------ ---------- ----------------------------------- --------- FLR000062208 279.C FR - 40 CFR 279.22 D 18-OCT-04 FLD982138117 279.C FR - 40 CFR 279.22 D 30-JUN-04 FL9170024260 279.C FR - 40 CFR 279.22 D 23-JUN-04 FLR000113662 279.C FR - 40 CFR 279.22 D 27-OCT-04 FLR000113159 279.C FR - 40 CFR 279.22 D 30-SEP-04 FLR000113266 279.C FR - 40 CFR 279.22 D 15-OCT-04 FLR000110668 279.C FR - 40 CFR 279.22 D 29-JUL-04

  13. LIKE string pattern (wildcard) spool like.txt select handler_id, violation_type, former_citation, determined_date from cviolation3 where handler_id like 'FL%‘ / spool off HANDLER_ID VIOLATION_ FORMER_CITATION DETERMINE ------------ ---------- ----------------------------------- --------- FL0000001735 279.C SR - 62-710.500 1 A 28-APR-04 FL0000001735 262.A SR - 62-730.150 5 F.A.C 08-JAN-96 FL0000002527 262.A FR - 265.173 A 18-NOV-98 FL0000002527 262.A FR - 262.34 D 5 18-NOV-98 FL0000002535 262.A SS - 376.3078 7 A 17-MAR-98 FL0000002535 262.A SR - 62-730.150 7 17-MAR-98 FL0000002584 262.A FS - 262.34 A 3 12-AUG-04 FL0000002584 262.A FS - 262.34 D 4 12-AUG-04 FL0000013938 263.A 09-AUG-95 FL0000013938 263.B 09-AUG-95 FL0000013938 263.A 09-AUG-95 FL0000013938 279.C FR - 40 CFR 279.45 E 24-MAR-97 FL0000014555 262.A FR - 262.34 A 3 02-JUL-01 FL0000014555 262.A FR - 262.34 C 2 02-JUL-01 FL0000014555 262.A FR - 265.54 D 02-JUL-01 FL0000014555 279.C FR - 279.22 D 3 02-JUL-01 FL0000014555 279.C FR - 279.22 C 1 13-JUN-97 FL0000014555 262.A FR - 40 CFR 262.11 06-JUL-93

  14. 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

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

  16. GROUP BY spool groupby.txt select activity_location, agency, count(handler_id) from cevaluation3 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

  17. SPECIFIC ORDEROrder 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 cevaluation3 where activity_location = 'AZ' and evaluation_start_date >= '01-Jan-2004' and agency = 'E' order by handler_id, evaluation_start_date / spool off AC HANDLER_ID A EVALUATIO -- ------------ - --------- AZ AZ0000337360 E 27-OCT-04 AZ AZ0000962530 E 17-JUN-04 AZ AZD000819615 E 22-JUN-04 AZ AZD009015389 E 14-APR-04 AZ AZD009015389 E 21-MAY-04 AZ AZD037612702 E 23-JUN-04 AZ AZD980735179 E 16-JUN-04 AZ AZD980818090 E 30-NOV-04 AZ AZD981421449 E 24-JUN-04 AZ AZD982014631 E 29-NOV-04 AZ AZD982418204 E 02-DEC-04 AZ AZD982441263 E 12-FEB-04 AZ AZD983481813 E 29-JUN-04 AZ AZR000000943 E 21-JUN-04 AZ AZR000030452 E 30-JUN-04 AZ AZR000031856 E 25-JUN-04 AZ AZR000042184 E 28-JUN-04 AZ AZR000500355 E 01-DEC-04

  18. 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.

  19. 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.

  20. 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 If user_variable does not exist, SQL*Plus will prompt for a value only once and will store the value for reuse. 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 HOST command Executes an operating system command from within SQL*Plus. DESCRIBE tablename Lists the names, status, and datatypes of columns for a table.

  21. QUESTIONS?

More Related