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.
Pages to are hidden for
"The Query Language SQL"Please download to view full document