What is SQL ? SQL Basics

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