ch4-2 - Chapter 4: SQL ! Basic Structure ! Set Operations !...

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

View Full Document Right Arrow Icon
©Silberschatz, Korth and Sudarshan 4.1 Database System Concepts Chapter 4: SQL Chapter 4: SQL ! Basic Structure ! Set Operations ! Aggregate Functions ! Null Values ! Nested Subqueries ! Derived Relations ! Views ! Modification of the Database ! Joined Relations ! Data Definition Language ! Embedded SQL, ODBC and JDBC ©Silberschatz, Korth and Sudarshan 4.2 Database System Concepts Schema Used in Examples Schema Used in Examples
Background image of page 1

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

View Full DocumentRight Arrow Icon
©Silberschatz, Korth and Sudarshan 4.3 Database System Concepts Basic Structure Basic Structure ! SQL is based on set and relational operations with certain modifications and enhancements ! A typical SQL query has the form: select A 1 , A 2 , . .., A n from r 1 , r 2 , . .., r m where P " A i s represent attributes " r i s represent relations " P is a predicate. ! This query is equivalent to the relational algebra expression. A 1 , A2, . .., An ( σ P (r 1 x r 2 x ... x r m )) ! The result of an SQL query is a relation. ©Silberschatz, Korth and Sudarshan 4.4 Database System Concepts The select Clause ! The select clause list the attributes desired in the result of a query " corresponds to the projection operation of the relational algebra ! E.g. find the names of all branches in the loan relation select branch-name from loan ! In the “pure” relational algebra syntax, the query would be: branch-name ( loan) ! NOTE : SQL does not permit the ‘-’ character in names, " Use, e.g., branch_name instead of branch-name in a real implementation. " We use ‘-’ since it looks nicer! ! NOTE : SQL names are case insensitive, i.e. you can use capital or small letters. " You may wish to use upper case where-ever we use bold font.
Background image of page 2
©Silberschatz, Korth and Sudarshan 4.5 Database System Concepts The select Clause (Cont.) The select Clause (Cont.) ! SQL allows duplicates in relations as well as in query results. ! To force the elimination of duplicates, insert the keyword distinct after select. ! Find the names of all branches in the loan relations, and remove duplicates select distinct branch-name from loan ! The keyword all specifies that duplicates not be removed. select all branch-name from loan ©Silberschatz, Korth and Sudarshan 4.6 Database System Concepts The select Clause (Cont.) The select Clause (Cont.) ! An asterisk in the select clause denotes “all attributes” select * from loan ! The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. ! The query: select loan-number, branch-name, amount 100 from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100.
Background image of page 3

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

View Full DocumentRight Arrow Icon
©Silberschatz, Korth and Sudarshan 4.7 Database System Concepts The where Clause The where Clause ! The where clause specifies conditions that the result must satisfy " corresponds to the selection predicate of the relational algebra.
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This document was uploaded on 01/10/2011.

Page1 / 49

ch4-2 - Chapter 4: SQL ! Basic Structure ! Set Operations !...

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

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