LIS 558: Database Management Systems

							                   October 19, 2006

LIS 558: Database Management
                      Systems

             Intro to SQL 2

          Margaret Kipp <mkipp@uwo.ca>
Faculty of Information Studies, University of Western Ontario
                      Outline
●   1. SQL DDL              ●   3. Complex Queries

       CREATE, ALTER and          Subqueries

        DROP
                                   Parameter Queries

●   2. Joins

       INNER JOINS

       OUTER JOINS
SQL Data Definition

    Language
          SQL DDL: Key Commands
●   CREATE

       tables

       indexes

       views (a stored query)

       stored procedures (short scripts like macros)

       Access only supports creating tables and indexes
    SQL DDL: Key Commands (cont.)

●   ALTER

       alters tables, e.g. adding a foreign key, adding a

        column (limited Access support)
    SQL DDL: Key Commands (cont.)

●   DROP

       remove tables, indexes, etc. from the database

        (limited Access support)


●   RENAME

       rename a table (no Access support)
                 SQL CREATE
●   CREATE is a command that allows you to
    create new objects in the database

●   CREATE can be used to create tables, views

    (basically a saved query), indexes, etc.

●   Can do this in Access from menus, but can also

    do it in the SQL View
SQL Datatypes: Conversion Table

Acce ss D a t a t yp e   SQL D a t a t yp e
NUMBER                   INT
TEXT                     VARCHAR(20)
DATE/TIME                DATE
YES/NO                   LOGICAL
MEMO                     VARCHAR(256)
OLE OBJECT               BLOB
HYPERLINK                VARCHAR(100)
       CREATE TABLE: Examples
CREATE TABLE person (

person_id INT NOT NULL,

name VARCHAR(30),

gender LOGICAL,

birthdate DATE,

address VARCHAR(50),

CONSTRAINT pk_person PRIMARY KEY (person_id)

);
CREATE TABLE: Examples (cont.)
CREATE TABLE favouritefood (

person_id INT,

food TEXT(20),

CONSTRAINT pk_favouritefood PRIMARY KEY (person_id, food),

CONSTRAINT fk_person_id FOREIGN KEY (person_id)

     REFERENCES person (person_id)

);
CREATE TABLE: Examples (cont.)
●   The create table command creates a table with
    the listed fields and data types

●   Primary keys and foreign keys can be specified

    in the create table command
CREATE TABLE: Examples (cont.)

●   Constraints or data validation can be specified in the
    create table command

       e.g. NOT NULL at the end of field definitions indicates a

        required value

●   can also use the UNIQUE keyword to specify that an item

    must be unique
             CREATE TABLE Syntax
CREATE TABLE <table> (

column1 Type [constraint],

column2 Type [constraint],

CONSTRAINT <pk_table> PRIMARY KEY (column1),

[CONSTRAINT <fk_column> FOREIGN KEY (<fk_id>) REFERENCES

     <fk_table> (<fk_id>)]

);
          Exercise: CREATE TABLE
●   What is the CREATE TABLE command to create the
    following table?

       the item table contains information about the items in the

        library, primary key is autogenerated, title, author, call

        number and location are required, other fields include

        subject, reading level and status (in library, out on loan, in

        cataloguing, etc)
                     ALTER TABLE
●   If you need to change a table later you can use the
    ALTER command

●   some possible reasons for altering a table include:

       adding a foreign key

       adding a constraint

       changing the table structure
            ALTER TABLE (cont.)
●   Sometimes it is not possible to add a foreign
    key at table creation, especially if the related
    table has not yet been created

●   Constraints may change (e.g. length of loan

    period or total number of items per person)
          ALTER TABLE Examples
●   ALTER TABLE circulation ADD

●   CONSTRAINT unique_item_id UNIQUE (item_id);

●   this command adds a constraint to the circulation

    table, the item_id must be unique to prevent the entry

    of a circulation record for the same item until it has

    been returned (record deleted)
         ALTER TABLE Examples
ALTER TABLE person ADD

email Text(50);

     adds a new field to the user table


ALTER TABLE person DROP COLUMN

phone;

     deletes the phone column from the user table
             ALTER TABLE Syntax
ALTER TABLE <table> (

ADD <column> datatype [NULL|NOT NULL] [CONSTRAINTS]


);


Other commands can be combined with the ALTER TABLE

     command including DROP
           Other Actions for ALTER
●   ADD CONSTRAINT <constraint name> PRIMARY KEY |
    UNIQUE <column>

●   ADD CONSTRAINT <constraint name>

FOREIGN KEY <column> REFERENCES <table of primary key>

    (primary key)

●   DROP <column>

●   DROP CONSTRAINT <constraint name>
         Exercise: ALTER TABLE
●   add a column to the person table for title

●   add a column to the favourite food table for

    comments
                        DROP
●   used to remove a table from the database or delete an
    entire database

●   DROP TABLE <table>;

●   DROP DATABASE <database>;

●   to be used with caution, cannot be undone and will

    delete all data
JOINS
                       Joining

