Web-Enabled Decision Support Systems

Document Sample
Web-Enabled Decision Support Systems Powered By Docstoc
					     Web-Enabled Decision Support Systems
              SQL: Creating and Processing RDBs




Prof. Name                                        name@email.com
Position                                            (123) 456-7890
University Name
                              1
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                   2
                              Introduction
   Structured Query Language (SQL) is a complete and widely used
    relational database (RDB) query language
    – Originally developed by IBM in the late 1970s
    – Currently the industry standard

    – Not a complete programming language
         Like Visual Basic, C/C++, or Java
    – SQL statements may be embedded into general purpose programming
      languages to create database applications


   Used for management of database objects:
    – Creation
    – Manipulation
    – Control




                                          3
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                   4
               Types of SQL Statements
   We divide SQL statements into four categories based on the database
    operations they perform:
    – Data Definition Language (DDL) statements
         Define data by creating, altering, and destroying database objects


    – Data Manipulation Language (DML) statements
         Manipulate (i.e., select, insert, update, and delete) data in the database tables


    – Data Control Language (DCL) statements
         Configure and control the database permissions, roles, and referential integrity
          controls


    – Database Stored Procedure Language (DSPL) statements
         Used for triggers, event handlers, and stored procedures in transaction
          processing




                                            5
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  6
    The Data Definition Language (DDL)
   Data Definition Language (DDL) statements define the structure of
    the data in a database

    – Create, alter, and drop database objects like Tables, relationships, keys,
      and indexes.

    – DDL statements can perform visual steps performed in Chapter 6 and 7 to
      create tables, keys, indexes, and relationships.

    – We focus on CREATE, DROP, and ALTER DDL statements for database
      tables.




                                        7
        Review of Database Terminology
   Tables are used to store and relate data
     – Two-dimensional grid
          Columns: data fields
          Rows: records


   A primary key is an attribute or minimal set of attributes that uniquely
    identifies each record within the table

   A foreign key is an attribute or a set of attributes in a table that acts as
    a primary key to the same or another table in the database

   A database index is the ordered list of indexing attributes with a
    pointer to table rows where the actual record can be found




                                        8
                The CREATE Statement
   CREATE statements create database objects
    – Define primary and foreign keys
    – Create an index on a table attribute

   Syntax:

     CREATE TABLE [Table Name]

                    ({<Column name> <Data type> <Constraint>}, {}…)

     CONSTRAINT     {<Key Name> <key Type> (Field1, Field2…)}, {}…;




                                        9
          CREATE Statement - Example
   Query:
    – “Create a new table, tblDepartment, to store department information. Store
      the department identification number, name, address, and phone number
      for all the departments in the university database.”


   SQL Statement:
     CREATE TABLE tblDepartment

                   (DeptID    VARCHAR2 (10)      NOT NULL,

                    Name      VARCHAR2 (50)             ,

                    Address   VARCHAR2 (200)            ,

                    Phone     NUMBER (30)        DEFAULT 0,

                    CollegeID VARCHAR2 (3)                  )

     CONSTRAINT   DeptPK      PRIMARY KEY (DeptID)

                  DeptFK FOREIGN KEY (CollegeID) REFERENCES tblCollege (CollegeID);




                                            10
                   SQL Field Constraints
   The following tables lists a few column constraints that can be used
    with the CREATE TABLE SQL statement:

     Constraints         Meaning                    MS Access Equivalent

     NOT NULL            Cannot be null.            Required.

     NULL                Set to null.               Allow Zero Length.

     UNIQUE              Should be unique.          -

     DEFAULT VALUE       Set the default value.     Default Value.

     CHECK (Condition)   Check for the condition.   Validation Rule.




                                             11
              CREATE Index - Example
   Query:
    – “Create an index (named tblDeptAddressIndx) on the Address field of the
      department table.”


   SQL Statement:

    CREATE INDEX         tblDeptAddressIndx

    ON                   tblDepartment (Address);




                                      12
                 The DROP Statement
   DROP statements removes database objects
    – Table, Index

   Syntax:

    DROP TABLE [Table Name];

    DROP INDEX [Index Name];




                                13
           DROP Statement - Examples
   Query 1:
    – “Remove the department table, its data, columns, and indexes.”


   SQL Statement 1:
    DROP TABLE tblDepartment;




   Query 2:
    – “Remove only the index on the Address field of the department table.”


   SQL Statement 2:
    DROP INDEX    tblDeptAddressIndx;




                                       14
                   The ALTER Statement
   ALTER statements alter existing database objects
    – Addition or deletion of columns or constraints
    – Revision of data types and field constraints


   Add Syntax:
     ALTER TABLE          [Table Name]

     ADD COLUMN           [<Column name> <Data type> <Constraint>]

     ADD CONSTRAINT       [<Key Name> <key Type> (Field1, Field2…)];

   Drop Syntax:
     ALTER TABLE          [Table Name]

     DROP COLUMN          [Column name]

     DROP CONSTRAINT      [Key Name];



                                       15
         ALTER Statement – Example 1
   Query:
    – “Add a field for the department chairperson to the department table. Make
      this field a foreign key in the faculty table.”


   SQL Statement:

