Docstoc

Standard Query Language_SQL_

Document Sample
Standard Query Language_SQL_ Powered By Docstoc
					Comp 231 Database Management Systems




      4. Structured Query Language




                Department of Computer Science and Engineering, HKUST
                                                                 Slide 1
                        Basic Structure

• SQL is based on set and relational operations with certain
  modifications and enhancements
• A typical SQL query has the form:
                         select A1, A2, …, An
                         from R1, R2, …, Rm
                         where P
        - Ai represent attributes
       - Ri represent relations
       - P is a predicate.
• This query is equivalent to the relational algebra expression:
                A1, A2, …, An(P(R1  R2  …  Rm))
• The result of an SQL query is a relation. I.e., SQL statements
  can be nested.

                              Department of Computer Science and Engineering, HKUST
                                                                               Slide 2
                            Projection
• The select clause corresponds to the projection operation of the
  relational algebra. It is used to list the attributes desired in the
  result of a query.
• Find the names of all branches in the loan relation
                select branch-name
                from loan
  Equivalent to:          branch-name(loan)
   or                    { t[branch-name] | t      loan }
• An asterisk in the select clause denotes “all attributes”
                select *
                from loan




                              Department of Computer Science and Engineering, HKUST
                                                                               Slide 3
                  Duplicate Removal

• SQL allows duplicates in relations as well as in query
  results. Use select distinct to force the elimination of
  duplicates.
  Find the names of all branches in the loan relation,
  and remove duplicates
                                                        force the DBMS to
              select distinct branch-name               remove duplicates
              from loan
• The keyword all specifies that duplicates not be
  removed.
              select all branch-name                    force the DBMS not
              from loan                                 to remove duplicates



                          Department of Computer Science and Engineering, HKUST
                                                                           Slide 4
   Arithmetic Operations on Retrieved Results

• The select clause can contain arithmetic expressions
  involving the operators,,, and , and operating on
  constants or attributes of tuples.
• The query:
      select branch-name, loan-number, amount * 100
      from loan
  would return a relation which is the same as the loan
  relations, except that the attribute amount is
  multiplied by 100




                         Department of Computer Science and Engineering, HKUST
                                                                          Slide 5
                     The where Clause

• The where clause specifies conditions that tuples in the relations
  in the from clause must satisfy.
• Find all loan numbers for loans made at the Perryridge branch
  with loan amounts greater than $1200.
        select loan-number
        from loan
        where branch-name=“Perryridge” and amount >1200
• SQL allows logical connectives and, or, and not. Arithmetic
  expressions can be used in the comparison operators.
• Note: attributes used in a query (both select and where parts)
  must be defined in the relations in the from clause.



                             Department of Computer Science and Engineering, HKUST
                                                                              Slide 6
              The where Clause (Cont.)

• SQL includes the between operator for convenience.
• Find the loan number of those loans with loan amounts between
  $90,000 and $100,000 (that is,  $90,000 and  $100,000)
               select loan-number
               from loan
               where amount between 90000 and
               100000




                           Department of Computer Science and Engineering, HKUST
                                                                            Slide 7
                   The from Clause
• The from clause corresponds to the Cartesian product
  operation of the relational algebra.
• Find the Cartesian product borrower  loan
                     select *
                     from borrower, loan
  It is rarely used without a where clause.
• Find the name and loan number of all customers
  having a loan at the Perryridge branch.
      select distinct customer-name, borrower.loan-number
      from borrower, loan
      where borrower.loan-number = loan.loan-number and
      branch-name = “Perryridge”

                         Department of Computer Science and Engineering, HKUST
                                                                          Slide 8
                 The Rename Operation

• Renaming relations and attributes using the as clause:
                       old-name as new-name
• Find the name and loan number of all customers having a loan
  at the Perryridge branch; replace the column name loan-number
  with the name loan-id.



