Intro to SQL by yaofenji

VIEWS: 1 PAGES: 12

									 STRUCTURED QUERY
 LANGUAGE
 SQL

           Fact of the Week:
 According to the 2010 outlook from the
BLS 286,600 new Computer, Network, and
Database administrator jobs will be added
          in the next decade.
Learning Objectives
   Describe and use basic SQL commands
   Explain how and why SQL is used, and why its
    important
   Compare and contrast DML and DDL
   Use SQL commands to create metadata structures
    and perform CRUD operations.
Brief History of SQL
      1970– E. Codd develops relational database
       concept
      1974-1979–System R with Sequel (later SQL)
       created at IBM Research Lab
      1979–Oracle markets first relational DB with
       SQL
      1986–ANSI SQL standard released
      1989, 1992, 1999, 2003, 2007–Major ANSI
       standard updates
      Current–SQL is supported by most major
       database vendors at 1999, 2003 and 2007

           http://www.jcc.com/sql.htm
The purpose of the SQL standard

                            Syntax and
                             Semantics




        Allow for                                  Intrinsic
        Growth /                                    Data
      Enhancement                                 Structures

                            SQL
               Levels:
             Minimal (L1)                Portability
              Complete
                 (L2)
Benefits of the SQL standard
   Reduced training costs
   Productivity
   Application portability
   Application longevity
   Reduced dependence on a single vendor
   Cross-system communication

Yes. The standard helps, but each of these benefits
is not as realized as other standards, such as HTML
The SQL Environment
  Server

      Instance
                 Catalog (db)              Catalog (db)
                  Schema        Schema

                   Objects




      Instance



                                     In this class we use:
                                     •SQL Server ist-s-students,1533
                                     •With the default instance
                                     •Each student has their own catalog (db)
                                     •Default schema (dbo)
    SQL Server 2005: ist-s-students
   Other Information:
     SQL Dialect: T-SQL / Transact-SQL
     Case Sensitive Collation?: Yes
     System tables used to represent meta data.
     Client / Server over TCP/IP Connect to database using:
       host/IP, port number, logon, password
       Client software used to manage data/meta data on server
     Everything can be expressed in SQL!
   Naming Rules for Objects in SQL Server:
     Up to 128 characters long
     Must begin with a letter
     Can contain digits, letters, _, .
     Spaces can be used but should be avoided.
My Naming Conventions
  What               Mike’s Convention             Rationale for use
  All object names   Use all lower case letters.   Disambiguation: eg. Employee
                                                   vs. employee for example
  All object names   Use underscore in place of    Avoid the need to place brackets
                     SPACE                         around identifiers.
                                                   employee_phone_number vs.
                                                   [employee phone number]
  Tables             Pluralize                     Should be employees table,
                                                   since it contains more than one
                                                   employee 
  Tables             Qualify with logical schema   Disambiguation of objects
                                                   within the same database: eg.
                                                   fudgemart_employees vs.
                                                   fudgeflix_employees
  Column names       Quality with table name       Helps define scope of object.
                                                   Eg. employee_zipcode .vs
                                                   vendor_zipcode
  Constraints        pk= primary key               Disambiguation of constraints,
                     fk=foreign key                for example:
                     u=unique                      ck_vendor_zipcode .vs.
                     ck=check                      fk_vendor_zipcode
                     i=index
 SQL: Language Breakdown
Domain         SQL Commands   Objects

Metadata       CREATE         Tables, functions,
(DDL)          ALTER          views, procedures, etc
               DROP

DATA           C - INSERT     Tables (as a target)
(DML)          R - SELECT
               U - UPDATE
               D - DELETE
Security       GRANT          Tables, functions,
(DCL)          REVOKE         views, procedures, etc.
Transactions   BEGIN TRANS    Controls DML
(DTL)          COMMIT         statements
               ROLLBACK
A Guide to Syntax Diagrams

  KEYWORD   SQL keywords are in upper case. They
            must be entered exactly as shown.
 variable This represents a user-defined value.

   ...n     This implies the aforementioned pattern
            repeats indefinitely.
     |      The “OR” Operator, meaning select only
            one item from those in the OR list.
    { }     The contents in curly braces are required.

    [ ]     The contents of the braces are optional.
Example: Syntax Diagrams

THE [adjective[, …n]]
{CAT|DOG|HORSE} verb [adverb].

   Examples (syntactically correct???):
     The old dog ate.
     The fat cat walked slowly.

     The sick, brown, ugly horse died.

     The dog slept lazily.
STRUCTURED QUERY
LANGUAGE

        On to the Demos…




  SQL Basics

								
To top