1
Principles of Information and
Database Management
198:336
Week 5 – Feb 21
Matthew Stone
Today
Joins and other advanced features of SQL
– Recap
– Loose ends
– Joins
– Optimization and relational algebra
– Summary statistics in SQL
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community
Ask the community for help and clear up your study doubts
University Rankings
Discover the best universities in your country according to Docsity users
Free resources
Our save-the-student-ebooks!
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
SQL Language, Relational Model Example - Lecture Slides | 198 336, Study notes of Computer Science
Material Type: Notes; Class: 198 - PRIN INFO & DATA MGT; Subject: COMPUTER SCIENCE; University: Rutgers University; Term: Unknown 1989;
Typology: Study notes
Pre 2010
1 / 21
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download SQL Language, Relational Model Example - Lecture Slides | 198 336 and more Study notes Computer Science in PDF only on Docsity!
Principles of Information and
Database Management
Week 5 – Feb 21
Matthew Stone
Today
Joins and other advanced features of SQL
- Recap
- Loose ends
- Joins
- Optimization and relational algebra
- Summary statistics in SQL
Recap
Relational model
sid name login age gpa 53666 Jones jones@cs 18 3. 53688 Smith smith@eecs 18 3. 53650 Smith smith@math 19 3.
In the relational model, IM stores
set of relations or tables
TELL INFORMATION MANAGER ASK
DEFINE
Relational model example
Tell statement
insert into books values ('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99);
Relational model example
Tell statement
- Adds a row to the specified table in the information manager to include the specified entity or relationship.
Relational model example
Query example
SELECT author,title FROM books WHERE price > 30;
Relational model example
This returns a new table
author title Michael Morgan
Java 2 for Professional Developers
... …
Loose ends
Null values and primary keys
create table nullness ( id integer primary key, stuff text );
insert into nullness values (null, "you lose");
Loose ends
Updating an existing row
update table
set column=expression
where restriction
Update
changes columns in existing table rows
- Set clause indicates which columns to modify and the values they should be given.
- Where clause specifies which rows should be updated.
Example
Book gets “new edition”
Current row of books:
- isbn='0-672-31697-8',
- author='Michael Morgan',
- title='Java 2 for Professional Developers',
- price=34.
Example
Book goes out of print
delete from books where isbn='0-672-31697-8‘
Loose ends
Adding or deleting columns: alter table
Add a column, give all rows null value:
alter table books
add column publisher char(40)
Loose ends
Adding or deleting columns: alter table
Get rid of a column:
alter table books
drop column publisher
Loose ends
Adding or deleting columns: alter table
Lots of other ways to use this command.
Demo break
Any questions?
Joins - Motivation
How do you combine information from
multiple tables?
Example, from book domain:
who ordered what titles?
Recap
Not useful:
select C.name, O.isbn from customers C, order_items O
- performs cross product on tables
- no connections between rows
Recap
Need to establish relationships
select C.name, I.isbn from customers C, orders O, order_items I where C.customerid = O.customerid and O.ordernumber = I.orderid
Selection
Extract rows from a relation
extract all the rows from relation R that
satisfy condition
condition ( R )
Example
Get the rows from S where rating > 8
Corresponds to
select * from S where rating > 8
rating 8 ( S )
Projection
Extract columns from a relation
make a smaller table from R with just the
specified columns
columns ( R )
Example
Extract sailor names and ratings
corresponds to
select sname, rating from S
sname rating , ( S )
Equijoins
Join conditions contain only equalities
Duplicated fields are dropped
Natural join: special case
- all fields in common are equated
R Ä R. i S i. S
Equivalences
Cascading of selections
Commutativity of selections
(^) c d ( S ) (^) c ( (^) d ( S ))
(^) c ( (^) d ( S )) (^) d ( (^) c ( S ))
Equivalences
Cascading projections
(^) c ( (^) d ( S )) c ( S )