select distinct customer-name, borrower.loan-number as loan-id
from borrower, loan
where borrower.loan-number = loan.loan-number and
         branch-name = “Perryridge”



                             Department of Computer Science and Engineering, HKUST
                                                                              Slide 9
                   Tuple Variables/Alias
• Tuple variables are defined in the from clause via the use of the
  “as” clause.
• Find the customer names and their loan numbers for all customers
  having a loan at some branch.

        select distinct customer-name, T.loan-number
        from borrower as T, loan as S
        where T.loan-number = S.loan-number

• Tuple variable/Alias can be used as short hand, but it is more than
  just a short hand (see next slide)



                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 10
                   Tuple Variables/Alias

• Find the names of all branches that have greater assets than
  some branch located in Brooklyn.

select distinct T.branch-name
from branch as T, branch as S
where T.assets > S.assets and S.branch-city=“Brooklyn”                  branches in
                                                                        Brooklyn

    branch                         branch                  S: a branch in
                                                           Brooklyn
    T


Does it returns branches within Brooklyn?
                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 11
                    String Operations

• Character attributes can be compared to a pattern:
   % matches any substring.
   _ matches any single character.
• Find the name of all customers whose street includes the
  substring „Main‟. (Eg Mainroad, Smallmain Road, AMainroad,…)
               select customer-name
               from customer
               where customer-street like “%Main%”
• How to match the name “Main%”: (Eg abcMain%,
  MainMain%,…)
               where customer-street like “Main\%”
  Use the escape “\” to specify % as a single character



                           Department of Computer Science and Engineering, HKUST
                                                                           Slide 12
            Ordering the Display of Tuples

• List in alphabetic order the names of all customers having a
  loan at Perryridge branch

        select distinct customer-name
        from borrower, loan
        where borrower.loan-number = loan.loan-number and
        branch-name = “Perryridge”
        order by customer-name

• order by customer-name desc, amount asc
  desc for descending order; asc for ascending order (default)
• SQL must perform a sort to fulfill an order by request. Since
  sorting a large number of tuples may be costly, it is desirable to
  sort only when necessary.
                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 13
                       Set Operations

• The set operation union, intersect, and except operate on
  relations and correspond to the relational algebra operations ,
   and .
• Each of the above operations automatically eliminates duplicate;
  to retain all duplicates use the corresponding multiset versions
  union all, intersect all and except all.
  Suppose a tuple occurs m times in r and n times in s, then, it
  occurs:
   – m + n times in r union all s
   – min(m,n) times in r intersect all s
   – max(0,m-n) times in r except all s




                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 14
                      Set operations

• Find all customers who have a loan, an account, or both:
                (select customer-name from depositor)
                union
                (select customer-name from borrower)
• Find all customers who have both a loan and an account.
                (select customer-name from depositor)
                intersect
                (select customer-name from borrower)
• Find all customers who have an account but no loan.
                (select customer-name from depositor)
                except
                (select customer-name from borrower)



                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 15
       SQL
Aggregate Functions




      Department of Computer Science and Engineering, HKUST
                                                      Slide 16
               Aggregate Functions

• Operates on a column of a relation, and return a
  value

             avg: average value
             min: minimum value
             max: maximum value
             sum: sum of values
             count: number of values




                       Department of Computer Science and Engineering, HKUST
                                                                       Slide 17
              Aggregate Functions(cont.)

• Find the average account balance at the Perryridge branch.

               select avg(balance)
               from account
               where branch-name=“Perryridge”

                                                balance
 account            select balance
                    from account
                    where branch-name                Avg()
                          =“Perryridge”                         120,000




                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 18
              Aggregate Functions(cont.)

• Find the numbers of tuples in the customer relation.
                select count(*)
                from customer
   – remember * stands for all attributes
   – compare to:
       select count(customer-city)
       from customer
• Find the number of depositors in the bank
                select count (distinct customer-name)
                from depositor
   – distinct is redundant if you know customer-name is a key




                           Department of Computer Science and Engineering, HKUST
                                                                           Slide 19
            Aggregate functions - Group by

