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