Docstoc

Structured query language

Document Sample
Structured query language Powered By Docstoc
					Structured query language




            SQL
SQL basics
   SQL allows you to access a database
   SQL executes queries against a database
   SQL commands are case independent.
       SELECT = select

   But column names or DATA are not case
    independent.
   SQL command are named after english words:
       Create, select, insert, update ...
       It's easy to learn
SQL is a standard
   SQL is an ANSI (American National Standards
    Institute) standard computer language for accessing
    and manipulating database systems.
   There are currently 2 main standards in use SQL92
    and SQL99
   Most relational databases conform to one of these
    standards
       They have also their own proprietary extensions in
        addition to the standard
DDL: create the tables

   Create table syntax
    CREATE TABLE tablename (
    column1 data_type [not null] [unique] [column_constraint] ,
    ...
    [table constraints]);

   Example:
    CREATE TABLE person (
          personID varchar(5) NOT NULL,
          Name varchar(25) NOT NULL,
          Firstname varchar(15) NULL);
SQL3 main data types
   Integer, float, Decimal [(precision[,scale])]
       Scale is the number of digit after the comma

   Double precision [(precision[,scale])]
       for more numbers after comma

   Char(length)
       Attention to length: space is reserved even if not used

   Varchar(length)
       No space pre-reserved.
       To use for variable length fields.
more...
   BLOB (size)
       Binary Large Object

   CLOB (Size)
   Character Large ObjectDate
       Dates relatives to UST
       Format YYYY-MM-DD
       Databases may have local date format

   Time
       HH:MM:SS
Constraints

   NOT NULL : the column cannot take the NULL
    value
   NULL : the column can take the null value (default)
   UNIQUE : the column has unique values
   DEFAULT def_val : if no value is given, the column
    takes the default value def_val
Example

CREATE TABLE person (
       personID varchar(5) NOT NULL UNIQUE,
       Name varchar(25) NOT NULL,
       Firstname varchar(15) NULL,
       City varchar(20) default 'Pula'
       age decimal);


   Decimal without options represents an integer
DDL: alter table

   Modifying the structure of a table. Add a column:
ALTER TABLE <tablename> ADD <column_name>
    <data_type> [DEFAULT <value>]
    [<constraint>]
       A new column cannot be NOT NULL, but can have a
        default value
Alter table ...

   modify the length of a column:
ALTER TABLE <tablename> MODIFY
    (<column_name>     <data_type> [DEFAULT
    <value>] [<constraint>])
   Add a constraint
ALTER TABLE <tablename> ADD <constraint>
DDL: drop table

   Delete a table
DROP TABLE tablename;


   It is very easy and fast to type the drop table
    command -> be careful !!!
    DML: Update Operations

INSERT - inserts a list of attributes .
        Can potentially violate any (or all) of the 4 constraints.
     INSERT INTO PERSON VALUES (008, Tome, Hector, J);

DELETE - removes a row from a table.
        Can potentially violate referential integrity constraint.
     DELETE FROM PERSON WHERE ID = 7;
   Attention “DELETE FROM PERSON” will delete
    the whole table
Update Operations

UPDATE - changes the value of one or more
 attributes in a row.
      When modifying an attribute that is not a primary
       key or foreign key, there is usually no danger of
       violating a constraint.
      When modifying a primary key, it is equivalent to
       doing a delete followed by an insert operation.
   UPDATE PERSON SET ID = 007 WHERE
     LastName=‘Tome’ AND FirstName=‘Pat’;
Select
   Select is used to retrieve data :

SELECT [DISTINCT] <column(s)>
  FROM <table(s)>
 [WHERE <condition>]
[ORDER BY <column(s) [ASC | DESC]>]

       In uppercase are the SQL keywords
       Between [] optional conditions
       Between <> what correspond to your table(s) definition
Select
                                FirstName   SurName

                                 JAMES       KIRK

Select all data                  JAMES       BOND

                                   ...       ...



   SELECT FIRSTNAME, SURNAME
    FROM PERSON


Select specific data, ordered


  SELECT FIRSTNAME, SURNAME FROM PERSON WHERE
    SURNAME = ‘BOND’ORDER BY FIRSTNAME ASC
