4%20-%20SQL

4%20-%20SQL - Basic Structure " Set Operations" Aggregate...

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

View Full Document Right Arrow Icon
Basic Structure Set Operations Aggregate Functions Null Values Nested Subqueries Derived Relations Views Modifcation o± the Database Joined Relations Data Defnition Language
Background image of page 1

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

View Full DocumentRight Arrow Icon
branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-city) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number)
Background image of page 2
SQL is based on set and relational operations with certain modifcations 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.
Background image of page 3

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

View Full DocumentRight Arrow Icon
The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. 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) An asterisk in the select clause denotes “all attributes” select * from loan NOTE : SQL does not permit the ʻ - ʼ character in names, so you would use, for example, branch_name instead of branch-name in a real implementation. We use ʻ - ʼ since it looks nicer! NOTE : SQL names are case insensitive, meaning you can use upper case or lower case. You may wish to use upper case in places where we use bold font.
Background image of page 4
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 speci±es that duplicates not be removed. select all branch-name from loan
Background image of page 5

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

View Full DocumentRight Arrow Icon
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 6
The where clause corresponds to the selection predicate of the relational algebra. If consists of a predicate involving attributes of the relations that appear in the from clause. The Fnd all loan number for loans made a the Perryridge branch with loan amounts greater than $1200. select loan-number from loan where branch-name = ʻ Perryridge ʼ and amount > 1200 Comparison results can be combined using the logical connectives and, or, and not.
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 01/24/2011 for the course CS 585 at USC.

Page1 / 66

4%20-%20SQL - Basic Structure " Set Operations" Aggregate...

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