●   two basic types of joins:

       INNER JOIN (default when using comma separated

        tables and a WHERE clause)

       OUTER JOIN (LEFT, RIGHT, FULL)

●   each type returns a different set of information
  SQL Join Types

LEFT             RIGHT




           I
           N
Table 1    N     Table 2
           E
           R




          FULL
                   INNER JOINS
●   INNER JOINS are automatically created when using
    commas and a WHERE clause

●   select all titles marked ReadAgain

    SELECT Book.Title

    FROM Book INNER JOIN ChildAndBOOK ON (book.id =

      childandbook.bookid)

    WHERE ReadAgain = True;
           INNER JOINS (cont.)
●   OR, allowing a default to INNER JOINS:

SELECT Book.Title

FROM Book, ChildAndBOOK

WHERE book.id = childandbook.bookid AND

    ReadAgain = True;
            INNER JOINS (cont.)
●   the syntax is different, but the results should be
    identical

●   all the usual modifiers and clauses can be used

    with INNER JOINS (e.g. DISTINCT, GROUP

    BY)
      More Complex INNER JOINS
●   joining 3 or more tables is more complex

●   e.g. list all books read by early readers

●   need to join the book table, the child and book

    table and the child table
                More Complex (cont.)
●   INNER JOIN can only join two tables, we must join two
    of the tables and then join this joined table to the third
    table

        e.g. join the child and childandbook tables then join this to

         the book table


●   equivalent to creating a temporary table consisting of

    child and childandbook
         More Complex (cont.)
SELECT Book.Title

FROM Book INNER JOIN (ChildAndBook INNER JOIN

 Child ON ChildAndBook.childid = Child.LibraryCard)

 ON Book.ID = ChildAndBook.bookid

WHERE Child.ReadingLevel = "Early Readers";
          Exercises: INNER JOIN
●   get the name of all children from the main
    branch

●   ?

●   get the titles of all books with comments

●   ?
               OUTER JOINS
●   INNER JOINS require that data matches on
    both sides of the join, OUTER JOINS do not

●   three types of OUTER JOINS: LEFT, RIGHT,

    FULL (LEFT is most common)

●   Access does not support FULL OUTER JOINS
               LEFT OUTER JOIN
●   gets all records from the left table (in the SELECT
    statement) regardless of whether there are matches in
    the right table

●   can be useful for seeing what is being eliminated by

    an INNER JOIN, i.e. what values do not have

    corresponding values in the right table
      Example: LEFT OUTER JOIN
●   get a list of all senior reading level titles along
    with whether the child would read them again
    and any comments, regardless of whether the
    book has been read
   Example: LEFT OUTER JOIN
SELECT Book.Title, ChildAndBook.ReadAgain,
 ChildAndBook.Comment

FROM Book LEFT OUTER JOIN ChildAndBook

 ON (Book.ID = ChildAndBook.bookid)

WHERE Book.ReadingLevel = "Senior";
      Exercise: LEFT OUTER JOIN
●   get a list of branches and the names and ages
    of all children who registered for the summer
    reading programme regardless of whether any
    children signed up at the branch
                RIGHT OUTER JOIN

●   extremely rare that this would be useful

●   usually indicates a problem with referential

    integrity

●   e.g. a foreign key references a non-existent

    primary key from another table
             FULL OUTER JOIN
●   selects all data from both tables involved in the
    join regardless of whether or not matches exist

●   also extremely rarely used

●   not supported by Access
Complex WHERE Clauses
                     Subqueries
●   SQL permits subqueries within:

       SELECT, INSERT SELECT or SELECT INTO

       DELETE or UPDATE

●   a subquery is just another select statement inside the

    main query

●   can be used in the WHERE clause
             Subquery Syntax
●   1. WHERE <Comparison> [ANY | SOME | ALL]
    <Subquery>

●   2. WHERE <Expression> NOT IN <Subquery>

●   OR 3. [NOT] EXISTS <Subquery>
        Examples Subquery Type 1
●   return all books with higher point values than the
    lowest ranked Senior books

SELECT Book.Title, Book.ReadingLevel

FROM Book

WHERE Book.Points > ANY (SELECT Book.Points

    FROM Book WHERE Book.ReadingLevel = "Senior");
       Examples Subquery Type 2
●   select all books that have not been read

SELECT Book.Title

FROM Book

WHERE Book.ID NOT IN (SELECT

    ChildAndBook.bookid FROM ChildAndBook);
            Examples Subquery 3
●   select all books that have not been read yet

SELECT Book.Title

FROM Book

WHERE NOT EXISTS (SELECT * FROM ChildAndBook

    WHERE Book.ID =ChildAndBook.bookid);
            Exercise: Subqueries
●   select the names of all children who have not
    yet read a book
             Parameter Queries
●   parameterised queries allow the user to specify
    what is being searched for

●   in QBE, parameters are indicated by square

    brackets [] and it is the same in Access SQL

●   e.g. [ReadingLevel] or [Enter a User Name]
      Exercise: Parameter Queries
●   get the titles of all books that a given child has
    read (use child id as the parameter)