LU03 Notes - DbWiki by panniuniu

VIEWS: 1 PAGES: 9

									IST459: I NTRODUCTION TO SQL

T ABLE     OF    C ONTENTS

Topic - Introduction to SQL ............................................................................................................................................2

   Learning Objectives ...................................................................................................................................................2

       SQL Goals ...............................................................................................................................................................2

   Part 1: SQL: What it is and what it’s not. ...................................................................................................................2

       SQL: Tow-may-tow, Tow-mah-tow? ......................................................................................................................3

       SQL: Not really a programming language per-se ................................................................................................3

       SQL: The Not-So-Standard, Standard ....................................................................................................................3

       ANSI SQL Standards ...............................................................................................................................................4

       Popular DBMS Software and Their SQL Implementations .....................................................................................4

       A word about SQL Implementation .......................................................................................................................4

   Part 2: SQL Language Internals ..................................................................................................................................4

       Case Sensitivity ......................................................................................................................................................4

       Naming conventions ..............................................................................................................................................5

       Comments..............................................................................................................................................................5

       Data Definition Language (DDL) ............................................................................................................................6

       Data Manipulation Language (DML) ......................................................................................................................6

       Data Control Language (DCL) .................................................................................................................................6

       Data Transaction Language (DTL) ..........................................................................................................................6

   Part 3: Syntax of SQL commands ...............................................................................................................................6

       Table Creation........................................................................................................................................................6

       SQL Data Types on Microsoft SQL Server ..............................................................................................................7

       Table Manipulation ................................................................................................................................................7

       Table Removal .......................................................................................................................................................7




                                                                                 Page 1
     Add data to a table ................................................................................................................................................7

     Read data from a table ..........................................................................................................................................8

     Update data in a table ...........................................................................................................................................8

     Remove data from a table .....................................................................................................................................8

     SQL Operators ........................................................................................................................................................8

  Appendix: Other important things to know ..............................................................................................................9

     dbo? Oh my! ..........................................................................................................................................................9

     INFORMATION_SCHEMA .......................................................................................................................................9




T OPIC - I NTRODUCTION                 TO    SQL



L EARNING O BJECTIVES

In this learning unit we will explore SQL, the database language used to implement the relational model in popular
DBMS products. Upon completion of this learning unit you should be able to:

         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.

SQL G OALS

Our SQL goals for this learning unit are to:

    1.    Understand how to manipulate tables with the CREATE TABLE, ALTER TABLE, and DROP TABLE commands
    2.    Understand basic use of the “fantastic four” DML commands: INSERT, SELECT, UPDATE and DELETE

P ART 1: SQL: W HAT                IT IS AND WHAT IT ’ S NOT .

SQL is a popular computer language for data and meta-data management in a relational DBMS. With SQL you can
create database structures, such as tables, constraints, and indexes and then populate the structures with data
programmatically. For the most part, SQL is a comprehensive language for controlling and manipulating all aspects
of the DBMS. As part of the implementation model, SQL is used to create the internal and external models from a
logical model. So if the conceptual model represents the ideas for a database, and the logical model represents the
actual blueprint for it, then SQL is the tool used to build the actual database implementation of the SDLC (aka
internal and external models).



                                                                             Page 2
SQL: T OW - MAY - TOW , T OW - MAH - TOW ?