• Find the number of accounts for each branch.
       select branch-name, count( distinct account-number)
       from account
       group by branch-name
• For each group of tuples with the same branch-name, apply
  aggregate function count and distinct to account-number




account table


                           Department of Computer Science and Engineering, HKUST
                                                                           Slide 20
                Aggregate functions - Group by
   • Relational Algebra:    G1,G2, … Gn  F1 A1, F2 A2,…, Fm Am(E)
                            branch-name  count ( distinct account-number )(account)


   • Attributes in select clause outside of aggregate functions must
     appear in group by list, why?
           select branch-name, balance, count( distinct account-number)
           from account
           group by branch-name
 correct
select … from account
group by branch-name, balance
                     OR
select branch-name, sum(balance), count(…)
from account group by branch-name

                                     Department of Computer Science and Engineering, HKUST
                                                                                     Slide 21
            Aggregate/Group by with Join

• Find the number of depositors for each branch.
   select branch-name, count( distinct customer-name)
   from depositor, account
   where depositor.account-number = account.account-number
   group by branch-name

• Perform Join then group by then count ( distinct () )
   depositor (customer-name, account-number)
   account (branch-name, account-number, balance)
   Join  (customer-name, account-number, branch-name, balance)

• Group by and aggregate functions apply to the Join result



                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 22
                         Example for Group by
                                        branch-name     cust-name
                                         Perryridge    John Wong
                                         Perryridge    Jacky Chan           count
select branch-name, customer-name          Uptown      John Wong
from depositor, account                    Uptown      Mary Kwan
where depositor.account-number           Downtown      John Wong
                                         Downtown        Pat Lee        branch-name count
= account.account-number                 Downtown      May Cheung        Perryridge   2
                                                                           Uptown     2
                                                        distinct         Downtown     3

  branch-name    cust-name              branch-name    cust-name
   Perryridge   John Wong                Perryridge   John Wong
   Downtown       Pat Lee                Perryridge   Jacky Chan
     Uptown     John Wong    group by    Perryridge   John Wong
   Perryridge   Jacky Chan                 Uptown     John Wong
     Uptown     Mary Kwan                  Uptown     Mary Kwan
   Downtown     John Wong                Downtown     John Wong
   Perryridge   John Wong                Downtown       Pat Lee
   Downtown     May Cheung               Downtown     May Cheung

                                        Department of Computer Science and Engineering, HKUST
                                                                                        Slide 23
   Aggregate Functions -Having Clause (new)

• Find the names of all branches where the average account
  balance is more than $700
                select branch-name, sum(balance)
                from account
                group by branch-name
                having avg (balance) >700
• predicates in the having clause are applied to each group after
  the formation of groups




                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 24
Modes of Interaction between Users and
                 DBMS




               Department of Computer Science and Engineering, HKUST
                                                               Slide 25
                   user
  Ad hoc                              query
                             result
                                                                             Stored
                                                        DBMS               procedures
                     query
Stored procedure                result

                                 SQL
                                             result
Embedded SQL          C/Java
                     program


                                                       Example

                               Department of Computer Science and Engineering, HKUST
                                                                               Slide 26
Embedded and Dynamic SQL




         Department of Computer Science and Engineering, HKUST
                                                         Slide 27
                     Embedded SQL

• The SQL standard defines embeddings of SQL in a variety of
  programming languages such as Pascal, PL/l, Fortran, C and
  Cobol.
• A language in which SQL queries are embedded is referred to as
  a host language, and the SQL structures permitted in the host
  language comprise embedded SQL.
• EXEC SQL statement is used to identify embedded SQL requests
  to the preprocessor

       EXEC SQL <embedded SQL statement > END EXEC




                           Department of Computer Science and Engineering, HKUST
                                                                           Slide 28
               Example Embedded SQL

From within a host language, find the names and account numbers
   of customers with more than the variable amount dollars in
   some account.
