Database Concepts - Syracuse University

Database Concepts - Syracuse University

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-1979System R with Sequel (later SQL) created at IBM Research Lab 1979Oracle markets first relational DB with SQL 1986ANSI SQL standard released 1989, 1992, 1999, 2003, 2007Major ANSI standard updates CurrentSQL 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 Semantic s Allow for Growth / Enhance ment Levels: Minimal (L1) Complete (L2) SQ L Intrinsic Data Structure s Portability

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) Schema

Catalog (db) 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-sstudents 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, _, . My Naming Conventions What Mikes 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 SPACE Avoid the need to place brackets 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 fk=foreign key u=unique ck=check i=index Disambiguation of constraints, for example:

ck_vendor_zipcode .vs. fk_vendor_zipcode SQL: Language Breakdown Domain SQL Commands Objects Metadata (DDL) CREATE ALTER DROP Tables, functions, views, procedures, etc DATA (DML) C - INSERT R - SELECT U - UPDATE

D - DELETE GRANT REVOKE Tables (as a target) BEGIN TRANS COMMIT ROLLBACK Controls DML statements Security (DCL) Transactions (DTL) Tables, functions, views, procedures, etc. A Guide to Syntax Diagrams SQL keywords are in upper case. They must be entered exactly as shown. variable This represents a user-defined value. KEYWORD

...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 The The The old dog ate. fat cat walked slowly. sick, brown, ugly horse died. dog slept lazily. STRUCTURED QUERY LANGUAGE On to the Demos SQL Basics

Recently Viewed Presentations

  • William Blake: A Lecture - ARMYTAGE.NET

    William Blake: A Lecture - ARMYTAGE.NET

    William Blake: A Lecture Introduction The Chimney Sweeper Holy Thursday Introduction Invocation Ballad stanza with trochaic tetra meter Inspiration from Jesus(God) Blake as a prophet-poet The Chimney Sweeper Child Labour in early Industrial Society Reiterating Sunday School Didacticism or Being...
  • Scientific Notation - Campbell County Central

    Scientific Notation - Campbell County Central

    Writing Scientific Notation Scientific notation is written using the product of 2 factors. 1.0 x 106 = 1,000,000 First factor is a number ≥ 1 but < 10. 1,000,000 = 1.0 Second factor is a power of 10 = (106)...
  • Therapeutic Products Bill-consultation phase Cells & Tissues Session

    Therapeutic Products Bill-consultation phase Cells & Tissues Session

    The cells or tissues may be viable or non-viable. Impacts for those supplying engineered cells & tissues (contd) Products that have only non-viable cells and / or tissues and don't act principally by pharmacological, metabolic or immunological action are regulated...
  • Rates of Reaction &amp; Equilibrium - Kenmore-Town of Tonawanda ...

    Rates of Reaction & Equilibrium - Kenmore-Town of Tonawanda ...

    Nature of Reactants . Concentration . Temperature . Surface Area . Catalysts. ... If additional reactants (or products) are added to a reaction system at equilibrium, the eqpoint (point of equilibrium) will shift favoring the reaction that would relieve the...
  • Genomic Prediction Results

    Genomic Prediction Results

    Chr1 Chr2 Haplotype is a set of single nucleotide polymorphisms (SNPs) associated on a single chromosome. Identification of a few alleles of a haplotype block can identify other polymorphic sites in the region.
  • Presentation Title John J. Presenter

    Presentation Title John J. Presenter

    Sub-populations within the Tribe can be identified to address health equity. ... This is especially applicable to the measures related to budget, audit, HIPAA training, and information systems. Examples of ethical issues include, for example, privately constructed sewers, distribution of...
  • PROGRAM MANAGER R Lessons from the Sherman Tank

    PROGRAM MANAGER R Lessons from the Sherman Tank

    German tank superiority is somewhat of a myth. Lethality generally overmatched survivability. Training was very important . Western Front tank engagements usually at 400-800 meters. In Russia and desert they were much longer. LtCol Jarymowycz claims most tank battles fought...
  • Breaking Through: Helping Low-Skilled Adults Enter and ...

    Breaking Through: Helping Low-Skilled Adults Enter and ...

    Multi-college district -more than one college under a board of trustees. ... Do you really want to die on that hill today? Lose gracefully. Being Successful in the Organization . ... Toot your own horn (softly)