The Query Language SQL

Document Sample
The Query Language SQL Powered By Docstoc
					                 Views in SQL
• View or Virtual Tables in SQL: A view in SQL is a
  single table that is derived from other tables
  (could be base table or previously defined views)
o A view does not necessarily exist in physical form
  – virtual table
o Base tables — tuples are actually stored in the
  database
  Restricts possible update operations on views
       No limitation on querying a view
                Views in SQL
• Consider two relations: students (sid, name, login,
  age, gpa), enrolled (cid, grade, sid) .
• Suppose that we are often interested in finding
  names and login of students who got a grade B in
  some course with the cid of the course
  Create View B-students (name, login, course)
  As Select s.name, s.sid, e.id
  From Students s, Enrolled e
  Where s.sid = e.sid And e.grade =’B’
                  Views in SQL
• Conceptually whenever B-students is used in a
  query, the view definition is first evaluated to
  obtain the corresponding instance of B-students,
  and then the rest of the query is evaluated
  treating B-students like any other relation
  referred to in the query.
• Update on view
   – View mechanism is to tailor how users see the data
   – SQL-92 allows update to be specified only on views that
     are defined on a single base table using just selection and
     projection, with no use of aggregation operation —
     updateable views
              Views in SQL
• Why need to restrict view updates
  – SQL-92 view updates are most stringent than
    necessary
  – Student (sid, name, login, age, gpa)
  – Club (cname, jyear, mname)
  – A tuple in club denotes that the student called
    mname has been a member of the club cname
    since the date jyear.
                 Views in SQL
• Why need to restrict view updates
   – Suppose we are often interested in finding the name and
     login of students with a gpa greater than 3 who belong to
     at least one club along with the club name and the date
     they joined the club.


Create View ActiveStudents (name, login, club,
  since) As
Select s.name, s.login, c.name, cjyear
  From Students s, Clubs c
     Where s.name = c.mname And s.gpa >3
Views in SQL
                Querying views
• If the view did not include a key for the
  underlying table, several rows in the table could
  correspond to a single row in the view (in the
  following example, mname is used instead of sid)
• Now we would like to delete the row (Smith,
  smith@ee, Hiking, 1997) from ActiveStudents
   – We must change either
      • Students or
      • Clubs
      • (or both)
   – in such a way that evaluating the view definition on the
     modified instance does not produce the row (Smith,
     smith@ee, Hiking, 1997).
               Querying views
• Two ways
   – Either
      • deleting row (53688, Smith, smith@ee, 18, 3.2) from
        Students or
      • deleting (Hiking, 1997, Smith) from Clubs.
   – Neither is satisfactory.
   – Removing from Students also delete tuple <Smith,
     Smith@ee, Rowing, 1998> from the view ActiveStudents.
     Removing the clubs row also has the effect of deleting
     the row <Smith, smith@math, Hiking, 1997> from
     ActiveStudents.
• Only reasonable solution is to disallow such
  updates on views.
                 Querying views
•   View Implementation:
    – Efficiently implement a view
    – Two main approaches:
    – Query modification: Modify view query into a query on
      the underlying base tables.
       • Inefficient for views defined via complex queries
           – We need answer very fast /interactive response time
           – Consider the total number of students for each club
             and the query is:
       Select Club, count(*) Form ActiveStudents
        Group by Club
                 Querying views
Select R.Club, count(*)
From (Select s.name as Name, s.login as Login, c.name
  as Club, cjyear as Year
  From Students s, Clubs c
     Where s.name = c.mname And s.gpa >3) as R
  Group by R.club
   – View materialization:
      • At first, we evaluate the view definition and store the
        result (i.e., physically create a temporary view table when
        the view is first queried).
          – When a query is now posed on the view, the (unmodified) query
            is executed on the pre-computed result
         Querying views
• Advantage: Much faster than query modifications
   – Complex view needs not be evaluated when the query is
     computed
• Disadvantage: Maintain the consistency of the pre-
  computed view whenever the underlying tables are
  updated
   – Incremental update has been developed where it is
     determined what new tuples must be inserted, deleted
     or modified in a materialized view table when a change
     is applied to one of the defining base tables
      Changing the database
• How do we initialize the database? How do
  we update and modify the database state?
• SQL supports an update language for
  insertions, deletions and modifications of
  tuples.
  – INSERT INTO R(A1,…,An) VALUES (V1,…,Vn);
  – DELETE FROM R WHERE <condition>;
  – UPDATE R SET <new-value assignments> WHERE
    <condition>;
             Tuple insertion
• Recall our rock climbing database, with the
  following instance of Routes:
       RId RName   Grade Rating Height
       1 Last Tango II     12    100
       2 Garden Path I      2     60
       3 The Sluice I       8     60
       4 Picnic    III      3    400
• To insert a new tuple into Routes:
INSERT INTO Routes(RId, Rname, Grade, Rating, Height)
VALUES (5, ―Desperation‖, III,12,600);
         Tuple insertion, cont.
• Alternatively, we could omit the attributes
  since the order given matches the DDL for
  Routes:
       INSERT INTO Routes
       VALUES (5, ―Desperation‖, III,12,600);

      RId RName   Grade Rating Height
      1 Last Tango II     12    100
      2 Garden Path I      2     60
      3 The Sluice I       8     60
      4 Picnic    III      3    400
      5 Desperation III    12   600
              Set insertion
• Suppose we had the following relation and
  wanted to add all the routes with rating > 8:
        HardClimbs:
        Route        Rating FeetHigh
        SlimyClimb      9       200
        The Sluice      8        60
INSERT INTO HardClimbs(Route,Rating,FeetHigh)
    SELECT DISTINCT Rname, Grade, Rating, Height
    FROM Routes WHERE rating>8;
         Route         Rating FeetHigh
         SlimyClimb       9     200
         The Sluice       8      60
         Last Tango      12     100
                 Deletion
• Deletion is set-oriented: the only way to
  delete a single tuple is to specify its key.
• Suppose we wanted to get rid of all tuples
  in HardClimbs that are in Routes:
   DELETE FROM HardClimbs
   WHERE Route in (SELECT Name FROM Routes)
       HardClimbs:
       Route       Rating FeetHigh
       SlimyClimb     9     200
          Modifying tuples
• Non-key values of a relation can be
  changed using UPDATE.
• Suppose we want to increase the age of all
  experienced climbers by 1:
           UPDATE Climbers
           SET Age = Age+1
           WHERE Skill = ―EXP‖;

• NOTE: SQL uses an ―old-value‖ semantics.
  New values are calculated using the old
  state, not a partially modified state.
        Old-value semantics
• ―Give a $1000 raise to every employee who
  earns less than their manager.‖
          Emp   Manager   Salary
          1        3      20,000
          2        3      21,500
          3              21,000
• Old-value semantics: employees 1 and 3 are
  given a raise.
• Otherwise: employee 2 will get a raise if
  they are considered after employee 3
  receives a raise!
       Schema modification
• Requirements change over time, so it is
  useful to be able to add/delete columns,
  drop tables and drop views:
  – DROP TABLE Climbers;
  – DROP VIEW ExpClimbers;
  – ALTER TABLE Climbs ADD Weather
    CHAR(50);
  – ALTER TABLE Routes DROP Grade;
                Summary
• Views are useful for frequently executed
  queries and as a layer to shield applications
  from changes in the schema.
• SQL has an update language that allows set-
  oriented updates. Updates (insertions,
  deletions and modifications) change the
  database state.