• Specify the query in SQL and declare a cursor for it

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




                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 29
           Why is cursor needed?


                        Embedded SQL


        host                                           DBMS
                     cursor
      program                          result

A program variable
can hold one value
at a time

 Cursor bridges the gap between value-oriented
 host program and set-oriented DBMS

                        Department of Computer Science and Engineering, HKUST
                                                                        Slide 30
                  Embedded SQL (cont.)

• When a cursor is defined, the embedded SQL is not executed
• The open statement causes the query to be evaluated
        EXEC SQL open c END-EXEC
• The fetch statement causes the values of one tuple in the query
  result to be placed in host language variables.
        EXEC SQL fetch c into :cn :an END-EXEC
  repeated calls to fetch get successive tuples in the query result;
  a variable in the SQL communication area indicates when end-
  of-file is reached.
• The close statement causes the database system to delete the
  temporary relation that holds the result of the query.
        EXEC SQL close c END-EXEC



                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 31
                      The Flow

                                   open c


                    host                                          DBMS
                               cursor
define c cursor   program                         result

    open c

    fetch c

 process data                                       PL/SQL JDBC

    close c
                        Department of Computer Science and Engineering, HKUST
                                                                        Slide 32
                            Dynamic SQL
• Allows programs to construct and submit SQL queries at run time.
• Example of the use of dynamic SQL within a C program.

    char* sqlcmd = “update account set balance = balance * 1.05 where
    account-number= ?”
    EXEC SQL prepare dynprog from :sqlcmd;
    char account[10] = “A-101”;
    EXEC SQL execute dynprog using :account;

•   The dynamic SQL program contains a ?, which is a place holder for a
    value that is provided when the SQL program is executed.




                                 Department of Computer Science and Engineering, HKUST
                                                                                 Slide 33
                       Other SQL features

• Fourth-generation languages- special language to assist
  application programmers in creating templates on the screen for
  a user interface, and in formatting data for report generation;
  available in most commercial database products. E.g., Delphi,
  Microsoft Access
• SQL sessions - provide the abstraction of a client and a server
    –   client connects to a SQL server, establishing a session
    –   executes a series of statements
    –   disconnects the session
    –   can commit or rollback the work carried out in the session
• An SQL environment contains several components, including a
  user identifier, and a schema, which identifies which of several
  schemas a session is using.

                                 Department of Computer Science and Engineering, HKUST
                                                                                 Slide 34
    Back to SQL …
Set and Nested Queries




       Department of Computer Science and Engineering, HKUST
                                                       Slide 35
                              Null values
•   It is possible for tuples to have a null value, denoted by null, for some of
    their attributes; null signifies an unknown value or that a value does not
    exist.
•   The result of any arithmetic expression involving null is null.
•   Roughly speaking, all comparisons involving null return false.
    More precisely,
     – Any comparison with null returns unknown (caution: Oracle treats it
         as false!)
     – (true or unknown) = true,         (false or unknown) = unknown
         (unknown or unknown) = unknown,
         (true and unknown) = unknown,             (false and unknown) = false
         (unknown and unknown) = unknown
     – Result of where clause predicate is treated as false if it evaluates to
         unknown
     – “P is unknown” evaluates to true if predicate P evaluates to unknown

                                  Department of Computer Science and Engineering, HKUST
                                                                                  Slide 36
                    Null Values (cont.)
• Find all loan numbers which appear in the loan relation with null
  values for amount.
                 select loan-number
                 from loan
                 where amount is null
• Total of all loan amounts
                 select sum(amount)
                 from loan
  Above statement ignores null amounts; result is null if there is
  no non-null amount.
• All aggregate operations except count(*) ignore tuples with null
  values on the aggregated attributes. (Caution: count(*) may not
  be equal to count(R) in Oracle!)


                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 37
                 SQL - Nested Subqueries

• Every SQL statement returns a relation/set in the result; remember
  a relation could be null or merely contain a single atomic value
