L11 - Modifying the database, grouping, filtering

L11 - Modifying the database, grouping, filtering - So...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
So far ( So far ( w.r.t w.r.t . the relational algebra) we . the relational algebra) we ve seen: ve seen: ± Six basic operators ² select: σ ² project: ² union: ² set difference: ² Cartesian product: x ² rename: ρ ± and four additional operators ² Set intersection ² Natural join ² Division ² Assignment ± and e xtended relational algebra operators ² Generalized Projection ² Aggregate Functions ² Outer Join ± Null Values and issues that must be handled along with how these are expressed and used in SQL
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Next: Modification of the Database Next: Modification of the Database ± The content of the database may be modified using the following operations: ² Deletion ² Insertion ² Updating ± All these operations are expressed using the assignment operator.
Background image of page 2
Deletion Deletion ± A delete request is expressed very much like a query, except instead of displaying tuples to the user, the selected tuples are removed from the database. ± Can delete only whole tuples; cannot delete values on only particular attributes ± A deletion is expressed in relational algebra by: r r E where r is a relation and E is a relational algebra query.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Deletion Examples (from text) Deletion Examples (from text) ± Delete all account records in the Perryridge branch. account account σ branch_name = “Perryridge” ( account ) delete from account where branch_name = “Perryridge”; ± Delete all loan records with amount in the range 0 to 50 loan loan σ amount 0 and amount 50 ( loan ) delete from loan where (amount > 0 and amount < 50);
Background image of page 4
Deletion Examples (from text) Deletion Examples (from text) ± Delete all accounts at branches located in Horseneck. r1 ←σ branch_city = “Horseneck” ( account branch ) r 2 ←∏ branch_name, account_number, balance (r1) r3 customer_name, account_number (r2 depositor) account account – r2 depositor depositor – r3 ± To do this in SQL, we need to learn about creating temporary tables and also about inserting values into tables in “batch” mode and deleting entries ± This gets the cart before the horse a little, but oh, well …
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
First create the temporary relations r1, r2, an dr3 First create the temporary relations r1, r2, an dr3 create temporary table r1 (branch_name varchar(15), account_number varchar(15), balance numeric(8,2), branch_city varchar(15), assets numeric (15,2)); create temporary table r2 (branch_name varchar(15) not null, account_number varchar(15) not null, balance numeric(8,2) not null); create temporary table r3 (customer_name varchar(15) not null, account_number varchar(15) not null); r1 ←σ branch_city = “Horseneck” ( account branch ) r 2 ←∏ branch_name, account_number, balance (r1) r3 customer_name, account_number (r2 depositor)
Background image of page 6
Then populate them with the proper data Then populate them with the proper data insert into r1 (branch_name,account_number,balance,branch_city,assets) select a.branch_name,account_number,balance,branch_city,assets from account a natural join branch where branch_city = 'Horseneck';
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 37

L11 - Modifying the database, grouping, filtering - So...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online