ALTER TABLE         tblDepartment

ADD COLUMN          Chair        NUMBER (10)

ADD CONSTRAINT DeptFK2 FOREIGN KEY(Chair) REFERENCES tblFaculty(FacultyID);




                                      16
         ALTER Statement – Example 2
   Query:
    – “Drop the Chair column and DeptFK2 constraint from the department
      table.”


   SQL Statement:

     ALTER TABLE          tblDepartment

     DROP COLUMN          Chair

     DROP CONSTRAINT      DeptFK2;




                                     17
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  18
The Data Manipulation Language (DML)
   Data Manipulation Language (DML) statements involve selection,
    insertion, update, and deletion operations

    – The core and most frequently used SQL statements

    – The manipulation involves selection, insertion, updates, and deletion
      operations and the SELECT, INSERT, UPDATE, and DELETE statements
      are used to perform these operations, respectively

    – All DML statements have equivalent Access query types




                                     19
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  20
                 The SELECT Statement
   SELECT statements are by far the most frequently used SQL
    statements
     – Used for a wide variety of purposes


   Structure:
      SELECT
                        Mandatory
      FROM

      WHERE

      GROUP BY
                        Optional, but must appear in this order
      HAVING

      ORDER BY




                                       21
        Projection: The SELECT Clause
   The basic SELECT statement presented below projects the selected
    table columns as a query output
    – SELECT clause
         Specify the columns of query output
    – FROM clause
         List the tables involved in the query design to select columns from


   Syntax:
     SELECT [Field name (s)]

     FROM     [Table name];




                                           22
Projection: The SELECT Clause - Examples
    Query 1:
     – “Project the student name, email, address, and phone fields from the
       student table.”


    SQL Statement 1:
      SELECT Name, Email, Address, Phone

      FROM      tblStudent;



    Query 2:
     – “Project all columns from the student table.”


    SQL Statement 2:
      SELECT * FROM tblStudent;



                                         23
          Hands-On Tutorial: Running
    DML Statements in the Access Environment
   How-to: Write, Execute, and Test SQL Statements in Access
     1. Click the Create Query in Design View option in the Database Window to
        open a new query in the Design View.
     2. Click the Close button in the Show Table dialog box without adding any
        tables to open the SQL View.
     3. Write the SQL statement and click the Run (!) button on the toolbar to
        execute the query.




           Running DML SQL Statements in the Access Environment


                                       24
                Join: The FROM Clause
   Join systematically draws data from multiple tables in order to provide
    a unified view of data in individual tables
     – A powerful and frequently used operation
     – We join tables in SQL by specifying table names (separated by commas)
       in the FROM clause


   Syntax:

     SELECT [Field name (s)]

     FROM     [Table1, Table 2, Table 3...];




                                      25
      Join: The FROM Clause - Example
   Query:
    – “Select the contact information and department name for faculty
      members.”


   SQL Statement:

    SELECT Name, Email, Address, Phone, Name

    FROM     tblFaculty, tblDepartment;




                                       26
         Renaming Query Tables and Fields
             Using the Keyword “AS”
   To resolve conflicts, identify columns by prefixing tables’ names:

SELECT tblFaculty.Name, Email, Address, Phone, tblDepartment.Name

FROM    tblFaculty, tblDepartment;




   If table names are long or complex, use the keyword AS to rename:

SELECT Fac.Name, Email, Address, Phone, Dept.Name