• You can replace a value or set of values with a SQL statement (ie.,
  a subquery)

   select *                      select *
   from loan                     from loan
   where amount > 1200           where amount > select avg(amount)
                                                 from loan
• Illegal if the subquery returns the wrong type for the comparison


 Questions: What if the subquery becomes:
 - select amount from loan
 - select amount from loan where loan-number=12345
                              Department of Computer Science and Engineering, HKUST
                                                                              Slide 38
                            Set Membership

•   f in r   t  r ( t = f )




     (5 in        ) returns true              f is typically an attribute name
                                              and the set of values is typically
                                              returned from a subquery.
                                              e.g., ProjNo in (… …)
     (5 in        ) returns false



     (5 not in        ) returns true
                                   Department of Computer Science and Engineering, HKUST
                                                                                   Slide 39
                    Example Nested Query

• Find all customers who have both an account and a
  loan in the bank.

         select distinct customer-name
         from borrower
         where customer-name in (select customer-name
                                   from depositor)

      Check for each borrower
      if he/she is also a depositor
                                             Return the set of depositors

Can you formulate the query with “intersect”?

                                 Department of Computer Science and Engineering, HKUST
                                                                                 Slide 40
                  Example Query

• Find all customers who have a loan at the bank but
  do not have an account at the bank.

  select distinct customer-name
  from borrower
  where customer-name not in (select customer-name
                                from depositor)




                       Department of Computer Science and Engineering, HKUST
                                                                       Slide 41
                     Set Membership Example

     • Find all customers who have both an account and a loan at the
         Perryridge branch.
                                                  Returns borrowers with
                                                  loan at Perryridge
         select distinct customer-name
         from borrower, loan
         where borrower.loan-number = loan.loan-number and
              branch-name=“Perryridge” and
              (branch-name, customer-name) in
                        (select branch-name, customer-name
Important link:         from depositor, account
(Perryridge, D Lee) where depositor.account-number =
                                        account.account-number)
                                           Returns depositor information

                                  Department of Computer Science and Engineering, HKUST
                                                                                  Slide 42
                  Set Comparison

• Find all branches that have greater assets than some
  branches located in Brooklyn.

             select distinct T.branch-name
             from branch as T, branch as S
             where T.assets > S.assets and
                    S.branch-city = “Brooklyn”




                       Department of Computer Science and Engineering, HKUST
                                                                       Slide 43
                   The Some Clause

• F <comp> some r   t ( t  r [ F <comp> t ] ) where <comp>
  can be : <, , >, , =, 

  (5 < some      ) returns true
                                   (read: 5 < some tuple in the relation)




• (5 < some      ) returns false




                          Department of Computer Science and Engineering, HKUST
                                                                          Slide 44
             The Some Clause (cont.)


(5 = some         ) = true

(5  some         ) = true (since 0  5)

• (= some)  in

• However, ( some)  not in



                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 45
                       Example Query

• Find all branches that have greater assets than some
  branch located in Brooklyn.

                select branch-name
                from branch                         branches in
                where assets > some                 Brooklyn
                       (select assets
                       from branch
                       where branch-city = “Brooklyn”)

        Assets of all branches in Brooklyn

  More efficient: … assets > (select min(assets) from … … )

                              Department of Computer Science and Engineering, HKUST
                                                                              Slide 46
                       The All Clause

• F <comp> all r   t ( t  r  [ F <comp> t ] )

  (5 < all      ) = false



  (5 < all      ) = true




                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 47
                 The All Clause (cont.)


(5 = all      ) = false

(5  all      ) = true (since 5  4 and 5  6)


• ( all)  not in

• However, (= all)  in

                          Department of Computer Science and Engineering, HKUST
                                                                          Slide 48
                       Example Query

• Find the names of all branches that have greater
  assets than all branches located in Brooklyn.

               select branch-name
               from branch
               where assets > all
                      (select assets
                      from branch
                      where branch-city=“Brooklyn”)

      Assets of all branches in Brooklyn


                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 49
                Test for Empty Relations