Select

Select all data where person firstname is “JAMES”
   SELECT * FROM PERSON WHERE FIRSTNAME='JAMES'
       PersonID FirstName SurName MidInitial
        045      JAMES     KIRK       T
         007     JAMES     BOND       X

 Select all data where person firstname is “JAMES” and surname is “BOND”
     SELECT * FROM PERSON WHERE FIRSTNAME='JAMES'
     AND SURNAME = 'BOND'
Select all data where person firstname begins with “JAM”
SELECT * FROM PERSON WHERE FIRSTNAME like 'JAM%'




      PersonID FirstName SurName MidInitial
       045      JAMES     KIRK       T
        007     JAMES     BOND       X
More examples
   Set conditions: <column> [NOT] IN (<list of
    values>)
       SELECT firstname FROM person WHERE surname IN
        ('BOND','KIRK');

   Null Value: <column> IS [NOT] NULL
       SELECT firstname FROM person WHERE MidInitial IS
        NOT NULL;

   Domain conditions: <column> [NOT] BETWEEN
    <lower bound> AND <upper bound>
Set Theoretic Operations

   Union, intersection, difference.
 Can only be applied to tuples (rows) of the same
  type.
PERSON
    PersonID   FirstName    SurName         MidInitial



PUBLICATION
    PubID      PersonID    Date   Journal      Authors   Title
Union
    Query: Give me the IDs of all people called „Smith‟, and
     those who have published in „Science‟.

SELECT PERSONID FROM PERSON WHERE SURNAME='SMITH
      UNION
SELECT PERSONID FROM PUBLICATION WHERE
  JOURNAL=                    'SCIENCE'


   The UNION operation appends rows from the first
    table to the rows of the seond table. Duplicate rows are
    eliminated.
Intersection
    Query: Give me the IDs of all people called „Jones‟ who have
     a paper in „Nature‟.



SELECT PERSONID FROM PERSON WHERE SURNAME=‘JONES’
      INTERSECT
SELECT PERSONID FROM PUBLICATION WHERE
  JOURNAL=                    ‘NATURE’


   The intersection of two tables could be seen as a third
    table that contains the common rows.
Difference
    Query: Give me the IDs of all people called „Jones‟who don‟t
     have a paper in a journal.



SELECT PERSONID FROM PERSON WHERE SURNAME=‘JONES’
    MINUS
SELECT PERSONID FROM PUBLICATION


   The difference of two tables is a third table that contain
    those rows that occur in the first table but not in the
    second.
   JOINS

Used to combine related tuples into a single tuple.


 PERSON

    PersonID FirstName SurName MidInitial      AddressID

  ADDRESS

    ID    HouseNo Street Town     PostCode
Often combine joins with a project to extract the
 fields of interest.


E.g. Is there a correlation between names and
  geographical area?


SELECT PERSON.FirstName ADDRESS.PostCode FROM
  PERSON, ADDRESS
WHERE PERSON.AddressID = Address.ID
Aggregate Functions
COUNT - returns the number of tuples.
  SELECT COUNT(*) FROM PERSON

SUM - sums all values for a column.
  SELECT SUM(Salary) FROM EMPLOYEE

MAX, MIN - gives max/min for a column.
  SELECT MAX(Salary) FROM EMPLOYEE

SIN, LOG, SQRT, ABS......
String Operations

   upper(<string>) : converts string to uppercase
   lower(<string>) : to lower case
   initcap(<string>): converts the first letter of every
    word in string to uppercase
   substr(<string>,n,[m]): extracts the part of string
    comprised between n and m. If m is not specified,
    the end of string is assumed.
    Views
VIEWS - „Virtual tables‟ created from actual tables.
CREATE VIEW ADDRESS_LABEL AS
 SELECT HouseNo, Street, Town, ADDRESS.Postcode
 FROM ADDRESS, POSTCODE
 WHERE ADDRESS.PostCode = POSTCODE.PostCode


   A view can be used the same way as a table
   Not all database support modification of data using the
    view (but it is in SQL99)
   A view is deleted using the “delete view_name”
    command

				
DOCUMENT INFO