{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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

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

View Full Document Right Arrow Icon
CSE 412/598 DATABASE MANAGEMENT COURSE NOTES 9. SQL Department of Computer Science & Engineering Arizona State University
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
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 Document Right 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 Document Right 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
CSE 412/598 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 Document Right Arrow Icon
CSE 412/598 SQL 8 SQL2 The original SQL standard was established in 1986 (and extended in 1989 with integrity enforcement). The standard was updated in 1992, called SQL2, to include joined tables and data definition.
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}