Table of Contents
SQL – What Is It?
- Structured Query Language
- Common Language For Variety of Databases
- ANSI Standard BUT
- Two Types of SQL
- DML – Data Manipulation Language (SELECT)
- DDL – Data Definition Language (CREATE TABLE)
Where To Use
- SQL*Plus
- TOAD
- SQL Navigator
- ODBC Supported Connections
- Excel
- Access
- Lotus 1-2-3
- Heart of PL/SQL
Pros & Cons of SQL
- Pros:
- Very flexible
- Universal (Oracle, Access, Paradox, etc)
- Relatively Few Commands to Learn
- Cons:
- Requires Detailed Knowledge of the Structure of the Database
- Can Provide Misleading Results
Basic SQL Components
- SELECT schema.table.column
- FROM table alias
- WHERE [conditions]
- ORDER BY [columns]
- ;
- Defines the end of an SQL statement
- Some programs require it, some do not (TOAD Does Not)
- Needed only if multiple SQL statements run in a script
SELECT Statement
- SELECT Statement Defines WHAT is to be returned (separated by commas)
- Database Columns (From Tables or Views)
- Constant Text Values
- Formulas
- Pre-defined Functions
- Group Functions (COUNT, SUM, MAX, MIN, AVG)
- “*” Mean All Columns From All Tables In the FROM Statement
FROM Statement
- Defines the Table(s) or View(s) Used by the SELECT or WHERE Statements
- You MUST Have a FROM statement statement
- Multiple Tables/Views are separated by Commas
Examples
- SELECT state_name, state_abbr FROM states
- SELECT * FROM agencies
- SELECT arithmetic_mean ––minimum_valueminimum_value
FROM annual_summaries
WHERE Clause
- Optional
- Defines what records are to be included in the query.
- Uses Conditional Operators
- =, >, >=, <, <=, != (<>)=, (<>)
- BETWEEN x AND yBETWEEN y
- IN (listlist)
- LIKE ‘‘%string%” is a wild wild-card)
- IS NULL
- NOT {BETWEEN / IN / LIKE / NULL}
- Multiple Conditions Linked with AND & OR Statements
- Strings Contained Within SINGLE QUOTES!!
AND & OR
- Multiple WHERE conditions are Linked by AND / OR Statements
- “AND” Means All Conditions are TRUE for the Record
- “OR” Means at least 1 of the Conditions is TRUE
- You May Group Statements with ( )
- BE CAREFUL MIXING “AND” & “OR” Conditions
Examples with WHERE
- SELECT * FROM annual_summaries WHERE sd_duration_code = ‘1’
- SELECT state_name FROM states WHERE state_population > 18000000
More Examples
- SELECT state_name, state_population FROM states WHERE state_name LIKE ‘%NORTH% ’
- SELECT * FROM annual_summaries WHERE sd_duration_code IN ( (‘1’, ‘W’, ‘X’) AND annual_summary_year = 2000
ORDER BY Statement
- Defines How the Records are to be Sorted
- Must be in the SELECT statement to be ORDER BY
- Default is to order in ASC (Ascending) order
- Can Sort in Reverse (Descending) Order with ““DESC””After the Column Name
ORDER BY Example
- SELECT * FROM agencies ORDER BY agency_desc
- SELECT cc_cn_stt_state_code, site_id FROM sites WHERE lut_land_use_type = ‘MOBILE’ ORDER BY cc_cn_stt_state_code DESC
Group Functions
- Performs Common Mathematical Operations on a Group of Records
- Must define what Constitutes a Group by Using the GROUP BY Clause
- All nonAll non-Group elements in the SELECT Statement Must be in the GROUP BY Clause (Additional Columns are Optional)
Group By Example
- SELECT si_si_id, COUNT(mo_id) FROM monitors GROUP BY si_si_id
- SELECT AVG(max_sample_value) FROM summary_maximums WHERE max_level <= 3 AND max_ind = ‘REG’ GROUP BY ans_ans_id
Primary & Foreign Keys
- Primary Keys
- 1 or More Columns Used to Uniquely Identify a record.
- All Columns Defined as PK’’s MUST be populated
- Foreign Keys
- Value on a table that references a Primary Key from a different table
Aliases
- “Shorthand” for Table or Column References
- SELECT Aliases Appear as Column Headers in the Output
- Aliases Cannot be Keywords
Why Use an Alias?
- Saves Typing
- Good Internal Documentation
- Better Headers
- If the same column name exists on multiple tables, SQL needs a way to know which element you are referencing (MO_MO_ID for example)
Basic Structural Elements
- SELECT
- FROM
- WHERE
- ORDER BY
- GROUP BY
- Selecting From Multiple Tables
- Join Multiple Tables via Primary & Foreign Keys
- Aliases