John F. Bergin               

Bellevue, WA

(425) 242-1394

John.F.Bergin@Comcast.net                            Resume

Word Formatted Resume (Right Click and "Save Target As")

Cover Letter                                                    White Papers                                            Code Examples

Senior Software Engineer                                                                                                                                                                                                          

Work In Progress...!

Structured Query Language

by John F. Bergin

    Select Statement

    SELECT * FROM ADDRESSES

    Returns all Rows of the ADDRESSES table. 

    Where Clause

    SELECT * FROM ADDRESSES

        WHERE City = 'Exton'

    Returns all Rows of the ADDRESSES table where the city column contains "Exton".

    Conditions - AND & OR

    SELECT * FROM ADDRESSES

          WHERE City = 'Exton'

        AND ( Street = 'Conner Dr.' OR Street = 'Fairview Dr.' )

    Returns all Rows of the ADDRESSES table where the City column contains "Exton",

    and the Street column contains either "Conner Dr." or "Fairview Dr.". 

    Order By

    SELECT * FROM ADDRESSES

          ORDER BY City

    Returns all Rows of the ADDRESSES table and sorts the list alphabetically by City.

    Insert Statement    

    INSERT INTO ADDRESSES

          VALUES ( 1, '1313', 'Mockingbird Lane', 'Hollywood', 'CA', '90210' )

    Inserts a record at row 1. 

    Update Statement    

    UPDATE ADDRESSES

          SET ZipCode = '19341' 

         WHERE City = 'Exton' 

    Updates all Zip Code records for the City of Exton.

    Delete Statement    

    DELETE FROM ADDRESSES

         WHERE City = 'Brighton' 

    Deletes all records for the City of Brighton.

    Primary Key

    The Primary Key is an attribute that uniquely identifies a record in a database table.

    Primary Keys can consist of a single attribute or a combination of attributes.  Primary

    Keys can also be generated by the database.

    CREATE TABLE CUSTOMER

        CustomerID    INTEGER  PRIMARY KEY

        CustomerName  STRING

    Foreign Key

    A Foreign Key is an attribute that points to a Primary Key in a different database table.

     Foreign Keys can consist of a single attribute or a combination of attributes.  Foreign

     Keys can be used to guarantee referential integrity of the data.  

    CREATE TABLE ORDER

        OrderID       INTEGER   PRIMARY KEY

        CustomerName  STRING

        CustomerID    INTEGER   references CUSTOMER(CustomerID)  // <-- The Foreign Key

    In this example, the Foreign Key "CustomerID" will not allow an Order to be created unless 

     there is a valid Customer in the CUSTOMER table.

    Unique Key

    A Unique Key is used to ensure that all values of an attribute are are unique.

    CREATE TABLE ORDER

        OrderID       INTEGER   UNIQUE  // <-- The Unique Key ensures no 2 orders have the same OrderID

        CustomerName  STRING

        CustomerID    INTEGER   references CUSTOMER(CustomerID)  // <-- The Foreign Key

    Inner Join

    An Inner or Left Join will return only rows where values are the same in both tables.

    SELECT * FROM ADDRESSES INNER JOIN ORDERS

          WHERE ADDRESSES.City = ORDERS.City

    Outer Join

    Indexes

    Stored Procedures

____________________________

Copyright © 2010 by John F. Bergin.

Home