• exists returns true if the argument subquery is
  nonempty.

• exists r  r  
• not exists r  r = 
  select … from …
  where not exist ( select * from branch where assets < 0 ) and … …


  select … from …
  where exist ( select * from branch where assets > 1000000 ) and … …



                              Department of Computer Science and Engineering, HKUST
                                                                              Slide 50
                            Example Query

•   Find all customers with an account at all branches located in Brooklyn.

           select distinct S.customer-name             Branches in Brooklyn
           from depositor as S                         where customer S
           where not exist (                           doesn’t have an account
                     (select branch-name
For each             from branch
customer S,          where branch-city=“Brooklyn”)
check ...            except
                                                        X – Y =   X 
                     (select R.branch-name
                     from depositor as T, account as R
 Branches where      where T.account-number = R.account-number and
 customer S                    S.customer-name = T.customer-name) )
 has an account


                                   Department of Computer Science and Engineering, HKUST
                                                                                   Slide 51
                   Test for Absence of Duplicate Tuples

    •   unique tests whether a subquery has any duplicate tuples in its result.
    •   Find all customers who have only one account at the Perryridge branch.

        select T.customer-name           For each depositor T, check ...
        from depositor as T
        where unique (                                         Find depositors with
              select R.customer-name                           same name as T
              from account, depositor as R
              where T.customer-name = R.customer-name and
              R.account-number = account.account-number and
              account.branch-name = “Perryridge”)


        Customers at Perryridge with same name as T
This is an example of defining two aliases for the depositor table because given a depositor
we need to find out whether that depositor has other accounts or not. Question: can T
                                         Department of in the subquery?
directly join with account on account.account-numberComputer Science and Engineering, HKUST
                                                                                     Slide 52
                               Example Query

   • Find all customers with at least 2 accounts at the Perryridge branch.

      select T.customer-name
      from depositor as T
      where not unique(
           select R.customer-name
           from account, depositor as R
           where T.customer-name = R.customer-name and
           R.account-number = account.account-number and
           account.branch-name = “Perryridge”)


      Similar to previous query except the “not”
      Question: what about “select distinct T.customer-name …”
Depositor (customer-name, account-number)
                                     Department of Computer Science and Engineering, HKUST
                                                                                     Slide 53
                      Derived Relations

• Find the average account balance of those branches where the
  average account balance is greater than $1200.
                                                          Return avg balance
  select branch-name, avg-balance                         of each branch
  from (select branch-name, avg(balance)
       from account
       group by branch-name)
       as result (branch-name, avg-balance)
  where avg-balance >1200

      Assuming result() is already created, equivalent to:
      select branch-name, avg-balance
      from result
      where avg-balance > 1200

                               Department of Computer Science and Engineering, HKUST
                                                                               Slide 54
                            Views

• Provide a mechanism to hide certain data from the view of
  certain users. To create a view we use the command:

               create view view-name as <query expression>
  where:
   – <query expression> is any legal SQL query
   – the name of the view is represented by view-name




                           Department of Computer Science and Engineering, HKUST
                                                                           Slide 55
                   Example Queries

• A view consisting of branches and their customers
  create view all-customer as
       (select branch-name, customer-name
       from depositor, account
       where depositor.account-number = account.name-number)
  union
       (select branch-name, customer-name
       from borrower, loan
       where borrower.loan-number = loan.loan-number)
• Find all customers of the Perryridge branch
                select customer-name
                from all-customer
                where branch-name = “Perryridge”


                         Department of Computer Science and Engineering, HKUST
                                                                         Slide 56
Data Manipulation Language (DML)




             Department of Computer Science and Engineering, HKUST
                                                             Slide 57
          Modification of the Database - Deletion

• Delete all account records at the Perryridge branch

                delete from account
                where branch-name = “Perryridge”

