sql - CSE 412/598 DATABASE MANAGEMENT COURSE NOTES 9. SQL...

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

View Full Document Right Arrow Icon
CSE 412/598 DATABASE MANAGEMENT COURSE NOTES 9. SQL Arizona State University
Background image of page 1

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

View Full DocumentRight Arrow Icon
CSE 412/598 SQL 2 SQL: STRUCTURED QUERY LANGUAGE SQL is the recognized industry-standard query language. Recall the formal query languages: Relational Algebra: procedural Relational Calculus: declarative Tuple Relational Calculus (TRC): variables range over tuples and attributes are referenced via dot notation (T.attribute) Domain Relational Calculus (DRC): variables range over domains/attributes rather than tuples and attributes are referenced by position SQL is declarative, based on tuple relational calculus!
Background image of page 2
CSE 412/598 SQL 3 SQL Basic Query Expressions Typical SQL query SELECT A 1 ,A 2 ,...,A n FROM r 1 ,r 2 ,...,r m WHERE P is equivalent to 1 2 n A ,A ,. ..,A P 1 2 m ( ( r x r x r )) π σ •••
Background image of page 3

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

View Full DocumentRight Arrow Icon
CSE 412/598 SQL 4 SQL Fundamental Relational Algebra Operators Relational Algebra SQL r s (select * from r) union (select * from s) r - s (select * from r) except (select * from s) r × s select * from r, s σ P (r) select * from r where P π A (r) select A from r
Background image of page 4
CSE 412/598 SQL 5 SQL EXAMPLES Fundamental Relational Algebra Operators cse_majors eee_majors: select * from cse_majors union select * from eee_majors cse_majors - eee_majors: select * from cse_majors except select * from eee_majors cse_profs × cse_courses: select * from cse_profs, cse_courses σ CLASS='SR' (cse_majors): select * from cse_majors where class = 'SR' π ID,NAME (cse_majors): select id, name from cse_majors
Background image of page 5

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

View Full DocumentRight Arrow Icon
CSE 412/598 SQL 6 SQL EXAMPLES Additional Relational Algebra Operators cse_majors eee_majors: select * from cse_majors intersect select * from eee_majors cse_profs NAME=TNAME teaches: select * from cse_profs P, teaches T - - note table alias names where P.name = T.tname cse_profs cse_courses teaches: select P.name, P.office, C.crsid, C.crstitle from cse_profs P, cse_courses C, teaches T where P.name = T.tname and T.tcrsid = C.crsid Division will be covered later . ..
Background image of page 6
SQL 7 SQL EXPRESSIVE POWER The examples illustrate that SQL is at least as powerful as relational algebra and (safe) relational calculus. An SQL relation is not a set of tuples but a multiset (bag) of tuples because a relation can have two or more tuples that are identical in all their attribute values. To force the elimination of duplicates, use distinct in the select clause: select distinct A 1 ,A 2 ,...,A n from r Note that union and except return a set of tuples. To allow duplicates, use union all or except all . SQL provides an order by clause to display the query result in sorted order: select A 1 ,...,A n from r where P order by A i SQL provides inherent support for aggregation: avg, min, max, sum, count SQL has more expressive power than relational algebra (and calculus). Are SQL query expressions safe?
Background image of page 7

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

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

This note was uploaded on 05/12/2010 for the course CSE 41412 taught by Professor Hassan during the Fall '10 term at University of Arizona- Tucson.

Page1 / 39

sql - CSE 412/598 DATABASE MANAGEMENT COURSE NOTES 9. SQL...

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