SQL - Section 4.8 - 4.15

							SQL - Section 4.8 - 4.15




       Presented by

        Sherly Rao
                  Overview


Short introduction to SQL
Complex Queries
Modification of the database
Joined Relations
Data-Definition Language
  Domain Types in SQL
  Schema Definition in SQL
Embedded SQL
Dynamic SQL
  JDBC
  ODBC
Other SQL Features
            SQL a Short Introduction


   SQL stands for Structured Query Language
    Queries are used to extract information from the
    database.
    An SQL expression/block consists of three main
    clauses:
      select (projection operation) : Lists the attributes
      desired in the result of a query
      from (Cartesian-product operation) : List the relation to
      be scanned in the evaluation of the expression.
      where (selection predicate) : Consists of predicate
      involving attributes of the relations that appear in the
      from clause
Example: select name from students where gpa = '4.0'

             attribute     relation          condition
                         Complex Queries
    What are complex queries?

     Queries that are hard to write as a single SQL block.

    Way to compose multiple SQL blocks:

          Derived Relations:

             Subquery expression to be used in the from clause.
             The result relation must be given a name and the attributes can
             be renamed.

Example: To find the average account balance of those branches where the
  avg acct balance is > 1200

   select branch-name, avg-balance from (select branch-name, avg(balance)
    from account group by branch-name) as branch-avg (branch-name, avg-balance)
    where avg-balance > 1200

                                             result relation         renamed attribute