First, is SQL pronounced “Ess-Queue-Elle” or “Sequel”? According to ANSI (http://www.ansi.org) the pronunciation
it is the former, S-Q-L, but I’ve heard both used both ways in practice. I suggest just accepting there is no right or
wrong way to pronounce it, and just move on. 


SQL: N OT    REALLY A PROGRAMM ING LANGUAGE PER - SE

SQL as a programming language is much different from C, Java or Visual Basic. The latter are general purpose
procedural programming languages and you can write almost anything in them. SQL is a set-based declarative
computer language that is domain specific. Declarative means you focus on “what you need to do” rather than on
“how you need to do it”. Domain specific implies the language solves a focused set of problems.

SQL is notoriously easily to learn, yet difficult to master. This is most likely because the language is not well suited
to solving more complicated problems. Accomplishing some tasks in SQL are so downright trivial you’ll wonder if
you’re even “programming”, while others require you re-learn what you may already know and understand about
computer programming in order to accomplish the task. Most often you need to re-think your logic to operate on
sets of data versus the individual items of data.


SQL: T HE N OT -S O -S TANDARD , S TANDARD

SQL was developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the 1970’s. Their concept was based
upon E. F. Codd’s paper on relational design, which we introduced earlier on. SQL was adopted as a standard by
ANSI in 1986, however by that time several DBMS implementations were already available, notably ones from IBM
and Oracle. Since that date SQL has always been driven by the market leaders as opposed to the actual standard.



                                                       Page 3
In addition, customers with production databases implemented in their DBMS of choice are often reluctant to
change database design to conform to the standard, forcing vendors to maintain backwards compatibility. DBMS
vendors are in direct competition with each other for your DBMS dollars, and they know once you select a product
your odds of switching low. As such, standards adherence takes a back seat to adding more whiz-bang features,
providing stability and good performance. The end result is a not-so-standard standard.


ANSI SQL S TANDARDS

Year Name          Comments

1986 SQL-86        First Standard, also called SQL-87
1989 SQL-89 (SQL1) Minor revision, the baseline of SQL.
1992 SQL-92 (SQL2) New Join syntax added
1999 SQL-99 (SQL3) Triggers, object-oriented features
2003 SQL:2003      XML support, Windows functions, cursors, identity columns
2006 SQL:2006      More XML support, XQuery support




P OPULAR DBMS S OFTWARE          AND   T HEIR SQL I MPLEMENTATIONS

DBMS Name             Maintainer          License Model

Oracle Database (10g) Oracle              Proprietary
DB2                   IBM                 Proprietary
SQL Server 2008       Microsoft           Proprietary
MySQL 5               Sun / MySQL AB      Open-Source
PostgreSQL            PostgreSQL GDG Open Source
Firebird              Firebird Foundation Open Source




A   WORD ABOUT     SQL I MPLEMENTATION

In this course, we will use Microsoft SQL server as our primary DBMS for learning about SQL and the internal and
external data models. It is important to understand that while we use MS SQL server for our examples, the focus of
this course is not SQL server training, but database design and management principles in general.

To get the most mileage out of our learning, I will make sure to differentiate between those items that are specific
to Microsoft SQL server and those which are part of the SQL standard. In addition, for any proprietary information,
we will discuss how it is implemented in other DBMS products.


P ART 2: SQL L ANGUAGE I NTERNALS

C ASE S ENSITIVITY




                                                     Page 4
In SQL keywords are case insensitive, thus the SELECT, SelecT and select commands are all the same command.
However, the data and the object definitions and data within the database can be set to either case-sensitive or
case-insensitive based on the default collation on the database. In a case-sensitive collation, tables named
Employee and employee are two separate tables! Yikes! It goes without saying that caution must be exercised,
which is why you need:


N AMING      CONVENTIONS

Naming conventions or “coding style” are a set of rules for naming programming objects. In SQL, we use naming
conventions to identify tables, columns, constraints and other objects. While not actually a part of SQL itself,
naming conventions become imperative as database designs get complex and there are multiple developers with
hands in the pot. Many a battle has been waged in the corporate, open source and internet communities over
naming conventions, especially over the use of Hungarian Notation.

MY OPINION OF NAMING CONVENTIONS IS THAT ANY ONE IS GOOD AS LONG AS YOU ADOPT AND ADHERE TO IT.
THE POINT OF NAMING CONVENTIONS IS STANDARDIZATION FOR CONSISTENCY AND READABILITY.

That being said here are some naming conventions I use in SQL:


What           The Fudge Convention           Rationale for use

All object
               Use lower case letters only    Disambiguation e.g. Employee vs. employee
names
All object     Use underscore in place of
                                              Avoids the need to place brackets around identifiers.
names          SPACE
                                              Since a table contains many 'things' it should be plural eg.
Tables         Pluralize
                                              Employees
                                              When learning SQL, helps disambiguate different tables in the same
Tables         qualify with logical schema
                                              database eg. fudgemart_customers vs. fudgeflix_customers
Column                                        When learning SQL, helps you define scope vendor_zipcode vs.
               Qualify with table name
names                                         employee_zipcode
               pk=primary key, fk=foreign key Disambiguation of constraints ck_vendor_zipcode vs.
constraints
               u=unique, ck=check, i=index    fk_vendor_zipcode




C OMMENTS

The ability to document what we do is truly what separates us from the animals, so use it. To place a comment in-
line, use double-dashes - -, to place a multi-line comment use /* */. SQL Comments are ignored by the SQL
interpreter, but useful to people! You may even find them useful later on to yourself if you need to correct your
own work or find an error.

For example:

-- This is a one line comment




                                                     Page 5
/* This comment
spans several
lines */



D ATA D EFINITION L ANGUAGE (DDL)

DDL commands are used to create, modify and delete the meta-data structures of the DBMS, such as tables and
indexes. There are three main DDL commands:

       CREATE - make a new object
       ALTER - manipulate an existing object
       DROP - delete an existing object, such as a table.



D ATA M ANIPULATION L ANGUAGE (DML)

DML commands manage the data in the tables. There are 4 DML commands, corresponding to each of the 4 CRUD
operations:

       INSERT INTO - creates or adds data into a table.
       SELECT - reads data from a table
       UPDATE- updates data in a table
       DELETE- removes data from a table.



D ATA C ONTROL L ANGUAGE (DCL)

The DCL commands process the authorization aspects of the database (who has access to what). We will learn
more about DCL in the database security learning unit.

       GRANT - Add rights.
       REVOKE - Remove rights



D ATA T RANSACTION L ANGUAGE (DTL)

The DTL commands allow the user to programmatically control and manage how data is manipulated in the
database. We will learn more about DTL and transactions in general during the database transactions and
concurrency control learning unit.


P ART 3: S YNTAX   OF   SQL   COMMANDS


T ABLE C REATION

CREATE TABLE tablename (
 colname datatype [NOT] NULL



                                                     Page 6
    ,...n
    ,CONSTRAINT name PRIMARY KEY(pkcolname, ..n)
    ,CONSTRAINT name CHECK (expression),
    ,CONSTRAINT name UNIQUE(ucolname, ...n),
    ,CONSTRAINT name FOREIGN KEY(fkcolname, ...n)
       REFERENCES fktablename(pkcol, ...n)]
)