• Conceptually, delete is done in two steps:
   – find the tuples you want to delete:

      select * from account
      where branch-name = “Perryridge”

    – delete the tuples you found.


                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 58
                Modification of the Database - Deletion

    •   Delete all accounts at every branch located in Needham.

        delete from depositor
        where account-number in (select account-number
                      from branch, account
                      where branch-city = “Needham”
                      and branch.branch-name = account.branch-name)
        delete from account
        where branch-name in (select branch-name from branch
                              where branch-city = “Needham”)

    •   The first delete is needed to enforce “every depositor must have at
        least one account”. Note that the two deletes can‟t be reordered.
                  account#1      Needham
depositor
                 account#2          Perryridge

                  account#3         Department of Computer Science and Engineering, HKUST
                                Needham
                                                                                    Slide 59
                      Example Query

• Delete the records of all accounts with balances below the
  average at the bank
                delete from account
                where balance < (select avg (balance)
                                    from account)
   – Problem: as we delete tuples from deposit, the average
     balance changes
   – solution used in SQL:
       • First, compute and save the average balance of the bank
         in a variable
       • Next, delete all accounts with balance less than the
         average


                           Department of Computer Science and Engineering, HKUST
                                                                           Slide 60
         Modification of the database - Insertion

• Add a new tuple to account

  insert into account values (“Perryridge”, A-9732, 1200)

  To reorder attributes, specify attribute names explicitly:

  insert into account (branch-name, balance, account-number)
       values (“Perryridge”, 1200, A-9732)

• Add a new tuple to account with balance set to null

  insert into account values ( “Perryridge”, “A-777”, null)



                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 61
          Modification of the Database - Insertion

• Create a $200 savings account for all loan customers of the
  Perryridge branch. Let the loan number serve as the account
  number for the new savings account.             Perryridge L-1234                200
                                                           Perryridge    L-4321    200
   insert into account
        select branch-name, loan-number, 200
        from loan
        where branch-name=“Perryridge”

   insert into depositor
        select customer-name, loan-number
        from loan, borrower
        where branch-name=“Perryridge”
        and loan.account-number = borrower.account-number

                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 62
          Modification of the database - Updates

• Increase all accounts with balance over $10,000 by 6%, all
  other accounts receive 5%.
   – Write two update statements:               select *
                update account                  from account
                                                where balance >10000
                set balance = balance *1.06
                where balance >10000

                update account
                set balance = balance *1.05
                where balance  10000
    – the order is important
    – can be done better using the case statement



                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 63
       Case Statement for Conditional Updates

• Same query as before: Increase all accounts with
  balances over $10,000 by 6%, all other accounts
  receive 5%.

     update account
     set balance = case
                        when balance <= 10000
                         then balance *1.05
                        else balance * 1.06
                      end


                       Department of Computer Science and Engineering, HKUST
                                                                       Slide 64
                     Update of a View

• Create a view of all loan data in the loan relation, hiding the
  amount attribute
                create view branch-loan as
                         select branch-name, loan-number
                         from loan
• Add a new tuple to branch-loan
                insert into branch-loan
                         values(“Perryridge”, “L-307”)
  This insertion must be represented by the insertion of the tuple
                (“Perryridge”, “L-307”,null)
  into the loan relation.
• Updates on more complex views are difficult or impossible to
  translate, and hence are disallowed.


                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 65
                          View Update Problems
      Why can‟t views be updated?
      create view works-on-view
       as select employee-name, project-name, hours
          from employee, project, works-on
          where employee.id=works-on.eid and project.id=works-on.pid
       works-on-view

                                                        on view: database  AI
                                                                                   database  AI
                                                                                   (incorrect!)
                                                  p123  p789 (correct)
employee                        works-on
                                                                  project




                                    e567   p123    15
                                           Department of Computer Science and Engineering, HKUST
                                                                                           Slide 66
                 Correct Update Procedure

                    Replace old pid with           Find the tuple relating “Dik Lee”
                    the pid of “AI”                to the “database” project