( Note: balance is an attribute in the relation. Since we're calculating the average
    balance it's more meaningful to rename balance to avg-balance )
         Complex Queries cont'd


With clause:

    Makes the query logic clearer by providing ways to
    define temporary views

    view, like procedures ( in structure programming ), can
    be broken up into smaller views for clarity and
    reusability.

    Permits view definition to be used in multiple places
    within a query.

    Example: Please refer to Pg. 157
       Modification of the Database

Add, Remove, and Change information.

     Insertion ( add ) : Insert data ( tuple or set of tuples ) into a
     relation
    Ex: insert into Students values ( 'Jane Doe', '4.0', 'honor');
    inserts the student Jane Doe into Students whose GPA 4.0 and who
is an honor student.

     Deletion ( remove ) : Deletes the entire tuple from a relation.
   Ex: delete from Students where name="Jane Doe";

     Update ( change ): Changes a value in a tuple without
     changing all values in the tuple.
  Ex:
   1. update Students set gpa = 3.5 where name = 'Jane Doe'
   2. update Students set dean_list = case
      when gpa < 4.0 then 'regular' else 'honor'
      end
                 Transactions


Consists of sequence of query and/or update statements.

Begins implicitly when an SQL statement is executed.

Terminates when Commit work OR Rollback work
statements are used:

    Commit work: Similar to saving changes to a
    document that is being edited
    Rollback work: Similar to quitting the edit session
    without saving.

Once commit work is executed, its effect can no longer be
undone by rollback work.
                  Joined Relations
SQL provides mechanisms for joining relations, including condition
joins and natural joins.

Ways to join relations:

     Inner join - Combines two relations which contain a common field
     and eliminating tuples that don't match.

     left outer join - Combines two relations which contain a common
     field that results in tuples in left relation to be preserved and the
     unmatched tuples in the right relation filled with null values.

     right outer join - Combines two relations which contain a common
     field that results in tuples in right relation to be preserved and the
     unmatched tuples in the left relation filled with null values.

     natural join – Similar to inner join, however, the common attributes
     of the relations will appear only once.
               Examples of joined relations:

   Suppose we have two relations loan and borrower:
   loan-num branch-name       amount            cust-name      loan-num
    L-170     Downtown         3000             Jones           L-170
    L-230       Redwood         4000            Smith           L-230
     `
    L-260       PerryRidge      1700            Hayes              L-155
                Loan                                    Borrower
Inner join relation:
loan inner join borrower on loan.loan-num = borrower.loan-num

    loan-num      branch-name          amount     cust-name           loan-num
     L-170         Downtown        3000           Jones               L-170
     L-230         Redwood             4000       Smith               L-230
Natural inner join: loan natural inner join borrower

    loan-num     branch-name       amount         cust-name
     L-170        Downtown         3000           Jones
     L-230        Redwood          4000           Smith
                   Joined relations cont’d

   left outer join:
Syntax: loan left outer join borrower on loan.loan-num =
   borrower.loan-num
  loan-num    branch-name    amount    cust-name    loan-num
   L-170      Downtown       3000      Jones        L-170
  L-230       Redwood        4000      Smith        L-230
  L-260        Perryridge    1700      null         null
   Right outer join:
Syntax: loan right outer join borrower on loan.loan-num =
   borrower.loan-num

   loan-num    branch-name    amount    cust-name    loan-num
    L-170      Downtown       3000      Jones        L-170
   L-230       Redwood        4000      Smith        L-230
   null         null          null      Hayes        L-155
            Data-Definition Language


Specifies set of relations and information about each relation in
a database.

Includes:

    The schema for each relation
    The domain of values associated with each attribute
    The integrity constraints
    The set of indices to be maintained for each relation
    The security and authorization information for each relation
    The physical storage structure of each relation on disk
              Domain Types in SQL
Built-in domain types
Includes:

  char(n): A fixed length character string with user-specified length n.
  varchar(n) : A variable-length character string with user-specified
  length n.
  int: An integer (a finite subset of the integers that is machine
  dependent)
  smallint: A small integer (a machine-dependent subset of the integer
  domain type)
  numeric(p,d) : A fixed-point number with user-specified precision.
  The number consists of p digits(plus a sign), and d of the p digits are
  to the right of the decimal point. eg: numeric(3,1) allows 44.5 to be
  stored exactly
  real, double precision: Floating-point and double-precision floating-
  point numbers with machine-dependent precision
  float(n): A floating-point number, with precision of atleast n digits
  date: A calendar date containing a (4-digit) year, month, and day of
  the month.
  time: The time of day, in hours, minutes, and seconds.
  timestamp: A combination of date and time
             Schema Definition in SQL
 Relations are defined by using the create table command:

 Syntax: create table r(A1D1,A2D2,.............,AnDn,
<integrity-constraint1>, ..................................,<integrity-constraintk>
where r is the name of the relation, each Ai is the name of an attribute in
 the schema relation r, and Di is the domain type of values in the domain
 of attribute Ai.

 The Integrity constraints ( constraint that can uniquely identify a tuple )
 include:

    Primary key(Aj1,Aj2,...,Ajm) :
       Attributes Aj1, Aj2, ...,Ajm form the primary key for the relation.
       Primary key attributes are required to be non-null and unique.

    check(P):
      Specifies a predicate P that must be satisfied by every tuple in the
      relation.
      Used to ensure that attribute values satisfy specified conditions.
   Schema Definition in SQL cont'd


Example:

create table account (account-number char(10),
                       branch-name char(15),
                       balance integer,
primary key (account-number), // account-number has to
               // be unique
check (balance >= 0))

 Note: If the balance is < 0 then SQL will flag an error and
the table creation will be aborted.
- account-number, branch-name, balance are the attributes
in account relation.
     Schema Definition in SQL cont'd
insert command - to load the data in to the relation, if the newly loaded
table is empty. ( i.e., inserts a table row )

drop table command - to remove a relation from the database.
         Syntax: drop table r

Note: drop table r is not the same as delete r
 drop table r - deletes all the information about the dropped relation
     from the database
 delete r - retains the relation, but deletes all tuples in it.

alter table command - to add or drop attributes to an existing relation r.
( i.e., inserts/deletes a table column )
          Syntax: alter table r add A D

where r is the name of an existing relation, A is the attribute and D is the
domain to be added.

Syntax: alter table r drop A
where r is the name of an existing relation, A is the attribute to be
dropped
               Embedded SQL


What is an Embedded SQL?

   Using SQL syntax inside other programming languages
( such as C, JAVA, COBOL etc. - Host Languages ).

Why Embedded SQL is required?

    Not all queries can be expressed in SQL but can be
    programmed in C, Java, Pascal, or Cobol.

     To perform actions such as printing a report,
    interacting with a user, or sending the result to a GUI
    that cannot be done from within SQL.
            Embedded SQL cont'd
How to use Embedded SQL?

  Program must be processed by a preprocessor prior to compilation.
    Preprocessor replaces embedded SQL requests with host
    language declarations and procedure calls that allows runtime
    execution of the database accesses
    Resulting program is compiled by the host-language compiler
  To identify embedded SQL requests to the preprocessor, we use the
  EXEC SQL statement:

 Syntax: EXEC SQL <embedded SQL statement > END-EXEC
      In Java: #SQL {<embedded SQL statement> };

  SQL INCLUDE - To identify the place where the preprocessor should
  insert the special variables used for communication between the
  program and the database system.

Example: in Pro* C
 /* Include the SQL communication Area. */
/* You can use #include or EXEC SQL INCLUDE. */
 #include <sqlca.h>
           Embedded SQL cont'd
Embedded SQL sample:

EXEC SQL
   declare c cursor for
     select customer-name, customer-city
     from depositor, customer, account
    where depositor.customer-name = customer.customer-
  name and account.account-number = depositor.account-
  number and account.balance > :amount
END-EXEC

declare cursor - To write a relational query
c - cursor for the query (To identify the query in the open
statement)
:amount - host-language variable
Note: To distinguish between SQL variables and host-language
variables, use : before the host-language variable.
   Embedded SQL Statements cont'd
 open statement - Evaluates the query, i.e, execute the query and
 saves the results within a temporary relation ( buffer ).

     Syntax : EXEC SQL open c END-EXEC

 fetch statement:
     - Places the values of one-tuple in the host-language variable
         i.e, to retrieve a tuple of the result.
    - Requires one host-language variable for each attribute of the
 result relation.
     - Single fetch request returns only one tuple.

  Syntax: EXEC SQL fetch c into :cn, :cc END-EXEC
  where cn is customer-name and cc is customer-city

 close statement - To tell the database system to delete the
 temporary relation that held the result of the query.

Syntax: EXEC SQL close c END-EXEC
                    Dynamic SQL

Allows programs to construct and submit SQL queries at run
time.

The widely used mechanism is to use an Application Program
Interface (API) to send SQL queries/updates to database system
and not make any changes in the programming language itself.

Two standards for connecting to an SQL database are:
  - Java Database Connectivity( JDBC) - API for Java
  - Open Database Connectivity(ODBC) - API for C language

All activities of the user or application are in the context of an
SQL session
  User/application:
    - Connects to an SQL server and establishes a connection
    - Executes a series of statements
    - Disconnects the session
                               JDBC
     Java programs can be used to connect to the database servers.
     Java Program that uses JDBC Interface:
public static void JDBCexample(String dbid, String userid, String passwd) {
 try {
    // Load the appropriate driver for the database
    Class.forName("oracle.jdbc.driver.OracleDriver");
    /* Open a connection to the database.
   Syntax: "protocol to be used to communicate with the database:
  machine name where the server runs: port number used for
     communication: schema on the server to be used", userid, password) */
    Connection conn = DriverManager.getConnection
     ("dbc:oracle:thin:@aura.bell-labs.com:2000:bankdb", userid, passwd);
    Statement stmt = conn.CreateStatement();
    // Modify the relation: Insert a tuple into the relation
    stmt.executeUpdate("insert into account values
   ('("insert into account values('A-1234','SJC'));
    // Retrieve information ( one tuple at a time ) from the database.
    ResultSet rset = stmt.executeQuery("select branch_name, avg(balance)
     from account");
   // close statement and the opened connection
   stmt.close();
   conn.close(); } catch { ..... } }
                         ODBC

Defines a way for an application program to communicate with
database server.

Defines an API that applications can use to :
 - Open a connection with a database
 - Send and update queries
 - Get results

Each database system supporting ODBC provides a library that
must be linked with the client program.

When the client program makes an ODBC API call:
  - The code in the library communicates with the server
  - Server carries out the requested action and fetch results

Example of C code using the ODBC API: Pg:177 Figure 4.9
              Other SQL features
Schemas, Catalogs, and Environments
    Early database systems had single namespace for all
    relations that resulted in name collision/conflicts.
     - To avoid name collision(two relations with same name)
          users had to coordinate
    Contemporary database uses three- level hierarchy for
    naming relations:
          Catalogs <-- Top level
          Schemas (contained within catalog)
          SQL objects and views (contained within schema)
    To uniquely identify a relation the three-part name must be
    used:
     Ex: Catalogs.bank-schema.account

     Note: Each user has a default catalog and schema and the
        combination is unique to a user
    SQL Environment contain Catalog, Schema, and user
    identifier
        Other SQL Features cont'd


Procedural Extensions and Stored Procedures

    SQL provides modules that contains multiple SQL
    procedures.

    Each procedure has a name, optional argument, and
    an SQL statement.

    Procedures stored in a database are called stored
    procedures.

    Stored procedures can be executed by using the call
    statement