SQL D ATA T YPES ON M ICROSOFT SQL S ERVER

Data Type                SQL Std? Description

char(length)             Y        Fixed-length character string of size length
varchar(length)          Y        Variable-length character string of size length
text                     Y        Variable-length character of up to 2GB, not indexable
numeric(precision,scale) Y        Fixed-length character string of size length
decimal(precision,scale) Y        Same as numeric
smallint                 Y        16-bit integer (2 bytes)
int                      Y        32-bit integer (4 bytes)
bigint                   Y        64-bit integer (8 bytes)
float                    Y        floating-point decimal
bit                      Y        One bit. Used for true/false and yes/no values
uniqueidentifier         N        A globally-unique 16-byte idenfier
rowversion               N        A timestamp used for replication
money                    N        synonym for decimal(18,4)


For a complete list of data types available in Microsoft SQL Server including samples of their usage, visit the SQL
Server books online: http://msdn.microsoft.com/en-us/library/ms187752.aspx


T ABLE M ANIPULATION

ALTER TABLE tablename
{ADD colname datatype colsettings}
| {ADD CONSTRAINT constraintinfo}
| {ALTER COLUMN colname colsettings}
| {DROP COLUMN|CONSTRAINT name}



T ABLE R EMOVAL

DROP TABLE tablename


A DD   DATA TO A TABLE

INSERT INTO tablename (
colname, ...n)
VALUES (data , ...n)




                                                      Page 7
R EAD   DATA FROM A TABLE

SELECT {colname , ..n | * }
FROM tablename
WHERE condition
ORDER BY colname [, ..n]


U PDATE   DATA IN A TABLE

UPDATE tablename SET
colname = data
, ..n
WHERE condition


R EMOVE   DATA FROM A TA BLE

DELETE FROM tablename
WHERE condition


SQL O PERATORS

These operators are used to test conditions for the WHERE clause and CHECK constraints


Operator Type          Purpose                         Example Usage

+           Arithmetic Addition, string concatenation a+b
-           Arithmetic Subtraction                      a-b
*           Arithmetic Multiplication                   a*b
/           Arithmetic Division                         a/b
>           Comparative Greater than                    a>b
<           Comparative Less than                       a<b
=           Comparative Equal to                        a=b
<>          Comparative Not equal to                    a<>b
>=          Comparative Greater than or equal to        a>=b
<=          Comparative Less than or equal to           a<=b
between Comparative Range checking                      a between c and d
is null     Null        Check for null                  a is null
is not null Null        Check for not null              a is not null
AND         Boolean     Logical AND - both must be true a AND b
OR          Boolean     Logical OR - one must be true a OR b
NOT         Boolean     Negation                        NOT a
exists      Set         check for existence             exists SQL
in          Set         check for membership            a in (1,2,3)
()          Misc.       Order of operations             (a > b) OR (c < d)


For a complete list of SQL operators supported by Microsoft SQL Server, visit this section of the SQL Server books
online: http://msdn.microsoft.com/en-us/library/ms174986.aspx



                                                     Page 8
A PPENDIX : O THER    IMPORTANT THING S TO KNOW


DBO ?   O H MY !

In Microsoft SQL Server, each DDL object can be prefixed with a name followed by a period. This represents the
owner of the object, and by default is the owner dbo or database owner. You can actually have two object names
in a database with different owners, such as mafudge450.employees and dbo.employees, which would be
considered different objects! Oh my!


INFORMATION_SCHEMA

The question you might be asking yourself at this point is if there’s a way to list the tables in a database or list
the columns in a table? Well, every DBMS implements the meta-data in its own internal structures of tables,
known as system tables. Yes, this is the ultimate in dogfooding. You can query the system tables using the
INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS system objects.




                                                       Page 9

								
To top