FROM    tblFaculty AS Fac, tblDepartment AS Dept;




                                      27
           Selection: The WHERE Clause
   The WHERE clause is an optional clause of the SELECT statement
    used to specify selection criteria
    – Often used to specify the selection condition of an inner join
         Usually equate the primary key and foreign key values
    – Can include following operators:
         Arithmetic Operators: =, <, <=, >, >=
         Clause Operators: BETWEEN, LIKE, IN
         Logical Operators: AND, OR, NOT


   Syntax:
    SELECT [Field Name (s)]

    FROM      [Table Name (s)]

    WHERE     [Criteria];




                                          28
Selection: The WHERE Clause - Example
   Query:
     – “Select the contact information and department name of the faculty
       members who joined the university after 1995 and whose salary exceeds
       $75,000.”


   SQL Statement:
     SELECT Fac.Name, Email, Address, Phone, Dept.Name

     FROM     tblFaculty AS Fac, tblDepartment AS Dept

     WHERE    (Fac.DeptID = Dept.DeptID) AND (Salary > 75000)

              AND (JoiningDate > #31/12/1995#);


Join Clause                                                Filter Clauses




                                      29
    Using the BETWEEN Clause Operator
   The BETWEEN clause operator is used in conjunction with the logical
    AND operator to specify a range of values

   Query:
       – “Select the contact information and department name of the faculty
         members whose salary is between $65,000 and $75,000 and who joined
         the university after 1995.”


   SQL Statement:
SELECT Fac.Name, Email, Address, Phone, Dept.Name

FROM      tblFaculty AS Fac, tblDepartment AS Dept

WHERE     (Fac.DeptID = Dept.DeptID) AND (Salary BETWEEN 65000 AND 75000)

          AND (JoiningDate > #31/12/1995#);




                                       30
         Using the LIKE Clause Operator
   The LIKE clause is widely applied in search criteria
     – Often used with the wildcard character: (*)


   Query:
     – “List the contact information for all the industrial engineering students who
       have taken computer- or database-related courses.”


   SQL Statement:

SELECT T1.Name, Email, Address, Phone

FROM tblStudent AS T1, tblTranscript AS T2, tblSection AS T3, tblCourse AS T4

WHERE (T1.StudentID = T2.StudentID) AND (T2.SectionID = T3.SectionID)

         AND (T3.CourseID = T4.CourseID) AND (T1.DeptID = “ISE”)

         AND ((T4.Name LIKE “*Computer*”) OR (T4.Name LIKE “*Database*”));




                                          31
             Using the IN Clause Operator
   The IN clause is often used for nested or sub queries
     – Specifies the inner query to look in
   Query:
     – “List the contact information for all the distinct industrial engineering
       students who have taken computer- or database-related courses.”
   SQL Statement:
    SELECT Name, Email, Address, Phone

    FROM   tblStudent

    WHERE (DeptID = ISE)

           AND (StudentID IN

                 (SELECT DISTINCT StudentID FROM tblTranscript WHERE SectionID IN

                   (SELECT SectionID FROM tblSection WHERE CourseID IN

                     (SELECT CourseID

                        FROM   tblCourse

                        WHERE (Name LIKE “*Computer*”) OR (Name LIKE “*Database*”)

               ) ) ) );

                                            32
            Using the Keyword DISTINCT
   We can obtain distinct records using the keyword DISTINCT in the
    SELECT clause
     – Prevents duplicate records in output recordset


   SQL Statement:
    SELECT Name, Email, Address, Phone

    FROM   tblStudent

    WHERE (DeptID = ISE)

           AND (StudentID IN

                 (SELECT DISTINCT StudentID FROM tblTranscript WHERE SectionID IN

                   (SELECT SectionID FROM tblSection WHERE CourseID IN

                     (SELECT CourseID

                        FROM   tblCourse

                        WHERE (Name LIKE “*Computer*”) OR (Name LIKE “*Database*”)

               ) ) ) );


                                            33
       Sorting: The ORDER BY Clause
   We can use the ORDER BY clause to sort the output records based
    on one or more attributes in ascending (ASC) or descending (DESC)
    order

   Syntax:
    SELECT    [Field Name (s)]

    FROM      [Table Name (s)]

    WHERE     [Criteria]

    ORDER BY [Field name (s)] <ASC, DESC>;




                                   34
    Sorting: The ORDER BY Clause - Example
   Query:
     – “Select the contact information and department name of the faculty
       members whose salary is between $65,000 and $75,000. List the output,
       first in ascending order of their joining date and then in descending order
       of their salary.”


   SQL Statement:
SELECT        Fac.Name, Email, Address, Phone, Dept.Name

FROM          tblFaculty AS Fac, tblDepartment AS Dept

WHERE         (Fac.DeptID = Dept.DeptID) AND (Salary BETWEEN 65000 AND 75000)

ORDER BY      JoiningDate ASC, Salary DESC;




                                         35
      Grouping: The GROUP BY Clause
   A GROUP BY clause combines similar records and treats them as a
    single unit or group
    – For use with aggregate functions:
         MIN, MAX: Provides the minimum or maximum value
         SUM, AVG: Calculates the sum or average value
         COUNT: Finds the count of records
    – Aggregate function syntax:
        [Function Name] [Field name] AS <Rename>


   Syntax:
     SELECT     [Field Name (s)]

     FROM       [Table Name (s)]

     WHERE      [Criteria]

     GROUP BY [Field name (s)];


                                      36
Grouping: The GROUP BY Clause - Example
   Query:
    – “Count the number of students in each department in the department
      table.”


   SQL Statement:
    SELECT    Dept.Name, COUNT (Std.StudentID) AS TotalStudents

    FROM      tblStudent AS Std, tblDepartment AS Dept

    WHERE     Std.DeptID = Dept.DeptID

    GROUP BY Dept.Name;




                                     37
              Using the HAVING Clause
   The HAVING clause is always used with the GROUP BY clause to
    specify the filtering criteria for group values
    – Similar to the WHERE clause


   Syntax:
     SELECT    [Field Name (s)]

     FROM      [Table Name (s)]

     WHERE     [Criteria]

     GROUP BY [Field name (s)]

     HAVING    [Criteria];




                                    38
    Using the HAVING Clause - Example
   Query:
     – “List the departments in the College of Engineering that have enrollments
       of 100 or more students.”


   SQL Statement:
    SELECT    Dept.Name, COUNT (Std.StudentID) AS TotalStudents

    FROM      tblStudent AS Std, tblDepartment AS Dept

    WHERE     (Std.DeptID = Dept.DeptID) AND (Dept.CollegeID = “COE”)

    GROUP BY Dept.Name

    HAVING    (COUNT (Std.StudentID)) > 100;




                                        39
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  40
               The INSERT Statement
   INSERT statements are DML statements that insert one or more
    records into database tables

   Syntax:

     INSERT INTO [Table Name]

                 ([Column1, Column2, Column3 ...])

     VALUES      ([Value1, Value2, Value3 ...]);




                                  41
      The INSERT Statement - Example
   Query:
    – “Insert information about the new department, “Digital Institute”, into the
      department table.”


   SQL Statement:

INSERT INTO tblDepartment

              (DeptID, Name, Address, Phone, CollegeID)

VALUES        (“DGI”, “Digital Institute”, “303FAB UFL 32611”, 3921044, “CFA”);




                                        42
    The INSERT Statement – More Examples
   Query 1:
    – “Insert information about the new department, setting the Phone to NULL.”

     INSERT INTO tblDepartment

                  (DeptID, Name, Phone, CollegeID)

     VALUES       (“BIE”, “Bio Informatics Eng”, NULL, “COE”);



   Query 2:
    – “Insert information about multiple new departments from tblDownload.”
INSERT INTO tblDepartment

                 (DeptID, Name, Address, Phone, CollegeID)

                 SELECT DeptID1, Name1, Address1, Phone1, CollegeID1

                 FROM tblDownload WHERE CollegeID1 = “COE”;


                                      43
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  44
               The DELETE Statement
   DELETE statements are DML statements that delete one or more
    records from database tables
    – Specify the deletion criteria using a WHERE clause


   Syntax:

    DELETE FROM [Table Name]

    WHERE         [Criteria];




                                      45
    The DELETE Statement – Example 1
   Query:
    – “Delete all last year (senior) industrial engineering students.”


   SQL Statement:

      DELETE    *

      FROM      tblStudent

      WHERE     Class = “Senior” AND DeptID = “ISE”;




                                         46
    The DELETE Statement – Example 2
   Query:
    – “Delete all last year (senior) industrial engineering students.”
         Delete records in conjunction with a sub query


   SQL Statement:
     DELETE    *

     FROM      tblStudent

     WHERE     StudentID IN

               (SELECT StudentID FROM tblStudent

                WHERE Class = “Senior” AND DeptID = “ISE”);




                                          47
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  48
              The UPDATE Statement
   UPDATE statements are DML statements that update one or more
    rows of a table
    – Specify the update criteria using a WHERE clause


   Syntax:

    UPDATE       [Table Name]

    SET          [Column1 = Value1, Column2 = Value2 ...]

    WHERE        [Criteria];




                                     49
    The UPDATE Statement – Example 1
   Query:
    – “Raise the salary field in the faculty table by 5% for all the faculty members
      who joined the university before 1995 and who earn less than $70,000.”


   SQL Statement:

    UPDATE         tblFaculty

    SET            Salary = Salary * 1.05

    WHERE          JoiningDate < #1/1/1995# AND Salary < 70000;




                                        50
    The UPDATE Statement – Example 2
   Query:
    – “Raise the salary field in the faculty table by 5% for all the faculty members
      who joined the university before 1995 and who earn less than $70,000.”
           Update records in conjunction with a sub query


   SQL Statement:

    UPDATE tblFaculty

    SET       Salary = Salary * 1.05

    WHERE     FacultyID IN

              (SELECT FacultyID FROM tblFaculty

              WHERE JoiningDate < #1/1/1995# AND Salary < 70000);




                                           51
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  52
                                  Summary
   We divide SQL statements into four categories:
    –   Data Definition Language (DDL) statements
    –   Data Manipulation Language (DML) statements
    –   Data Control Language (DCL) statements
    –   Database Stored Procedure Language (DSPL) statements


   The SELECT statement features two clauses:
    – SELECT clause
          Specify the columns of query output
    – FROM clause
          List the tables involved in the query design to select columns from




                                            53
                         Summary (cont.)
   We can use the ORDER BY clause to sort the output records based on
    one or more attributes in ascending (ASC) or descending (DESC)
    order

   We obtain distinct records using the keyword DISTINCT in the
    SELECT clause

   A SELECT clause can have those fields without aggregate functions
    grouped in the GROUP BY clause

   A HAVING clause can have those fields that either appear in the
    GROUP BY clause or are used with aggregate functions in the
    SELECT clause




                                    54
                          Summary (cont.)
   CREATE, DROP, and ALTER statements are used to create, delete,
    and update database objects such as tables and indexes

   INSERT statements insert one or multiple records into database tables

   DELETE statements delete one or more records from database tables
    – We specify the deletion criteria using a WHERE clause


   UPDATE statements update one or more rows of database tables
    – We specify the update criteria using a WHERE clause




                                      55
                           Overview
   9.1 Introduction
   9.2 Types of SQL Statements
   9.3 The Data Definition Language (DDL)
   9.4 The Data Manipulation Language (DML)
   9.5 The SELECT Statement
   9.6 The INSERT Statement
   9.7 The DELETE Statement
   9.8 The UPDATE Statement
   9.9 Summary
   9.10 In-Class Assignment




                                  56
                     In-Class Assignment
   Write SQL statements for the following queries based on the university
    database:
     – Query 1:
          “Create a new faculty table, tblFaculty. Assume appropriate fields and their data
           types, including a primary key field. The table must have a DeptID as a foreign
           key referring to the department table, tblDepartment. Also, create an index on
           the primary key of the faculty table.”


     – Query 2:
          “Display the number of faculty members working for each department in the
           department table.”


     – Query 3:
          “Delete all those faculty members who have been at the university for 15 years
           (use JoiningDate fields) and who are earning less than $50,000 (use Salary
           field).”




                                            57
                       Additional Links
   Refer SQL: The Complete Reference by James R Groff and Paul N.
    Weinberg for additional details on SQL.

   Also refer Access 2003 Bible by Cary N. Prague, Michael R. Irwin,
    and Jennifer Reardon.

   Also see: http://msdn.microsoft.com/ for useful tips on SQL and
    Access.




                                    58

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:2/13/2012
language:
pages:58