update   works-on
set      pid = ( select id from project where name=`AI‟ )
where    eid = ( select id from employee where name = `Dik Lee‟ )
and      pid = ( select id from project where name = „database‟ )


 • The correct update procedure is application dependent and
   must be written by the database application programmer

 • A trigger can invoke the procedure automatically upon updates



                                Department of Computer Science and Engineering, HKUST
                                                                                Slide 67
               Rules for Updatable Views

Rules for legal view updates:

• A view built on a single defining table is updatable if the view
  contains the primary key of the defining table
• Views defined on multiple tables are in general not updatable
• Views involving aggregate functions on the defining table are
  not updatable




                                Department of Computer Science and Engineering, HKUST
                                                                                Slide 68
Outer Join




 Department of Computer Science and Engineering, HKUST
                                                 Slide 69
                      Joined Relations

• These additional operations are typically used as subquery
  expressions in the from clause.
• Join condition - defines which tuples in the two relations match,
  and what attributes are present in the result of the join.
   – Natural join natural
   – join condition specified by on <predicate>
   – join attributes specified by using (A1,A2,… ,An)
• Join type - defines how tuples in each relation that do not match
  any tuple in the other relation(base on the join condition) are
  treated.
   – Inner join, left outer join, right outer join, full outer join



                            Department of Computer Science and Engineering, HKUST
                                                                            Slide 70
         Joined Relations - Datasets for example

• Relation loan




• Relation borrower




                        Department of Computer Science and Engineering, HKUST
                                                                        Slide 71
                 Joined Relations - examples

• select * from loan inner join borrower
                   on loan.loan-number = borrower.loan-number




• select * from loan left outer join borrower
                  on loan.loan-number=borrower.loan-number




                                  Department of Computer Science and Engineering, HKUST
                                                                                  Slide 72
             Joined Relations - examples

• select * from loan natural inner join borrower




• select * from loan natural right outer join borrower




                              Department of Computer Science and Engineering, HKUST
                                                                              Slide 73
                Joined Relations - example

• select * from loan full outer join borrower using (loan-number)




• Find all customers who have either an account or a loan (but not both)
        select customer-name
        from (depositor natural full outer join borrower)
        where account-number is null
            or loan-number is null


                                Department of Computer Science and Engineering, HKUST
                                                                                Slide 74
Summary




 Department of Computer Science and Engineering, HKUST
                                                 Slide 75
SQL Data Definition Language




          Department of Computer Science and Engineering, HKUST
                                                          Slide 76
            Data Definition Language(DDL)

Allows the specification of not only a set of relations but
also information about each relation, including:
   –   The schema for each relation.
   –   The domain of values associated with each attribute.
   –   Integrity constraints.
   –   The set of indices to be maintained for each relation.
   –   Security and authorization information for each relation.
   –   The physical storage structure of each relation on disk.




                             Department of Computer Science and Engineering, HKUST
                                                                             Slide 77
               Domain Types in SQL

• char(n) Fixed length character string, with user-
  specified length n.
• varchar(n) Variable length character string, with
  user-specified maximum length n.
• int integer (a finite subset of the integers that is
  machine-dependent).
• smallint Small integer (a machine-dependent subset
  of the integer domain type).
• Numeric(p,d) Fixed point number, with user-specified
  precision of p digits, with d digits to the right of
  decimal point.
                       Department of Computer Science and Engineering, HKUST
                                                                       Slide 78
              Domain types in SQL (cont.)

• real, double precision Floating point and double-precision
  floating point numbers, with machine-dependent precision.
• float(n) Floating point number, with user-specified precision of
  at least n digits.
• date Dates, containing a (4 digits) year, month and date.
• time Time of day, in hours, minutes and seconds.



 • Null values are allowed in all the domain types. Declaring an
   attribute to be not null prohibits null values for that attribute.
 • create domain in SQL-92 creates user-defined domain types
   create domain person-name char(20) not null


                              Department of Computer Science and Engineering, HKUST
                                                                              Slide 79